Home » SQL & PL/SQL » SQL & PL/SQL » Regarding bulk insert (merged topics)
Regarding bulk insert (merged topics) [message #240983] Mon, 28 May 2007 02:26 Go to next message
rolex.mp
Messages: 161
Registered: February 2007
Senior Member
I wanna use bulk insert for populating a table but i get an error wen i use the same :

The code and the error message are pasted below :

declare
  P_MODE VARCHAR2(5) := 'PRE';
  P_TG NUMBER := 1;
  l_flag varchar2(1);
  TYPE ARRAY IS TABLE OF dba_objects.owner%TYPE;
  TYPE ARRAY1 IS TABLE OF dba_objects.object_type%TYPE;
  TYPE ARRAY2 IS TABLE OF dba_objects.object_name%TYPE;
  c_owner ARRAY;
  c_object_name ARRAY1;
  c_object_type ARRAY2;
  cursor c_invalid_object is
  select owner ,object_name ,object_type from dba_objects where status='INVALID' and owner='APPS';
BEGIN
      l_flag := 'Y';
      OPEN c_invalid_object;
          LOOP
                FETCH c_invalid_object BULK COLLECT INTO c_owner,c_object_name,c_object_type LIMIT 100;
                FORALL i IN 1..c_owner.count
                        INSERT INTO  AD_INVALID_OBJECTS_TEMP(TG_ID,SCHEMA,OBJECT,OBJECT_TYPE,pre_flag,created_date)
                values(p_tg,c_owner(i),c_object_name(i),c_object_type(i),l_flag,sysdate);
                EXIT WHEN c_invalid_object%NOTFOUND;
          END LOOP;
      CLOSE c_invalid_object;
      commit;
EXCEPTION
  WHEN OTHERS THEN
  dbms_output.put_line('errror occur with code : '||sqlcode||' in GEN_INVALID_OBJECTS');
  dbms_output.put_line('error message : '||SQLERRM);
END GEN_INVALID_OBJECTS;
/




errror occur with code : -6502 in GEN_INVALID_OBJECTS
error message : ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated
Bind



Let me know what should be done ?

[Updated on: Mon, 28 May 2007 03:03] by Moderator

Report message to a moderator

can we bulk collect cursor on multiple nested tables ? [message #240987 is a reply to message #240983] Mon, 28 May 2007 02:41 Go to previous messageGo to next message
rolex.mp
Messages: 161
Registered: February 2007
Senior Member
declare
TYPE ARRAY IS TABLE OF dba_objects.owner%TYPE;
TYPE ARRAY1 IS TABLE OF dba_objects.object_type%TYPE;
TYPE ARRAY2 IS TABLE OF dba_objects.object_name%TYPE;
c_owner ARRAY;
c_object_name ARRAY1;
c_object_type ARRAY2;
cursor c_invalid_object is
select owner,object_name,object_type from dba_objects where status='INVALID' and owner='APPS';
BEGIN
      l_flag := 'Y';
      OPEN c_invalid_object;
          LOOP
                FETCH c_invalid_object BULK COLLECT INTO c_owner,c_object_name,c_object_type LIMIT 100;
.................



I am getting an error if i run this block saying

errror occur with code : -6502 in GEN_INVALID_OBJECTS
error message : ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated
Bind
Re: Regarding bulk insert [message #240995 is a reply to message #240983] Mon, 28 May 2007 03:01 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I'm guessing here, but try replacing l_flag in the INSERT VALUES clause with 'Y'. Possibly you can't mix scalars with arrays..... perhaps.

Also, place your EXIT WHEN clause immediately below the FETCH - this is actually more likely the problem.

Also, get rid of the EXCEPTION clause - you are just masking the real line number where the error occurred.

Ross Leishman

Re: Regarding bulk insert (merged topics) [message #241083 is a reply to message #240983] Mon, 28 May 2007 05:22 Go to previous messageGo to next message
rolex.mp
Messages: 161
Registered: February 2007
Senior Member
But actually the error occurs due to this stmt

FETCH c_invalid_object BULK COLLECT INTO c_owner,c_object_name,c_object_type LIMIT 100;

any idea ?
Re: Regarding bulk insert (merged topics) [message #241121 is a reply to message #241083] Mon, 28 May 2007 07:04 Go to previous messageGo to next message
ukumarjsr2
Messages: 6
Registered: May 2007
Location: Mumbai
Junior Member
Hi,


TYPE ARRAY IS TABLE OF dba_objects.owner%TYPE;
TYPE ARRAY1 IS TABLE OF dba_objects.object_type%TYPE;
TYPE ARRAY2 IS TABLE OF dba_objects.object_name%TYPE;
c_owner ARRAY;

I think you should use ARRAY0 insted of ARRAY.

Re: Regarding bulk insert (merged topics) [message #241234 is a reply to message #241121] Mon, 28 May 2007 23:41 Go to previous message
bonker
Messages: 402
Registered: July 2005
Senior Member
why not just insert into ... select?




INSERT INTO  AD_INVALID_OBJECTS_TEMP(TG_ID,SCHEMA,OBJECT,OBJECT_TYPE,pre_flag,created_date)

select p_tg,owner ,object_name ,object_type,l_flag,sysdate from dba_objects where status='INVALID' and owner='APPS';





Previous Topic: How to insert Folder path name?
Next Topic: How to execute a query as parameter/variable in PLSQL block
Goto Forum:
  


Current Time: Sat Dec 03 22:07:37 CST 2016

Total time taken to generate the page: 0.10047 seconds