Saturday, September 15, 2012

PL/SQL : Display the table values



CREATE OR REPLACE PROCEDURE dispemp
 IS
     no            NUMBER;
     nm           VARCHAR2(30);
     chndl        INTEGER;
     ignore       INTEGER;
BEGIN

     chndl := dbms_sql.open_cursor;
     DBMS_SQL.PARSE(chndl, 'select eno, ename from emp', DBMS_SQL.NATIVE);

     DBMS_SQL.DEFINE_COLUMN(chndl, 1, no);
     DBMS_SQL.DEFINE_COLUMN(chndl, 2, nm, 30);

     ignore := DBMS_SQL.EXECUTE(chndl);

  -- Fetch a row from the table
     LOOP
       IF DBMS_SQL.FETCH_ROWS(chndl) > 0 THEN
         -- get column values of the row
         DBMS_SQL.COLUMN_VALUE(chndl , 1, no);
         DBMS_SQL.COLUMN_VALUE(chndl, 2, nm);

         DBMS_OUTPUT.PUT_LINE(no || '  ' || nm);  

      ELSE
        EXIT;
      END IF;

    END LOOP;

     DBMS_SQL.CLOSE_CURSOR(chndl);
   EXCEPTION
     WHEN OTHERS THEN
       IF DBMS_SQL.IS_OPEN(chndl) THEN
         DBMS_SQL.CLOSE_CURSOR(chndl);
       END IF;
  END;
/

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect