Coldfusion with Oracle - debugging intermittent error

Posted on 2011-10-26
Last Modified: 2012-06-27
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 “


“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()>


	adminObj = createObject("component","cfide.adminapi.administrator"); 
	// Instantiate the data source object. 
	myObj = createObject("component","cfide.adminapi.datasource"); 
	// Create a DSN. 
	    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();


Open in new window

Question by:roger_v
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    Are you doing dynamic SQL?  Not a cold fusion person but can help with the Oracle SQL piece.
    LVL 1

    Author Comment


    So the code snippet above is not the problem? Or is it?
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    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.
    LVL 1

    Author Comment

    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#

    I'm not sure which one is causing the error though.
    LVL 76

    Accepted Solution

    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.
    LVL 1

    Author Closing Comment


    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!
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    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.


    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Suggested Solutions

    Hi. There are several upload tutorials using jquery and coldfusion. I found a very interesting one here Upload Your Files using Jquery & ColdFusion and Preview them ( . I did keep the main js functions but made sever…
    From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
    This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
    This video shows how to recover a database from a user managed backup

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now