|
|
Re: USING OBJECT TYPE WITH BULK COLLECT [message #348098 is a reply to message #348087] |
Mon, 15 September 2008 10:59   |
neurons
Messages: 4 Registered: September 2008
|
Junior Member |
|
|
HELLO EXPERTS,
I am trying to use BULK COLLECT to insert/update into the table.
Data sets are first collected to cursor before update/INSERT.
This is just a small example I came up with to ease off the prob.
please help
declare
type r_cursor is REF CURSOR;
c_emp r_cursor;
type rec_emp is record
(
USER_ID varchar2(20),
USERNAME number(20)
);
recemp rec_emp;
BEGIN
sql_stmt := 'select USER_ID,USERNAME from all_users';
OPEN c_emp FOR SELECT sql_stmt ;
fetch c_emp BULK COLLECT into recemp;
WHILE c_emp%FOUND LOOP
dbms_output.put_line(recemp(i));
end loop;
CLOSE c_emp;
END;
|
|
|
|
Using Bulk Collect with Ref cursors [message #348134 is a reply to message #348087] |
Mon, 15 September 2008 14:19   |
neurons
Messages: 4 Registered: September 2008
|
Junior Member |
|
|
Hello Experts,
I am trying to implement something like this.
The procedure uses BULK INSERT to load/update data from REF CURSOR to any table. I am going for BULK INSERT/OBJECT TYPE as this is one of the fastest way or please let me know if there is any better way out.
I did successfully run the same procedure defining each variables individually but I need to define it in an object.
DECLARE
sql_stmt varchar2(100);
TYPE curs_t is REF CURSOR;
curs curs_t;
TYPE names_t IS object
(
ALLID VARCHAR(20),--all_users.USER_ID%TYPE,
allnames VARCHAR(20) --all_users.USERNAME%TYPE
);
names names_t;
BEGIN
sql_stmt := 'select USER_ID,USERNAME from all_users';
OPEN curs FOR sql_stmt;
FETCH curs BULK COLLECT INTO NAMES;
FOR i IN 1..curs%rowcount
LOOP
dbms_output.put_line('USERID'||' '||'USERNAME '||'=>>'||ALLID(I)||'::'||allnames(I));
END LOOP;
CLOSE curs;
END;
The Error I get is shown below:
39 1 ORA-06550: line 5, column 1:
PLS-00540: object not supported in this context.
ORA-06550: line 14, column 34:
PLS-00497: cannot mix between single row and multi-row (BULK) in INTO list
ORA-06550: line 17, column 69:
PL/SQL: Statement ignored
[Updated on: Mon, 15 September 2008 14:21] Report message to a moderator
|
|
|
|
|