Re: PL/SQL Problem

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 30 Aug 2001 10:47:12 -0700
Message-ID: <9mlu7002lss@drn.newsguy.com>

In article <191C91BDFE8ED411B84400805FBE794C16AD2800_at_pfs21.ex.nus.edu.sg>, "Zeyar says...
>
>Hi Everybody,
>
>In PL/SQL, is there any way to refer to a field (attribute) in a CURSOR by
>its position (rather than by field name)?
>
>For example, in VB, we have some construct like
>rstMyRecordset.field(1).
>
>Actually, my problem is to assign a CURSOR with 256 fields into a VARRAY,
>and I want to do it using a loop (rather than using 256 assignment
>statements). Is there any way to do this?
>
>All of your helps are appreciated. Thanks.
>
>Best regards,
>Zeyar
>
>----------------------------------------------------------------------------
>Zeyar Aung
>Graduate Research Student
>School of Computing
>National University of Singapore
>Email: zeyaraun_at_comp.nus.edu.sg
>URL: http://www.comp.nus.edu.sg/~zeyaraun
>----------------------------------------------------------------------------
>
>

using DBMS_SQL -- yes.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create or replace   2 function query_to_array( p_query in varchar2 ) return arrayType   3 is

  4      l_theCursor     integer default dbms_sql.open_cursor;
  5      l_columnValue   varchar2(4000);
  6      l_status        integer;
  7      l_colCnt        number default 0;
  8      l_data          arrayType := arrayType();
  9      l_descTbl       dbms_sql.desc_tab;
 10  begin
 11      dbms_sql.parse(  l_theCursor,  p_query, dbms_sql.native );
 12  
 13      dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
 14  
 15      for i in 1 .. l_colCnt
 16      loop
 17          dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
 18       end loop;
 19  
 20      l_status := dbms_sql.execute(l_theCursor);
 21  
 22      /* just getting first row in this demo... */
 23  
 24      if ( dbms_sql.fetch_rows(l_theCursor) > 0 )
 25      then
 26          for i in 1 .. l_colCnt loop
 27              l_data.extend;
 28              dbms_sql.column_value( l_theCursor, i, l_data(i) );
 29          end loop;
 30      end if;
 31  
 32      dbms_sql.close_cursor(l_theCursor);
 33  
 34      return l_data;

 35 end query_to_array;
 36 /

Function created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> show error No errors.
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> declare   2 l_row arrayType;
  3 begin
  4 l_row := query_to_array( 'select * from all_objects' );   5

  6      for i in 1 .. l_row.count
  7      loop
  8          dbms_output.put_line( 'l_row(' || i || ') = ' || l_row(i) );
  9      end loop;

 10 end;
 11 /

l_row(1) = SYS
l_row(2) = /1005bd30_LnkdConstant
l_row(3) =
l_row(4) = 7056
l_row(5) =
l_row(6) = JAVA CLASS
l_row(7) = 28-AUG-01
l_row(8) = 28-AUG-01
l_row(9) = 2001-08-28:15:51:35
l_row(10) = VALID
l_row(11) = N
l_row(12) = N
l_row(13) = N

PL/SQL procedure successfully completed.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Thu Aug 30 2001 - 12:47:12 CDT