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.

problem faced

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

error_log

2 answers

  • answered 2018-11-14 10:09 Littlefoot

    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;
    

  • answered 2018-11-14 14:13 thatjeffsmith

    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.

    enter image description here

    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.

    enter image description here

    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.