Home » SQL & PL/SQL » SQL & PL/SQL » What is the Worng in this Code (Oracle 11gr1 OEL5.5 64bit)
What is the Worng in this Code [message #594677] Sun, 01 September 2013 10:33 Go to next message
9390512774
Messages: 101
Registered: January 2011
Location: hyd
Senior Member
Hi What is the wrong in the below code

DECLARE

  TYPE Emp_info IS RECORD
  (
  empno       scott.EMP.empno%TYPE,
  ename     scott.EMP.ename%TYPE,
  job         scott.EMP.job%TYPE,
  mgr         scott.EMP.mgr%TYPE,
  sal         scott.EMP.sal%TYPE
  );
  TYPE CurEmpInfo IS REF CURSOR;
ref_cur CurEmpInfo;
ref_cur1 CurEmpInfo;

  TYPE Emp_info_Rec IS TABLE OF Emp_info;
  v_tab  Emp_info_Rec := Emp_info_Rec();
BEGIN
  for cur in (SELECT * FROM scott.EMP) 
  LOOP
    v_tab.extend;
/*  This commented part of code is working but the below is not working 
    v_tab(v_tab.last).empno := cur.empno;
    v_tab(v_tab.last).ename := cur.ename;
    v_tab(v_tab.last).job := cur.job;
    v_tab(v_tab.last).mgr := cur.mgr;
    v_tab(v_tab.last).sal := cur.sal;*/  
/* have a problem with the below line of code*/
  v_tab(v_tab.last) := Emp_info_Rec(cur.empno,cur.ename,cur.job,cur.mgr,cur.sal); 
    DBMS_OUTPUT.PUT_LINE(v_tab(v_tab.last).Emp||'  '||v_tab(v_tab.last).ename||' '||v_tab(v_tab.last).job||' '||v_tab(v_tab.last).mgr||' '||v_tab(v_tab.last).sal);           
  END LOOP;
/* The below is also not working */
OPEN ref_cur FOR 
   SELECT  *
   FROM TABLE(CAST(v_tab AS Emp_info_Rec));
LOOP
    FETCH Cur_EmpInfo INTO ref_cur1;
    EXIT WHEN Cur_EmpInfo%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(ref_cur1.empno);
    DBMS_OUTPUT.PUT_LINE(ref_cur1.ename);
    DBMS_OUTPUT.PUT_LINE(ref_cur1.job);
    DBMS_OUTPUT.PUT_LINE(ref_cur1.mgr);
    DBMS_OUTPUT.PUT_LINE(ref_cur1.sal);
  END LOOP;
EXCEPTION
 WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE(SQLERRM);
  DBMS_OUTPUT.PUT_LINE(SQLCODE);
END;


Thanks In Advance
Re: What is the Worng in this Code [message #594678 is a reply to message #594677] Sun, 01 September 2013 10:36 Go to previous messageGo to next message
BlackSwan
Messages: 23139
Registered: January 2009
Senior Member
You should NOT do in PL/SQL that which can be done in plain SQL.

Remove, delete, & eliminate the whole EXCEPTION handler code section!

SQL> @scott
SQL> DECLARE
  2      TYPE emp_info IS RECORD (
  3        empno scott.emp.empno%TYPE,
  4        ename scott.emp.ename%TYPE,
  5        job scott.emp.job%TYPE,
  6        mgr scott.emp.mgr%TYPE,
  7        sal scott.emp.sal%TYPE );
  8      TYPE curempinfo IS ref CURSOR;
  9      ref_cur  CUREMPINFO;
 10      ref_cur1 CUREMPINFO;
 11      TYPE emp_info_rec
 12        IS TABLE OF EMP_INFO;
 13      v_tab    EMP_INFO_REC := Emp_info_rec();
 14  BEGIN
 15      FOR cur IN (SELECT *
 16                  FROM   scott.emp) LOOP
 17          v_tab.extend;
 18  
 19          /*  This commented part of code is working but the below is not working
 20              v_tab(v_tab.last).empno := cur.empno;
 21              v_tab(v_tab.last).ename := cur.ename;
 22              v_tab(v_tab.last).job := cur.job;
 23              v_tab(v_tab.last).mgr := cur.mgr;
 24              v_tab(v_tab.last).sal := cur.sal;*/
 25          /* have a problem with the below line of code*/
 26          V_tab(v_tab.last) :=
 27          Emp_info_rec(cur.empno, cur.ename, cur.job, cur.mgr, cur.sal);
 28  
 29          dbms_output.Put_line(V_tab(v_tab.last).emp
 30                               ||'  '
 31                               ||V_tab(v_tab.last).ename
 32                               ||' '
 33                               ||V_tab(v_tab.last).job
 34                               ||' '
 35                               ||V_tab(v_tab.last).mgr
 36                               ||' '
 37                               ||V_tab(v_tab.last).sal);
 38      END LOOP;
 39  
 40      /* The below is also not working */
 41      OPEN ref_cur FOR
 42        SELECT *
 43        FROM   TABLE(Cast(v_tab AS EMP_INFO_REC));
 44  
 45      LOOP
 46          FETCH cur_empinfo INTO ref_cur1;
 47  
 48          EXIT WHEN cur_empinfo%NOTFOUND;
 49  
 50          dbms_output.Put_line(ref_cur1.empno);
 51  
 52          dbms_output.Put_line(ref_cur1.ename);
 53  
 54          dbms_output.Put_line(ref_cur1.job);
 55  
 56          dbms_output.Put_line(ref_cur1.mgr);
 57  
 58          dbms_output.Put_line(ref_cur1.sal);
 59      END LOOP;
 60  EXCEPTION
 61      WHEN OTHERS THEN
 62        dbms_output.Put_line(SQLERRM);
 63  
 64        dbms_output.Put_line(SQLCODE);
 65  END;
 66  /
        Emp_info_rec(cur.empno, cur.ename, cur.job, cur.mgr, cur.sal);
        *
ERROR at line 27:
ORA-06550: line 27, column 9:
PLS-00306: wrong number or types of arguments in call to 'EMP_INFO_REC'
ORA-06550: line 27, column 9:
PLS-00306: wrong number or types of arguments in call to 'EMP_INFO_REC'
ORA-06550: line 27, column 9:
PLS-00306: wrong number or types of arguments in call to 'EMP_INFO_REC'
ORA-06550: line 27, column 9:
PLS-00306: wrong number or types of arguments in call to 'EMP_INFO_REC'
ORA-06550: line 27, column 9:
PLS-00306: wrong number or types of arguments in call to 'EMP_INFO_REC'
ORA-06550: line 26, column 9:
PL/SQL: Statement ignored
ORA-06550: line 29, column 48:
PLS-00302: component 'EMP' must be declared
ORA-06550: line 29, column 9:
PL/SQL: Statement ignored
ORA-06550: line 43, column 34:
PL/SQL: ORA-00902: invalid datatype
ORA-06550: line 42, column 7:
PL/SQL: SQL Statement ignored
ORA-06550: line 46, column 15:
PLS-00201: identifier 'CUR_EMPINFO' must be declared
ORA-06550: line 46, column 9:
PL/SQL: SQL Statement ignored
ORA-06550: line 48, column 19:
PLS-00201: identifier 'C


SQL> 


[Updated on: Sun, 01 September 2013 10:40]

Report message to a moderator

Re: What is the Worng in this Code [message #594679 is a reply to message #594677] Sun, 01 September 2013 10:53 Go to previous message
Michel Cadot
Messages: 59981
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
To emphasize what BlackSwan said, read WHEN OTHERS.

Regards
Michel
Previous Topic: How to scheduling a job from 8 am to 8 pm
Next Topic: get numbers which is not in the
Goto Forum:
  


Current Time: Thu Dec 18 09:56:19 CST 2014

Total time taken to generate the page: 0.05650 seconds