Help executing PL/SQL

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

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:

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:   _____  

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 >

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 >   _____  

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 -----

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 >   _____   _____  

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 >   _____   _____   _____  

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 -----   _____  

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 >

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 -----   _____  

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 -----   _____   _____  

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 >   _____