This discussion is archived
6 Replies Latest reply: Dec 4, 2012 1:06 AM by Ashu_Neo RSS

PL/SQL simple code error

piku Newbie
Currently Being Moderated
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 Pro
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    Yes I checked. I Changed my comments a bit. Actually, I just saw the error and replied on that.

Legend

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