14 Replies Latest reply: Jan 21, 2013 1:14 AM by Nitesh. RSS

    Need help in calling stored proc

    980493
      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
          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
            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
              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
                Billy~Verreynne
                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
                  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
                    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.
                      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
                        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.
                          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
                            Billy~Verreynne
                            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
                              Billy~Verreynne
                              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.
                                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
                                  Billy~Verreynne
                                  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.
                                    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 ..