Home » SQL & PL/SQL » SQL & PL/SQL » Problem with %ROWTYPE  () 1 Vote
Problem with %ROWTYPE [message #247223] Mon, 25 June 2007 02:23 Go to next message
rajesh_bhadu
Messages: 48
Registered: June 2007
Member

Hi all,

I am creating a procedure as below

create or replace
PROCEDURE spGet_data (tablename VARCHAR2) is

 p_retcur  SYS_REFCURSOR ;

 at_rec nuauthusers%rowtype;
 
BEGIN
  Open p_retcur for 'select * from  '||tablename;
  
 loop
 fetch p_retcur into at_rec;
 exit when p_retcur%NOTFOUND;
   
   
   end loop;
   close p_retcur;
END spGet_data;


In my procedure i am passing table name as a parameter.

My problem is how can i use this tabalename parameter to declare rowtype 'at_rec nuauthusers%rowtype;';

If i m using like this.
....
at_rec p_retcur%rowtype;
.........


Error(6,9): PLS-00320: the declaration of the type of this expression is incomplete or malformed



if i m using like this way then

..........
at_rec tablename%rowtype;
..............

Error(6,9): PLS-00310: with %ROWTYPE attribute, 'TABLENAME' must name a table, cursor or cursor-variable



please give me your valuable suggestions.

Thanks

rajesh
Re: Problem with %ROWTYPE [message #247226 is a reply to message #247223] Mon, 25 June 2007 02:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't.
As your table is dynamic, you have to dynamically search for all columns.

Search for T. Kyte's print_table function (http://asktom.oracle.com). It is a nice example of how to do it.

Regards
Michel
Re: Problem with %ROWTYPE [message #247231 is a reply to message #247226] Mon, 25 June 2007 03:03 Go to previous messageGo to next message
rajesh_bhadu
Messages: 48
Registered: June 2007
Member
link is not working?????????? Embarassed Embarassed
Re: Problem with %ROWTYPE [message #247232 is a reply to message #247231] Mon, 25 June 2007 03:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, AskTom is currently down.
Wait a while.
Last week it lasted 4 or 5 days and there it is down since yesterday.

When it will be up, the full url to print_table will be: http://asktom.oracle.com/tkyte/print_table/

Regards
Michel
Re: Problem with %ROWTYPE [message #300381 is a reply to message #247232] Fri, 15 February 2008 04:40 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi Michel,
Can you please send me the code snippet as I was not able to search the code in AskTom.

Thanks,
Mona
Re: Problem with %ROWTYPE [message #300385 is a reply to message #300381] Fri, 15 February 2008 04:45 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You don't have to search, you just have to click on the link.

Regards
Michel
Previous Topic: ranking
Next Topic: How to assign record count to a variable
Goto Forum:
  


Current Time: Sun Dec 04 17:05:15 CST 2016

Total time taken to generate the page: 0.10284 seconds