Oracle read nth column using a cursor

Question

hi,

can i read column data in a cursor giving the index of the column?

thanks...

---

From the questioner's comments : "I need to create a generic procedure which will read a table or view (name of the view or table is the argument of the proc) and encrypt the data in the column and then will write the encrypted data to a OS file. "

Answer 1

This should give you a head start. Just plug in your encryption code for the column number you want. I've used VARCHAR everywhere. If you want dates and numbers (or more exotic datatypes), then you'll need to handle the conversion.

create or replace function qry_dump
  (p_tab_name in varchar2, p_rownum in number default 5)
return tab_char_4000 AUTHID CURRENT_USER pipelined is
  v_line      varchar2(2000);
  v_col_cnt   INTEGER;
  v_ind       NUMBER;
  rec_tab     dbms_sql.desc_tab;
  v_tab       dbms_sql.varchar2a;
  v_temp      VARCHAR2(32000);
  v_cursor    NUMBER;
  v_clause    VARCHAR2(200);
begin
  --
  -- Initial values
  --  v_ind := 1;
  v_temp := 'select * from '||p_tab_name||' where rownum <= '||nvl(p_rownum,5);
  --
  -- Identify the columns in the target and build the new query
  --
  v_cursor := dbms_sql.open_cursor;
  dbms_sql.parse(v_cursor, v_temp, dbms_sql.native);
  dbms_sql.describe_columns(v_cursor, v_col_cnt, rec_tab);
  --
  FOR v_pos in 1..rec_tab.LAST LOOP
    v_line := rec_tab(v_pos).col_name;
    dbms_sql.define_column( v_cursor, v_pos, v_line, 2000);
  END LOOP;
  v_ind := dbms_sql.execute( v_cursor );
  --
  -- Fetch each row from the result set
  --
  LOOP
    v_ind := DBMS_SQL.FETCH_ROWS( v_cursor );
    EXIT WHEN v_ind = 0;
    pipe row( '=============================================================');
    --
    -- Go through each column and display it
    --
    FOR v_col_seq IN 1 .. rec_tab.COUNT LOOP
      -- Get the value
      dbms_sql.column_value( v_cursor, v_col_seq, v_line );
      pipe row( rpad(rec_tab(v_col_seq).col_name,35)||'>'||v_line);
    END LOOP;
  END LOOP;
  return;
end qry_dump;
/

select * from table(qry_dump('DEPT',3));

Answer 2

Additional answer for CLOBs. Simpler code as I've hardcoded the table/column_name. The main difference is that v_line is now a CLOB and that the final parameter (length) is dropped from the call to DEFINE_COLUMN since it is only relevant to VARCHAR2.

If you are dealing in very large CLOBs (eg 10s or 100s MB plus), then I can foresee other challenges (memory, performance...).

create or replace function clob_dump
return tab_char_4000 AUTHID CURRENT_USER pipelined is
  v_line      clob;
  v_col_cnt   INTEGER;
  v_ind       NUMBER;
  rec_tab     dbms_sql.desc_tab;
  v_cursor    NUMBER;
begin
  --
  -- Identify the columns in the target and build the new query
  --
  v_cursor := dbms_sql.open_cursor;
  dbms_sql.parse(v_cursor, 'select sql_fulltext from gm_c where rownum <= 5', dbms_sql.native);
  dbms_sql.describe_columns(v_cursor, v_col_cnt, rec_tab);
  --
  FOR v_pos in 1..rec_tab.LAST LOOP
    v_line := rec_tab(v_pos).col_name;
    dbms_sql.define_column( v_cursor, v_pos, v_line);
  END LOOP;
  v_ind := dbms_sql.execute( v_cursor );
  --
  -- Fetch each row from the result set
  --
  LOOP
    v_ind := DBMS_SQL.FETCH_ROWS( v_cursor );
    EXIT WHEN v_ind = 0;
    pipe row( '=============================================================');
    --
    -- Go through each column and display it
    --
    FOR v_col_seq IN 1 .. rec_tab.COUNT LOOP
      -- Get the value
      dbms_sql.column_value( v_cursor, v_col_seq, v_line );
      pipe row( rpad(rec_tab(v_col_seq).col_name,35)||'>'||substr(v_line,1,100));
    END LOOP;
  END LOOP;
  return;
end clob_dump;
/

select * from table(clob_dump);