oracle - nested table insertion data through procedure in pl/sql

Question

How to insert data in nested table using procedure in pl/SQL?

how to pass parameter in nested table through procedure?

CREATE OR REPLACE TYPE  "CYBER_B" AS OBJECT
 ( REC_ID VARCHAR2(250),
  START_DATE TIMESTAMP,
  END_DATE TIMESTAMP,
  CREATION_DATE TIMESTAMP,
  ADV_DURATION TIMESTAMP,
  ADV_IMAGE BLOB,
  IMG_FILENAME VARCHAR2(250),
  IMG_MIMETYPE VARCHAR2(250)
  );
CREATE TYPE CYBERB1 AS TABLE OF CYBER_B;

CREATE TABLE  "CYBER1_TABLE" 
   (    "AD_ID" VARCHAR2(64), 
    "USERNAME" VARCHAR2(250) NOT NULL ENABLE, 
    "SELLER_DETAIL1"  "CYBERB1" , 
     PRIMARY KEY ("AD_ID") ENABLE
   ) 
 NESTED TABLE "SELLER_DETAIL1" STORE AS "ADV1_DETAIL";

cyber_b is object cyberb1 is type cyber_table is my table

I want to create a procedure which inserts data in nested table

Answer 1

In the update statement:

CREATE PROCEDURE add_cyber1_seller_detail(
  in_ad_id   CYBER1_TABLE.AD_ID%TYPE,
  in_seller  CYBER_B
)
IS
BEGIN
  UPDATE cyber1_table
  SET    seller_detail1 = COALESCE( seller1_detail, CYBERB1() )
                          MULTISET UNION ALL
                          CYBERB1( in_seller )
  WHERE  ad_id = in_ad_id;
END;
/

Using PL/SQL:

CREATE PROCEDURE add_cyber1_seller_detail(
  in_ad_id   CYBER1_TABLE.AD_ID%TYPE,
  in_seller  CYBER_B
)
IS
  sellers CYBERB1;
BEGIN
  SELECT seller_detail1
  INTO   sellers
  FROM   cyber1_table
  WHERE  ad_id = in_ad_id
  FOR UPDATE OF seller_detail1;

  IF sellers IS NULL THEN
    sellers := CYBERB1( in_seller );
  ELSE
    sellers.EXTEND(1);
    sellers( sellers.COUNT ) := in_seller;
  END IF;

  UPDATE cyber1_table
  SET    seller_detail1 = sellers
  WHERE  ad_id = in_ad_id;
END;
/