Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

how to assign values to types

new learnerAug 27 2010 — edited Aug 30 2010
oracle 10 g

how do i assing values to l_outbound_rec. i cannot use bulk collect. since i have to calculate each and every field seperately and assign it to l_outbound_rec.
and then i can use l_outbound_rec in bulk inserts.
SET serveroutput on;

DECLARE
/*
CREATE OR REPLACE TYPE init_queue AS OBJECT (
   seq_num_q                 NUMBER,
   source_system_name        VARCHAR2 (50 BYTE),
   milestone_id              NUMBER,
   milestone_date            DATE,
   downstream_order_number   VARCHAR2 (175 BYTE),
   leg_id                    VARCHAR2 (175 BYTE),
   statuspro_captured_date   DATE,
   pon                       VARCHAR2 (26 BYTE),
   milestone_source          VARCHAR2 (26 BYTE),
   session_id                NUMBER,
   reconfirmation_flag       CHAR (1 BYTE),
   vec_flag                  CHAR (1 BYTE)
);

CREATE OR REPLACE TYPE tab_init_queue AS TABLE OF init_queue;

*/
   v_list           tab_init_queue := tab_init_queue ();

   TYPE outbound_rec IS RECORD (
      seq_num_q                 DBMS_SQL.number_table,
      tin                       DBMS_SQL.varchar2_table,
      nasp_id                   DBMS_SQL.varchar2_table,
      nasp_par_id               DBMS_SQL.varchar2_table,
      customer_name             DBMS_SQL.varchar2_table,
      downstream_order_number   DBMS_SQL.varchar2_table,
      lead_order_number         DBMS_SQL.varchar2_table,
      milestone_id              DBMS_SQL.number_table,
      milestone_date            DBMS_SQL.date_table,
      source_system_name        DBMS_SQL.varchar2_table
   );

   l_outbound_rec   outbound_rec;
BEGIN
   v_list.EXTEND;
   v_list (v_list.COUNT) :=
      init_queue (1,
                  '1',
                  11,
                  SYSDATE,
                  '1',
                  '1',
                  SYSDATE,
                  '1',
                  '1',
                  1,
                  '1',
                  '1'
                 );
   v_list.EXTEND;
   v_list (v_list.COUNT) :=
      init_queue (2,
                  '2',
                  11,
                  SYSDATE,
                  '2',
                  '2',
                  SYSDATE,
                  '2',
                  '2',
                  1,
                  '2',
                  '2'
                 );

   FOR i IN (SELECT seq_num_q, source_system_name, milestone_id,
                    milestone_date, downstream_order_number, leg_id,
                    statuspro_captured_date, pon, milestone_source,
                    session_id, reconfirmation_flag, vec_flag
               FROM TABLE (CAST (v_list AS tab_init_queue)) a
              WHERE milestone_id = 11)
   LOOP
   
     l_outbound_rec.seq_num_q(i) := i.seq_num_q;  -- ****Failing here****
      DBMS_OUTPUT.put_line (   i.seq_num_q
                            || ','
                            || i.source_system_name
                            || ','
                            || i.milestone_id
                            || ','
                            || i.milestone_date
                            || ','
                            || i.downstream_order_number
                            || ','
                            || i.leg_id
                            || ','
                            || i.statuspro_captured_date
                            || ','
                            || i.pon
                            || ','
                            || i.milestone_source
                            || ','
                            || i.session_id
                            || ','
                            || i.reconfirmation_flag
                            || ','
                            || i.vec_flag
                           );
   END LOOP;
END;
/
thanks
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 27 2010
Added on Aug 27 2010
13 comments
1,608 views