I got it ... but had to go about it with a cursor, not very elegant but I didn't have alot of time to think it over.. This is the update procedure I have the insert, select, delete procs working as well. Right now I am trying to fit the pieces together into a package. There are some caveats to this .... I haven't built in a check for the column lengths when you add the P_ or V_, it assumes you have ctrl_date or ctrl_user in each table. This should get you going. I am excited about this because we use alot of audit tables and triggers here so I can modify this code to do this... If someone comes up with something more elegant then what I have I would be interested to see it. The user that runs the procs has dba privs and create and alter any procedure privs. Have fun. Lathy CREATE OR REPLACE PROCEDURE GENERATE_UPDATE_PROC( P_OWNER_NAME in DBA_TABLES.OWNER%TYPE, P_TABLE_NAME in DBA_TABLES.TABLE_NAME%TYPE) IS err_num binary_integer; err_msg varchar(300); v_sql varchar2(4000); v_sql_middle1 varchar2(2000); v_sql_middle2 varchar2(2000); v_sql_loop varchar2(2000); v_check char(1); V_TABLE_NAME dba_tables.table_name%type; V_OWNER_NAME dba_tables.owner%type; V_COLUMN_NAME dba_tab_columns.column_name%type; CURSOR cur_symbol is select column_name from dba_tab_columns where owner = upper(P_OWNER_NAME) and table_name = upper(P_TABLE_NAME) and column_id != 1 and column_name not like '%CTRL_DATE%' and column_name not like '%CTRL_USER_ID'; BEGIN if P_OWNER_NAME is null or P_TABLE_NAME is null then raise_application_error(-20001,'Owner and Table Name need to be supplied'); end if; begin select 'x' into v_check from dba_tables where owner = upper(P_OWNER_NAME ) and table_name = upper( P_TABLE_NAME ); exception when no_data_found then raise_application_error(-20001,'Owner and/or Table Name are not correct.'); end; V_TABLE_NAME := upper (P_TABLE_NAME); V_OWNER_NAME := upper (P_OWNER_NAME); select column_name into v_column_name from dba_tab_columns where column_id = 1 and table_name = V_TABLE_NAME and owner = V_OWNER_NAME order by column_id; FOR cur_recs in cur_symbol Loop select ' P_' || RPAD(cur_recs.column_name,30) || ' in ' || V_TABLE_NAME ||'.'|| cur_recs.column_name ||'%type,' ||chr(10) into v_sql_loop from dual; v_sql_middle1 := rtrim(v_sql_middle1,' ') || rtrim(v_sql_loop,' '); select ' '|| cur_recs.column_name ||' = ' || ' P_'|| cur_recs.column_name || ',' || chr(10) into v_sql_loop from dual where cur_recs.column_name not like '%CTRL%'; v_sql_middle2 := rtrim(v_sql_middle2,' ')|| rtrim(v_sql_loop,' '); End loop; /* take off the carriage return and last comma */ v_sql_middle1 := rtrim(v_sql_middle1,chr(10)); v_sql_middle1 := substr(rtrim(v_sql_middle1,' ') ,1,(length(rtrim(v_sql_middle1,' ')))-1 ) ; v_sql_middle2 := rtrim(v_sql_middle2,chr(10)); v_sql_middle2 := substr(rtrim(v_sql_middle2,' ') ,1,(length(rtrim(v_sql_middle2,' ')))-1 ) ; /* PROCEDURE UPDATE_PROC ( all columns except for ctrl_date and ctrl_user_id */ v_sql := 'CREATE OR REPLACE PROCEDURE ' || V_OWNER_NAME||'.'|| P_TABLE_NAME || '_UPDATE_PROC ( ' || chr(10) || ' P_'|| V_COLUMN_NAME || ' in ' || V_TABLE_NAME || '.' || V_COLUMN_NAME || '%type,' ||chr(10) || v_sql_middle1 || chr(10) ; /* ) as begin update P_TABLE_NAME set */ v_sql := rtrim(v_sql,' ') || ' )' ||chr(10) || ' as ' || chr(10) || ' begin ' || chr(10) || ' update ' ||V_OWNER_NAME || '.'|| V_TABLE_NAME || chr(10) || ' set ' || chr(10) || v_sql_middle2 || chr(10) || ' where ' || V_COLUMN_NAME || ' = P_'|| V_COLUMN_NAME || ';' || chr(10) || 'End;' ; v_sql := rtrim(v_sql,' '); execute immediate v_sql; EXCEPTION WHEN OTHERS THEN err_num := SQLCODE; err_msg := SQLERRM; raise_application_error(-20011,'Oracle error '||err_num||' generate_update_proc: '||err_msg); END GENERATE_UPDATE_PROC; -----Original Message----- From: Ruth Gramolini [mailto:rgramolini@xxxxxxxxxxxxxxx] Sent: Friday, April 16, 2004 11:59 AM To: oracle-l@xxxxxxxxxxxxx Subject: RE: dynamic dynamic sql within a procedure - ora_00911 I have been trying to write an sql script that will do the same thing. I am having a bear of a time. I can get the pieces easily enough but I can't put them together in a meaningful way. I have been trying with a union to get the create or replace part unioned to the stuff from source$. If you comeup with a solution let me know, if I do I will share too. Ruth -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Duret, Kathy Sent: Thursday, April 15, 2004 11:52 AM To: 'oracle-l@xxxxxxxxxxxxx' Subject: RE: dynamic dynamic sql within a procedure - ora_00911 Ruth, What I am trying to do is create a code generator..... I am creating new tables and want to create insert, delete, update and select procedures for all of the tables. Rather than do them one by one and having to fix them every time the developers want to change the name, etc.... I decided to try to build a code generator. So I want my admin procedure which is passed an owner and table name to spit out select, delete, insert and update procedure for that user. This is what I have which WORKS for the select all proc. You need to create a type package for the cursorvar and the dba user needs create any and drop any priveledges for procedures. It will have to be executed by the user exec TABLE_NAME_select_all_proc in this case. v_sql := 'CREATE or REPLACE PROCEDURE '|| upper(P_OWNER_NAME) ||'.'|| upper(P_TABLE_NAME) || '_SELECT_ALL_PROC ( ' || chr(10)|| ' P_ALL_ROWS_CURSOR out types.cursorvar ' || chr(10) || ' ) is ' || chr(10) || ' Begin ' || chr(10) || ' open P_ALL_ROWS_CURSOR for ' || chr(10) || ' select * ' || chr(10) || ' from ' || upper(P_OWNER_NAME) ||'.'|| upper(P_TABLE_NAME) || chr(10)|| ' order by 1; ' || chr(10)|| ' End;' ; v_sql := rtrim(v_sql,' '); execute immediate v_sql; This is what I ended up for this one.... I was doing to much work for the select all procedure , but I face the problem with delete, select and insert where I need to dynamic dynamic.... I am going to try to execute sql into a variable I think then try to || them together Unless someone here comes up with a better plan...:) Thanks, Kathy -----Original Message----- From: Ruth Gramolini [mailto:rgramolini@xxxxxxxxxxxxxxx] Sent: Thursday, April 15, 2004 9:23 AM To: oracle-l@xxxxxxxxxxxxx Subject: RE: dynamic dynamic sql within a procedure - ora_00911 Once this procedure is created, how do you run it. I asked my boss, the super duhveloper, but he didn't know. I know this is a stupic question, but I don't do much sql. Thanks in advance, Ruth -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Mercadante, Thomas F Sent: Thursday, April 15, 2004 7:57 AM To: 'oracle-l@xxxxxxxxxxxxx' Cc: 'kduret@xxxxxxxxxxxxxxxxxxxx' Subject: RE: dynamic dynamic sql within a procedure - ora_00911 Kathy, I modified your proc a bit and got it to create the procedure for me - but I was missing the types.cursorvar variable. I'm assuming that you have this created in your database someplace. This is a pretty interesting idea! Here is the new proc: CREATE OR REPLACE PROCEDURE Generate_Select_All_Proc ( P_OWNER_NAME IN DBA_TABLES.OWNER%TYPE, P_TABLE_NAME IN DBA_TABLES.TABLE_NAME%TYPE) IS err_num BINARY_INTEGER; err_msg VARCHAR(300); v_sql VARCHAR2(2000) := NULL; BEGIN v_sql := 'CREATE OR REPLACE PROCEDURE ' || P_TABLE_NAME || '_SELECT_ALL_PROC ( ' || ' P_ALL_ROWS_CURSOR OUT types.cursorvar ' || ' ) IS ' || ' BEGIN ' || ' OPEN P_ALL_ROWS_CURSOR FOR ' || ' SELECT * FROM ' || P_OWNER_NAME||'.'|| P_TABLE_NAME || ' ORDER BY 1; ' || 'END;'; v_sql := RTRIM(v_sql,' '); dbms_output.put_line (SUBSTR(v_sql,1,255)); dbms_output.put_line (SUBSTR(v_sql,256,255)); EXECUTE IMMEDIATE v_sql; EXCEPTION WHEN OTHERS THEN err_num := SQLCODE; err_msg := SQLERRM; RAISE_APPLICATION_ERROR(-20011,'Oracle error '||err_num|| 'generate_select_all_proc : '||err_msg); END Generate_Select_All_Proc; Good Luck! Tom Mercadante Oracle Certified Professional -----Original Message----- From: Duret, Kathy [mailto:kduret@xxxxxxxxxxxxxxxxxxxx] Sent: Wednesday, April 14, 2004 5:50 PM To: oracle-l@xxxxxxxxxxxxx Subject: dynamic dynamic sql within a procedure - ora_00911 Ok, I am trying to create a sql generator. I am trying to start out simple. What I want to do it to pass an owner, table name into a procedure and have it create a procedure for this owner table name In this case I want to create a procedure that will create a procedure tablename_select_all_proc that will select * from owner.tablename The syntax when I pull it apart looks ok. But when I run it I get an ora_00911, the procedure and output are below. It looks to me as if it is splicing the lines up. Instead of using the execute immediate do I have to use dbms_sql to parse and fetch this sql? Or is there a way to do this? platform 10G, Solaris. Thanks, Kathy -------------- Procedure CREATE OR REPLACE PROCEDURE GENERATE_SELECT_ALL_PROC ( P_OWNER_NAME in DBA_TABLES.OWNER%TYPE, P_TABLE_NAME in DBA_TABLES.TABLE_NAME%TYPE) IS err_num binary_integer; err_msg varchar(300); v_sql varchar2(2000) := null; BEGIN v_sql := 'Select ''CREATE OR REPLACE PROCEDURE '' || P_TABLE_NAME || ''_SELECT_ALL_PROC ( '' || chr(10)|| '' P_ALL_ROWS_CURSOR out types.cursorvar '' || '' ) is '' || '' Begin '' || chr(10) || '' open P_ALL_ROWS_CURSOR for '' || '' select * from '' || P_OWNER_NAME||''.''|| P_TABLE_NAME || '' order by 1; '' || chr(10) || '' END '' || P_TABLE_NAME||''_SELECT_ALL_PROC;'' from dual;'; v_sql := rtrim(v_sql,' '); dbms_output.put_line (substr(v_sql,1,255)); dbms_output.put_line (substr(v_sql,256,255)); execute immediate v_sql; EXCEPTION WHEN OTHERS THEN err_num := SQLCODE; err_msg := SQLERRM; raise_application_error(-20011,'Oracle error '||err_num||' generate_select_all_proc : '||err_msg); END generate_select_all_proc; ----------------------- OUTPUT FROM DBMS_PUT_LINE Select 'CREATE OR REPLACE PROCEDURE ' || P_TABLE_NAME || '_SELECT_ALL_PROC ( ' || chr(10)|| ' P_ALL_ROWS_CURSOR out types.cursorvar ' || ' ) is ' || ' Begin ' || chr(10) || ' open P_ALL_ROWS_CURSOR for ' || ' select * from ' || P_OWNER _NAME||'.'|| P_TABLE_NAME || ' order by 1; ' || chr(10) || ' END ' || P_TABLE_NAME||'_SELECT_ALL_PROC;' from dual; declare * ERROR at line 1: ORA-00911: invalid character ORA-06512: at line 26 This transmission contains information solely for intended recipient and may be privileged, confidential and/or otherwise protect from disclosure. If you are not the intended recipient, please contact the sender and delete all copies of this transmission. This message and/or the materials contained herein are not an offer to sell, or a solicitation of an offer to buy, any securities or other instruments. The information has been obtained or derived from sources believed by us to be reliable, but we do not represent that it is accurate or complete. Any opinions or estimates contained in this information constitute our judgment as of this date and are subject to change without notice. Any information you share with us will be used in the operation of our business, and we do not request and do not want any material, nonpublic information. Absent an express prior written agreement, we are not agreeing to treat any information confidentially and will use any and all information and reserve the right to publish or disclose any information you share with us. ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- This transmission contains information solely for intended recipient and may be privileged, confidential and/or otherwise protect from disclosure. If you are not the intended recipient, please contact the sender and delete all copies of this transmission. This message and/or the materials contained herein are not an offer to sell, or a solicitation of an offer to buy, any securities or other instruments. The information has been obtained or derived from sources believed by us to be reliable, but we do not represent that it is accurate or complete. Any opinions or estimates contained in this information constitute our judgment as of this date and are subject to change without notice. Any information you share with us will be used in the operation of our business, and we do not request and do not want any material, nonpublic information. Absent an express prior written agreement, we are not agreeing to treat any information confidentially and will use any and all information and reserve the right to publish or disclose any information you share with us. ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- This transmission contains information solely for intended recipient and may be privileged, confidential and/or otherwise protect from disclosure. If you are not the intended recipient, please contact the sender and delete all copies of this transmission. This message and/or the materials contained herein are not an offer to sell, or a solicitation of an offer to buy, any securities or other instruments. The information has been obtained or derived from sources believed by us to be reliable, but we do not represent that it is accurate or complete. Any opinions or estimates contained in this information constitute our judgment as of this date and are subject to change without notice. Any information you share with us will be used in the operation of our business, and we do not request and do not want any material, nonpublic information. Absent an express prior written agreement, we are not agreeing to treat any information confidentially and will use any and all information and reserve the right to publish or disclose any information you share with us. ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------