Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2012-09-13
4
Medium Priority
?
2,532 Views
Last Modified: 2012-09-19
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!
0
Comment
Question by:structuredweb
  • 2
4 Comments
 
LVL 21

Accepted Solution

by:
Amitkumar P earned 2000 total points
ID: 38399441
0
 
LVL 1

Author Comment

by:structuredweb
ID: 38399486
the table of CLOB, or using the Java constructor, look promising. i will try this on Monday
0
 
LVL 1

Author Closing Comment

by:structuredweb
ID: 38409808
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
 
LVL 53

Expert Comment

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

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authoriā€¦
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
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.
Suggested Courses
Course of the Month13 days, 7 hours left to enroll

581 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