CODEWITHSUNDEEP
sqloracle-database
codepoetly
codepoetly

Reputation: 17

procedure created but not executing

create or replace procedure para_cursor_exe as 
cursor c_p_det(tar_val number) is select name, salary from fees where salary < tar_val;
nname varchar2(30);
ssalary number(5);

begin 
<<block_exe>>
begin
Open c_p_det(&tar_val);
LOOP
FETCH c_p_det into nname, ssalary;
DBMS_OUTPUT.PUT_LINE('NAME : ' || NNAME || ' :::: SALARY : ' || SSALARY);
EXIT WHEN C_P_DET%NOTFOUND;
END LOOP;
CLOSE C_P_DET;
end;

execute immediate ' block_exe ' ;
dbms_output.put_line('done processing !!');

end;
/  

SQL> @para_cursor_exe.sql;
Enter value for tar_val: 1000
old   9:  Open c_p_det(&tar_val);
new   9:  Open c_p_det(1000);

Procedure created.

The procedure is compiled but on call gives an error as follows 

SQL> call para_cursor_exe;
call para_cursor_exe
 *
ERROR at line 1:
ORA-06576: not a valid function or procedure name

i wanna process the procedure dynamically for different target values which are supposed to be passed to the cursor at runtime. How do i call it or is the logic wrong?

Upvotes: 0

Views: 266

Answers (1)

David דודו Markovitz
David דודו Markovitz

Reputation: 44921

The error states that call is not not a valid function or procedure name. 

create or replace procedure p as begin null; end;
/

Use call with parenthesis - ()

call p();

or don't use call at all

begin
    p;
end;

Upvotes: 1

Related Questions