Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2003
    Posts
    17

    Unanswered: 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;
    /
    Last edited by nightowlky; 06-02-03 at 17:11.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

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

    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:

    Code:
    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;

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

  3. #3
    Join Date
    Jun 2003
    Posts
    17
    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+ ??)

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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).

  5. #5
    Join Date
    Jun 2003
    Posts
    17
    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!

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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?

  7. #7
    Join Date
    Jun 2003
    Posts
    17
    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.

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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!

  9. #9
    Join Date
    Jun 2003
    Posts
    17

    Talking

    Ah!

    The sweet smell of success!

    Thank you, Andrew!

    Thank you!


  10. #10
    Join Date
    May 2003
    Posts
    87
    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 ...

  11. #11
    Join Date
    Jun 2003
    Posts
    17
    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)

  12. #12
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •