my DBA is having problems getting the his scripts run on the Oracle server (he does not have direct access and we have to pass off anything to a higher dba to run WHENEVER he may have a chance) and would like me to create some pages where he can drop his PL/SQL scripts between CFQUERY tags to run.
I know I've seen posting in the past where people have done this and I have even reviewed the OraFusion (http://www.orafusion.com) site with their examples and work-around for CF5:
========================================
Running Anonymous PL/SQL blocks: Anonymous PL/SQL blocks can be executed through cfquery with ODBC drivers - you simply enclose your code block within a matching pair of cfquery tags with the appropriate data source attributes. If you try this with native drivers, however, you will get a ORA-06550 error. There is a workaround which was pointed out to me by Mike Morris of KLA - Tencor (Thanks, Mike!) : assign the code block to a ColdFusion variable and then output the variable contents within cfquery, as shown below.
< cfset plsql_code=
"declare
cursor c_table is
select
*
from
your_table;
begin
for r_table in c_table loop
null;
end loop;
end;">
<cfquery name="yourQuery"
datasource="yourNativeDSN"
username="yourUsername"
password="yourPwd">
#plsql_code#
</cfquery>
========================================
When I tried to something as simple as:
========================================
<cfset plsqlcode=
"BEGIN
UPDATE SYSTEMINFO
SET LOGINPAGEMESSAGE = 'Welcome to here'
WHERE DATACALL = 2;
END;">
<cfquery name="davidTest" datasource="#mysession.dcname#">
#plsqlcode#
</cfquery>
========================================
It in fact returns an ORA-06550 error.
However, if I do this (put the entire pl/sql program on one line):
========================================
<!--- set PL/SQL code to be on a SINGLE line instead of multiple lines when assigning to a variable --->
<cfset plsqlcode="BEGIN UPDATE SYSTEMINFO SET LOGINPAGEMESSAGE = 'Welcome to here' WHERE DATACALL = 2 END;">
<cfquery name="davidTest" datasource="#mysession.dcname#">
#plsqlcode#
</cfquery>
========================================
The query runs the PL/SQL code just fine.
Okay great! Unfortunately the PL/SQL scripts that the dba wants to run are NOT SIMPLE but near (and over) 100+ lines when viewed in SQL+ .... this could be a nightmare trying to get that amount of PL/SQL code onto one line and subsequently edit.
Yes I realize this is nothing more then a simple UPDATE statement that really doesn't need to have the 'BEGIN' and 'END' as part of it.
(But you don't want (or need) to read 100+ lines of pl/sql code to get the idea of the problem.)
So, What I'm looking for is the 'correct' syntax to use within the CFQUERY tags for PL/SQL code.
I hope I've expressed my problem a bit more thoroughly and that it's not really the SIMPLE EXAMPLE I'm trying to resolve but what the true coding technique should be to do such a thing.
Specs: WinNT / CF5 / Oracle 9i
Thanks in advance,
-------------------------
Steven Semrau
SRA International, Inc.
Web Solutions Group
Senior Member, Professional Staff
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
