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

LVL 1
roger vAsked:
Who is Participating?
 
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
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.