Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    5

    Unanswered: displaying value of a CLOB

    Hi all,

    I have written a PL/SQL function that returns a CLOB.

    I now want to execute the function to see if it is working the way I expect it to.

    To do this, I have logged into SQL*Plus, and entered the following commands:

    variable foo clob;
    exec :foo := my_function();
    print foo;

    The problem is, is that this only outputs the first [small] part of the CLOB. Is there a way that I can examine the contents of a CLOB from SQL*Plus?

    I have also tried adding code to the inside of the function at the very end like this (where theClobRetVal is a CLOB variable has been populated with data and is about to be returned from the function:

    DBMS_LOB.READ( theClobRetVal,80,1,theVarChar);
    DBMS_OUTPUT.PUT_LINE('Data 1: ' || theVarChar);
    DBMS_LOB.READ( theClobRetVal,80,81,theVarChar);
    DBMS_OUTPUT.PUT_LINE('Data 2: ' || theVarChar);
    DBMS_OUTPUT.PUT_LINE('finishing...');
    RETURN theClobRetVal;

    However, when I re-execute the function using the method

    set serverout on
    exec :foo := my_function();

    I still don't get anything output to the screen, other than

    PL/SQL procedure successfully completed.

    Can anyone tell me how I can examine the contents of this CLOB variable?

    thanks heaps guys - I've been searching the web, and various technical books, but so far to no avail...

    thanks

    David Buddrige.

  2. #2
    Join Date
    Feb 2004
    Posts
    5

    Re: displaying value of a CLOB

    ok - the first problem of the dbms_output.put_line() statements not outputting was a) because I'd accidentally put them in a commented out section, and b) the syntax of the DBMS_LOB.READ( theClobRetVal,80,1,theVarChar); statements is incorrect....

    I am still not able to see the contents of the clob though...

    any suggestions very welcome. 8-)

    thanks

    David.


    Originally posted by dbuddrige
    Hi all,

    I have written a PL/SQL function that returns a CLOB.

    I now want to execute the function to see if it is working the way I expect it to.

    To do this, I have logged into SQL*Plus, and entered the following commands:

    variable foo clob;
    exec :foo := my_function();
    print foo;

    The problem is, is that this only outputs the first [small] part of the CLOB. Is there a way that I can examine the contents of a CLOB from SQL*Plus?

    I have also tried adding code to the inside of the function at the very end like this (where theClobRetVal is a CLOB variable has been populated with data and is about to be returned from the function:

    DBMS_LOB.READ( theClobRetVal,80,1,theVarChar);
    DBMS_OUTPUT.PUT_LINE('Data 1: ' || theVarChar);
    DBMS_LOB.READ( theClobRetVal,80,81,theVarChar);
    DBMS_OUTPUT.PUT_LINE('Data 2: ' || theVarChar);
    DBMS_OUTPUT.PUT_LINE('finishing...');
    RETURN theClobRetVal;

    However, when I re-execute the function using the method

    set serverout on
    exec :foo := my_function();

    I still don't get anything output to the screen, other than

    PL/SQL procedure successfully completed.

    Can anyone tell me how I can examine the contents of this CLOB variable?

    thanks heaps guys - I've been searching the web, and various technical books, but so far to no avail...

    thanks

    David Buddrige.

  3. #3
    Join Date
    Jul 2003
    Location
    Near Paris France
    Posts
    60

    Re: displaying value of a CLOB

    Try it with OracleTool availabl at this adress

    http://membres.lycos.fr/pmscontact/

    It is free

  4. #4
    Join Date
    Feb 2004
    Posts
    5

    Re: displaying value of a CLOB

    Unfortunately, when I attempt to execute the program it brings up a message saying:

    "Needs OracleTool PRO Version to be executed".

    cheers

    David.


    Originally posted by pmscontact
    Try it with OracleTool availabl at this adress

    http://membres.lycos.fr/pmscontact/

    It is free

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •