Re: help with pl sql arrays
Date: Fri, 5 Jun 2009 08:12:39 -0700 (PDT)
Message-ID: <6d20c054-2d07-4638-96ad-9bb9aa0826b6_at_p4g2000vba.googlegroups.com>
On Jun 5, 10:38 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> 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 --
Thanks Mark,
I can't use the select * from source table, because I need to make
trim statements on some columns.
I figured out how to populate a simple array, but haven't yet figured
out how to dereference that array at the end.
SET SERVEROUTPUT ON;
DECLARE
TYPE typ_coltab IS TABLE OF dba_tab_columns%ROWTYPE INDEX BY
pls_integer;
coltab_table typ_coltab ;
coltab_rec dba_tab_columns%ROWTYPE ;
i pls_integer ;
CURSOR cur is SELECT *
FROM dba_tab_columns where owner='my_schema' and table_name='CONFIG' ;
BEGIN
i := 0 ;
OPEN cur ;
LOOP
FETCH cur into coltab_rec ;
exit when cur%notfound ;
i := i+1 ;
coltab_table(i) := coltab_rec ;
CASE coltab_rec.data_type
WHEN 'VARCHAR2' THEN
coltab_rec.column_name := 'trim(' || coltab_table(i).column_name ||
')' ;
-- DBMS_OUTPUT.PUT_LINE (coltab_rec.column_name ) ;
WHEN 'CHAR' THEN
coltab_rec.column_name := 'trim(' || coltab_table(i).column_name ||
')' ;
-- DBMS_OUTPUT.PUT_LINE (coltab_rec.column_name ) ;
ELSE
-- DBMS_OUTPUT.PUT_LINE (coltab_rec.column_name ) ;
END CASE;
END LOOP;
END;
/
exit
Received on Fri Jun 05 2009 - 10:12:39 CDT