Home » SQL & PL/SQL » SQL & PL/SQL » Problem with nested types (merged)
Problem with nested types (merged) [message #406792] Fri, 05 June 2009 23:00 Go to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Hi,
I am creating a procedure to fill multiple defined types, but getting an error as below :-

CREATE OR REPLACE PACKAGE xxg_temp 
IS
procedure conc_request;

end;
/

CREATE OR REPLACE PACKAGE BODY xxg_temp
IS

 TYPE t_rec_cont IS RECORD (acc_num NUMBER
                          ,sal_channel VARCHAR2(30)
                          ,city VARCHAR2(30)
                          ,party_name VARCHAR2(30)
                          ,country VARCHAR2(30)
                          ,state VARCHAR2(30)
                          ,add1 VARCHAR2(30)
                          ,add2 VARCHAR2(30)
                          ,post_code VARCHAR2(30)
                          ,f_name VARCHAR2(30)
                          ,l_name VARCHAR2(30)
                          ,per_iden VARCHAR2(30)
                          ,l_u_dt DATE
                          ,status VARCHAR2(1)
                          ,b_t_c_Dt DATE
                          ,b_l_u_Dt DATE
                          ,p_flag VARCHAR2(1)
                          ,location VARCHAR2(30)
                          ,c_limit NUMBER
                          ,title VARCHAR2(30)
                          ,ph VARCHAR2(30)
                          ,fax VARCHAR2(30)
                          ,email VARCHAR2(30)
                          ,ph_type VARCHAR2(30)
                          ,ph_length NUMBER
                          ,coll_name VARCHAR2(30)
                          ,bu VARCHAR2(30)
                          ,p_channel VARCHAR2(30)
                          ,s_channel VARCHAR2(30)
                          ,site_use_id NUMBER
                          );
-- TYPE g_rec_cont IS TABLE OF g_rec_cont1 INDEX BY BINARY_INTEGER;
TYPE t_rec_disp IS RECORD (cm_reason_code VARCHAR2(30)
                          ,status   VARCHAR2(10)
                          ,creation_date DATE
                          ,promise_date DATE
                          ,promose_amt NUMBER
                          ,unp_reason varchar2(30));

--TYPE g_rec_disp IS TABLE OF g_rec_disp1 INDEX BY BINARY_INTEGER;
TYPE t_rec_trx IS RECORD (trx_number            VARCHAR2(20)
                         ,po_number            VARCHAR2(20)
                         ,credits NUMBER
                         ,due_date              DATE
                         ,curr_code        VARCHAR2(30)
                         ,org_id NUMBER
                         ,so_no VARCHAR2(30)
                         ,l_u_dt DATE
                         ,amt_due NUMBER
                         ,amt_rem NUMBER
                         ,ps_id NUMBER
                         ,site_use_id NUMBER
                         ,trx_id NUMBER);
 --TYPE g_rec_trx IS TABLE OF g_rec_trx1 INDEX BY BINARY_INTEGER;
 TYPE t_rec_type IS RECORD (tx    t_rec_trx
                           ,con   t_rec_cont
                           ,dsp   t_rec_disp);
 TYPE t_report IS TABLE OF t_rec_type INDEX BY BINARY_INTEGER;
-- v_report cp_report :=cp_report();
 v_report   t_report;
--*********get_contact_info********************************************************
  PROCEDURE  get_contact_info(line_report IN OUT t_report) IS
   -- v_tab_cont  g_tab_cont;
   CURSOR c1 IS
           SELECT     '0',
                       'sal_channel',
                       'city',
                          'party_name',
                          'country' ,
                          'state',
                          'add1',
                          'add2',
                          'add2',
                          'add2',
                          'add2',
                          'add2',
                          sysdate,
                          'A',
                          sysdate,
                          sysdate,
                          'A' ,
                          'location',
                          '0',
                          'title',
                          'ph',
                          'fax',
                          'email',
                          'ph_type',
                          '0',
                          'coll_name',
                          'bu',
                          'p_channel',
                          's_channel ',
                          '0' 
     FROM     DUAL;
  BEGIN
       OPEN c1;
       FETCH c1 INTO line_report.con ;
       CLOSE c1;
    /*FOR i IN 1..v_report.con.Count loop
      Dbms_Output.put_line('this is a test message ' ||v_report.con(i).city);
    END LOOP;  */
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line('err2');
  END get_contact_info;



 --*********conc_request*******************************************************
   PROCEDURE conc_request  IS


     CURSOR c3 IS
             SELECT      'trx_number',
                         'po_number',
                         '0',
                         sysdate,
                         'curr_code',
                         '0',
                         'so_no',
                         sysdate,
                         '0',
                         '0',
                         '0',
                         '0',
                         '0'
			FROM DUAL;                         
  BEGIN
        OPEN c3;
        FETCH c3 BULK COLLECT INTO  v_report.tx;
        CLOSE c3;

        get_contact_info(v_report);

  EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('err');
  END conc_request;
--***********************************************************************************************

 END xxg_temp;
/

SQL> show err
Errors for PACKAGE BODY XXG_TEMP:

LINE/COL ERROR
-------- -----------------------------------------------------------------
101/8    PL/SQL: SQL Statement ignored
101/34   PLS-00302: component 'CON' must be declared
134/9    PL/SQL: SQL Statement ignored
134/46   PLS-00302: component 'TX' must be declared


I am getting error while compiling. Do need to create static DB types for this kind of requirement?

Regards
Himanshu
Re: Problem with nested types [message #406799 is a reply to message #406792] Fri, 05 June 2009 23:27 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
LINE/COL ERROR
-------- -----------------------------------------------------------------
101/8    PL/SQL: SQL Statement ignored
101/34   PLS-00302: component 'CON' must be declared
134/9    PL/SQL: SQL Statement ignored
134/46   PLS-00302: component 'TX' must be declared


While you may know line #101 & 134, we do not.
Best Wishes fixing syntax errors.
Re: Problem with nested types [message #406801 is a reply to message #406792] Fri, 05 June 2009 23:38 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

It is while fetching the values into the defined types - not really a syntax error...

Line 101 - FETCH c1 INTO line_report.con ;
Line 134 - FETCH c3 BULK COLLECT INTO v_report.tx;

any valueable feedback in appreciated...
Re: Problem with nested types [message #406802 is a reply to message #406792] Fri, 05 June 2009 23:41 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Oracle does not know what CON or TX are.
Re: Problem with nested types [message #406808 is a reply to message #406792] Sat, 06 June 2009 00:45 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
First and foremost: get rid of your exception handlers. The only thing they do is obfuscate what really happens at runtime.

Now, about your problem:
t_report is a table. That means you cannot refer to v_report.con(i), but you need an index on the TABLE part: v_report(i).con
Previous Topic: demo tables(emp,dept) recovery in scott
Next Topic: update with no wait
Goto Forum:
  


Current Time: Sat Dec 10 09:18:01 CST 2016

Total time taken to generate the page: 0.07348 seconds