Home » SQL & PL/SQL » SQL & PL/SQL » USING OBJECT TYPE WITH BULK COLLECT
USING OBJECT TYPE WITH BULK COLLECT [message #348087] Mon, 15 September 2008 10:33 Go to next message
neurons
Messages: 4
Registered: September 2008
Junior Member
No Message Body

[Updated on: Mon, 15 September 2008 10:43]

Report message to a moderator

Re: Read forum guide [message #348092 is a reply to message #348087] Mon, 15 September 2008 10:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No Message Body
Re: USING OBJECT TYPE WITH BULK COLLECT [message #348098 is a reply to message #348087] Mon, 15 September 2008 10:59 Go to previous messageGo to next message
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;



Re: USING OBJECT TYPE WITH BULK COLLECT [message #348100 is a reply to message #348098] Mon, 15 September 2008 11:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You didn't read OraFAQ Forum Guide:
- format your post
- copy and paste your session
- give your Oracle version (4 decimals)
- don't post your title in UPPER case

Of course, BULK COLLECT will return an array and not a single value or record.

Regards
Michel

[Updated on: Mon, 15 September 2008 11:03]

Report message to a moderator

Using Bulk Collect with Ref cursors [message #348134 is a reply to message #348087] Mon, 15 September 2008 14:19 Go to previous messageGo to next message
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

Re: Using Bulk Collect with Ref cursors [message #348135 is a reply to message #348134] Mon, 15 September 2008 14:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
read and FOLLOW OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Use SQL*Plus and copy and paste your session.

AND READ THE ANSWER WE GIVE BEFORE POSTING THE SAME ERROR.

Regards
Michel
Re: USING OBJECT TYPE WITH BULK COLLECT [message #348247 is a reply to message #348087] Tue, 16 September 2008 03:47 Go to previous message
panyam
Messages: 146
Registered: May 2008
Senior Member

Hi neurons

Follw the Link and you will get the idea to obtain the desired result.

http://www.adp-gmbh.ch/ora/plsql/bc/index.html
Previous Topic: Hints for Date Range
Next Topic: MV Refresh Not Happen Automatically
Goto Forum:
  


Current Time: Fri Dec 09 21:03:42 CST 2016

Total time taken to generate the page: 0.13293 seconds