6 Replies Latest reply: Dec 4, 2012 3:06 AM by Ashu_Neo RSS

    PL/SQL simple code error

    piku
      Hi All,

      Below some simple PL/SQL code which is generate all details about all emp on a particular dept and input as dept_id.
      create or replace procedure total_info_dept
         (id IN number)
        
          IS
          emp_name varchar2(30);
          dep varchar2(30);
          sal number;
        
          Begin
       for id in (select b.name,c.dept_name,a.salary into emp_name,dep,sal
         from salary a,emp b,dept c where b.emp_id=a.emp_id and b.dept_id=c.dept_id)
         LOOP
       DBMS_OUTPUT.PUT_LINE('The Dept id is '||id||' Salary ' || sal);
         END LOOP;
        END total_info_dept;
      Compilation error is given below:-

      LINE/COL ERROR
      -------- -----------------------------------------------------------------
      24/6 PL/SQL: Statement ignored
      24/27 PLS-00306: wrong number or types of arguments in call to '||'

      Please help

      Regards
      Atanu
        • 1. Re: PL/SQL simple code error
          Ashu_Neo
          DBMS_OUTPUT.PUT_LINE('The Dept id is '||id||' Salary ' || sal);
          Use to_char for id and salary in dbms output for this type error..

          NB:- For formatting for your code use
           not 
          at end and I am not sure about your logic and your code is correct and what are you trying to achieve.
          Please elaborate, so that we can try in different way.

          Thanks!

          Edited by: Ashu_Neo on Dec 4, 2012 2:30 PM
          -- Added more comment
          • 2. Re: PL/SQL simple code error
            APC
            The FOR ... CURSOR constructs is a record not an index, so you cannot reference ID itself. You need to include a counter of some description in the query, such as ROWNUM.

            Also you don't need the INTO in the select statement, as you're selecting into the variable declared by the FOR clause.

            This is why you must reference columns in the result set with the record's name.
            for rec in (select b.name,c.dept_name,a.salary 
                                    , rownum as id
                          from salary a,emp b,dept c 
                          where b.emp_id=a.emp_id 
                          and b.dept_id=c.dept_id)
            LOOP
            DBMS_OUTPUT.PUT_LINE('The Dept id is '|| rec.id ||' Salary ' || rec.sal);
            All this syntax is documented in the PL/SQL User's Guide, so you could easily have answered this question for yourself. [url http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/sqloperations.htm#i45288]Find out more.

            Your message is confusing, as a r4easonable person would expect the Dept ID to be the column of that name, but it's a toy example.

            Cheers, APC

            P.S. The {code} tag is not normal markup, and doesn't take a backslash in the closing tag.
            • 3. Re: PL/SQL simple code error
              Purvesh K
              piku wrote:
              Hi All,

              Below some simple PL/SQL code which is generate all details about all emp on a particular dept and input as dept_id.
              create or replace procedure total_info_dept
              (id IN number)
              
              IS
              emp_name varchar2(30);
              dep varchar2(30);
              sal number;
              
              Begin
              for id in (select b.name,c.dept_name,a.salary                    ---> Modification here.
              from salary a,emp b,dept c where b.emp_id=a.emp_id and b.dept_id=c.dept_id)
              LOOP
              DBMS_OUTPUT.PUT_LINE('The Dept id is '||id||' Salary ' || sal);
              END LOOP;
              END total_info_dept;
              You haven't selected any Department ID in the select statement. Moreover, ID stands for record and does not hold any value. So to refer any column fetched in the Select query of Loop use Index.column_name, in your situation it will be
                   Begin
                for id in (select b.name,c.dept_name, c.dept_id,a.salary            -----> Added Dept_id here, Modification here.
                  from salary a,emp b,dept c where b.emp_id=a.emp_id and b.dept_id=c.dept_id)
                  LOOP
                DBMS_OUTPUT.PUT_LINE('The Dept id is '||id.dept_id||' Salary ' || id.sal);          ----> modification here
                  END LOOP;
                 END total_info_dept;
              • 4. Re: PL/SQL simple code error
                Purvesh K
                Ashu_Neo wrote:
                Use to_char for id and salary in dbms output.

                NB:- For formatting for your code use
                 not 
                at end.

                Thanks!
                That's incorrect!!!

                Please read what APC/My reply states. Its a Record variable.
                • 5. Re: PL/SQL simple code error
                  AlbertoFaenza
                  Hi Piku,

                  first of all to format your code you don't need to put backslash on last {noformat}
                  {noformat} tag.
                  
                  This is the correct way of formatting your code
                  i.e.:
                  {noformat}
                  {noformat}
                  SELECT ...
                  {noformat}
                  {noformat}
                  
                  You error is due to the fact that you are not using correctly the cursor.
                  
                  Your code should be changed in this way:
                  CREATE OR REPLACE PROCEDURE total_info_dept (id IN NUMBER)
                  IS
                  -- emp_name VARCHAR2 (30); -- no need of local variable
                  -- dep VARCHAR2 (30);
                  -- sal NUMBER;
                  BEGIN
                  FOR id IN (SELECT b.name, c.dept_name, a.salary
                  -- INTO emp_name, dep, sal -- for cursor no need to copy into local variable
                  FROM hsalary a, emp b, dept c
                  WHERE b.emp_id = a.emp_id AND b.dept_id = c.dept_id)
                  LOOP
                  DBMS_OUTPUT.put_line ('The Dept id is ' || id.dept_name || ' Salary ' || id.salary);
                  END LOOP;
                  END total_info_dept;
                  Regards.
                  Al                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                  • 6. Re: PL/SQL simple code error
                    Ashu_Neo
                    Yes I checked. I Changed my comments a bit. Actually, I just saw the error and replied on that.