Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: dynamic dynamic sql within a procedure - ora_00911

RE: dynamic dynamic sql within a procedure - ora_00911

From: Ruth Gramolini <rgramolini_at_tax.state.vt.us>
Date: Thu, 15 Apr 2004 10:23:19 -0400
Message-ID: <002401c422f5$33695710$8459699f@vttaxnet.tax.state.vt.us>


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_at_freelists.org
  [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Mercadante, Thomas F   Sent: Thursday, April 15, 2004 7:57 AM   To: 'oracle-l_at_freelists.org'
  Cc: 'kduret_at_starkinvestments.com'
  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_at_starkinvestments.com]   Sent: Wednesday, April 14, 2004 5:50 PM   To: oracle-l_at_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

  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;

  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_at_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_at_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_at_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
-----------------------------------------------------------------
Received on Thu Apr 15 2004 - 10:47:12 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US