Home of the ColdFusion Community
|
Mailing Lists
|
Home /
Groups /
ColdFusion Talk (CF-Talk)
Help executing PL/SQL
Author: Semrau Steven Ctr SAF/IE
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32830#164865
Hehe, that's okay I appreciate all the help you've given -
These are scripts for our development so that drop/recreate tables - pre-populate
information - and so on can be done by us without having to go through these
'higher-up' DBAs every time we need to mess with the data.
I just wish I could get this figured out so I could tell the dba to just format
your scripts like 'this and that' and drop them between the cfquery tags.
Steve
Hm. Then I don't know what to tell ya.
> Same results.
>
> <cfsavecontent variable="plsql">
> BEGIN UPDATE SYSTEMINFO SET LOGINPAGEMESSAGE = 'Welcome to here' WHERE
DATACALL = 2; END;
----- Excess quoted text cut - see Original Post for more -----
ended ORA-06550: line 1, column 7: PL/SQL: SQL Statement ignored
>
_____
Author: Whittingham, P
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32830#164864
we have native driver with no problems...we don't use ODBC.
Pat
Deanna,
I'm sorry I overlooked your statement before:
It does work if it's all on one line, not set as a variable first? (I can't test
it, as we don't have ODBC datasources set up.)
I am trying to do this with the Oracle Native drivers not the ODBC.
Okay, what if you take that one (without line breaks) and use cfsavecontent
to save it to a variable and try to run it. Does that work? (In other words,
I'm trying to figure out if it's a problem with the replace statement, or a
problem with the way CF is passing the variable into the cfquery.)
> Yes, doing this:
>
> <cfquery name="davidTest" datasource="#mysession.dcname#">
> BEGIN UPDATE SYSTEMINFO SET LOGINPAGEMESSAGE = 'Welcome to here' WHERE
DATACALL = 2; END;
----- Excess quoted text cut - see Original Post for more -----
DATACALL = 2; END;
----- Excess quoted text cut - see Original Post for more -----
_____
_____
Author: Semrau Steven Ctr SAF/IE
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32830#164855
Deanna,
I'm sorry I overlooked your statement before:
It does work if it's all on one line, not set as a variable first? (I can't test
it, as we don't have ODBC datasources set up.)
I am trying to do this with the Oracle Native drivers not the ODBC.
Okay, what if you take that one (without line breaks) and use cfsavecontent
to save it to a variable and try to run it. Does that work? (In other words,
I'm trying to figure out if it's a problem with the replace statement, or a
problem with the way CF is passing the variable into the cfquery.)
> Yes, doing this:
>
> <cfquery name="davidTest" datasource="#mysession.dcname#">
> BEGIN UPDATE SYSTEMINFO SET LOGINPAGEMESSAGE = 'Welcome to here' WHERE
DATACALL = 2; END;
----- Excess quoted text cut - see Original Post for more -----
DATACALL = 2; END;
----- Excess quoted text cut - see Original Post for more -----
_____
Author: Deanna Schneider
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32830#164852
Hm. Then I don't know what to tell ya.
> Same results.
>
> <cfsavecontent variable="plsql">
> BEGIN UPDATE SYSTEMINFO SET LOGINPAGEMESSAGE = 'Welcome to here' WHERE
DATACALL = 2; END;
----- Excess quoted text cut - see Original Post for more -----
ended ORA-06550: line 1, column 7: PL/SQL: SQL Statement ignored
>
Author: Semrau Steven Ctr SAF/IE
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32830#164826
Same results.
<cfsavecontent variable="plsql">
BEGIN UPDATE SYSTEMINFO SET LOGINPAGEMESSAGE = 'Welcome to here' WHERE DATACALL =
2; END;
</cfsavecontent>
<cfquery name="davidTest" datasource="#mysession.dcname#">
#plsql#
</cfquery>
results in:
ORA-06550: line 1, column 50: PL/SQL: ORA-00933: SQL command not properly ended
ORA-06550: line 1, column 7: PL/SQL: SQL Statement ignored
SQL = "BEGIN UPDATE SYSTEMINFO SET LOGINPAGEMESSAGE = ''Welcome to here'' WHERE
DATACALL = 2; END;"
Okay, what if you take that one (without line breaks) and use cfsavecontent
to save it to a variable and try to run it. Does that work? (In other words,
I'm trying to figure out if it's a problem with the replace statement, or a
problem with the way CF is passing the variable into the cfquery.)
> Yes, doing this:
>
> <cfquery name="davidTest" datasource="#mysession.dcname#">
> BEGIN UPDATE SYSTEMINFO SET LOGINPAGEMESSAGE = 'Welcome to here' WHERE
DATACALL = 2; END;
----- Excess quoted text cut - see Original Post for more -----
DATACALL = 2; END;
----- Excess quoted text cut - see Original Post for more -----
_____
Author: Deanna Schneider
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32830#164725
Okay, what if you take that one (without line breaks) and use cfsavecontent
to save it to a variable and try to run it. Does that work? (In other words,
I'm trying to figure out if it's a problem with the replace statement, or a
problem with the way CF is passing the variable into the cfquery.)
> Yes, doing this:
>
> <cfquery name="davidTest" datasource="#mysession.dcname#">
> BEGIN UPDATE SYSTEMINFO SET LOGINPAGEMESSAGE = 'Welcome to here' WHERE
DATACALL = 2; END;
----- Excess quoted text cut - see Original Post for more -----
DATACALL = 2; END;
----- Excess quoted text cut - see Original Post for more -----
Author: Semrau Steven Ctr SAF/IE
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32830#164724
Pat,
thanks for your reply. If you look at my original posting I have tried that
route as suggested by OraFusion ( http://www.orafusion.com). I have also tried
using the CFSAVECONTENT, then replacing chr(10)chr(13) with a space; I have also
tried by putting BR tags at the end of each line and then replacing the BR tags
with a space - same results every time.
Steve
just use a variable to build your pl/sql... and concat it.
Pat
Yes, doing this:
<cfquery name="davidTest" datasource="#mysession.dcname#">
BEGIN UPDATE SYSTEMINFO SET LOGINPAGEMESSAGE = 'Welcome to here' WHERE
DATACALL = 2; END;
</cfquery>
returns:
davidTest (Records=0, Time=94ms)
SQL =
BEGIN UPDATE SYSTEMINFO SET LOGINPAGEMESSAGE = 'Welcome to here' WHERE DATACALL =
2; END;
And doing this:
<cfquery name="davidTest" datasource="#mysession.dcname#">
BEGIN
UPDATE SYSTEMINFO
SET LOGINPAGEMESSAGE = 'Welcome to here'
WHERE DATACALL = 2;
END;
</cfquery>
returns:
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
SQL = "BEGIN UPDATE SYSTEMINFO SET LOGINPAGEMESSAGE = 'Welcome to here' WHERE
DATACALL = 2; END;"
How irritating is that!? Hehehe - man I'm at wits end trying to figure this out.
It does work if it's all on one line, not set as a variable first? (I can't
test it, as we don't have ODBC datasources set up.)
> Oracle Error Code = 6550
>
>
> ORA-06550: line 1, column 6: PLS-00103: Encountered the symbol "" when
expecting one of the following: begin case declare exit for go to 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
>
_____
_____
_____
Author: Whittingham, P
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32830#164718
just use a variable to build your pl/sql... and concat it.
Pat
Yes, doing this:
<cfquery name="davidTest" datasource="#mysession.dcname#">
BEGIN UPDATE SYSTEMINFO SET LOGINPAGEMESSAGE = 'Welcome to here' WHERE
DATACALL = 2; END;
</cfquery>
returns:
davidTest (Records=0, Time=94ms)
SQL =
BEGIN UPDATE SYSTEMINFO SET LOGINPAGEMESSAGE = 'Welcome to here' WHERE DATACALL =
2; END;
And doing this:
<cfquery name="davidTest" datasource="#mysession.dcname#">
BEGIN
UPDATE SYSTEMINFO
SET LOGINPAGEMESSAGE = 'Welcome to here'
WHERE DATACALL = 2;
END;
</cfquery>
returns:
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
SQL = "BEGIN UPDATE SYSTEMINFO SET LOGINPAGEMESSAGE = 'Welcome to here' WHERE
DATACALL = 2; END;"
How irritating is that!? Hehehe - man I'm at wits end trying to figure this out.
It does work if it's all on one line, not set as a variable first? (I can't
test it, as we don't have ODBC datasources set up.)
> Oracle Error Code = 6550
>
>
> ORA-06550: line 1, column 6: PLS-00103: Encountered the symbol "" when
expecting one of the following: begin case declare exit for go to 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
>
_____
_____
Author: Semrau Steven Ctr SAF/IE
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32830#164699
Yes, doing this:
<cfquery name="davidTest" datasource="#mysession.dcname#">
BEGIN UPDATE SYSTEMINFO SET LOGINPAGEMESSAGE = 'Welcome to here' WHERE
DATACALL = 2; END;
</cfquery>
returns:
davidTest (Records=0, Time=94ms)
SQL =
BEGIN UPDATE SYSTEMINFO SET LOGINPAGEMESSAGE = 'Welcome to here' WHERE DATACALL =
2; END;
And doing this:
<cfquery name="davidTest" datasource="#mysession.dcname#">
BEGIN
UPDATE SYSTEMINFO
SET LOGINPAGEMESSAGE = 'Welcome to here'
WHERE DATACALL = 2;
END;
</cfquery>
returns:
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
SQL = "BEGIN UPDATE SYSTEMINFO SET LOGINPAGEMESSAGE = 'Welcome to here' WHERE
DATACALL = 2; END;"
How irritating is that!? Hehehe - man I'm at wits end trying to figure this out.
It does work if it's all on one line, not set as a variable first? (I can't
test it, as we don't have ODBC datasources set up.)
> Oracle Error Code = 6550
>
>
> ORA-06550: line 1, column 6: PLS-00103: Encountered the symbol "" when
expecting one of the following: begin case declare exit for go to 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
>
_____
Author: Deanna Schneider
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32830#164697
It does work if it's all on one line, not set as a variable first? (I can't
test it, as we don't have ODBC datasources set up.)
> Oracle Error Code = 6550
>
>
> ORA-06550: line 1, column 6: PLS-00103: Encountered the symbol "" when
expecting one of the following: begin case declare exit for go to 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
>
Author: Semrau Steven Ctr SAF/IE
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32830#164693
Deanna, thanks for the quick reply.... I tried what you suggested below but it
returned the same results:
Oracle Error Code = 6550
ORA-06550: line 1, column 6: PLS-00103: Encountered the symbol "" when expecting
one of the following: begin case declare exit for go to 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
SQL = "BEGIN UPDATE SYSTEMINFO SET LOGINPAGEMESSAGE = ''Welcome to here'' WHERE
DATACALL = 2; END;"
I tried replacing the 3rd attribute with just a (space) instead of the (comma) -
same error results
I tried removing the var setting for replaceList() - same error results
Argh, this is aggravating - I know it shouldn't be this hard to accomplish :( any
other suggestions?
Did you try my suggestion? Here, let me be more verbose:
<!--- Write your nicely formatted pl/sql code --->
<cfsavecontent variable="plsql">
BEGIN
UPDATE SYSTEMINFO
SET LOGINPAGEMESSAGE = 'Welcome to here'
WHERE DATACALL = 2;
END;
</cfsavecontent>
<!--- strip out the line breaks --->
<cfset plsql = replacelist(plsql, "#chr(10)#,#chr(13)#", " , ")>
<!--- run it --->
<cfquery...>
#plsql#
</cfquery>
> Here is my situation:
>
> 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:
_____
Author: Deanna Schneider
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32830#164683
Did you try my suggestion? Here, let me be more verbose:
<!--- Write your nicely formatted pl/sql code --->
<cfsavecontent variable="plsql">
BEGIN
UPDATE SYSTEMINFO
SET LOGINPAGEMESSAGE = 'Welcome to here'
WHERE DATACALL = 2;
END;
</cfsavecontent>
<!--- strip out the line breaks --->
<cfset plsql = replacelist(plsql, "#chr(10)#,#chr(13)#", " , ")>
<!--- run it --->
<cfquery...>
#plsql#
</cfquery>
> Here is my situation:
>
> 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:
Author: Semrau Steven Ctr SAF/IE
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32830#164680
Here is my situation:
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
|
May 24, 2012
|
Latest Fusion Authority Articles
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
