RE: dynamic dynamic sql within a procedure - ora_00911
- From: "Duret, Kathy" <kduret@xxxxxxxxxxxxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 16 Apr 2004 12:22:54 -0500
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
-----------------------------------------------------------------
Other related posts:
