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 -----------------------------------------------------------------