Home » SQL & PL/SQL » SQL & PL/SQL » Weak REF CURSOR and %ROWTYPE
icon5.gif  Weak REF CURSOR and %ROWTYPE [message #193023] Thu, 14 September 2006 10:11 Go to next message
Kruppi
Messages: 2
Registered: September 2006
Junior Member
Hi,

i have a little problem writing a pl/sql procedure that copy data from one table to another. The procedure should be using two parameters, the table name and a commit count, to generate dynamic sql statements.

The procedure should do the following :

1. Truncate the first table
2. Load the data from the second table in steps (BULK COLLECT)
3. Write the data in steps to the first table (FORALL)

The procedure is running fine when i use static sql statements, but because we have a lot of tables i have the same code a lot of times. So i want to write a general procedure that could take the table name.

I can't use a
INSERT INTO t1 SELECT * FROM t2
Statement because we want do a commit every 10000 or 100000 rows.

This is what i try, but i get an 'PLS-00320: the declaration of the type of this expression is incomplete or malformed' at the line of the GENERIC_ROW Declaration.

PROCEDURE RUN_GENERIC(p_TableName IN VARCHAR2, p_CommitCount IN NUMBER) IS

    genericCursor SYS_REFCURSOR;

    TYPE GENERIC_ROW IS TABLE OF genericCursor%ROWTYPE;

    genericRow GENERIC_ROW;

BEGIN

    EXECUTE IMMEDIATE('TRUNCATE TABLE t_' || p_TableName);

    OPEN genericCursor FOR 'SELECT * FROM v_' || p_TableName;

    LOOP

        FETCH genericCursor BULK COLLECT INTO genericRow LIMIT p_CommitCount;

        FORALL i IN genericRow.FIRST..genericRow.LAST SAVE EXCEPTIONS
            EXECUTE IMMEDIATE('INSERT INTO t_' || p_TableName || ' VALUES(:1)') USING genericRow(i);

        COMMIT;

    END LOOP;

    CLOSE genericCursor;

END RUN_GENERIC;


What i see from the documentation and asktom is, that the %ROWTYPE probably only work with strong ref cursors.

But how can i store the row from the cursor ? Is there a solution ?

Matthias

PS: Just for info, we're using Oracle 9.2.0.6.0
Re: Weak REF CURSOR and %ROWTYPE [message #193060 is a reply to message #193023] Thu, 14 September 2006 13:24 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
the really difficult way would be to stuff the whole record into a varchar2(32k) pl/sql table as a delimited string and then using DBMS_SQL.describe on the original query to determine the column datatypes and sizes. I doubt I'd attempt it though...

Maybe you can avoid the need for the commit interval by Appending into the target table (using a hint)...
Re: Weak REF CURSOR and %ROWTYPE [message #193155 is a reply to message #193060] Fri, 15 September 2006 01:53 Go to previous message
Kruppi
Messages: 2
Registered: September 2006
Junior Member
Hi,

thank you for your fast answer. I think the first solution, with the varchar2 table, is little bit to insecure. When the data is longer than a varchar2 then the hole thing crashes.

So, i think i will do it without the bulk operations and will use the append hint. I hope this will not be result in a much worser performance.

Matthias
Previous Topic: regarding bulk collect
Next Topic: How to send a email alerter using PL/SQL?
Goto Forum:
  


Current Time: Wed Dec 07 16:14:25 CST 2016

Total time taken to generate the page: 0.05645 seconds