12 Replies Latest reply: Oct 9, 2013 9:16 AM by Pleiadian RSS

    PLS-00306: wrong number or types of arguments in call to PUT_LINE

    880862

      Dear all,

           i m new to the pl sql, when i execute the below block i have get the errors.

      kindly help me on this.

       

       

      DECLARE

        TYPE siva IS TABLE OF VARCHAR2(100);

        s siva;

        CURSOR cur_i IS SELECT FIRST_NAME FROM employee;

      BEGIN

        OPEN cur_i;

        LOOP

        FETCH cur_i INTO s;

        EXIT WHEN cur_i%NOTFOUND;

        DBMS_OUTPUT.PUT_LINE(s);

        END LOOP;

        CLOSE cur_i;

      END;


      i m getting the below error:

      PLS-00597: expression 'S' in the INTO list is of wrong type

      PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'



        • 1. Re: PLS-00306: wrong number or types of arguments in call to PUT_LINE
          user6939971

          The subscript for the type element is missing .

          dbms_output.put_line(s(1));

           

          Also are you trying to populate the entire collection then this would not work. use a bulk collect or add some mechanism to have subscripts

          • 2. Re: PLS-00306: wrong number or types of arguments in call to PUT_LINE
            Pleiadian

            S is of the wrong type. It should be the same type as employee.first_name:

             

            DECLARE

              TYPE siva IS TABLE OF VARCHAR2(100);

              s employee.first_name%type;

              CURSOR cur_i IS SELECT FIRST_NAME FROM employee;

            BEGIN

              OPEN cur_i;

              LOOP

              FETCH cur_i INTO s;

              EXIT WHEN cur_i%NOTFOUND;

              DBMS_OUTPUT.PUT_LINE(s);

              END LOOP;

              CLOSE cur_i;

            END;

            • 3. Re: PLS-00306: wrong number or types of arguments in call to PUT_LINE
              kendenny

              Is this maybe what you want?

              DECLARE
                TYPE siva IS TABLE OF VARCHAR2(100);
                s siva;
                CURSOR cur_i IS SELECT FIRST_NAME FROM employee;
              BEGIN
                OPEN cur_i;
                FETCH cur_i BULK COLLECT INTO s;
                FOR i IN 1..s.count LOOP
                DBMS_OUTPUT.PUT_LINE(s(i));
                END LOOP;
                CLOSE cur_i;
              END;

              Or maybe this:

              DECLARE
                -- TYPE siva IS TABLE OF VARCHAR2(100);
                s varchar2(100);
                CURSOR cur_i IS SELECT FIRST_NAME FROM employee;
              BEGIN
                OPEN cur_i;
                LOOP
                FETCH cur_i INTO s;
                EXIT WHEN cur_i%NOTFOUND;
                DBMS_OUTPUT.PUT_LINE(s);
                END LOOP;
                CLOSE cur_i;
              END;

              dbms_output.put_line wants a parameter of a varchar2. You're giving it a table of varchar2.

              • 4. Re: PLS-00306: wrong number or types of arguments in call to PUT_LINE
                880862

                Hi Pleiadian,

                i understood ur reply.

                 

                then how to retrieve the records using the collection?

                can you give one small example using the collection to retrieve  the records.

                 

                Thanks,

                sivakumar a

                • 5. Re: PLS-00306: wrong number or types of arguments in call to PUT_LINE
                  Suri

                  Hi,

                   

                  I beleive you are learning/practicing PL/SQL. See below approach.

                   

                  declare

                    type siva is table of varchar2(100);

                    s siva;

                    cursor cur_i is
                      select first_name from employees;

                  begin

                    open cur_i;

                    loop 
                      fetch cur_i bulk collect
                        into s; 
                     exit when cur_i%notfound; 
                    end loop;

                    close cur_i;

                    -- Printing collection values

                    for i in s.first .. s.last loop
                      dbms_output.put_line(s(i));
                    end loop;

                  end;

                   

                  Thanks,

                  Suri

                   

                  Message was edited by: Suri Removed basic loop and exit when conditions since we are fetching all the records in one shot into the collection using bulk collect.

                  • 6. Re: PLS-00306: wrong number or types of arguments in call to PUT_LINE
                    Pleiadian

                    Hi Sivakumar,

                     

                    Kendenny and Suri have shown how to do it using collections and bulk collect. I would advise though to use

                     

                    type siva is table of employee.first_name%type;

                     

                    instead of

                     

                    type siva is table of varchar2(100);

                     

                    It is more robust and easier to understand:

                    • If you look at the code later, you will immediately know what you're going to use siva for.
                    • Using the %type construct, Oracle will look at the referenced field when the script runs. If there has been a change in this field, the script wont fail. For instance, it used to be a varchar2(100), but now it's a varchar2(200).
                    • 7. Re: PLS-00306: wrong number or types of arguments in call to PUT_LINE
                      Billy~Verreynne

                      Your code is dangerous ito server resource utilisation and overall stability,

                       

                      DBMS_OUTPUT is not an interface to write server data to the client. Calling Put_Line() does not write to the client's display.

                       

                      DBMS_OUTPUT has an internal buffer (array). When you call Put_Line(), you are writing server data into this buffer - effectively copying server data in one memory location to another.

                       

                      A client (like SQL-Plus or SQL-Developer) can interrogate this buffer after it has made a server call. Read the content from this buffer. And display that content in the application window/display device.

                       

                      This makes your code example nonsensical. You are reading data from a SQL table into server memory. Copying that data to other server memory. And then expect the client app to read the duplicated data on the server - as unstructured text.

                       

                      So what you have written is not only dangerous ito server resources, but conceptually flawed too.

                       

                      If you want to create a cursor in PL/SQL and display the cursor contents in the client, the CORRECT interface to use is ref cursors. Simple example:

                      declare

                        c sys_refcursor;

                      begin

                        -- create cursor

                        open c for select firstname from employees order by 1;

                       

                        -- pass the cursor to the client via a bind variable

                        :clientVar := c;

                      end;

                       

                      To use this anonymous code block from a sqlplus client:

                      SQL> -- create client bind variable

                      SQL> var myCursor refcursor

                      SQL>

                      SQL> -- execute code block above

                      ..

                      PL/SQL procedure successfully completed.

                       

                      SQL> -- now have the client display the cursor's contents

                      SQL> print myCursor

                       

                      This is also how the vast majority of Oracle client-server applications are, and should be, using Oracle.

                       

                      As for cursor FOR loops in PL/SQL. That is the WRONG approach the majority of time. It treats SQL data in a record-by-record fashion. It dates back to the 80's style of Cobol code processing ISAM files. It has very little relevance today.

                       

                      So if you see PL/SQL source code examples showing cursor FOR loop coding - beware. It is likely to be wrong, will be slow performance wise, and will be unable to scale with data volumes. It may have been the norm to process data in this fashion 30 years ago. But today it is not the norm and very much an exception.

                       

                      And in that sense you are honestly wasting your time trying to learn PL/SQL using cursor FOR loops.

                      • 8. Re: PLS-00306: wrong number or types of arguments in call to PUT_LINE
                        Billy~Verreynne

                        Suri wrote:

                         

                        Hi,

                         

                        I beleive you are learning/practicing PL/SQL. See below approach.

                         

                        declare

                          type siva is table of varchar2(100);

                          s siva;

                          cursor cur_i is
                            select first_name from employees;

                        begin

                          open cur_i;

                          loop 
                            fetch cur_i bulk collect
                              into s; 
                           exit when cur_i%notfound; 
                          end loop;

                          close cur_i;

                          -- Printing collection values

                          for i in s.first .. s.last loop
                            dbms_output.put_line(s(i));
                          end loop;

                        end;

                         

                        Thanks,

                        Suri

                         

                        Message was edited by: Suri Removed basic loop and exit when conditions since we are fetching all the records in one shot into the collection using bulk collect.

                         

                        And with that you could have crashed the server by exhausting all available server memory on PGA space due to an unconstrained bulk collect. And yes, I have seen this happen on 10gr2 in production. More than once.

                         

                        Besides, copying a SQL table/cursor's content wholly into server memory raises serious questions about just what the intentions are of that code by duplicating server data.

                        • 9. Re: PLS-00306: wrong number or types of arguments in call to PUT_LINE
                          Suri

                          Hi Billy,

                           

                          Understood your point. Thought OP just need to know how to read data from a collection. Since the program is on employees table, I didnt use LIMITS cluase as well. But any way thanks for explaining all these indetail.

                           

                          Cheers,

                          Suri

                          • 10. Re: PLS-00306: wrong number or types of arguments in call to PUT_LINE
                            Billy~Verreynne

                            Understand that you've posted a safe example - the problem is however that some (more than a few?) will not understand it and attempt something similar in production for the wrong reasons.

                            • 11. Re: PLS-00306: wrong number or types of arguments in call to PUT_LINE
                              Umesh P

                              Issue with your code is at

                              TYPE siva IS TABLE OF VARCHAR2(100);

                                s siva;

                               

                               

                              You have declared the variable. But you didn't define it.

                              You have to define it inside begin block.

                              Then use it.

                              • 12. Re: PLS-00306: wrong number or types of arguments in call to PUT_LINE
                                Pleiadian

                                I don't think so... The issue was that you cannot do a select into a collection. You either bulk collect into a collection OR select into a scalar datatype.