On 05/25/2015 05:24 AM, Medhavi Mahansaria wrote:
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?
See here: http://www.postgresql.org/docs/9.4/interactive/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING
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.mahansaria@xxxxxxx
-- Adrian Klaver adrian.klaver@xxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
