help with pl sql arrays
Date: Fri, 5 Jun 2009 05:37:51 -0700 (PDT)
Message-ID: <5381b692-efeb-4732-8432-06348cfaa1a3_at_v4g2000vba.googlegroups.com>
Hi,
pretty new to Oracle and am not a programmer by profession. Am trying to write a stored procedue that will create statements to insert across a gateway, and have some of the insert statements contain trims, depending on the column type.
so I am looping through one record at a time from dba_tab_columns.
Basically the below.
FOR tab in (
SELECT table_name,column_name,data_type
FROM dba_tab_columns where owner='MY_OWNER'
)
LOOP
....
END LOOP;
END;
But, what I really need to do is to loop all of the columns found for
each table into an array, so that at the end of the loop for each
table, one line is printed out something like this:
insert into my_table ( col1,col2 ... col_last) select col1,col2 .. col_last from table_at_gateway_link.
The problem is in how to use the arrays to get each loop iteration to be stored, and all printed out in one line at the end of each loop iteration.
I hope I'm explaining this clearly enough. It sounds pretty simple, but I am not able to figure it out.
Thanks for any help.
fwellers. Received on Fri Jun 05 2009 - 07:37:51 CDT