| Filling the Rows of a PL/SQL Nested Table [message #621369] |
Tue, 12 August 2014 23:16  |
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 #621371 is a reply to message #621370] |
Tue, 12 August 2014 23:37  |
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 #621380 is a reply to message #621373] |
Wed, 13 August 2014 00:07 |
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 #621400 is a reply to message #621380] |
Wed, 13 August 2014 01:40 |
 |
Barbara Boehmer
Messages: 9082
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
