how to retrieve CLOB variable from Oracle stored procedure to coldFusion 9
Posted on 2012-09-13
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:
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.