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

Hello,

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

PROCEDURE yyyy (
            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:
      <cfprocparam
                  type="OUT"
                  cfsqltype="CF_SQL_CLOB"
                  variable="OUTcontent">

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.

thanks!
LVL 1
structuredwebAsked:
Who is Participating?
 
structuredwebAuthor Commented:
the table of CLOB, or using the Java constructor, look promising. i will try this on Monday
0
 
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

<cfstoredproc
......
    <cfprocparam
    type="OUT"
    cfsqltype="CF_SQL_CLOB"
    DBVARNAME="OUTContent"
    variable="MyOUTContent">
......
</cfstoredproc>

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


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