Re: PL/SQL Insert

Daniel Clamage schrieb in Nachricht <01bdd68e$5547a260$ec28c9cd_at_saturn>...
>You can skip the list of columns when you are using all columns, in the
>order in which they occur in the table. You still have to list every value
>to match every column in the VALUES clause.
>You could partially automate getting the columns list by querying the data
>dictionary, i.e. user_tab_columns.

Some days ago I had a similar problem and I've written a short PL/SQL function for it, which takes as parameters (casesensitive!) the tablename and the tableowner. The returnvalue will be a string of the form "column1,column2,column3" (all columns of the table sorted by columnname, change it if you don't like this order). I had to place it in the Schema SYS, although the User I tried first had DBA-role. With the latter I got some error which I can't remember.

• start of code ---- function list_tab_col(strTablename VARCHAR2, strOwner VARCHAR2) return VARCHAR2 is strColumns VARCHAR2(2000); Cursor cCol is select column_name from dba_tab_columns where table_name=strTablename and owner=strOwner order by column_name; begin for rCol in cCol loop strColumns := strColumns || ',' || rCol.column_name; end loop; return (ltrim(strColumns,',')); end list_tab_col;
• End of code ----

With this function (you need execute priv on it, of course) you can easily produce your insert statements for all tables your interested in:

select 'insert into table x ( ' || list_tab_col('X','USER_Y')

    || ') values (myvalue1,myvalue2);' from dual;

Try it, Peter

peter.haeberli_at_buwal.admin.ch Received on Thu Sep 03 1998 - 07:31:43 CDT