how to retrieve CLOB variable from Oracle stored procedure to coldFusion 9


we have a stored procedure in Oracle 11g returning a CLOB parameter. looks like this:

            xxxx                 IN  NUMBER DEFAULT 0,
            INcontent        IN  CLOB,
            OUTContent    OUT CLOB,
            DESC_OUT       OUT VARCHAR2

working fine when i test it from a pl/sql block.

i'm trying to access the procedure from coldFusion 9 standard, using Oracle thin JDBC driver.

when i tried this:

i'm getting back what seems to be just  a handle to a CLOB; when i tried to change the sqltype to CF_SQL_LONGVARCHAR i'm getting an error "numeric or value error". when i put in temporary code inside the SP to save the value in a table, the content is fine, so at least CFM is passing the IN variables OK t the procedure.

How do i retrieve the variable? i know i could use workarounds like the stored procedure writing to a table then the CFM page reading it after the call but its a hack and slows down the system; any way to handle this? the CFM documentation is hazy and its hard to reconcile it with the numerous JDBC documentation out there.

Who is Participating?
structuredwebAuthor Commented:
the table of CLOB, or using the Java constructor, look promising. i will try this on Monday
structuredwebAuthor Commented:
working! i tried item 2,  the Java solution, as its the simplest and doesn't need any modification of the original stored procedure. this code snippet shows i got it to work


<cfset Layout = CreateObject("java","java.lang.String").init(MyOUTContent)>

and then "Layout" is a valid CFM variable containing the CLOB data.
Weird that none of the standard functions (toString, etc..) can convert it automatically. But good there's a simple solution :)
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.