Home » SQL & PL/SQL » SQL & PL/SQL » Regrading bulk upload(FOR ALL)
Regrading bulk upload(FOR ALL) [message #251612] Sun, 15 July 2007 08:51 Go to next message
rolex.mp
Messages: 161
Registered: February 2007
Senior Member
I am working on 10g Release 2

I am using two tables value_set_stg and fnd_flex_value_sets .

desc fnd_flex_value_sets
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 FLEX_VALUE_SET_ID                         NOT NULL NUMBER(10)
 FLEX_VALUE_SET_NAME                       NOT NULL VARCHAR2(60)
 LAST_UPDATE_DATE                          NOT NULL DATE
 LAST_UPDATED_BY                           NOT NULL NUMBER(15)
 CREATION_DATE                             NOT NULL DATE
 CREATED_BY                                NOT NULL NUMBER(15)
 LAST_UPDATE_LOGIN                         NOT NULL NUMBER(15)
 VALIDATION_TYPE                           NOT NULL VARCHAR2(1)
 PROTECTED_FLAG                            NOT NULL VARCHAR2(1)
 SECURITY_ENABLED_FLAG                     NOT NULL VARCHAR2(1)
 LONGLIST_FLAG                             NOT NULL VARCHAR2(1)
 FORMAT_TYPE                               NOT NULL VARCHAR2(1)
 MAXIMUM_SIZE                              NOT NULL NUMBER(3)
 ALPHANUMERIC_ALLOWED_FLAG                 NOT NULL VARCHAR2(1)
 UPPERCASE_ONLY_FLAG                       NOT NULL VARCHAR2(1)
 NUMERIC_MODE_ENABLED_FLAG                 NOT NULL VARCHAR2(1)
 DESCRIPTION                                        VARCHAR2(240)
 DEPENDANT_DEFAULT_VALUE                            VARCHAR2(60)
 DEPENDANT_DEFAULT_MEANING                          VARCHAR2(240)
 PARENT_FLEX_VALUE_SET_ID                           NUMBER(10)
 MINIMUM_VALUE                                      VARCHAR2(150)
 MAXIMUM_VALUE                                      VARCHAR2(150)
 NUMBER_PRECISION                                   NUMBER(2)
 SECURITY_GROUP_ID                                  NUMBER

desc VALUE_SET_STG

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 FLEX_VALUE_SET_NAME                       NOT NULL VARCHAR2(60)
 LAST_UPDATE_DATE                          NOT NULL DATE
 OWNER                                     NOT NULL NUMBER(15)
 CREATION_DATE                             NOT NULL DATE
 CREATED_BY                                NOT NULL NUMBER(15)
 LAST_UPDATE_LOGIN                         NOT NULL NUMBER(15)
 VALIDATION_TYPE                           NOT NULL VARCHAR2(1)
 PROTECTED_FLAG                            NOT NULL VARCHAR2(1)
 SECURITY_ENABLED_FLAG                     NOT NULL VARCHAR2(1)
 LONGLIST_FLAG                             NOT NULL VARCHAR2(1)
 FORMAT_TYPE                               NOT NULL VARCHAR2(1)
 MAXIMUM_SIZE                              NOT NULL NUMBER(3)
 ALPHANUMERIC_ALLOWED_FLAG                 NOT NULL VARCHAR2(1)
 UPPERCASE_ONLY_FLAG                       NOT NULL VARCHAR2(1)
 NUMERIC_MODE_ENABLED_FLAG                 NOT NULL VARCHAR2(1)
 DESCRIPTION                                        VARCHAR2(240)
 DEPENDANT_DEFAULT_VALUE                            VARCHAR2(60)
 DEPENDANT_DEFAULT_MEANING                          VARCHAR2(240)
 PARENT_FLEX_VALUE_SET_NAME                         VARCHAR2(60)
 MINIMUM_VALUE                                      VARCHAR2(150)
 MAXIMUM_VALUE                                      VARCHAR2(150)
 NUMBER_PRECISION                                   NUMBER(2)


I am using a cursor on value_set_stg and then I am bulk collecting the same into a table type .

A few transformations are done on the table type to update the data in it , and a few conditions are checked and if they are not met the corresponding data are deleted from the table type .

Now after these steps I want to update table fnd_flex_value_sets based on the data in the table type .
I have to do it using bulk upload (FORALL) but if the record that I wanna update doesn't exist then I have to insert the same .

How to do this using FORALL ?
Re: Regrading bulk upload(FOR ALL) [message #251615 is a reply to message #251612] Sun, 15 July 2007 09:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post your code and not just the explaination. Just the relevant part of your code not thousand lines.

Regards
Michel
Re: Regrading bulk upload(FOR ALL) [message #251620 is a reply to message #251615] Sun, 15 July 2007 09:51 Go to previous messageGo to next message
rolex.mp
Messages: 161
Registered: February 2007
Senior Member
type value_set_stg_type is table of value_set_stg%rowtype;
TYPE who_type IS RECORD
    (created_by NUMBER
   creation_date     DATE,
   last_updated_by   NUMBER,
   last_update_date  DATE,
   last_update_login NUMBER                                 
   );  
type who_blk_type is table of who_type;
 
PROCEDURE up_value_set_blk(
                    p_upload_phase IN VARCHAR2 DEFAULT NULL,
    		    p_upload_mode  IN VARCHAR2,
                    p_custom_mode  IN VARCHAR2 DEFAULT NULL) 
IS
 	cursor cur_flex_load is select * from value_set_stg;
 	value_set_t			value_set_stg_type;
        l_file_who        		who_blk_type;          
        l_count             		NUMBER;
        l_db_who     			who_blk_type;
BEGIN
 	open cur_flex_load;
 	fetch cur_flex_load bulk collect into value_set_t;

        FOR i in value_set_t.first..value_set_t.last LOOP
                IF (NOT is_upload_allowed 
     (p_custom_mode => p_custom_mode,  
     p_file_owner => value_set_t(i).owner,  
     p_file_last_update_date => value_set_t(i).last_update_date,
     p_db_last_updated_by => fnd_flex_t(i).last_updated_by,         
     p_db_last_update_date => fnd_flex_t(i).last_update_date,       	    
     x_file_who    => l_file_who(i))) THEN                  
		           value_set_t.delete(i)
		           		
		END IF;
        END LOOP; 

        FORALL i IN INDICES OF t
		DELETE FROM value_set_stg 
	          WHERE flex_value_set_name != value_set_t(i).flex_value_set_name;
 
        FORALL i IN INDICES OF t
	        UPDATE value_set_stg SET
	          owner = l_file_who(i).last_updated_by,
	          last_update_date = l_file_who(i).last_update_date,
	          	WHERE flex_value_set_name = value_set_t(i).flex_value_set_name;

        MERGE INTO fnd_flex_value_sets f USING value_set_stg s
	        ON (f.flex_value_set_name = s.flex_value_set_name)
	        WHEN MATCHED THEN
	            UPDATE SET f.last_updated_by = s.owner
                               ......
                WHEN NOT MATCHED THEN
	            INSERT(flex_value_set_id,flex_value_set_name,........) 
                    VALUES(fnd_flex_value_sets_s.NEXTVAL,
                           s.p_flex_value_set_name);

............



Sorry , I was not able to restrict the code much .

Here what I am doing is

Step 1 : Bulk Collecting the cursor into table type
Step 2 : Updating the table type based on transformation logic and deleting records in table which do not satisfy certain conditions ( I am using separate procedures for the same)
Step 3 : Deleting records from stage table(value_set_stg) correspoding to the records that are not found in table type .
Step 4 : Updating data in value_set_stg
Step 5 : Merging data into fnd_flex_value_sets based on value_set_stg

Here the problem is I have to delete records in the table which does not match the ones in the table type(means which are deleted)
Also following this I have to update the stage table n then merge it with the original table.

Instead of it is there any means to UPDATE using FORALL and to INSERT the records into the orginal table using FORALL when the record to be updated is not found ?

Let me know if I am not clear

[Updated on: Sun, 15 July 2007 23:23] by Moderator

Report message to a moderator

Re: Regrading bulk upload(FOR ALL) [message #251625 is a reply to message #251612] Sun, 15 July 2007 12:13 Go to previous messageGo to next message
rolex.mp
Messages: 161
Registered: February 2007
Senior Member
I was trying to use merge with forall

I tried the same and faced a error

SQL> desc a
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 N                                                  NUMBER
 N1                                                 VARCHAR2(30)

SQL> desc b
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 N                                                  NUMBER
 N1                                                 VARCHAR2(30)

SQL> create type t1 as object(n number,n1 varchar2(30));
  2  /

Type created.

SQL> create or replace type tt1 as table of t1;
  2  /

Type created.

  1  declare
  2    t tt1;
  3  begin
  4    select * bulk collect into t from a;
  5    forall i in t.first..t.last
  6      merge into b using (select * from table(t)) b1
  7             on (b.n=b1.n)
  8     when matched then
  9             update set
 10                     b.n=b1.n
 11     when not matched then
 12             insert(n1) values(b1.n1);
 13* end;
SQL> /
  select * bulk collect into t from a;
                               *
ERROR at line 4:
ORA-06550: line 4, column 32:
PL/SQL: ORA-00947: not enough values
ORA-06550: line 4, column 3:
PL/SQL: SQL Statement ignored
ORA-06550: line 6, column 5:
PLS-00435: DML statement without BULK In-BIND cannot be used inside FORALL




Let me know what should be done ?
Re: Regrading bulk upload(FOR ALL) [message #251629 is a reply to message #251625] Sun, 15 July 2007 13:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no FORALL MERGE (not yet).
Only INSERT, DELETE and UPDATE.

Regards
Michel
Re: Regrading bulk upload(FOR ALL) [message #251653 is a reply to message #251629] Sun, 15 July 2007 22:26 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
There are two ways.

You can perform a FORALL INSERT and capture the exceptions with the EXCEPTIONS INTO clause and then process the exceptions collection with FORALL UPDATE. This is a bit ugly and probably slow.

The better way is to LEFT OUTER JOIN to the target table in the main cursor and use it to determine whether an INSERT or an UPDATE will be required. Load INSERTS and UPDATES into separate arrays and submit them as separate FORALL statements.

Ross Leishman
Re: Regrading bulk upload(FOR ALL) [message #251676 is a reply to message #251612] Mon, 16 July 2007 01:17 Go to previous messageGo to next message
rolex.mp
Messages: 161
Registered: February 2007
Senior Member
In the case of FORALL and SAVE EXCEPTIONS

Let me know how to proceed . This is what I tried

BEGIN
	FORALL i IN INDICES OF value_set_t SAVE EXCEPTIONS
		INSERT INTO fnd_flex_value_sets   
                            (flex_value_set_id,
                            flex_value_set_name,
                            created_by,
                            creation_date,
                            last_updated_by,
                            last_update_date,
                            last_update_login)
                            VALUES 
                            (fnd_flex_value_sets_s.NEXTVAL,
                            value_set_t(i).flex_value_set_name,
                            l_file_who(i).created_by,
                            l_file_who(i).creation_date,
                            l_file_who(i).last_updated_by,
                            l_file_who(i).last_update_date,
                            l_file_who(i).last_update_login);
		EXCEPTION
        		WHEN dml_errors THEN
                                 -- FORALL UPDATE
END;



In the exceptions clause , How can I update only the statements for which insert failed using FORALL?
Re: Regrading bulk upload(FOR ALL) [message #251682 is a reply to message #251676] Mon, 16 July 2007 01:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Build an array with the indice of the errors, something like (indx is a table of integer):
EXCEPTION WHEN dml_errors THEN
  FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
    indx(i) := SQL%BULK_EXCEPTIONS(i).ERROR_INDEX;
  END LOOP;
  FORALL i IN VALUES OF indx 
    UPDATE ...
END;

Regards
Michel
Re: Regrading bulk upload(FOR ALL) [message #251695 is a reply to message #251653] Mon, 16 July 2007 02:07 Go to previous message
rolex.mp
Messages: 161
Registered: February 2007
Senior Member
Quote:

The better way is to LEFT OUTER JOIN to the target table in the main cursor and use it to determine whether an INSERT or an UPDATE will be required. Load INSERTS and UPDATES into separate arrays and submit them as separate FORALL statements.



I didn't understand what is meant here

How to use this logic ?
Previous Topic: oracle parallelism
Next Topic: Calling Procedure from External DLL
Goto Forum:
  


Current Time: Thu Dec 08 14:42:09 CST 2016

Total time taken to generate the page: 0.10743 seconds