Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Friday, 14 July 2017

Oracle PL/SQL Cursor Attributes




Cursor Attributes



Explicit Cursors Attributes
Explicit Cursor: Example -1

DECLARE
   -- Step 1: Cursor declaration
   CURSOR empcur
   IS
      SELECT empno, ename
        FROM emp;
   v_empno   emp.empno%TYPE;
   v_name    emp.ename%TYPE;
BEGIN
   OPEN empcur;                         -- Step2: Open the Cursor for a query
   LOOP
      -- Step 3: Fetch the values from table results into PL/SQL variables.
      FETCH empcur
       INTO v_empno, v_name;
      EXIT WHEN empcur%NOTFOUND;
      DBMS_OUTPUT.put_line(v_empno || ' ' || v_name);
   END LOOP;
   CLOSE empcur;                                   -- Step 4: Close the cursor
END;

Explicit Cursor: using %TYPE and RECORD

DECLARE
   TYPE t_emprec IS RECORD (
      ename   emp.ename%TYPE,
      sal     emp.sal%TYPE,
      job     emp.job%TYPE
   );
   r_emp   t_emprec;
   CURSOR c_emp
   IS
      SELECT ename, sal, job
        FROM emp;
BEGIN
   OPEN c_emp;
   LOOP
      FETCH c_emp
       INTO r_emp;
      EXIT WHEN c_emp%NOTFOUND;
      DBMS_OUTPUT.put_line(r_emp.ename || ', ' || r_emp.sal || ', '
                            || r_emp.job
                           );
   END LOOP;
   CLOSE c_emp;
END;
Share this article :

2 comments:

  1. Selamat Petang,

    Great info! I recently came across your blog and have been reading along.
    I thought I would leave my first comment. I don’t know what to say except that I have

    I noticed that after my SQL instance crashed and came back online, One of the huge databases showed as recovering 10%..20% even though it was accessible and I was able to browse tables and query. I am surprised as to why it showed as recovering when it was all up and running.


    Super likes !!! for this amazing post
    . I thinks everyone should bookmark this.

    Thanks and Regards

    ReplyDelete
  2. Ohayo,


    Best thing I have read in a while on this #topic. There should be a standing ovation button. This is a great piece.

    1) I am new to Oracle, Im not a DBA and I ve tring to figure out this error in a log:

    ERROR in QUETOL.tol_lc_movimientos_pg.movimientos SQLCODE[=ORA-20109:
    ORA-20109: ORA-01422: exact fetch returns more than reques]


    The problem arises I dont even know exactly this error which object references to: ie package, procedure,table,etc

    QUETOL.tol_lc_movimientos_pg.movimientos

    I tried to find this object as a Package but i could not find it?

    SQL> SELECT object_name,OBJECT_TYPE,CREATED,STATUS,GENERATED FROM dba_objects
    2 WHERE object_name = 'QUETOL.tol_lc_movimientos_pg.movimientos'
    3 AND object_type = 'PACKAGE';

    no rows selected

    I tried to find as any other object but i didn’t get anything.
    SQL> SELECT object_name,OBJECT_TYPE,CREATED,STATUS,GENERATED FROM dba_objects
    2 WHERE object_name LIKE '%tol_lc_movimientos_pg.movimi%'
    3 ;

    no rows selected

    I thought it was a Package so I could see its DML code.

    I appreciate your help in advanced to identify which Object this log error point to point.


    Anyways great write up, your efforts are much appreciated.


    Obrigado,

    ReplyDelete