SQL & PL/SQL

Do you mean the EXECUTE command in SQL*Plus?

You can't use it to run a procedure with an out parameter.

Edit: EXECUTE only "Executes a single PL/SQL statement." To handle the out parameter you need more than a single statement. But you can assign the value to a variable if you define it first.

Regards

Marcus

1043556 wrote:
Hi all please advise 
how to run a package through execute command in oracle pl sql, my package is having one out parameter.
EXECUTE PKG_LOD_PATCH.SP_LOD_RPT(null); 
Getting error
below script is working fine.
DECLARE
 W_ERRMSG VARCHAR2(1000);
BEGIN 
 PKG_LOD_PATCH.SP_LOD_RPT(W_ERRMSG);
END; 
/

What error?

Are you trying to assign somethimg to null? That won't work, you'll need to set up a variable for it to be contained in, if using sqlplus then you can

var sErrorMessage varchar2(1000)

exec pkg_lod_patch.sp_lod_rpt(:sErrorMessage)

print :sErrorMessage

BTw you don't execute a package, you execute a stored program that's inside the package.

And it is utterly silly to return errors like this, raise them! You are making life very difficult for yourself. You didn't even check the contents of that variable in your own block which just shows how crazy an idea it is!

this is not sql *plus, i want to execute in sql command window in plsql developer tool.

You're executing a *Procedure* in a *Package*.  Does it have to be a Procedure  ?  Can it be a Function ?

Hemant K Chitale

Marwim wrote:
Do you mean the EXECUTE command in SQL*Plus?
You can't use it to run a procedure with an out parameter.
Edit: EXECUTE only "Executes a single PL/SQL statement." To handle the out parameter you need more than a single statement. But you can assign the value to a variable if you define it first.
Regards
Marcus

Just to clarify with an example, as you CAN run a procedure with an out parameter...

SQL> create or replace procedure x(x out number) is
  2  begin
  3    x := 1;
  4  end;
  5  /
Procedure created.
SQL> var myX number;
SQL> execute x(:myX);
PL/SQL procedure successfully completed.
SQL> print myX;
      MYX
----------
        1
SQL>

write package/procedure name into worksheet, right click menu and click "Test".

If I understood your question correctly then, you want to test the EXECUTE command from your client IDE PL/SQL developer by Allround Automation. It is better if you would have asked in the tool specific forum.

However, EXECUTE is a SQL * Plus (another client tool) specific command, which you can use in PL/SQL developer also, if you use the command line tool there.

Like this

nimish@garg> create or replace procedure myproc(p_out out number)
  2  is
  3  begin
  4    select count(*) into p_out from emp;
  5  end;
  6  /
Procedure created.
nimish@garg> var cnt number;
nimish@garg> execute myproc(:cnt);
PL/SQL procedure successfully completed.
nimish@garg> print cnt;
       CNT
----------
        14
nimish@garg> select :cnt from dual;
      :CNT
----------
        14