Printable View
I am trying check whether or not a department number I am passing from the calling environment is valid. The return value must be boolean. Something is wrong with this program here. Can anyone explaine it to me? Thanks!!
CREATE OR REPLACE FUNCTION valid_deptno
(v_deptno DEPT.DEPTNO%TYPE)
RETURN BOOLEAN
IS
v_valid DEPT.DEPTNO%TYPE;
BEGIN
SELECT deptno
INTO v_valid
FROM dept
WHERE deptno = v_deptno;
IF SQL%NOTFOUND THEN
RETURN FALSE;
ELSIF SQL%FOUND THEN
RETURN TRUE;
END IF;
END;
/
Begin
If valid_deptno(10) then
dbms_output.put_line('True');
else
dbms_output.put_line('false');
end if;
end;
above syntax could be used for your function, which returns a boolean.
If valid_deptno(10) then
or
If not valid_deptno(10) then -> to check for -return false value
hope it helps
bye
murugs
Thanks Indian2000!
Well, I am using this function VALID_DEPTNO to check the deptno for a procedure. There are two things I don't understand:
1. How can I execute VALID_DEPTNO as a stand alone function in sql*plus so that I won't get the following errors?
SQL> VARIABLE g_deptno NUMBER;
SQL> EXECUTE :g_deptno := valid_deptno (10);
BEGIN :g_deptno := valid_deptno (10); END;
*
ERROR at line 1:
ORA-06550: line 1, column 20:
PLS-00382: expression is of wrong type
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
2. When I am executing the following procedure, is there a way I can avoid hard-coding the department number? Can anybody help me on this? Thanks!!
e.g. valid_deptno (40)
CREATE OR REPLACE PROCEDURE new_emp
(v_ename emp.ename%TYPE)
IS
v_job emp.job%TYPE DEFAULT 'SALESMAN';
v_mgr emp.mgr%TYPE DEFAULT 7839;
v_sal emp.sal%TYPE DEFAULT 1000;
v_comm emp.comm%TYPE DEFAULT 0;
v_deptno emp.deptno%TYPE DEFAULT 30;
BEGIN
IF valid_deptno (40) THEN
INSERT INTO emp (empno, ename, job, mgr, sal, comm, deptno)
VALUES (seq_empno.nextval, v_ename, v_job, v_mgr, v_sal, v_comm, v_deptno);
END IF;
END;
/
sql*plus command "VARIABLE" only returns
NUMBER | CHAR | CHAR (n) | VARCHAR2 (n) |
NCHAR | NCHAR (n) | NVARCHAR2 (n) |
CLOB | NCLOB | REFCURSOR ]
so, as your function returns BOOLEAN, you cannot use a variable which type is NUMBER to hold the result. Try returning a varchar2. Generally a function is used in IF statements and SQL ...
F.
Mnemonical,
If I understand you correctly, you suggested I use VARCHAR2 this way?
SQL> VARIABLE g_deptno VARCHAR2(6);
SQL> EXECUTE :g_deptno := valid_deptno (10);
BEGIN :g_deptno := valid_deptno (10); END;
*
ERROR at line 1:
ORA-06550: line 1, column 20:
PLS-00382: expression is of wrong type
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
If so, still doesn't seem to execute.
No, I suggested you to return varchar2 (from function) for using with VARIABLE command when declaring a varchar2 variable in sql*plus. But it's not necessary, as I told you, functions generally are used in IF statements and SQL, why do you want to declare a sql*plus variable for holding a function return? :) Your first script works fine the way indian2000 suggested.
F.
hi oraka6
for your first question, you cannot directly execute the function from SQL> execute....... like executing the procedure. but there is way to execute the function using Dbms package(sorry I dont remember it).
For your 2nd question, it depends where you are going to use the procedure. Like forms/report. you can call the procedure from the form assigning values from the FORMS. But when you run the from PL/SQL, you can do like this
CREATE OR REPLACE PROCEDURE new_emp
(v_ename emp.ename%TYPE)
IS
v_job emp.job%TYPE DEFAULT 'SALESMAN';
v_mgr emp.mgr%TYPE DEFAULT 7839;
v_sal emp.sal%TYPE DEFAULT 1000;
v_comm emp.comm%TYPE DEFAULT 0;
v_deptno emp.deptno%TYPE DEFAULT 30;
a number :=&a;
BEGIN
IF valid_deptno (a) THEN
INSERT INTO emp (empno, ename, job, mgr, sal, comm, deptno)
VALUES (seq_empno.nextval, v_ename, v_job, v_mgr, v_sal, v_comm, v_deptno);
END IF;
END;
this is only temporary solution or you call this proce' and pass the parameter to deptno. hope it helps
Murugs
O.K. Got it. I am not allow to test function with booleans in sql*plus!!
However, is there another way to pass deptno without using ampersend (&)? Tks!
ok you can do like this, add one in parameter on your procedure for your Deptno
CREATE OR REPLACE PROCEDURE new_emp
(v_ename emp.ename%TYPE,a number )
IS
v_job emp.job%TYPE DEFAULT 'SALESMAN';
v_mgr emp.mgr%TYPE DEFAULT 7839;
v_sal emp.sal%TYPE DEFAULT 1000;
v_comm emp.comm%TYPE DEFAULT 0;
v_deptno emp.deptno%TYPE DEFAULT 30;
BEGIN
IF valid_deptno (a) THEN
INSERT INTO emp (empno, ename, job, mgr, sal, comm, deptno)
VALUES (seq_empno.nextval, v_ename, v_job, v_mgr, v_sal, v_comm, v_deptno);
END IF;
END;
and then call the procedure where ever you want and pass valriable name, deptno(In parameters) OR execute it in SQL
sql> execute new_emp('XZY',10);
bye
murugs