This discussion is archived
14 Replies Latest reply: Jan 20, 2013 11:14 PM by Nitesh. RSS

Need help in calling stored proc

980493 Newbie
Currently Being Moderated
Hi Experts,

I've created below procedure, while am calling it, it's throwing an error.
CREATE OR REPLACE PROCEDURE spdemo (param1 OUT NUMBER)
 is
 BEGIN
 SELECT COUNT(*) INTO param1
 FROM TEST_DEMO;
 END;
Can any1 help me out to get the output or to call this procedure?

Thnx in advance...

Edited by: 977490 on Jan 18, 2013 2:25 AM
  • 1. Re: Need help in calling stored proc
    Hoek Guru
    Currently Being Moderated
    How are you exactly calling the procedure?
    Why don't you give us the exact error code/message as well?
    Did you search for that error code in the Online Documentation and do you understand it's cause and the action to take?
    {message:id=9360002}
  • 2. Re: Need help in calling stored proc
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    977490 wrote:
    ... I've created below procedure, while am calling it, it's throwing an error.
    If the problem is with the call, then post the call.

    Here's one possible way to call spdemo and see the results:
    SET   SERVEROUTPUT   ON
    
    
    DECLARE
        test_demo_cnt  NUMBER;
    BEGIN
        spdemo (test_demo_cnt);
    
        dbms_output.put_line (test_demo_cnt || ' = value returned by spdemo');
    END;
    /
    CREATE OR REPLACE PROCEDURE spdemo (param1 OUT NUMBER)
    is
    BEGIN
    SELECT COUNT(*) INTO param1
    FROM TEST_DEMO;
    END;
    I would be tempted to make this a function, not a procedure.
    CREATE OR REPLACE FUNCTION spdemo
    RETURN NUMBER
     IS
        cnt          NUMBER;
     BEGIN
         SELECT  COUNT (*) 
         INTO    cnt
         FROM    test_demo;
    
         RETURN  cnt;
     END;
    /
    SHOW ERRORS
    Then you can call it in SQL statements, just like you call built-in functions such as SYSDATE:
    SELECT     spdemo
    FROM     dual
    ;
  • 3. Re: Need help in calling stored proc
    980493 Newbie
    Currently Being Moderated
    I am calling like below:
    execute spdemo(1);
    ERROR at line 1:
    ORA-06550: line 1, column 14:
    PLS-00363: expression '1' cannot be used as an assignment target
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored
    execute spdemo();
    ERROR at line 1:
    ORA-06550: line 1, column 7:
    PLS-00306: wrong number or types of arguments in call to 'SPDEMO'
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored
    execute spdemo(param1);
    ERROR at line 1:
    ORA-06550: line 1, column 14:
    PLS-00201: identifier 'PARAM1' must be declared
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored
  • 4. Re: Need help in calling stored proc
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    977490 wrote:

    Can any1 help me out to get the output or to call this procedure?
    Calls to database server for executing PL/SQL code, needs to be done as an anonymous PL/SQL code block. As the server code returns data, a bind variable needs to be used - this variable receives the "output" of the call.

    The anonymous block will look as follows:
    begin
      spdemo( param => :0 );
    end;
    From a SQL*Plus client, it looks as follows:
    // define a client variable
    SQL> var v0 number
    SQL> 
    // bind the client variable to the server code's execution
    SQL> begin
      2    spdemo( param => :v0 );
      3  end;
      4  /
    From a PHP or C/C++ or Java client, it will look similar - but need a couple of extra calls that SQL*Plus client does automatically. These calls will do the binding and then read the bind value from the cursor execution into a local variable.

    What client environment and client language are you using?
  • 5. Re: Need help in calling stored proc
    Hoek Guru
    Currently Being Moderated
    In addition to the points already made by Frank and Billy, you might be interested in reading http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:325417134618
    As Frank pointed out, I'd probably use a function as well here, instead of a procedure.
  • 6. Re: Need help in calling stored proc
    980493 Newbie
    Currently Being Moderated
    Still it's thrwoing error, regardless using the declare keyword or not.
      1  declare
      2  var v0 number;
      3  begin
      4      spdemo( param => :v0 );
      5*     end;
    SQL> /
    SP2-0552: Bind variable "V0" not declared.
  • 7. Re: Need help in calling stored proc
    Nitesh. Explorer
    Currently Being Moderated
    CREATE OR REPLACE PROCEDURE spdemo (param1 OUT NUMBER)
    is
    BEGIN
    SELECT COUNT(*) INTO param1
    FROM TEST_DEMO;
    END;

    In a simple way call in this way mentioned below :


    declare
    p1 number;
    begin
    spdemo(p1);
    dbms_output.put_line(p1);
    end;
    /
  • 8. Re: Need help in calling stored proc
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    977490 wrote:
    I am calling like below:
    execute spdemo(1);
    ERROR at line 1:
    ORA-06550: line 1, column 14:
    PLS-00363: expression '1' cannot be used as an assignment target
    Spdemo changes the value of its argument. Regardless of what the value of that argument was when you call spdemo, it will be set to the number of rows in test_domo after a sucessful call. You can't change the value of 1. (Think of the problems that would be created if you could!)
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored
    execute spdemo();
    ERROR at line 1:
    ORA-06550: line 1, column 7:
    PLS-00306: wrong number or types of arguments in call to 'SPDEMO'
    Spdemo requires 1 argument, but here you're calling it with 0 arguments. That's the wrong number of arguments.
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored
    execute spdemo(param1);
    ERROR at line 1:
    ORA-06550: line 1, column 14:
    PLS-00201: identifier 'PARAM1' must be declared
    All variables must be Declared , that is, there must be some kind of statmentment that says you're going to to use a paticular name (such as param1) as a particular data type (such as NUMBER). In the anonymous block I posted earlier, cnt was declared to be a NUMBER.
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored
  • 9. Re: Need help in calling stored proc
    Sven W. Guru
    Currently Being Moderated
    977490 wrote:
    Still it's thrwoing error, regardless using the declare keyword or not.
    Not it is not regardless. This matters.
    1  declare
    2  var v0 number;
    3  begin
    4      spdemo( param => :v0 );
    5*     end;
    SQL> /
    SP2-0552: Bind variable "V0" not declared.
    In this case using the declare keyword is wrong. Declare starts a PL/SQL block. However the code that Billy posted uses a sql*plus variable. The VAR keyword is not allowed inside a pl7sql block.
    However variables declared in sql*plus can be used in pL/sql using the bind syntax with ":". Therefore Billys code works using an sql*plus variable. Franks code works using a pl/sql variable.
    Your code didn't work because it mixed a sql*plus and pl/sql in the wrong way.

    Edited by: Sven W. on Jan 18, 2013 12:13 PM - Typo corrections
  • 10. Re: Need help in calling stored proc
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Niteshkhush wrote:

    In a simple way call in this way mentioned below :


    declare
    p1 number;
    begin
    spdemo(p1);
    dbms_output.put_line(p1);
    end;
    /
    Not simple, but wrong.

    The procedure's output is not returned to the caller. Instead it is written as plain text into a text array in the DBMS_SQL package, residing in the server process.

    This is not how one returns data from server to client when the client makes a PL/SQL stored procedure call.

    Fundamentals are important when explaining and showing how a stored proc needs to be called, and output parameters from that returned to the client. And this code example is fundamentally wrong in that respect.
  • 11. Re: Need help in calling stored proc
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    977490 wrote:
    Still it's thrwoing error, regardless using the declare keyword or not.
    1  declare
    2  var v0 number;
    3  begin
    4      spdemo( param => :v0 );
    5*     end;
    SQL> /
    SP2-0552: Bind variable "V0" not declared.
    It will not throw an error if you correctly copy-and-pasted the sample code.

    Nor would you have added a declare to the sample if you made an effort to understand the fundamental principle of clients using bind variables, that I tried to convey in simple and easy terms.
  • 12. Re: Need help in calling stored proc
    Nitesh. Explorer
    Currently Being Moderated
    Hey see the procedure just have OUT parameter which surely going to return some value so for that purpose if u just pass a paramter of same datatype then it will return a value and print will be done through dbms_output simple .. What's there here to be too complicated .. Its a simple case ..
  • 13. Re: Need help in calling stored proc
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    You are missing the point and seemingly do not understand what DBMS_OUTPUT is and how to use it.

    Again - your sample code is wrong as an example of a client calling the Oracle server, running a stored proc, and receiving data from that stored proc.
  • 14. Re: Need help in calling stored proc
    Nitesh. Explorer
    Currently Being Moderated
    You arguing with me but my sample code working fine, I mean it meets requirement of question been asked here , even you can test it .. I am not deep to "dbms_output" predefined package but I know this alone that it is used to give output, Anyways no problem as you going too deep so you too right but my query is not wrong is my point here .. Thank you Sir ..

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points