Clever Geek Handbook

Couldn't collecting mass into a table from a PLSQL table type?

SELECT *   BULK COLLECT INTO CONNECT_RET_ITEM_DETAIL FROM TABLE(RET_ITEM_IN);

Here RET_ITEM_IN

is an instance of the table type RET_ITM_DTL_TMP

;

desc RET_ITM_DTL_TMP;

-------------------------------- 
 TYPE RET_ITM_DTL_TMP AS   OBJECT   (
    ITEM_ID       VARCHAR2(50),
    ITEM_DESC     VARCHAR2(50),
    ORDER_NUMBER  VARCHAR2(50),
    PARCEL_NUMBER VARCHAR2(50),
    STORE_NUMBER  VARCHAR2(50),
    QUANTITY      NUMBER ) ;  


desc CONNECT_RET_ITEM_DETAIL

Name          Null Type         
------------- ---- ------------ 
 ITEM_ID            VARCHAR2(50) 
 ITEM_DESC          VARCHAR2(50)  
 ORDER_NUMBER       VARCHAR2(50)  
 PARCEL_NUMBER      VARCHAR2(50)  
 STORE_NUMBER       VARCHAR2(50)
 QUANTITY           NUMBER

I can successfully harvest mass in the following situations:

  • Object to object
  • Object table
  • Table to table

However, the object type cannot be inserted into the table. Getting the following error:

Error (118.6): PLS-00403: Expression 'CONNECT_RET_ITEM_DETAIL' cannot be used as INTO target of SELECT / FETCH statement

+3


source to share


1 answer


Not sure if this completely solves your problem, but maybe this code will be helpful. Create a table like here:

create table CONNECT_RET_ITEM_DETAIL of RET_ITM_DTL_TMP;


Further, this PL / SQL worked for me:

declare
  type RET_ITM_DTL_TBL is table of RET_ITM_DTL_TMP;
  RET_ITEM_IN RET_ITM_DTL_TBL;
begin
  RET_ITEM_IN := RET_ITM_DTL_TBL(
      RET_ITM_DTL_TMP ('a11', 'a12', 'a13', 'a14', 'a15', 11), 
      RET_ITM_DTL_TMP ('b11', 'b12', 'b13', 'b14', 'b15', 21),
      RET_ITM_DTL_TMP ('b21', 'b22', 'b23', 'b24', 'b25', 22) );

  FORALL i IN RET_ITEM_IN.first .. RET_ITEM_IN.last 
    INSERT INTO CONNECT_RET_ITEM_DETAIL VALUES RET_ITEM_IN(i);
end;
0


source






More articles:


All Articles