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
WHERE deptno = v_deptno;
IF SQL%NOTFOUND THEN
ELSIF SQL%FOUND THEN
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?
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.
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