• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 676
  • Last Modified:

Coldfusion with Oracle - debugging intermittent error

Hi Experts,

I have a Coldfusion 9 application that interfaces with Oracle 11g database. I need to log on dynamically to the oracle db and then once logged in, perform certain DB operations. I found a chunk of code online that lets me create a dynamic datasource using the cf admin api. The code works most of the time but some times I keep getting the following errors:

 “ORA-00922: missing or invalid option “

                    or
                   ----

“ORA-00911: invalid character”

Now when I do a page refresh after these errors, the code works the second time. But it is really annoying and I was wondering if any one else had a similar issue and how that was resolved? My code that builds the cf admin datasource is attached.
<cfobject name="myCFC" component="cfc.dataAccess">

<cfset mySID = sid>
<cfset myHost = targethost>
<cfset myPort = port>

<cfset tmpDSName = CreateUUID()>

<cfscript>

	adminObj = createObject("component","cfide.adminapi.administrator"); 
	adminObj.login("xxx77xx"); 
	
	// Instantiate the data source object. 
	myObj = createObject("component","cfide.adminapi.datasource"); 
	
	// Create a DSN. 
	myObj.setOracle(driver="Oracle",  
	    name= tmpDSName,  
	    host = myHost,                                                                                                           
		port = myPort, 
	    sid = mySid,
	    username = accountID, 
	    password = tmpPassword,
	    login_timeout = "30", 
	    timeout = "20", 
	    interval = 7, 
	    buffer = "64000", 
	    blob_buffer = "64000", 
	    setStringParameterAsUnicode = "false", 
	    description = "dual database", 
	    pooling = true, 
	    maxpooledstatements = 300, 
	    enableMaxConnections = "true", 
	    maxConnections = "299", 
	    enable_clob = false, 
	    enable_blob = false, 
	    disable = false, 
	    storedProc = false, 
	    alter = true, 
	    grant = true, 
	    select = true, 
	    update = false, 
	    create = false, 
	    delete = false, 
	    drop = false, 
	    revoke = false ); 
	    
	    verifyMsg = myObj.verifyDsn(tmpDSName);      

  thestruct = StructNew();

</cfscript>

Open in new window

0
roger v
Asked:
roger v
  • 4
  • 3
1 Solution
 
slightwv (䄆 Netminder) Commented:
Are you doing dynamic SQL?  Not a cold fusion person but can help with the Oracle SQL piece.
0
 
roger vAuthor Commented:
@slightwv:

So the code snippet above is not the problem? Or is it?
0
 
slightwv (䄆 Netminder) Commented:
I don't see any SQL.  Maybe it is my lack of Cold Fusion knowledge.

Are you thinking the error is being generated from the connect itself?  I guess it's possible but that is an Oracle error so it has to be making it to the database far enough to generate the error.

If so, it is cold fusion specific and I'll have to bow out.  A CF Expert should be along soon.
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
roger vAuthor Commented:
Oh I see now. Yes there is a bunch of sql being run after the connect. There is this line of pl/sql that coldfusion is running:

<cfquery datasource="#dyndsn#">
alter user #userid# identified by #generatedPwd#
</cfquery>

I'm not sure which one is causing the error though.
0
 
slightwv (䄆 Netminder) Commented:
I can see errors if special characters are used for username and/or password.

For example, if a ';' is generated in the password, it should syntax error.

Now when you 'refresh' the page, does it generate a 'new' password?  That could explain things.
0
 
roger vAuthor Commented:
@slightwv:

Yes, refresh generates a new password. Ok so, writing a log file everytime a password is generated, then checking that password anytime there is an error checking to see if any special characters are present should positively identify the issue. Will give that a shot and let you know, appreciate your help!
0
 
slightwv (䄆 Netminder) Commented:
Glad to help.

You might be able to 'quote' the password using single quotes (assuming a single quote isn't allowed in the generated password).  If so, then maybe a string replace on the password before it is passed off to Oracle.

In Oracle use two singles quotes to get one:  'wouldn''t it be great for this to work'.

note: two single quotes side by side, not a double quote.

0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now