Calling a Stored Procedure in Oracle

Question

Hey all,

This should be something fairly simple and straight-forward but for some reason I can't get it to work. I've created my SProc like so:

create or replace procedure zadmit_migrate_data (zadmit_seq_no number)
is
    thisPIDM number;
begin
    select pidm into thisPIDM
    from saturn.zadmit_core_data
    where pk_seqno = zadmit_seq_no;

    if thisPIDM is not null then
        dbms_output.put_line('thisPIDM is NOT null!');    
    else
        dbms_output.put_line('thisPIDM is null!');
    end if;
end zadmit_migrate_data;

And now I've trying to call it like this:

call zadmit_migrate_data(4);

And then I get this error:

ORA-06575 Package or function is in an invalid state.

So I tried this:

execute zadmit_core_data(4);

And instead get this error:

ORA-00900 Invalid SQL statement.

It might be less time consuming to point out where I'm going right, but if anyone can tell me where I'm going wrong that would probably be more useful. :)

Thanks, Matt

Answer 1

Shouldn't your execute statement be "execute zadmit_migrate_data(4);" ?

At any rate, running this command:

SELECT object_name FROM user_objects WHERE status='INVALID';

will tell you if you procedure is valid or not.

Executing your CREATE or REPLACE ... command, then immediately executing the command

SHOW ERRORS

should tell you what compilation errors were encountered.

Answer 2

If you Google for error ORA-06575, you find:

You tried to execute an SQL statement that referenced a PLSQL function that is in an invalid state. This happens when the function is compiled with errors.

You can resolve this by:

Correct the errors and then re-compile the function. Then re-execute the SQL statement.

Once your procedure compiles, you could execute it like:

begin 
    zadmit_migrate_data(4);
end;

Answer 3

Run this

SQL>  alter procedure zadmit_migrate_data compile;
SQL>  show errors

Given the simplicity of your procedure, it shouldn't be hard to diagnose the resulting error stack.