Re: help with pl sql arrays

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Fri, 5 Jun 2009 07:38:58 -0700 (PDT)
Message-ID: <34814cf3-dc77-4190-9dd6-ddbf046938f0_at_l28g2000vba.googlegroups.com>

On Jun 5, 8:37 am, fwell..._at_gmail.com wrote:
> 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.

Look up the PL/SQL collection types in your PL/SQL manual. I would just use the old pl/sql table which is a single dimension array.

With a cursor to read the columns for a specific owner.table_name you just fetech the column_names into array positions.

Then you print logic (dbms_output, utl_file, etc ...) can just plug array positions into the insert statement string you are apparently building.

If the target tables look exactly like the source table you could just use insert into table select * from source_table

To be more efficient you could look up the BULK collect feature.

HTH -- Mark D Powell -- Received on Fri Jun 05 2009 - 09:38:58 CDT