CREATE OR REPLACE TYPE OBJ_TXN_LOG AS OBJECT 
(
       SALES_NO     VARCHAR2(24)
      ,sALES_SEQ        NUMBER(4)
) ;

CREATE OR REPLACE TYPE TYP_TBL_TXN_LOG
    AS TABLE OF OBJ_TXN_LOG ;

CREATE TABLE SLS_UPDT_LOG
(
      TXN_LOG TYP_TBL_TXN_LOG
)
NESTED TABLE TXN_LOG STORE AS NESTED_TAB 
TABLESPACE PCARDUSR_DAT_MT01 ;
 

DECLARE
	CURSOR CUR_TMP 
	IS
		SELECT
			AA
			,BB
			,CC
			,DD
		FROM TABLE1 ;

	TBL_TXN_LOG	TYP_TBL_TXN_LOG  := TYP_TBL_TXN_LOG () ;
	LV_SQL_QRY	VARCHAR2(4000) ; 
        I               NUMBER := 0 ;
BEGIN
	FOR REC_TMP IN CUR_TMP LOOP

                I = I + 1 ;
		LV_SQL_QRY := ' UPDATE TABLE2 N PARTITION( TABLE2_01 ) '
			      || ' SET N.X = ' || CHR(39) || REC_TMP.AA || CHR(39)
			      || ' WHERE N.P = ' || CHR(39) || REC_TMP.BB || CHR(39)
			      || ' WHERE N.T = ' || CHR(39) || REC_TMP.CC || CHR(39) 
			      || ' RETURNING N.SALES_NO, N.SALES_SEQ INTO :1, :2 ' ;

			EXECUTE IMMEDIATE( LV_SQL_QRY ) 
			RETURNING INTO TBL_TXN_LOG(I).SALES_NO, TBL_TXN_LOG(I).SALES_SEQ ;
	END LOOP ;

	INSERT INTO SLS_UPDT_LOG VALUES( TBL_TXN_LOG ) ;

	COMMIT WORK ;

END ;

CREATE OR REPLACE TYPE OBJ_TXN_LOG AS OBJECT 
(
       SALES_NO         VARCHAR2(24)
      ,SALES_SEQ        NUMBER(4)
) ;

CREATE OR REPLACE TYPE TYP_TBL_TXN_LOG
    AS TABLE OF OBJ_TXN_LOG ;

CREATE TABLE SLS_UPDT_LOG
(
      TXN_LOG TYP_TBL_TXN_LOG
)
NESTED TABLE TXN_LOG STORE AS NESTED_TAB 
TABLESPACE PCARDUSR_DAT_MT01 ;
       

CREATE TABLE SALES_DETAIL ( SALES_NO  VARCHAR2(24), SALES_SEQ NUMBER(4), CLOSING_DATE  DATE ) 
TABLESPACE PCARDUSR_DAT_MT01 ;

CREATE TABLE SALES_DETAIL_RPT ( SALES_NO  VARCHAR2(24), SALES_SEQ NUMBER(4), CLOSING_DATE  DATE ) 
TABLESPACE PCARDUSR_DAT_MT01;

INSERT INTO SALES_DETAIL( SALES_NO, SALES_SEQ , CLOSING_DATE ) 
VALUES( '01234', 1, TRUNC(SYSDATE-20) ) ;

INSERT INTO SALES_DETAIL( SALES_NO, SALES_SEQ , CLOSING_DATE ) 
VALUES( '01235', 1, TRUNC(SYSDATE-20) ) ;

INSERT INTO SALES_DETAIL( SALES_NO, SALES_SEQ , CLOSING_DATE ) 
VALUES( '01236', 1, TRUNC(SYSDATE-20) ) ;

INSERT INTO SALES_DETAIL_RPT( SALES_NO, SALES_SEQ , CLOSING_DATE ) 
VALUES( '01234', 1, NULL ) ;

INSERT INTO SALES_DETAIL_RPT( SALES_NO, SALES_SEQ , CLOSING_DATE ) 
VALUES( '01235', 1, NULL ) ;

INSERT INTO SALES_DETAIL_RPT( SALES_NO, SALES_SEQ , CLOSING_DATE ) 
VALUES( '01236', 1, NULL ) ;

COMMIT ;




create or replace
procedure procedure1 as 
	CURSOR CUR_TMP 
	IS
		SELECT
			 SALES_NO
       			,SALES_SEQ
       			,CLOSING_DATE
		FROM SALES_DETAIL ;

	TBL_TXN_LOG	TYP_TBL_TXN_LOG  := TYP_TBL_TXN_LOG () ;
	LV_SQL_QRY	VARCHAR2(4000) ; 
  	I           	NUMBER := 0 ;
BEGIN
	FOR REC_TMP IN CUR_TMP LOOP

		I := I + 1 ;
		TBL_TXN_LOG.EXTEND(1) ;
LV_SQL_QRY := ' UPDATE SALES_DETAIL_RPT T2 SET T2.CLOSING_DATE = ' || CHR(39) || 
REC_TMP.CLOSING_DATE || CHR(39)
			      || ' WHERE T2.SALES_NO = ' || CHR(39) || 
REC_TMP.SALES_NO || CHR(39)
			      || ' AND T2.SALES_SEQ = ' || CHR(39) || REC_TMP.SALES_SEQ || CHR(39) 
			      || ' RETURNING T2.SALES_NO , T2.SALES_SEQ INTO :1, :2 ' ;

		EXECUTE IMMEDIATE( LV_SQL_QRY ) 
			RETURNING INTO TBL_TXN_LOG(1).SALES_NO, TBL_TXN_LOG(1).SALES_SEQ ;

	END LOOP ;

	INSERT INTO SLS_UPDT_LOG VALUES( TBL_TXN_LOG ) ;

	COMMIT WORK ;

EXCEPTION
    WHEN OTHERS THEN
    	  ROLLBACK WORK ;
          DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE ) ;
END ;

LV_SQL_QRY := ' UPDATE SALES_DETAIL_RPT PARTITION( SALES_DTL_1201 ) T2 ' 
	      || ' SET T2.CLOSING_DATE = ' || CHR(39) || REC_TMP.CLOSING_DATE || CHR(39)
	      || ' WHERE T2.SALES_NO = ' || CHR(39) || REC_TMP.SALES_NO || CHR(39)
	      || ' AND T2.SALES_SEQ = ' || CHR(39) || REC_TMP.SALES_SEQ || CHR(39) 
	      || ' RETURNING T2.SALES_NO , T2.SALES_SEQ INTO :1, :2 ' ;

SCOTT@orcl_11gR2> CREATE OR REPLACE TYPE OBJ_TXN_LOG AS OBJECT
  2  (
  3  	    SALES_NO	     VARCHAR2(24)
  4  	   ,SALES_SEQ	     NUMBER(4)
  5  ) ;
  6  /

Type created.

SCOTT@orcl_11gR2> CREATE OR REPLACE TYPE TYP_TBL_TXN_LOG
  2  	 AS TABLE OF OBJ_TXN_LOG ;
  3  /

Type created.

SCOTT@orcl_11gR2> CREATE TABLE SLS_UPDT_LOG
  2  (
  3  	   TXN_LOG TYP_TBL_TXN_LOG
  4  )
  5  NESTED TABLE TXN_LOG STORE AS NESTED_TAB
  6  --TABLESPACE PCARDUSR_DAT_MT01
  7  ;

Table created.

SCOTT@orcl_11gR2> CREATE TABLE SALES_DETAIL
  2  ( SALES_NO  VARCHAR2(24), SALES_SEQ NUMBER(4), CLOSING_DATE  DATE )
  3  --TABLESPACE PCARDUSR_DAT_MT01
  4  ;

Table created.

SCOTT@orcl_11gR2> CREATE TABLE SALES_DETAIL_RPT
  2  ( SALES_NO  VARCHAR2(24), SALES_SEQ NUMBER(4), CLOSING_DATE  DATE )
  3  --TABLESPACE PCARDUSR_DAT_MT01
  4  ;

Table created.

SCOTT@orcl_11gR2> BEGIN
  2    INSERT INTO SALES_DETAIL( SALES_NO, SALES_SEQ , CLOSING_DATE )
  3  	 VALUES( '01234', 1, TRUNC(SYSDATE-20) ) ;
  4    INSERT INTO SALES_DETAIL( SALES_NO, SALES_SEQ , CLOSING_DATE )
  5  	 VALUES( '01235', 1, TRUNC(SYSDATE-20) ) ;
  6    INSERT INTO SALES_DETAIL( SALES_NO, SALES_SEQ , CLOSING_DATE )
  7  	 VALUES( '01236', 1, TRUNC(SYSDATE-20) ) ;
  8    INSERT INTO SALES_DETAIL_RPT( SALES_NO, SALES_SEQ , CLOSING_DATE )
  9  	 VALUES( '01234', 1, NULL ) ;
 10    INSERT INTO SALES_DETAIL_RPT( SALES_NO, SALES_SEQ , CLOSING_DATE )
 11  	 VALUES( '01235', 1, NULL ) ;
 12    INSERT INTO SALES_DETAIL_RPT( SALES_NO, SALES_SEQ , CLOSING_DATE )
 13  	 VALUES( '01236', 1, NULL ) ;
 14    COMMIT ;
 15  END;
 16  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> create or replace procedure procedure1
  2  as
  3    CURSOR CUR_TMP IS
  4    SELECT SALES_NO
  5  	    , SALES_SEQ
  6  	    , CLOSING_DATE
  7    FROM   SALES_DETAIL ;
  8    TBL_TXN_LOG   TYP_TBL_TXN_LOG  := TYP_TBL_TXN_LOG () ;
  9    LV_SQL_QRY    VARCHAR2(4000) ;
 10    I	     NUMBER := 0 ;
 11  BEGIN
 12    FOR REC_TMP IN CUR_TMP LOOP
 13  	 I := I + 1 ;
 14  	 TBL_TXN_LOG.EXTEND(1) ;
 15  	 TBL_TXN_LOG(I) := OBJ_TXN_LOG (NULL, NULL);
 16  	 LV_SQL_QRY :=
 17  	   ' UPDATE SALES_DETAIL_RPT T2
 18  	     SET    T2.CLOSING_DATE = ' || CHR(39) || REC_TMP.CLOSING_DATE || CHR(39) ||
 19  	   ' WHERE  T2.SALES_NO = ' || CHR(39) || REC_TMP.SALES_NO || CHR(39) ||
 20  	   ' AND    T2.SALES_SEQ = ' || CHR(39) || REC_TMP.SALES_SEQ || CHR(39) ||
 21  	   ' RETURNING T2.SALES_NO, T2.SALES_SEQ INTO :1, :2 ' ;
 22  	 EXECUTE IMMEDIATE( LV_SQL_QRY )
 23  	   RETURNING INTO TBL_TXN_LOG(I).SALES_NO, TBL_TXN_LOG(I).SALES_SEQ ;
 24    END LOOP ;
 25    INSERT INTO SLS_UPDT_LOG VALUES( TBL_TXN_LOG ) ;
 26    COMMIT WORK ;
 27  EXCEPTION
 28    WHEN OTHERS THEN
 29  	 ROLLBACK WORK ;
 30  	 DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE ) ;
 31  END ;
 32  /

Procedure created.

SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> EXEC procedure1

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> SELECT * FROM sales_detail_rpt
  2  /

SALES_NO                  SALES_SEQ CLOSING_D
------------------------ ---------- ---------
01234                             1 26-FEB-12
01235                             1 26-FEB-12
01236                             1 26-FEB-12

3 rows selected.

SCOTT@orcl_11gR2> SELECT * FROM sls_updt_log
  2  /

TXN_LOG(SALES_NO, SALES_SEQ)
--------------------------------------------------------------------------------
TYP_TBL_TXN_LOG(OBJ_TXN_LOG('01234', 1), OBJ_TXN_LOG('01235', 1), OBJ_TXN_LOG('0
1236', 1))


1 row selected.

SCOTT@orcl_11gR2>

CREATE OR REPLACE TYPE OBJ_TXN_LOG AS OBJECT 
(
       SALES_NO         VARCHAR2(24)
      ,SALES_SEQ        NUMBER(4)
) ;

CREATE OR REPLACE TYPE TYP_TBL_TXN_LOG
    AS TABLE OF OBJ_TXN_LOG ;

CREATE TABLE SLS_UPDT_LOG
(
      TXN_LOG       TYP_TBL_TXN_LOG
)
NESTED TABLE TXN_LOG STORE AS NESTED_TAB 
TABLESPACE PCARDUSR_DAT_MT01 ;
       

CREATE TABLE SALES_DETAIL ( SALES_NO  VARCHAR2(24), SALES_SEQ NUMBER(4), CLOSING_DATE  DATE ) 
TABLESPACE PCARDUSR_DAT_MT01 ;

CREATE TABLE SALES_DETAIL_RPT ( SALES_NO  VARCHAR2(24), SALES_SEQ NUMBER(4), CLOSING_DATE  DATE ) 
TABLESPACE PCARDUSR_DAT_MT01;

INSERT INTO SALES_DETAIL( SALES_NO, SALES_SEQ , CLOSING_DATE ) 
VALUES( '4567', 1, TRUNC(SYSDATE-20) ) ;

INSERT INTO SALES_DETAIL( SALES_NO, SALES_SEQ , CLOSING_DATE ) 
VALUES( '01235', 1, TRUNC(SYSDATE-20) ) ;

INSERT INTO SALES_DETAIL( SALES_NO, SALES_SEQ , CLOSING_DATE ) 
VALUES( '01236', 1, TRUNC(SYSDATE-20) ) ;

INSERT INTO SALES_DETAIL_RPT( SALES_NO, SALES_SEQ , CLOSING_DATE ) 
VALUES( '01234', 1, NULL ) ;

INSERT INTO SALES_DETAIL_RPT( SALES_NO, SALES_SEQ , CLOSING_DATE ) 
VALUES( '01235', 1, NULL ) ;

INSERT INTO SALES_DETAIL_RPT( SALES_NO, SALES_SEQ , CLOSING_DATE ) 
VALUES( '01236', 1, NULL ) ;

COMMIT ;

create or replace
procedure procedure1 as 
	CURSOR CUR_TMP 
	IS
		SELECT
			  SALES_NO
       ,SALES_SEQ
       ,CLOSING_DATE
		FROM SALES_DETAIL ;

	TBL_TXN_LOG	    TYP_TBL_TXN_LOG  := TYP_TBL_TXN_LOG( NULL, NULL ) ;
	LV_SQL_QRY	VARCHAR2(4000) ; 
  I           NUMBER := 0 ;
BEGIN

	FOR REC_TMP IN CUR_TMP LOOP

    I := I + 1 ;
    TBL_TXN_LOG.EXTEND( 1 ) ;
    TBL_TXN_LOG(I) := OBJ_TXN_LOG( NULL, NULL ) ;
LV_SQL_QRY := ' UPDATE SALES_DETAIL_RPT T2 SET T2.CLOSING_DATE = ' || CHR(39) || 
REC_TMP.CLOSING_DATE || CHR(39)
			      || ' WHERE T2.SALES_NO = ' || CHR(39) || REC_TMP.SALES_NO || CHR(39)
			      || ' AND T2.SALES_SEQ = ' || CHR(39) || REC_TMP.SALES_SEQ || CHR(39) 
			      || ' RETURNING T2.SALES_NO, T2.SALES_SEQ INTO :1, :2 ' ;

		EXECUTE IMMEDIATE( LV_SQL_QRY ) 
			RETURNING INTO TBL_TXN_LOG(I).SALES_NO, TBL_TXN_LOG(I).SALES_SEQ ;
      
      TBL_TXN_LOG(I).SALES_NO := '1234' ;
      TBL_TXN_LOG(I).SALES_SEQ := 1 ;

	END LOOP ;

	INSERT INTO SLS_UPDT_LOG VALUES( TBL_TXN_LOG ) ;

	COMMIT WORK ;

END ;

BEGIN
PROCEDURE1 ;
END ;

ORA-22805: cannot insert NULL object into object tables or nested tables
ORA-06512: at "STAGING.PROCEDURE1", line 34
ORA-06512: at line 2
22805. 00000 -  "cannot insert NULL object into object tables or nested tables"
*Cause:    An attempt was made to insert a NULL object into an object table
           or a Nested Table.
*Action:   Ensure that a non-NULL object is inserted into the table or
           insert an object with attributes whose values are NULL.