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 :

3 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
  3. Oracle apps and Fusion Self Paced Training Videos by Industry Experts. Please Check oracleappstechnical.com

    ReplyDelete