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 (DOT)

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.

On Jun 5, 8:37*am, fwell... (AT) gmail (DOT) com wrote:
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 --

On Jun 5, 10:38*am, Mark D Powell <Mark.Pow... (AT) eds (DOT) com> wrote:
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

On Jun 5, 11:12*am, fwell... (AT) gmail (DOT) com wrote:
Without trying your code I think what you are looking for is
coltab_rec(pls_integer).column_name

HTH -- Mark D Powell --