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

Posted on 2012-09-13
Last Modified: 2012-09-19

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.

Question by:structuredweb
    LVL 20

    Accepted Solution

    LVL 1

    Author Comment

    the table of CLOB, or using the Java constructor, look promising. i will try this on Monday
    LVL 1

    Author Closing Comment

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

    Expert Comment

    Weird that none of the standard functions (toString, etc..) can convert it automatically. But good there's a simple solution :)

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Java Flight Recorder and Java Mission Control together create a complete tool chain to continuously collect low level and detailed runtime information enabling after-the-fact incident analysis. Java Flight Recorder is a profiling and event collectio…
    Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.

    733 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

    20 Experts available now in Live!

    Get 1:1 Help Now