From oracle-l-bounce@freelists.org Thu Apr 15 06:53:56 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i3FBra832004 for ; Thu, 15 Apr 2004 06:53:41 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i3FBrV631980 for ; Thu, 15 Apr 2004 06:53:36 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 949076355BF; Thu, 15 Apr 2004 06:47:33 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 15086-37; Thu, 15 Apr 2004 06:47:33 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id DBD9463547C; Thu, 15 Apr 2004 06:47:32 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Thu, 15 Apr 2004 06:46:22 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 36401635491 for ; Thu, 15 Apr 2004 06:46:22 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 13788-79 for ; Thu, 15 Apr 2004 06:46:22 -0500 (EST) Received: from GAVHSEN0A1AD.HSEN (exchsen0a1ra.dfa.state.ny.us [198.22.236.246]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B8B7763548A for ; Thu, 15 Apr 2004 06:46:21 -0500 (EST) Received: from unknown(172.16.96.46) by GAVHSEN0A1AD.HSEN via csmap id d3112500_8ed3_11d8_81c3_0002b3c8ffd5_10332; Thu, 15 Apr 2004 07:55:43 -0400 (EDT) Received: from snysdolmail1.labor.state.ny.us ([172.16.96.117]) by exchsen0a1ra.dfa.state.ny.us with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2657.72) id HWBY8JSH; Thu, 15 Apr 2004 07:56:55 -0400 Received: by snysdolmail1.labor.state.ny.us with Internet Mail Service (5.5.2650.21) id <2BR9FQ3Z>; Thu, 15 Apr 2004 07:56:53 -0400 Message-ID: X-Sybari-Trust: da2c2f68 61ed6794 d2508de6 0000013d From: "Mercadante, Thomas F" To: "'oracle-l@freelists.org'" Cc: "'kduret@starkinvestments.com'" Subject: RE: dynamic dynamic sql within a procedure - ora_00911 Date: Thu, 15 Apr 2004 07:56:49 -0400 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2650.21) Content-type: text/plain X-Virus-Scanned: by amavisd-new at freelists.org Content-Transfer-Encoding: 8bit X-archive-position: 3138 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: thomas.mercadante@labor.state.ny.us Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org 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@starkinvestments.com] Sent: Wednesday, April 14, 2004 5:50 PM To: oracle-l@freelists.org 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@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://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@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------