Oracle, SQL+ and calling a stored proc in a package

I have this code:

declare
return_status number;
return_msg varchar2(255);
variable retval number;
execute :retval := update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', return_status, return_msg);
/

I get SP2-0552: Bind variable "RETVAL" not declared.

I have searched Google and the Oracle docs and I cannot find ONE single decent example of calling a stored procedure that's in a package and where that stored procedure has OUT parameters.

HELP!!?!?!

n/m

this worked:

declare
return_status number;
return_msg varchar2(255);
begin
update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', return_status, return_msg);
end;
/Originally posted by nightowlky
I have this code:

declare
return_status number;
return_msg varchar2(255);
variable retval number;
execute :retval := update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', return_status, return_msg);
/

I get SP2-0552: Bind variable "RETVAL" not declared.

I have searched Google and the Oracle docs and I cannot find ONE single decent example of calling a stored procedure that's in a package and where that stored procedure has OUT parameters.

HELP!!?!?!

n/m

this worked:

declare
return_status number;
return_msg varchar2(255);
begin
update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', return_status, return_msg);
end;
/
The problem is that you are confusing SQL Plus syntax with PL/SQL syntax. VARIABLE and EXECUTE are SQL Plus commands, so your example should look like one of these:

1) All PL/SQL variables:

declare
return_status number;
return_msg varchar2(255);
retval number; -- no VARIABLE keyword
begin
retval := -- No EXECUTE and no ":"
update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', return_status, return_msg);
end;
/

2) SQL Plus variables:

SQL> variable retval number;
SQL> variable return_status number;
SQL> variable return_msg varchar2(255);
SQL> execute :retval := update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', :return_status, :return_msg);

3) Combination of SQL Plus and PL/SQL variables:

SQL> variable retval number;

declare
return_status number;
return_msg varchar2(255);
begin
:retval := update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', return_status, return_msg);
end;
/

The difference is that SQL Plus variables are available to SQL Plus after the PL/SQL block has completed execution, i.e. in examples 2 and 3 you can go on to use :retval again.|||Thanks, Andrew.

But, using all 'variable' statements, I get this:

Wrote file afiedt.buf

1 variable retval number;
2 variable return_status number;
3 variable return_msg varchar2(255);
4* execute :retval := update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', :return_status, :return_msg);
SQL> run
1 variable retval number;
2 variable return_status number;
3 variable return_msg varchar2(255);
4* execute :retval := update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', :return_status, :return_msg);
SP2-0552: Bind variable "RETURN_MSG" not declared.

All I'm really trying to do is be able to print out the values of return_status and return_msg.

I'm all new to Oracle, SQL+, PL/SQL. I come from a long history of MS SQL Server. Speaking of which, are there tools similar to the MS tools for writing queries, importing data, etc.? Seems all I have installed on this workstation is SQL+ and WinSQL (which chokes on syntax that works in SQL+ ??)|||Originally posted by nightowlky
Thanks, Andrew.

But, using all 'variable' statements, I get this:

Wrote file afiedt.buf

1 variable retval number;
2 variable return_status number;
3 variable return_msg varchar2(255);
4* execute :retval := update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', :return_status, :return_msg);
SQL> run
1 variable retval number;
2 variable return_status number;
3 variable return_msg varchar2(255);
4* execute :retval := update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', :return_status, :return_msg);
SP2-0552: Bind variable "RETURN_MSG" not declared.

All I'm really trying to do is be able to print out the values of return_status and return_msg.

I'm all new to Oracle, SQL+, PL/SQL. I come from a long history of MS SQL Server. Speaking of which, are there tools similar to the MS tools for writing queries, importing data, etc.? Seems all I have installed on this workstation is SQL+ and WinSQL (which chokes on syntax that works in SQL+ ??)
The problem is that the RUN command treats contents of the buffer as a single command, which this is not. Instead of using RUN, try this:

SQL> @.afiedt.buf

That correctly processes 4 "statements" (3 VARIABLE commands and one EXECUTE command).|||ah...

well...

it gets better:

SQL> @.afiedt.buf

PL/SQL procedure successfully completed.

variable return_status number;
*
ERROR at line 1:
ORA-00900: invalid SQL statement

DOH!|||Originally posted by nightowlky
ah...

well...

it gets better:

SQL> @.afiedt.buf

PL/SQL procedure successfully completed.

variable return_status number;
*
ERROR at line 1:
ORA-00900: invalid SQL statement

DOH!
What does afiedt.buf contain now?|||VARIABLE return_status NUMBER
VARIABLE return_msg VARCHAR2(255)
EXECUTE update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', :return_status, :return_msg)
/

I don't believe the whole procedure returns a value (other than the out parameters) so I removed the retval variable.|||Originally posted by nightowlky
VARIABLE return_status NUMBER
VARIABLE return_msg VARCHAR2(255)
EXECUTE update_project_mgmt_pkg.initialize_agency_project_ mgmt(14004,12, 'username', :return_status, :return_msg)
/

I don't believe the whole procedure returns a value (other than the out parameters) so I removed the retval variable.
You don't need or want that "/" on the last line. Remove that and the error should go away!|||Ah!

The sweet smell of success!

Thank you, Andrew!

Thank you!

:D|||Why dont you try using dbms_output.put_line function to display the output ? The steps are as follows :

-- test1.sql
declare
l_ret_val number;
begin
l_ret_val := call_function();
dbms_output.put_line(l_ret_val);
end;
/

Now at sql prompt execute the following commands :
sql> set serveroutput on
sql> @.test1.sql

Hope this helps ...|||I'll try that, too.

As I said...I'm quite the Oracle newbie and I'm trying to just find stuff that works. And finding examples of some of this stuff is like trying to put your elbow in your ear (can almost get there but not quite) :)|||Originally posted by nightowlky
I'll try that, too.

As I said...I'm quite the Oracle newbie and I'm trying to just find stuff that works. And finding examples of some of this stuff is like trying to put your elbow in your ear (can almost get there but not quite) :)
When using VARIABLE in SQL Plus, you will find the PRINT command handy:

SQL> variable a number
SQL> exec :a := 123

PL/SQL procedure successfully completed.

SQL> print a

A
----
123