RE: dynamic dynamic sql within a procedure - ora_00911
- From: "Duret, Kathy" <kduret@xxxxxxxxxxxxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 15 Apr 2004 08:33:21 -0500
Found one of my problems. I left the ; at the end of dual ;
Now I am getting:
ERROR at line 1:
ORA-20011: Oracle error -904 generate_select_all_proc : ORA-00904:
"P_TABLE_NAME": invalid identifier
ORA-06512: at "INVADMIN.GENERATE_SELECT_ALL_PROC", line 57
ORA-06512: at line 1
Trying to use bind variables and get the same results.
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 ||''. :T''||
'' order by 1; using '' || P_TABLE_NAME || chr(10) ||
'' END; ''
from dual';
-----Original Message-----
From: Duret, Kathy [mailto:kduret@xxxxxxxxxxxxxxxxxxxx]
Sent: Wednesday, April 14, 2004 4: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
-----------------------------------------------------------------
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
-----------------------------------------------------------------
Other related posts:
