Oracle PLSQL in CFQUERY
Sometimes, you may want to execute an anonymous PL/SQL block directly from within the CFQUERY tag instead of calling a stored procedure or function. So you write this (simplified) code in your ColdFusion template:
<cfquery name="q" datasource="yourDSN">
declare
x number;
begin
x := 0;
end;
</cfquery>
Only to get the following error when executing the above code:
Error Executing Database Query.
[Macromedia][Oracle JDBC Driver][Oracle]ORA-06550:
line 1, column 8: PLS-00103: Encountered the symbol ""
when expecting one of the following: begin function
package pragma procedure ...
The workaround is to put the PL/SQL code in a ColdFusion variable and use the variable inside the CFQUERY tag:
<cfset variables.plsql = "
declare
x number;
begin
x := 0;
end;
"
>
<cfquery name="q" datasource="yourDSN">
#variables.plsql#
</cfquery>
The above works like a charm!
Related articles:
- Writing/Reading images to/from DB
- ColdFusion to Oracle via a Boolean
- Oracle REF CURSOR and ColdFusion
You are a genius!
I tried tons of approaches: , writing the SQL statement in one-single line, removing all indents, using space instead of tab for indent, etc. and all failed… but I’ve never think of your approach.
Even the Oralce forum does not provide any solution! You worth the praise~
Thanks for saving me from days of googling~~~
Your post gave us a good ‘workaround’. After further investigation, we found that if the file is saved in DOS format with end-of-line as chr(13)chr(10), we get this error. If the file is saved in UNIX format with EOL as chr(10), it works!!!!
Joe
dOES ANYONE KNOW IF THERE IS AWAY VIA COLDFUSION TO PULL A FILE FROM THE PC DRIVE (I.E. d:)AND MANIPULATE THE DATA?
Dave, it looks like you forgot your Caps Lock on. You may use cffile to manage interactions with server files.
I have an application that is half Fusebox and half regular ColdFusion. When I set Session Parameters in a COldFusion Program, ad soon as I call the fuseaction the Session Paramters are lost? How do get the Fuseaction receiving program to recognize the Session parameters? THX
Dave, I suggest you ask your question on the Fusebox forums.