Home » SQL & PL/SQL » SQL & PL/SQL » Filling the Rows of a PL/SQL Nested Table (10g)
Filling the Rows of a PL/SQL Nested Table [message #621369] Tue, 12 August 2014 23:16 Go to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Hi
May i know how can i fill the nested table below with some fixed data for further processing.

DECLARE
TYPE col_type is record (
    name                  varchar2(10),
    column                varchar2(10),
    type                  varchar2(10)
  );
Type col_type_tab IS TABLE OF col_type;
col_type_rec  col_type_tab ;


BEGIN
--  FOR i IN col_type_rec.FIRST .. col_type_rec.LAST LOOP
 --   dbms_output.put_line( col_type_rec(i).NAME || ', ' || col_type_rec(i).column  || ', ' || col_type_rec(i).type );
 -- END LOOP;
null;
END;
Re: Filling the Rows of a PL/SQL Nested Table [message #621370 is a reply to message #621369] Tue, 12 August 2014 23:21 Go to previous messageGo to next message
BlackSwan
Messages: 23026
Registered: January 2009
Senior Member
>May i know how can i fill the nested table below with some fixed data for further processing.
what exctly is the source of the fixed data?
why are you trying to move fixed data from source table into nested table?

As a general rule temporary table is not required in Oracle.

What problem are you really trying to solve?
Re: Filling the Rows of a PL/SQL Nested Table [message #621371 is a reply to message #621370] Tue, 12 August 2014 23:37 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
The data have some hard-coded values, for instance, i've have a list of partition tables which have to be stored in the nested table & every end of the month, i will have to create a new partition on this table ie aug2014, or if i've some tables which i need to do a weekly backup for certain data, so how do i populate the relevant information in a nested table so i could further process it


BlackSwan wrote on Wed, 13 August 2014 12:21
>May i know how can i fill the nested table below with some fixed data for further processing.
what exctly is the source of the fixed data?
why are you trying to move fixed data from source table into nested table?

As a general rule temporary table is not required in Oracle.

What problem are you really trying to solve?

Re: Filling the Rows of a PL/SQL Nested Table [message #621373 is a reply to message #621371] Tue, 12 August 2014 23:43 Go to previous messageGo to next message
BlackSwan
Messages: 23026
Registered: January 2009
Senior Member
>The data have some hard-coded values,
hard coded WHERE?
are you manually changing the code every period?
If you do not know how to properly use nested tables, then you should avoid using them.
Re: Filling the Rows of a PL/SQL Nested Table [message #621380 is a reply to message #621373] Wed, 13 August 2014 00:07 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
The idea is to have the hard-coded values stored in the nested table. So i'm trying to figure out how do i populate it in the nested table that i'm planning to use

For instance, if i've 2 records as below, how do i fill it in the nested table
Quote:
1,'test','test'
2,'test1','test2'


BlackSwan wrote on Wed, 13 August 2014 12:43
>The data have some hard-coded values,
hard coded WHERE?
are you manually changing the code every period?
If you do not know how to properly use nested tables, then you should avoid using them.

[Updated on: Wed, 13 August 2014 00:08]

Report message to a moderator

Re: Filling the Rows of a PL/SQL Nested Table [message #621382 is a reply to message #621380] Wed, 13 August 2014 00:13 Go to previous messageGo to next message
BlackSwan
Messages: 23026
Registered: January 2009
Senior Member
nested table is beyond your capability.
so do what you know how to do.
Re: Filling the Rows of a PL/SQL Nested Table [message #621400 is a reply to message #621380] Wed, 13 August 2014 01:40 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8005
Registered: November 2002
Location: California, USA
Senior Member
The following answers your question directly, demonstrating one method. However, your ultimate goal is unclear. I have to suspect you may be trying to do something the hard way, instead of using a simpler method. If you could provide more details, we might be able to provide a better solution.

SCOTT@orcl12c> DECLARE
  2    TYPE col_type is record (
  3  	 name		       varchar2(10),
  4  	 column 	       varchar2(10),
  5  	 type		       varchar2(10)
  6    );
  7    Type col_type_tab IS TABLE OF col_type;
  8    col_type_rec  col_type_tab;
  9  BEGIN
 10    -- initialize:
 11    col_type_rec := col_type_tab();
 12    -- extend;
 13    col_type_rec.EXTEND;
 14    -- populate:
 15    col_type_rec(col_type_rec.LAST).name := 1;
 16    col_type_rec(col_type_rec.LAST).column := 'test';
 17    col_type_rec(col_type_rec.LAST).type := 'test';
 18    -- extend:
 19    col_type_rec.EXTEND;
 20    -- populate:
 21    col_type_rec(col_type_rec.LAST).name := 2;
 22    col_type_rec(col_type_rec.LAST).column := 'test1';
 23    col_type_rec(col_type_rec.LAST).type := 'test2';
 24    FOR i IN 1 .. col_type_rec.COUNT LOOP
 25  	 dbms_output.put_line
 26  	   (col_type_rec(i).NAME || ', ' ||
 27  	    col_type_rec(i).column  || ', ' ||
 28  	    col_type_rec(i).type );
 29    END LOOP;
 30  END;
 31  /
1, test, test
2, test1, test2

PL/SQL procedure successfully completed.

Re: Filling the Rows of a PL/SQL Nested Table [message #621418 is a reply to message #621400] Wed, 13 August 2014 03:20 Go to previous message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Thanks Barbara for the help, will try to figure out on how to keep it simple.

[Updated on: Wed, 13 August 2014 07:55] by Moderator

Report message to a moderator

Previous Topic: SQL
Next Topic: parent and Child issue ?
Goto Forum:
  


Current Time: Sat Nov 22 08:38:48 CST 2014

Total time taken to generate the page: 0.10845 seconds