SQL & PL/SQL

Hi all,

I am having a situation where I wanted to test a db procedure whose i/p parameter is table type. I am not much of a expert in collections, I am having questions on how can I pass data into that table type.

oracle version:

My Procedure:

submit_order(param1 varchar2 default null,

               '',

                '',

                p\_order  in xxc\_order\_tbl\_type default xxc\_order\_tbl\_type(),

                x\_retcode  out varchar2);

Table Type Definition:

CREATE OR REPLACE TYPE xxc_order_tbl_type AS TABLE OF xxc_order_tbl_type_rec_type

Record type:

CREATE OR REPLACE

TYPE BODY xxc_order_tbl_type_rec_type AS

CONSTRUCTOR FUNCTION xxc_order_tbl_type_rec_type

RETURN SELF AS RESULT

IS

BEGIN

SFC_ACCOUNT_ID := NULL;

CONTRACT_NUMBER := NULL;

SFC_OPPORTUNITY_ID := NULL;

SUB_CHANNEL := NULL;

BATCH_ID := NULL;

''

''

''

RETURN;

END xxc_order_tbl_type_rec_type;

END;

Anonymous block:

DECLARE

x_retcode VARCHAR2 (1000);

x_errbuf VARCHAR2 (1000);

x_batch_id NUMBER;

x_sfcOrderSts xxc_order_tbl_type;

p_mode VARCHAR2 (100);

p_serviceConsumer VARCHAR2 (100);

p_sfcorder xxc_order_tbl_type;

p_debug_flag VARCHAR2 (1) := 'Y';

p_attribute1 VARCHAR2 (100);

p_attribute2 VARCHAR2 (100);

p_attribute3 VARCHAR2 (100);

p_attribute4 VARCHAR2 (100);

p_attribute5 VARCHAR2 (100);

BEGIN

--How can I pass the data into table type which is of record type

p_mode := 'INSERT';

p_sfcorder.SFC_ACCOUNT_ID := '0016000000UiBwBAAV';

p_sfcorder.CONTRACT_NUMBER := 'CS762777-H19';

p_sfcorder.SFC_OPPORTUNITY_ID := '006J000000JDLR2IAU';

p_sfcorder.created_by := fnd_global.user_id;

p_sfcorder.creation_date := SYSDATE;

XXC_ORDER_PKG.SUBMIT_ORDER (x_retcode,

                                     x\_errbuf,

                                     x\_batch\_id,

                                     x\_sfcOrderSts,

                                     p\_mode,

                                     p\_serviceConsumer,

                                     p\_sfcorder,

                                     p\_debug\_flag,

                                     p\_attribute1,

                                     p\_attribute2,

                                     p\_attribute3,

                                     p\_attribute4,

                                     p\_attribute5);

IF x_retcode = '0'

THEN

  COMMIT;

ELSE

  DBMS\_OUTPUT.put\_line (

     'Error:' || x\_errbuf || ' Batch Id: ' || x\_batch\_id);

END IF;

EXCEPTION

WHEN OTHERS

THEN

  DBMS\_OUTPUT.put\_line ('Unknown Error:' || SQLERRM);

END;

Any suggestions on how can I pass data into the table type which is of record type are appreciated.

Thanks!