Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic SQL error : Want to define dynamic type for a variable in Oracle 10g (Oracle 10g)
Dynamic SQL error : Want to define dynamic type for a variable in Oracle 10g [message #590372] Wed, 17 July 2013 09:21 Go to next message
abhenav123
Messages: 6
Registered: July 2013
Junior Member
I m trying to give table name as parameter to this function. It is compiling properly.
But when an anonymous block is created to call this table value, it has to be fetched into a variable of Rowtype of this inputted table. So I am not able to create any ROWTYPE variable for this table dynamically.

//Function
create or replace function instant_tabula(tabula in varchar) return sys_refcursor
as
cur sys_refcursor;
str varchar2(20000);
begin
str := 'select * from ' || tabula;
open cur for str;
return cur;
end;

//Anonymous Block
declare
curs sys_refcursor := instant_tabula('dept');
type cur_type is table of curs%rowtype;
var cur_type;
begin
loop
fetch curs into var;
exit when curs%notfound;
dbms_output.put_line(var.name || var.id);
end loop;
end;

[Updated on: Wed, 17 July 2013 09:21]

Report message to a moderator

Re: Dynamic SQL error : Want to define dynamic type for a variable in Oracle 10g [message #590377 is a reply to message #590372] Wed, 17 July 2013 09:26 Go to previous messageGo to next message
Michel Cadot
Messages: 58847
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

You have to use DBMS_SQL for this.
Have a look at T.Kyte's print_table function.

Regards
Michel
Re: Dynamic SQL error : Want to define dynamic type for a variable in Oracle 10g [message #590383 is a reply to message #590377] Wed, 17 July 2013 09:39 Go to previous messageGo to next message
abhenav123
Messages: 6
Registered: July 2013
Junior Member
Thanks Michel for reply,
But i tried using dbms_sql, but that enables us to execute,bind,etc query at runtime. But my problem is :
var curs%ROWTYPE; -- m getting error creating var of curs rowtype...

Once I m able to fetch value into this "var" ... i will be printing all these values...

Thanks ... [waiting for reply]
Re: Dynamic SQL error : Want to define dynamic type for a variable in Oracle 10g [message #590384 is a reply to message #590383] Wed, 17 July 2013 09:49 Go to previous messageGo to next message
cookiemonster
Messages: 10903
Registered: September 2008
Location: Rainy Manchester
Senior Member
abhenav123 wrote on Wed, 17 July 2013 15:39

var curs%ROWTYPE; -- m getting error creating var of curs rowtype...

That would be because you can't do that. So you have to take a different approach as Michel already suggested.
Re: Dynamic SQL error : Want to define dynamic type for a variable in Oracle 10g [message #590385 is a reply to message #590383] Wed, 17 July 2013 09:50 Go to previous messageGo to next message
BlackSwan
Messages: 22686
Registered: January 2009
Senior Member
a few syntax errors need to be fixed before code can be run

 1  declare
  2  curs sys_refcursor := instant_tabula('dept');
  3  type cur_type is table of curs%rowtype;
  4  var cur_type;
  5  begin
  6  loop
  7  fetch curs into var;
  8  exit when curs%notfound;
  9  dbms_output.put_line(var.name || var.id);
 10  end loop;
 11* end;
SQL> /
type cur_type is table of curs%rowtype;
                          *
ERROR at line 3:
ORA-06550: line 3, column 27:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 3, column 1:
PL/SQL: Item ignored
ORA-06550: line 7, column 17:
PLS-00597: expression 'VAR' in the INTO list is of wrong type
ORA-06550: line 7, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 9, column 26:
PLS-00302: component 'NAME' must be declared
ORA-06550: line 9, column 1:
PL/SQL: Statement ignored
Re: Dynamic SQL error : Want to define dynamic type for a variable in Oracle 10g [message #590386 is a reply to message #590385] Wed, 17 July 2013 09:59 Go to previous messageGo to next message
abhenav123
Messages: 6
Registered: July 2013
Junior Member
apart from syntax errors, if u can please suggest a solution to fetch cursor into var variable,
which still needs to know it's rowtype table ... if u can help with some short example ..

thank you.
Re: Dynamic SQL error : Want to define dynamic type for a variable in Oracle 10g [message #590388 is a reply to message #590386] Wed, 17 July 2013 10:03 Go to previous messageGo to next message
BlackSwan
Messages: 22686
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Dynamic SQL error : Want to define dynamic type for a variable in Oracle 10g [message #590392 is a reply to message #590386] Wed, 17 July 2013 10:36 Go to previous message
cookiemonster
Messages: 10903
Registered: September 2008
Location: Rainy Manchester
Senior Member
abhenav123 wrote on Wed, 17 July 2013 15:59
apart from syntax errors, if u can please suggest a solution to fetch cursor into var variable,
which still needs to know it's rowtype table ... if u can help with some short example ..

thank you.


Again, you can't.
Oracle has to know the column list for the type at compile time. It can't because the structure of the cursor isn't known at compile time.
If all the different selects that can by run by the cursor return the same column list (or equivalent at least, same number and type of columns) then you can declare a static record type based on a hard-coded list of columns and use that. Otherwise you can't use a table type variable at all.
Previous Topic: Build Parametric queries in SQL
Next Topic: How to retain special characters while extracting from DB in UNIX
Goto Forum:
  


Current Time: Wed Aug 20 22:02:21 CDT 2014

Total time taken to generate the page: 0.08094 seconds