Home » SQL & PL/SQL » SQL & PL/SQL » bulk collect (EE 10.2.0.3.0)
icon7.gif  bulk collect [message #318731] Wed, 07 May 2008 15:55 Go to next message
djehres
Messages: 3
Registered: June 2005
Location: Garland, TX
Junior Member
I am trying to use a procedure to populate one partition from another partition in the same FACT table. I use

MONTH_ID -1 to get the data to go into the partition. I am getting the error below. Does anybody know what this means?

Any help is appreciated.




CREATE OR REPLACE PROCEDURE process_all_rows (limit_in IN PLS_INTEGER DEFAULT 100)
IS
CURSOR snapshot_cur IS
SELECT PTD_MERCH_EARNED_PTS,
PTD_MERCH_EARNED_IND,
...


PTD_ADJUSTMENT_IND,
PTD_EXPIRED_PTS,
PTD_CLOSED_PTS
from F_TABLE_NAME PARTITION(P200711);

TYPE snapshot_aat IS TABLE OF snapshot_cur%ROWTYPE
INDEX BY PLS_INTEGER;

l_snapshot snapshot_aat;
BEGIN
OPEN snapshot_cur;
LOOP
FETCH snapshot_cur BULK COLLECT INTO l_snapshot LIMIT limit_in;

FOR indx IN 1..l_snapshot.COUNT
LOOP
insert into F_TABLE_NAME PARTITION(P200710)(l_snapshot(indx)); <== LINE 208
END LOOP;

EXIT WHEN l_snapshot.COUNT < limit_in;

END LOOP;

CLOSE snapshot_cur;
END;




> show errors
Errors for PROCEDURE PROCESS_ALL_ROWS:

LINE/COL ERROR
-------- -----------------------------------------------------------------
208/13 PL/SQL: SQL Statement ignored
208/73 PL/SQL: ORA-00917: missing comma
Re: bulk collect [message #318732 is a reply to message #318731] Wed, 07 May 2008 16:01 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
>Does anybody know what this means?
it means the INSERT statement is not valid SQL syntax.
It appears to be missing a keyword & possibly other errors.
Re: bulk collect [message #318803 is a reply to message #318731] Thu, 08 May 2008 01:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why not just "insert select"?

Regards
Michel
Re: bulk collect [message #318818 is a reply to message #318731] Thu, 08 May 2008 01:38 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
It seems you have high time to start reading the documentation, found eg. online on tahiti.oracle.com/.

PL/SQL User's Guide and Reference
Chapter 5 Using PL/SQL Collections and Records
Assigning values to Records
Example 5-50 Inserting a PL/SQL Record Using %ROWTYPE

Reading manuals really does not require expert, so I wonder why did you put this into the Expert forum.
Previous Topic: Query Giving Wrong Output
Next Topic: How to multiply one column data to another column
Goto Forum:
  


Current Time: Mon Dec 05 09:05:23 CST 2016

Total time taken to generate the page: 0.11313 seconds