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. edit: you're on DB 10g, I'm guessing object_id isn't in the all arguments view. 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.
To fix this, go find an OLD copy of SQLDev that supports 10g..like maybe 2.1, OR upgrade your DB to at least 11.2.0.4.
SQL Developer 1.5.5, and it does execute without facing any issues. What is your database version and SQL Developer version?