Home » SQL & PL/SQL » SQL & PL/SQL » Fetch special datas of any table in a procedure with ref cursor
Fetch special datas of any table in a procedure with ref cursor [message #281073] Thu, 15 November 2007 11:37 Go to next message
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 #281076 is a reply to message #281073] Thu, 15 November 2007 11:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ You can't do that
2/ Learn with something that is younger than a 7 years old article. You no more code PL/SQL that way now.

Regards
Michel
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 Go to previous messageGo to next message
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.
Re: Fetch special datas of any table in a procedure with ref cursor [message #281081 is a reply to message #281078] Thu, 15 November 2007 12:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Application Developer's Guide - Fundamentals
PL/SQL User's Guide and Reference

Regards
Michel
Re: Fetch special datas of any table in a procedure with ref cursor [message #281084 is a reply to message #281078] Thu, 15 November 2007 12:12 Go to previous message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
http://www.quest-pipelines.com/newsletter-v3/0202_E.htm
Previous Topic: display records for Dates every 7 days
Next Topic: Average every 7 days
Goto Forum:
  


Current Time: Sun Dec 04 19:00:38 CST 2016

Total time taken to generate the page: 0.08208 seconds