Re: PL/SQL question for dynamic SQL

"Dirk" <dirk_at_ackernet.de> wrote in message news:5169dd26.0402250634.4bf9235e_at_posting.google.com...
| dear list,
|
| I want to write some PL/SQL code that does some SELECT statements that
| are generated dynamically. e.g the Table Name are passed as parameters....
|
| When compiling the Code I always get an error for the line
| 'brec bcur%ROWTYPE'
|
| PLS-00320: the declaration of the type of this expression is incomplete or
malformed
|
|
| Please advise...
|
| Dirk
|
|
|
| CREATE OR REPLACE.... (schema in VARCHAR2)
| ....
|
| ....
|
| AS
|
| TYPE bcur_t is REF CURSOR;
| bcur bcur_t;
| brec bcur%ROWTYPE;
|
| BEGIN
|
| OPEN bcur FOR 'SELECT * FROM '||schema;
| ..
| ..
| END;
%ROWTYPE is used to apply the structure of a cursor or table to the definition of a record variable

however, i would venture that your generic REF CURSOR has not structure, therefore is not valid for use with ROWTYPE

if you need to know the columns in the dynamically execute SELECT, i think you might have to drop back to DBMS_SQL and use its DESCRIBE_COLUMNS function

;-{ mcs Received on Wed Feb 25 2004 - 09:14:44 CST