Fetch special datas of any table in a procedure with ref cursor [message #281073] |
Thu, 15 November 2007 11:37  |
novalex
Messages: 19 Registered: November 2007
|
Junior Member |
|
|
Hi!
I went through the Oracles's tutorial http://www.oracle.com/oramag/oracle/01-jan/o11sql.html
and especially I tried out this script:
CREATE OR REPLACE PROCEDURE show_me_the_money (
candidate_in IN VARCHAR2)
IS
TYPE refCur IS REF CURSOR;
money_cv refCur;
money_rec bush_funds%ROWTYPE;
BEGIN
OPEN money_cv FOR
'SELECT amount, source ' ||
'FROM ' ||candidate_in || '_funds ' ||
'ORDER BY amount DESC';
LOOP
FETCH money_cv INTO money_rec;
EXIT WHEN money_cv%NOTFOUND;
DBMS_OUTPUT.put_line (
money_rec.source || ': $' ||
TO_CHAR (money_rec.amount)
);
END LOOP;
CLOSE money_cv;
END;
Everything is ok so far. But I really would like to know what to do if I don't know the bush_funds table? I mean how should I declare (change) then this: "money_rec bush_funds%ROWTYPE;"?
I would like to change the function that any table could be used. Somthing like "anytable%ROWTYPE".
So that the function goes through all rows and all columns.
Maybe I can make "money_rec bush_funds%ROWTYPE;" somehow dynamic?
I tried this:
PROCEDURE show_me_the_money (candidate_in in user_tables.table_name%type)
IS
TYPE refCur IS REF CURSOR;
money_cv refCur;
money_rec candidate_in%ROWTYPE;
...
But unfortunatelly it doesn't work.
So what do I have to do?
Hope, you can help me.
thx
[Updated on: Thu, 15 November 2007 11:41] Report message to a moderator
|
|
|
|
Re: Fetch special datas of any table in a procedure with ref cursor [message #281078 is a reply to message #281073] |
Thu, 15 November 2007 11:51   |
novalex
Messages: 19 Registered: November 2007
|
Junior Member |
|
|
So, is there no way just to go through the fields of a table?
Is it possible only for already known (existing) tables?
What should I do if I need a special data (in a special field) of a table which name I don't know yet? So there must be a possibility to do that. The doc says that the ref cursor is dynamic but if I can't do that it is not dynamic enough.
|
|
|
|
|