oracle sql developer ora-00904 stored procedure execution error

Question

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

Answer 1

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;

Answer 2

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.