Re: Dynamic SQL: dbms_sql package
Date: 1997/04/01
Message-ID: <5hs2eu$lgp_at_gap.cco.caltech.edu>#1/1
You can only define one variable per column, but you can do this:
DBMS_SQL.DEFINE_COLUMN(c, 1, random_value);
for i in 1..TEST.count LOOP
if DBMS_SQL.FETCH_ROWS(c) = 0 then
exit;
end if;
DBMS_SQL.COLUMN_VALUE(c, 1, test(i).cvalue);
END LOOP;
DEFINE_COLUMN is just a declaration of your intention to retrieve
a value. COLUMN_VALUE actuallys gets it. You don't have to use
the same variable in the DEFINE_COLUMN that you later use in the
COLUMN_VALUE procedure. You must however make sure you use the same
variable TYPE for the corresponding column numbers.
Hope that helps.
- Art Clarke aclarke_at_us.oracle.com
CNT78721 (drosario_at_americasm01.nt.com) wrote:
> Is there anyway that I can use a PL/SQL table in a
> dbms_sql.define_column declarations?
> PROBLEM: I want to declare variables for the columns in a dynamically
> created SELECT statement where the number of columns is variable.
> Example:
> Created a 2-column PL/SQL table called TEST(c_name VARCHAR2,c_value
> VARCHAR2) indexed by an integer i.Populated this table from a cursor
> which returned the column names that would be used in the dynamically
> created SELECT string. My define column statement looked like this:
> dbms_sql.define_column(cursor_id,i,test(i).c_value);
> END LOOP
> The ORACLE error message was there were 'too many declarations' for this
> define column.
> Thank you.
--Received on Tue Apr 01 1997 - 00:00:00 CEST