Re: Dynamic SQL

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/06/08
Message-ID: <8hmq7v$t1p$1_at_nnrp1.deja.com>#1/1

In article <393E57FA.CEE0780F_at_nomagiclt.com>,   Dainius Januska <dainius_at_nomagiclt.com> wrote:
> I have the following procedure with dynamic SQL :
>
> -----------------------------
> DECLARE
> cid Number;
> sql_st Varchar2(100);
> rows_processed Number;
> unit_name dbms_sql.Varchar2_Table;
> BEGIN
>
> sql_st := 'select name from my_table';
> cid := dbms_sql.open_cursor;
> dbms_sql.parse(cid, sql_st, 0);
> dbms_sql.define_array(cid, 1, unit_name, 10, 1);
> rows_processed := dbms_sql.execute(cid);
> rows_processed := dbms_sql.fetch_rows(cid);
>
> --at this point rows_processed = 10
>
> dbms_sql.column_value(CID, 1, unit_name);
>
> --unit_name.count returns 0
> ---------------------------------------------------------
>
> That is unit_name is empty. Does anyone have an idea ?
>
> Thanks in advance,
> Dainius
>
>
your code worked fine for me:

ops$tkyte_at_8i> set serveroutput on
ops$tkyte_at_8i> DECLARE

  2     cid                      Number;
  3     sql_st                  Varchar2(100);
  4     rows_processed       Number;
  5     unit_name            dbms_sql.Varchar2_Table;
  6  BEGIN
  7     sql_st := 'select table_name from all_tables';
  8     cid := dbms_sql.open_cursor;
  9     dbms_sql.parse(cid, sql_st, 0);
 10     dbms_sql.define_array(cid, 1, unit_name, 10, 1);
 11     rows_processed := dbms_sql.execute(cid);
 12     rows_processed := dbms_sql.fetch_rows(cid);
 13
 14     --at this point rows_processed = 10
 15
 16     dbms_sql.column_value(CID, 1, unit_name);
 17     dbms_output.put_line( unit_name.count );
 18     --unit_name.count  returns  0

 19 end;
 20 /
10
^^ it is 10 for me....
PL/SQL procedure successfully completed.

can you put some dbms_outputs in the block and run it in sqlplus to see whats going on?

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Thu Jun 08 2000 - 00:00:00 CEST