dokuwiki

This is an old revision of the document!

---

PROCEDURE fetch_table_data
   ( table_name IN varchar2)
   IS
   statement_cursor integer;
   row_selected      number;
   v_first_name     employees.first_name%type;
   v_last_name      employees.last_name%type;
BEGIN
   statement_cursor := dbms_sql.open_cursor;
   dbms_sql.parse(statement_cursor,
                  'select first_name, last_name from employees',
                  dbms_sql.native);
   DBMS_SQL.DEFINE_COLUMN(statement_cursor, 1, v_first_name, 100); 
   DBMS_SQL.DEFINE_COLUMN(statement_cursor, 2, v_last_name, 100);                  
   row_selected := dbms_sql.execute(statement_cursor);
    LOOP 
       IF DBMS_SQL.FETCH_ROWS(statement_cursor)>0 THEN 
         -- get column values of the row 
         DBMS_SQL.COLUMN_VALUE(statement_cursor, 1, v_first_name); 
         DBMS_SQL.COLUMN_VALUE(statement_cursor, 2, v_last_name);                                      
         dbms_output.put_line(v_first_name||' '||v_last_name);         
       else
         exit;
       end if;
    end loop;
   dbms_sql.close_cursor(statement_cursor);

END; -- Procedure