Re: help with pl sql arrays

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Sat, 6 Jun 2009 13:27:47 -0700 (PDT)
Message-ID: <eeb2c1b2-a9df-4198-9977-941387f3b25e_at_n4g2000vba.googlegroups.com>



On Jun 5, 11:12 am, fwell..._at_gmail.com wrote:
> 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- Hide quoted text -
>
> - Show quoted text -

Without trying your code I think what you are looking for is coltab_rec(pls_integer).column_name

HTH -- Mark D Powell -- Received on Sat Jun 06 2009 - 15:27:47 CDT

Original text of this message