Path: news.cambrium.nl!textnews.cambrium.nl!feeder1.cambriumusenet.nl!feed.tweaknews.nl!138.195.8.3.MISMATCH!news.ecp.fr!news.glorb.com!news2.glorb.com!postnews.google.com!n4g2000vba.googlegroups.com!not-for-mail From: Mark D Powell Newsgroups: comp.databases.oracle.misc Subject: Re: help with pl sql arrays Date: Sat, 6 Jun 2009 13:27:47 -0700 (PDT) Organization: http://groups.google.com Lines: 114 Message-ID: References: <5381b692-efeb-4732-8432-06348cfaa1a3@v4g2000vba.googlegroups.com> <34814cf3-dc77-4190-9dd6-ddbf046938f0@l28g2000vba.googlegroups.com> <6d20c054-2d07-4638-96ad-9bb9aa0826b6@p4g2000vba.googlegroups.com> NNTP-Posting-Host: 67.167.134.14 Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Trace: posting.google.com 1244320067 8813 127.0.0.1 (6 Jun 2009 20:27:47 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Sat, 6 Jun 2009 20:27:47 +0000 (UTC) Complaints-To: groups-abuse@google.com Injection-Info: n4g2000vba.googlegroups.com; posting-host=67.167.134.14; posting-account=qJFqbQkAAACYQSLN0-cvP6ydkRfuOu6u User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; Trident/4.0; .NET CLR 1.0.3705; .NET CLR 1.1.4322; .NET CLR 2.0.50727; .NET CLR 3.0.04506.30; .NET CLR 3.0.04506.648; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729; OfficeLiveConnector.1.3; OfficeLivePatch.0.0),gzip(gfe),gzip(gfe) Xref: news.cambrium.nl On Jun 5, 11:12=A0am, fwell...@gmail.com wrote: > On Jun 5, 10:38=A0am, Mark D Powell wrote: > > > > > > > On Jun 5, 8:37=A0am, fwell...@gmail.com wrote: > > > > Hi, > > > =A0pretty 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 ( > > > =A0 SELECT table_name,column_name,data_type > > > =A0 FROM dba_tab_columns where owner=3D'MY_OWNER' > > > ) > > > LOOP > > > =A0 .... > > > 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: > > > =A0 =A0insert into my_table ( col1,col2 ... col_last) select col1,col= 2 .. > > > col_last from table@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. =A0I 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, > =A0 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=3D'my_schema' and table_name=3D'CONFIG' = ; > > BEGIN > i :=3D 0 ; > OPEN cur ; > LOOP > FETCH cur into coltab_rec ; > exit when cur%notfound ; > i :=3D i+1 ; > coltab_table(i) :=3D coltab_rec ; > > CASE coltab_rec.data_type > WHEN 'VARCHAR2' THEN > coltab_rec.column_name :=3D 'trim(' || coltab_table(i).column_name || > ')' ; > -- DBMS_OUTPUT.PUT_LINE (coltab_rec.column_name ) ; > WHEN 'CHAR' THEN > coltab_rec.column_name :=3D '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 --