This discussion is archived
5 Replies Latest reply: Jun 19, 2012 2:26 PM by rp0428 RSS

CallableStatement with NCLOB param

845675 Newbie
Currently Being Moderated
Hi all,
I've a stored procedure and i should call it through my program.
I have to pass a parameter in it as NCLOB but i receive it as org.w3c.dom.Document (it's an xml)
How should i do it?
Thanks in advance!

Edited by: wkgrp on 19-giu-2012 11.02
  • 1. Re: CallableStatement with NCLOB param
    BIJ001 Explorer
    Currently Being Moderated
    Is your question essentially "how to convert a org.w3c.dom.Document into an XML String?"

    [www.lmgtfy.com/?q=convert+Document+into+XML+JAVA]
  • 2. Re: CallableStatement with NCLOB param
    845675 Newbie
    Currently Being Moderated
    Thanks for the reply but it's not what im looking for.
    I'll try to explain it better.
    Here is the code.
     
    public Clob callStoredProcedure(Connection iconn, Document xmlRequest) {
         CallableStatement proc_stmt = null;
         ResultSet rs = null;
         Clob c = null;
         Clob r = null;
         try {
              proc_stmt = iconn.prepareCall("{ call MyStoredProcedure(?, ?) }");
              proc_stmt.set ? ? ? ? ? ? ?;
              proc_stmt.setClob(2, c);
              rs = proc_stmt.executeQuery();
              if (rs.next()) {
                   Clob r = rs.getClob(1);
              } else {
                   System.out.println("NO RESULT");
              }
         } catch (SQLException e) {
              // TODO Auto-generated catch block
              e.printStackTrace();
         } catch (FileNotFoundException e) {
              // TODO Auto-generated catch block
              e.printStackTrace();
         }
              return r;
    }
    I've to pass to MyStoredProcedure the first parameter - xmlRequest - as a Clob and i dont know how to "cast" or "convert" and which set i've to use to do it properly!
    Thanks!
  • 3. Re: CallableStatement with NCLOB param
    BIJ001 Explorer
    Currently Being Moderated
    java.sql.Clob has this method:
    setCharacterStream
    
    Writer setCharacterStream(long pos)
                              throws SQLException
    /*
    Retrieves a stream to be used to write a stream of Unicode characters to the CLOB value that this Clob object represents, at position pos. 
    */
    If I have to write Blob values into a (btw Oracle) database using stored procedures, I use the Blob parameter of a stored procedure from my Java program as follows. I suggest the DB guys to create a new Blob and return its handle, and then I get the stream and write it through from the Java program in the same transaction. This works.
  • 4. Re: CallableStatement with NCLOB param
    845675 Newbie
    Currently Being Moderated
    Thanks, i think this is the way I should follow.

    I guess the SP is well-done, as you said.
    It's declared in this way:
    create or replace
    PROCEDURE  "MyStoredProcedure" (
    STR IN NCLOB,
    RES OUT NCLOB
    )
    Am i setting and reading wrong from the rs?
    Thanks for helping me!
  • 5. Re: CallableStatement with NCLOB param
    rp0428 Guru
    Currently Being Moderated
    >
    Am i setting and reading wrong from the rs?
    >
    Yes - If you are working with NCLOBs then you need to use the NCLOB methods; you are using the CLOB methods.

    See the examples in the JDBC Developer's Guide
    http://docs.oracle.com/cd/B28359_01/java.111/b31224.pdf

    See the section 'Working with BLOBs, CLOBs and NCLOBs' in Chapter 14. It has examples of reading and writing NCLOBs using CallableStatements.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points