Home » SQL & PL/SQL » SQL & PL/SQL » Search in Nested Tables and Insert the result into new Nested Table!
Search in Nested Tables and Insert the result into new Nested Table! [message #499510] Tue, 15 March 2011 02:30 Go to next message
ahmet
Messages: 6
Registered: March 2011
Junior Member
How can I search in Nested Tables ex: (pr_travel_date_range,pr_bo_arr) using the SQL below and insert the result into a new Nested Table: ex:g_splited_range_arr.

Here are the DDL and DML SQLs;
*Don't worry about the NUMBER( 8 )*

CREATE OR REPLACE TYPE DATE_RANGE IS OBJECT ( start_date NUMBER( 8 ), end_date NUMBER( 8 ) );

CREATE OR REPLACE TYPE DATE_RANGE_ARR IS TABLE OF DATE_RANGE;

DECLARE
   g_splited_range_arr   DATE_RANGE_ARR := DATE_RANGE_ARR( );
   g_travel_range        DATE_RANGE := DATE_RANGE( '20110101', '99991231' );
   g_bo_arr              DATE_RANGE_ARR := DATE_RANGE_ARR( DATE_RANGE( '20110312', '20110317' ), DATE_RANGE( '20110315', '20110329' ) );

   FUNCTION split_date_sql( pr_travel_date_range    DATE_RANGE,
                            pr_bo_arr               DATE_RANGE_ARR )
      RETURN DATE_RANGE_ARR
   IS
      l_splited_range_arr   DATE_RANGE_ARR;
   BEGIN
      SELECT start_date, end_date
        INTO l_splited_range_arr(start_date, end_date)
        FROM (WITH all_dates
                      AS (SELECT tr_start_date AS a_date, 0 AS black_out_val FROM TABLE( pr_travel_date_range )
                          UNION ALL
                          SELECT tr_end_date, 0 FROM TABLE( pr_travel_date_range )
                          UNION ALL
                          SELECT bo_start_date - 1, 1 FROM TABLE( pr_bo_arr )
                          UNION ALL
                          SELECT bo_end_date + 1, -1 FROM TABLE( pr_bo_arr )),
                   got_analytics
                      AS (SELECT a_date AS start_date,
                                 LEAD( a_date ) OVER (ORDER BY a_date, black_out_val) AS end_date,
                                 SUM( black_out_val ) OVER (ORDER BY a_date, black_out_val) AS black_out_cnt
                            FROM all_dates)
                SELECT start_date, end_date
                  FROM got_analytics
                 WHERE black_out_cnt = 0 AND start_date < end_date
              ORDER BY start_date);

      RETURN l_splited_range_arr;
   END;
BEGIN

    g_splited_range_arr := split_date_sql(g_travel_range,g_bo_arr);
   
    FOR index_g_splited_range_arr IN g_splited_range_arr .FIRST .. g_splited_range_arr .LAST LOOP        
        DBMS_OUTPUT.PUT_LINE('g_splited_range_arr[' || index_g_splited_range_arr || ']: ' || g_splited_range_arr(index_g_splited_range_arr).start_date || '-'  || g_splited_range_arr(index_g_splited_range_arr).end_date );
    END LOOP;
   
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      NULL;
   WHEN OTHERS
   THEN
      NULL;
END;


Or can I create a VIEW with parameters of Nested Tables in it so I can simply call
SELECT  *
  BULK COLLECT INTO g_splited_range_arr
  FROM view_split_date(g_travel_range,g_bo_arr);
Re: Search in Nested Tables and Insert the result into new Nested Table! [message #499515 is a reply to message #499510] Tue, 15 March 2011 02:38 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please repost keeping your lines in 80-character width.

   WHEN OTHERS
   THEN
      NULL;

Read http://asktom.oracle.com/pls/asktom/asktom.search?p_string=%22i+hate+your+code%22

Regards
Michel
Re: Search in Nested Tables and Insert the result into new Nested Table! [message #499528 is a reply to message #499515] Tue, 15 March 2011 03:18 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7973
Registered: November 2002
Location: California, USA
Senior Member
When you comment out the exception section, the first error that it encounters is due to attempting to use TABLE on a variable of data type date_range instead of date_range_arr within a select statement. Please see the simplified reproduction and correction below. I will leave it to you to fix that, then deal with the rest.

-- test environment:
SCOTT@orcl_11gR2> CREATE OR REPLACE TYPE DATE_RANGE IS OBJECT
  2    ( start_date NUMBER( 8 ),
  3  	 end_date NUMBER( 8 ) );
  4  /

Type created.

SCOTT@orcl_11gR2> CREATE OR REPLACE TYPE DATE_RANGE_ARR IS TABLE OF DATE_RANGE
  2  /

Type created.


-- reproduction of error:
SCOTT@orcl_11gR2> VARIABLE g_refcur REFCURSOR
SCOTT@orcl_11gR2> DECLARE
  2  	g_splited_range_arr   DATE_RANGE_ARR := DATE_RANGE_ARR( );
  3  	g_travel_range	      DATE_RANGE := DATE_RANGE( '20110101', '99991231' );
  4  BEGIN
  5    OPEN :g_refcur FOR SELECT * FROM TABLE ( g_travel_range );
  6  END;
  7  /
  OPEN :g_refcur FOR SELECT * FROM TABLE ( g_travel_range );
                                   *
ERROR at line 5:
ORA-06550: line 5, column 36:
PL/SQL: ORA-22905: cannot access rows from a non-nested table item
ORA-06550: line 5, column 22:
PL/SQL: SQL Statement ignored


-- correction of error:
SCOTT@orcl_11gR2> DECLARE
  2  	g_splited_range_arr   DATE_RANGE_ARR := DATE_RANGE_ARR( );
  3  	g_travel_range	      DATE_RANGE_ARR := DATE_RANGE_ARR
  4  						  ( DATE_RANGE( '20110101', '99991231' ));
  5  BEGIN
  6    OPEN :g_refcur FOR SELECT * FROM TABLE ( g_travel_range );
  7  END;
  8  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> PRINT g_refcur

START_DATE   END_DATE
---------- ----------
  20110101   99991231

1 row selected.

SCOTT@orcl_11gR2>


Re: Search in Nested Tables and Insert the result into new Nested Table! [message #499543 is a reply to message #499528] Tue, 15 March 2011 04:03 Go to previous messageGo to next message
ahmet
Messages: 6
Registered: March 2011
Junior Member
Thank you @Barbara. Sorry about DATE_RANGE_ARR I haven't seen it.

Here is the current solution:

CREATE OR REPLACE TYPE DATE_RANGE IS OBJECT ( start_date NUMBER( 8 ), end_date NUMBER( 8 ) );

CREATE OR REPLACE TYPE DATE_RANGE_ARR IS TABLE OF DATE_RANGE;

DECLARE
   g_splited_range_arr   DATE_RANGE_ARR;
   g_travel_range        DATE_RANGE_ARR := DATE_RANGE_ARR( DATE_RANGE( '20110101', '99991231' ) );
   g_bo_arr              DATE_RANGE_ARR := DATE_RANGE_ARR( DATE_RANGE( '20110312', '20110317' ), DATE_RANGE( '20110315', '20110329' ) );

   FUNCTION split_date_sql( pr_travel_date_range DATE_RANGE_ARR, pr_bo_arr DATE_RANGE_ARR )
      RETURN DATE_RANGE_ARR
   IS
      l_splited_range_arr   DATE_RANGE_ARR := DATE_RANGE_ARR( );
      l_date                DATE_RANGE := DATE_RANGE( NULL, NULL );
   BEGIN
   
      -- Used cursor for inserting into another Nested Table=l_splited_range_arr
      FOR rec_splited_date
         IN ( WITH 
                   nt_travel AS (SELECT * FROM TABLE( pr_travel_date_range )),   -- Select from Nested Table
                   nt_black_out_dates AS (SELECT * FROM TABLE( pr_bo_arr )),     -- Select from Nested Table
                   all_dates AS (SELECT start_date AS a_date, 0 AS black_out_val FROM nt_travel    -- Used Nested Table in SQL
                                 UNION ALL
                                 SELECT end_date, 0 FROM nt_travel                                 -- Used Nested Table in SQL
                                 UNION ALL
                                 SELECT start_date - 1, 1 FROM nt_black_out_dates                  -- Used Nested Table in SQL
                                 UNION ALL
                                 SELECT end_date + 1, -1 FROM nt_black_out_dates),                 -- Used Nested Table in SQL
                   got_analytics
                      AS (SELECT a_date AS start_date,
                                 LEAD( a_date ) OVER (ORDER BY a_date, black_out_val) AS end_date,
                                 SUM( black_out_val ) OVER (ORDER BY a_date, black_out_val) AS black_out_cnt
                            FROM all_dates)
               SELECT start_date, end_date
                 FROM got_analytics
                WHERE black_out_cnt = 0 AND start_date < end_date
             ORDER BY start_date )
      LOOP
         l_date.start_date := rec_splited_date.start_date;
         l_date.end_date := rec_splited_date.end_date;
         -- Inserting into another Nested Table=l_splited_range_arr from cursor=rec_splited_date
         l_splited_range_arr.EXTEND;
         l_splited_range_arr( l_splited_range_arr.LAST ) := l_date;
      END LOOP;

      RETURN l_splited_range_arr;
   END;
BEGIN
   g_splited_range_arr := split_date_sql( g_travel_range, g_bo_arr );

   FOR index_g_splited_range_arr IN g_splited_range_arr.FIRST .. g_splited_range_arr.LAST
   LOOP
      DBMS_OUTPUT.PUT_LINE(
              'g_splited_range_arr['
           || index_g_splited_range_arr
           || ']: '
           || g_splited_range_arr( index_g_splited_range_arr ).start_date
           || '-'
           || g_splited_range_arr( index_g_splited_range_arr ).end_date );
   END LOOP;
END;
Re: Search in Nested Tables and Insert the result into new Nested Table! [message #499544 is a reply to message #499543] Tue, 15 March 2011 04:04 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Use the "Preview Message" button to verify.

Regards
Michel
Re: Search in Nested Tables and Insert the result into new Nested Table! [message #499595 is a reply to message #499510] Tue, 15 March 2011 07:19 Go to previous messageGo to next message
ahmet
Messages: 6
Registered: March 2011
Junior Member
Please write your off topics using your PMs. And also the rules messages could have been sent to me using PM.
@Admin: If possible please delete the unrelated messages. I want the forum to be clean and understandable to others.
Re: Search in Nested Tables and Insert the result into new Nested Table! [message #499597 is a reply to message #499595] Tue, 15 March 2011 07:27 Go to previous messageGo to next message
Littlefoot
Messages: 19525
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Done! Off-topic discussion moved here.

Sorry for spoiling your topic.

P.S. OraFAQ Forum Guide is here.

[Updated on: Tue, 15 March 2011 07:44]

Report message to a moderator

Re: Search in Nested Tables and Insert the result into new Nested Table! [message #499599 is a reply to message #499595] Tue, 15 March 2011 07:33 Go to previous message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I want the forum to be clean and understandable to others.

Why don't you then post accordingly to the forum guide?
Why don't you also answer and feedback when we ask you some questions (see your previous topic).
Be as clean as possible.

Regards
Michel
Previous Topic: Large Tables
Next Topic: Split a date into new dates according to black out dates! (2 Merged)
Goto Forum:
  


Current Time: Fri Aug 29 23:14:55 CDT 2014

Total time taken to generate the page: 0.14369 seconds