Home » SQL & PL/SQL » SQL & PL/SQL » Getting error when record type is used. (Oracle 11g)
Getting error when record type is used. [message #594844] Tue, 03 September 2013 08:19 Go to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi Experts,

Getting error when using record type as in parameter.

PLS-00306: wrong number or types of arguments in call to 'SAL_UPDATE_PROC'
PLS-00302: component 'ENAME' must be declared
PLS-00302: component 'SAL' must be declared

CREATE OR REPLACE PACKAGE emp_details_proc
IS
TYPE emp_record IS RECORD
(empno emp.empno%TYPE,
ename emp.ename%TYPE,
sal emp.sal%TYPE);

TYPE emp_type is TABLE OF emp_record INDEX BY binary_integer;
PROCEDURE EMP_PROC;
  
  PROCEDURE sal_update_PROC
  (    p_rule in  emp_type
     , p_deptno in number
     , p_job in number
  );
END;
/

CREATE OR REPLACE PACKAGE BODY emp_details_proc
IS
PROCEDURE EMP_PROC
  IS
    V_DEPTNO NUMBER := 10;
    V_JOB VARCHAR2(20):='CLERK';
  BEGIN
for rc in (SELECT EMPNO,ENAME,SAL FROM emp where deptno =v_deptno)
         loop
              sal_update_PROC
              (  p_rule => rc
                , p_deptno => V_DEPTNO
                , p_job =>V_JOB
              );
  end loop;

EXCEPTION
     WHEN OTHERS THEN
DBMS_output.put_line(SQLERRM);

END;

  PROCEDURE  sal_update_PROC
  (    p_rule in  emp_type
     , p_deptno in number
     , p_job in number
  )
  IS
    V_ename VARCHAR2(20);
    V_SAL NUMBER;
  BEGIN
      V_ename := p_rule.ename;
      V_sal := p_rule.sal;

        IF V_sal <=3000 THEN
        UPDATE EMP SET sal=sal+v_sal*10 WHERE ename=v_ename;
        commit;
      END IF; 
dbms_output.put_line(v_ename||'   '||v_sal);       
      END;
      END;
      /
      

Instead of this 
SELECT EMPNO,ENAME,SAL FROM emp where deptno =v_deptno;
  PROCEDURE  sal_update_PROC
  (    p_rule in  emp_type
     , p_deptno in number
     , p_job in number
  )

If I use 
SELECT * FROM emp where deptno =v_deptno
  PROCEDURE  sal_update_PROC
  (    p_rule in  emp%ROWTYPE
     , p_deptno in number
     , p_job in number
  )


I am not getting any error.

Please help me.

Thanks in advance.
Re: Getting error when record type is used. [message #594845 is a reply to message #594844] Tue, 03 September 2013 08:21 Go to previous messageGo to next message
BlackSwan
Messages: 21940
Registered: January 2009
Senior Member
Do NOT do in PL/SQL that which can be done in plain SQL.
Re: Getting error when record type is used. [message #594847 is a reply to message #594845] Tue, 03 September 2013 08:34 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi

Actually in my complete code some other procedure are being used.
Mainly I am looking for how to use record type in my code.

Please help me.

Thanks.
Re: Getting error when record type is used. [message #594848 is a reply to message #594847] Tue, 03 September 2013 08:36 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi All,

Please help me what is wrong in my code.

Thanks.
Re: Getting error when record type is used. [message #594849 is a reply to message #594848] Tue, 03 September 2013 08:37 Go to previous messageGo to next message
pablolee
Messages: 2590
Registered: May 2007
Location: Scotland
Senior Member
Stop incessantly bumping your posts. If someone knows, and has the time to respond, they will. There is NO requirement for you to bump the post every couple of minutes.
Re: Getting error when record type is used. [message #594850 is a reply to message #594849] Tue, 03 September 2013 08:43 Go to previous messageGo to next message
Roachcoach
Messages: 1125
Registered: May 2010
Location: UK
Senior Member
Get rid of the crappy exception handler (or should I say: exception smuggler)

14:41:41 SQL> l
  1  begin
  2  insert into fail select * from dual;
  3  exception
  4  when others then
  5  dbms_output.put_line(sqlerrm);
  6* end;
14:41:41 SQL> /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00


"afiedt.buf" 7 lines, 114 characters

  1  begin
  2  insert into fail select * from dual;
  3  --exception
  4  --when others then
  5  --dbms_output.put_line(sqlerrm);
  6* end;
14:41:59 SQL> /
begin
*
ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at line 2


Elapsed: 00:00:00.01

[Updated on: Tue, 03 September 2013 08:44]

Report message to a moderator

Re: Getting error when record type is used. [message #594851 is a reply to message #594849] Tue, 03 September 2013 08:55 Go to previous messageGo to next message
sss111ind
Messages: 427
Registered: April 2012
Location: India
Senior Member


 UPDATE EMP SET sal=sal+20*10 WHERE ename='MILLER' AND SAL>3000;


This single statment is enough for you inside a procedure .Why do you want all those things .
I mean what do you want to achieve literally.
Re: Getting error when record type is used. [message #594852 is a reply to message #594848] Tue, 03 September 2013 08:58 Go to previous messageGo to next message
Michel Cadot
Messages: 57606
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ajaykumarkona wrote on Tue, 03 September 2013 15:36
Hi All,

Please help me what is wrong in my code.

Thanks.


Answer:
EXCEPTION
     WHEN OTHERS THEN
DBMS_output.put_line(SQLERRM);


Read WHEN OTHERS.

Regards
Michel

[Updated on: Tue, 03 September 2013 08:59]

Report message to a moderator

Re: Getting error when record type is used. [message #594853 is a reply to message #594852] Tue, 03 September 2013 09:05 Go to previous messageGo to next message
BlackSwan
Messages: 21940
Registered: January 2009
Senior Member
PLS-????? are compile time errors
Read The Fine Manual on how to write correct PL/SQL syntax.
http://asktom.oracle.com contains many fine coding examples.
Re: Getting error when record type is used. [message #594865 is a reply to message #594853] Tue, 03 September 2013 11:36 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi All,

My aim is I want to implement this with record type.
Please forget about remaining things.

Please help me how to use record type in this procedure

Thanks
Re: Getting error when record type is used. [message #594866 is a reply to message #594865] Tue, 03 September 2013 11:44 Go to previous messageGo to next message
BlackSwan
Messages: 21940
Registered: January 2009
Senior Member
when all else fails Read The Fine Manual

http://docs.oracle.com/cd/E16655_01/appdev.121/e17622/create_type.htm#BABHJHEB
Re: Getting error when record type is used. [message #594867 is a reply to message #594865] Tue, 03 September 2013 12:02 Go to previous messageGo to next message
Michel Cadot
Messages: 57606
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Please forget about remaining things.


Not possible, first fix your code, your WHEN OTHERS is the biggest bug you can write in PL/SQL, everything else is detail.

Regards
Michel
Re: Getting error when record type is used. [message #594868 is a reply to message #594865] Tue, 03 September 2013 12:03 Go to previous messageGo to next message
flyboy
Messages: 1750
Registered: November 2006
Senior Member
ajaykumarkona wrote on Tue, 03 September 2013 18:36
Please help me how to use record type in this procedure

Simply use it. Why did you decide to use collection type instead?
TYPE emp_type is TABLE OF emp_record INDEX BY binary_integer;

Again: EMP_TYPE is collection type. EMP_RECORD is record type. You want to use record type. Which one of them will you choose?

Additionally, column EMP.JOB does not have NUMBER data type, as your procedure SAL_UPDATE_PROC declares (and then does not use at all).
Re: Getting error when record type is used. [message #594869 is a reply to message #594866] Tue, 03 September 2013 12:11 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi All,

If I use as below I am not getting any error.

SELECT * FROM emp where deptno =v_deptno
  PROCEDURE  sal_update_PROC
  (    p_rule in  emp%ROWTYPE
     , p_deptno in number
     , p_job in number
  )

My aim is instead of all columns I want to use only three columns
from emp table.

And all three columns will be passed as single In parameter(P_rule).

Please help me.

Thanks.
Re: Getting error when record type is used. [message #594870 is a reply to message #594869] Tue, 03 September 2013 12:17 Go to previous messageGo to next message
Michel Cadot
Messages: 57606
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

http://www.orafaq.com/forum/mv/msg/189293/594415/102589/#msg_594415

Use SQL*Plus and copy and paste your session, the WHOLE session.

Regards
Michel
Re: Getting error when record type is used. [message #594923 is a reply to message #594870] Wed, 04 September 2013 01:41 Go to previous messageGo to next message
sss111ind
Messages: 427
Registered: April 2012
Location: India
Senior Member

For record type usage test
create or replace
PACKAGE emp_details_proc
IS
TYPE emp_record IS RECORD
(empno emp.empno%TYPE,
ename emp.ename%TYPE,
sal emp.sal%TYPE);

--TYPE emp_type is TABLE OF emp_record INDEX BY binary_integer;
PROCEDURE EMP_PROC;
  
  PROCEDURE sal_update_PROC
  (    p_rule in  emp_record
     , p_deptno in number
     , p_job in VARCHAR2
  );
END;

create or replace
PACKAGE BODY emp_details_proc
IS
PROCEDURE EMP_PROC
  IS
    V_DEPTNO NUMBER := 10;
    V_JOB VARCHAR2(20):='CLERK';
    r_emp_data emp_record;
  BEGIN
for rc in (SELECT EMPNO,ENAME,SAL FROM emp where deptno =v_deptno)
         loop         
         r_emp_data.empno:=rc.empno;
         r_emp_data.ename:=rc.ename;
         r_emp_data.sal:=rc.sal;    
              sal_update_PROC
              (  p_rule => r_emp_data --sending record type data only
                , p_deptno => V_DEPTNO
                , p_job =>V_JOB
              );
  end loop;

--EXCEPTION
--     WHEN OTHERS THEN
--DBMS_output.put_line(SQLERRM);

END;

  PROCEDURE  sal_update_PROC
  (    p_rule in  emp_record --need here record type data
     , p_deptno in number
     , p_job in VARCHAR2
  )
  IS
    V_ename VARCHAR2(20);
    V_SAL NUMBER;
  BEGIN
      V_ename := p_rule.ename;
      V_sal := p_rule.sal;

        IF V_sal <=3000 THEN
        UPDATE EMP SET sal=sal+v_sal*10 WHERE ename=v_ename;
--        commit;
      END IF; 
dbms_output.put_line(v_ename||'   '||v_sal);       
      END;
      END;


[Updated on: Wed, 04 September 2013 01:44]

Report message to a moderator

Re: Getting error when record type is used. [message #594924 is a reply to message #594870] Wed, 04 September 2013 01:43 Go to previous message
_jum
Messages: 485
Registered: February 2008
Senior Member
Quote:
My aim is instead of all columns I want to use only three columns
from emp table.
And all three columns will be passed as single In parameter(P_rule).

May be You can use a (strong) cursor instead:
DECLARE

  --both definitions are possible
  --CURSOR c_emp IS SELECT empno, ename FROM emp;
  CURSOR c_emp IS SELECT * FROM emp;
  
  c_rec   c_emp%ROWTYPE;
  
  PROCEDURE proc_test (cp_rec c_emp%ROWTYPE)
  IS
  BEGIN
    DBMS_OUTPUT.put_line (cp_rec.empno);
  END;

BEGIN

  OPEN c_emp;

  LOOP
    FETCH c_emp INTO c_rec;
    EXIT WHEN c_emp%NOTFOUND;
    proc_test(c_rec);
  END LOOP;
END;

Previous Topic: set transaction use rollback
Next Topic: please convert this procedure to oracle.
Goto Forum:
  


Current Time: Thu Apr 17 13:38:18 CDT 2014

Total time taken to generate the page: 0.08931 seconds