Calling a Stored Procedure in Oracle

Question

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. :)

Answer 1

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.