oracle sql developer ora-00904 stored procedure execution error
I have wrote simple stored procedure in Oracle SQL Developer but found, attached ,error on execution/run step.
Here is my code:
CREATE OR REPLACE PROCEDURE EMP_NAME (EMP_ID_IN IN VARCHAR2, EMP_NAME_OUT OUT VARCHAR2) AS BEGIN SELECT first_name INTO EMP_NAME_OUT FROM employee WHERE emp_id = EMP_ID_IN; END EMP_NAME;
It also shows this error
The procedure itself seems to be OK. However, its execution is somewhat strange.
I suggest you to run it from the Worksheet itself, such as
declare l_out employee.first_name%type; begin emp_name(100, l_out); dbms_output.put_line('Result = ' || l_out); end; /
Though, why is it a procedure? Wouldn't a function be a better choice? E.g.
create or replace function emp_name (emp_id_in in varchar2) return employee.first_name%type is retval employee.first_name%type; begin select first_name into retval from employee where emp_id = emp_id_in; return retval; end; /
You'd run it in a simple manner, as
select emp_name(100) from dual;
There's something wrong with your data dictionary. When we go to execute a stored procedure, we ask the database for some information about your code.
SELECT data_type, argument_name name FROM all_arguments a, all_objects o WHERE a.object_id=o.object_id AND o.object_name=? and o.owner=? and a.package_name is NULL order by position
The error about an invalid object_id - that's coming from this query. What version Oracle Database are you running? Can you see your PL/SQL object in ALL_OBJECTS and do your arguments show up in ALL_ARGUMENTS?
I've taken your code and modified it for the HR.EMPLOYEES table.
It works as expected.
We run some code to be able to show you the two parameters.
I put in a value of '101' for employee number or ID, and hit OK.
Then the OUT parameter is displayed below in the Log panel.
If you open your log panel (view -> log), you'll see a 'Statements' page as well. It's there that you can see ALL the code we execute on the database. That's where I went to get the SQL that's failing for you on the OBJECT_ID. Go look at that, and walk the code and confirm what's not working.