Hello,
I am porting my application from Oracle to PostgreSQL.
We are using BULK COLLECT functionality of oracle.
How can i change the 'BULK COLLECT' fetch of the data from the cursor to
make if compatible for pg/plsql?
A small example is as below (This is just an example and the query is much
more complex which returns huge amount of data)
CREATE OR REPLACE FUNCTION abc() RETURNS VOID AS $body$
DECLARE
l_data b%ROWTYPE;
POPULATE_STATS CURSOR IS
(
SELECT * from a
)
; // query returning a huge amount of data
BEGIN
OPEN POPULATE_STATS;
LOOP
FETCH POPULATE_STATS BULK COLLECT INTO l_data LIMIT 1000;
IF POPULATE_STATS%ROWCOUNT > 0
THEN
FORALL i IN 1..l_data.COUNT
INSERT INTO b VALUES l_data(i);
END IF;
IF NOT FOUND THEN EXIT; END IF;
END LOOP;
CLOSE POPULATE_STATS;
EXCEPTION
WHEN OTHERS THEN
CODE := SQLSTATE;
MSG := SQLERRM;
INSERT INTO tracker VALUES (CODE,MSG,LOCALTIMESTAMP);
RAISE NOTICE 'SQLERRM';
RAISE NOTICE '%', SQLSTATE;
RAISE NOTICE '%', MSG;
END;
$body$
LANGUAGE PLPGSQL;
How can i change the 'BULK COLLECT' fetch of the data from the cursor to
make if compatible for pg/plsql?
Thanks & Regards
Medhavi Mahansaria
Mailto: medhavi.mahansa...@tcs.com
=====-----=====-----=====
Notice: The information contained in this e-mail
message and/or attachments to it may contain
confidential or privileged information. If you are
not the intended recipient, any dissemination, use,
review, distribution, printing or copying of the
information contained in this e-mail message
and/or attachments to it are strictly prohibited. If
you have received this communication in error,
please notify us by reply e-mail or telephone and
immediately and permanently delete the message
and any attachments. Thank you
