The problem, in a nutshell, is that we (dba and me) have very limited access to the Oracle server and ZERO access to the CF5 server box and my DBA is going nutz waiting around for someone else to run his PL/SQL script every time he makes a change on the development side. I know I've seen threads, articles and the like with examples of PL/SQL between CFQUERY tags, but when I try to do the same thing - I get the error described earlier. Truly, the PL/SQL my DBA would like me to try and run with CFQUERY is 100+ lines and I figured if I can't get a simpler statement (like my example earlier) to run there is no sense in putting a 100+ line SQL out in the forum.
I found the following at <http://www.orafusion.com/> Orafusion ( http://www.orafusion.com) (see attached code).
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>
However, assigning the script to a variable first and then using it within the CFQUERY tags produces the same resulting error.
I guess I should also mention this is a CF5 box.
-----Original Message-----
From: J E VanOver [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 25, 2004 4:46 PM
To: CF-Talk
Subject: RE: Need Help with SQL inside CFQUERY
dump the "BEGIN" and "END"
<cfquery name="myName" datasource="#mydatasource#">
UPDATE SYSTEMINFO
SET LOGINPAGEMESSAGE = 'Welcome to here'
WHERE DATACALL = 2
</cfquery>
-----Original Message-----
From: Semrau Steven Ctr SAF/IE [mailto:[EMAIL PROTECTED]
Sent: Monday, May 24, 2004 1:18 PM
To: CF-Talk
Subject: Need Help with SQL inside CFQUERY
Okay, I'm new to doing straight SQL inside of a CFQUERY tag so any and all
advice / help is greatly appreciated - here is what I'm running into:
My DB guy wants (reasons I can't explain) me to run some SQL with the
CFQUERY tag. When I run the code below as is (multiple lines):
<cfquery name="myName" datasource="#mydatasource#">
BEGIN
UPDATE SYSTEMINFO
SET LOGINPAGEMESSAGE = 'Welcome to here'
WHERE DATACALL = 2;
END;
</cfquery>
I'm getting an error of:
Oracle Error Code = 6550
ORA-06550: line 1, column 7: PLS-00103: Encountered the symbol "" when
expecting one of the following: begin case declare exit for goto if loop mod
null pragma raise return select update while with << close current delete
fetch lock insert open rollback savepoint set sql execute commit forall
merge pipe
However, if I run the SQL as ONE line:
<cfquery name="myName" datasource="#mydatasource#">
BEGIN UPDATE SYSTEMINFO SET LOGINPAGEMESSAGE = 'Welcome to here' WHERE
DATACALL = 2; END;
</cfquery>
This runs fine.
Our situation is that I have SQL that is a couple hundred lines long and
putting that all on one line just doesn't make sense. I've seen messages in
the past with SQL code done line by line within a CFQUERY - where am I going
wrong here!?!
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
