Re: Why use Pro*C for spooling result sets to flat files when you can do the same thing in SQL*Plus?

More specifically, it is a data type limit. Varchar2 can hold 4k, if you'd use CLOB instead, you get a better result (set long and set chunksize to influence how much of your CLOB is displayed):


10:05:20 [OPS$LINGENT@HOME1] SQL> create table test ( a varchar2(4000) , b clob );

Table created.

10:05:32 [OPS$LINGENT@HOME1] SQL> insert into test values( lpad('a', 4000, 'b' ) , lpad('a', 4000, 'b' ) );

1 row created.

10:06:02 [OPS$LINGENT@HOME1] SQL> select a||a from test;
select a||a from test
                *
ERROR at line 1:
ORA-01489: result of string concatenation is too long


10:06:12 [OPS$LINGENT@HOME1] SQL> select b||b from test;

B||B
================================================================================
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb

1 row selected.

Still, in SQL*Plus the LONG variable is limited to 2,000,000,000 bytes (in 10.2). If you store the terabytes that Oracle insists you can in a CLOB, and you actually want to retrieve the lot, SQL*Plus may not be the best way to do it...

Cheers,
Tony

Steve Baldwin wrote:
That is an Oracle limitation, not an SQL*Plus one. You would receive the same error if that statement were executed from a Pro*C program.

Steve

On Thu, Sep 2, 2010 at 9:40 AM, Michael Dinh <mdinh@xxxxxxxxx <mailto:mdinh@xxxxxxxxx>> wrote:

    Here is a simple test case.  May be I am doing something wrong,
    but was not able to figure it out.

    create table t1(id varchar2(4000));

    insert into t1 values (lpad('a', 4000, 'b' ));

    set linesize 4000

    select id||id from t1;

    ERROR at line 1:

    ORA-01489: result of string concatenation is too long

    Michael Dinh : XIFIN : 858.436.2929

    NOTICE OF CONFIDENTIALITY - This material is intended for the use
    of the individual or entity to which it is addressed, and may
    contain information that is privileged, confidential and exempt
    from disclosure under applicable laws.  BE FURTHER ADVISED THAT
    THIS EMAIL MAY CONTAIN PROTECTED HEALTH INFORMATION (PHI). BY
    ACCEPTING THIS MESSAGE, YOU ACKNOWLEDGE THE FOREGOING, AND AGREE
    AS FOLLOWS: YOU AGREE TO NOT DISCLOSE TO ANY THIRD PARTY ANY PHI
    CONTAINED HEREIN, EXCEPT AS EXPRESSLY PERMITTED AND ONLY TO THE
    EXTENT NECESSARY TO PERFORM YOUR OBLIGATIONS RELATING TO THE
    RECEIPT OF THIS MESSAGE.  If the reader of this email (and
    attachments) is not the intended recipient, you are hereby
    notified that any dissemination, distribution or copying of this
    communication is strictly prohibited. Please notify the sender of
    the error and delete the e-mail you received. Thank you.

    *From:* oracle-l-bounce@xxxxxxxxxxxxx
    <mailto:oracle-l-bounce@xxxxxxxxxxxxx>
    [mailto:oracle-l-bounce@xxxxxxxxxxxxx
    <mailto:oracle-l-bounce@xxxxxxxxxxxxx>] *On Behalf Of *Bill Myers
    *Sent:* Wednesday, September 01, 2010 4:15 PM
    *To:* Michael Dinh
    *Cc:* oracle-l@xxxxxxxxxxxxx <mailto:oracle-l@xxxxxxxxxxxxx>
    *Subject:* Re: Why use Pro*C for spooling result sets to flat
    files when you can do the same thing in SQL*Plus?

    Michael,
    It looks like SQL*Plus has a max total row width of 32,767
    characters
    
(http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/apa.htm#SQPUG141).
    Is this what you are referring to?

    Thanks.
    Bill

    On Wed, Sep 1, 2010 at 3:46 PM, Michael Dinh <mdinh@xxxxxxxxx
    <mailto:mdinh@xxxxxxxxx>> wrote:

    IF all the concatenated columns exceed 4000 characters, then
    SQL*Plus will choke.

    We don't use Pro*C; however, it was done in Java instead.

    Michael Dinh : XIFIN : 858.436.2929

    NOTICE OF CONFIDENTIALITY - This material is intended for the use
    of the individual or entity to which it is addressed, and may
    contain information that is privileged, confidential and exempt
    from disclosure under applicable laws.  BE FURTHER ADVISED THAT
    THIS EMAIL MAY CONTAIN PROTECTED HEALTH INFORMATION (PHI). BY
    ACCEPTING THIS MESSAGE, YOU ACKNOWLEDGE THE FOREGOING, AND AGREE
    AS FOLLOWS: YOU AGREE TO NOT DISCLOSE TO ANY THIRD PARTY ANY PHI
    CONTAINED HEREIN, EXCEPT AS EXPRESSLY PERMITTED AND ONLY TO THE
    EXTENT NECESSARY TO PERFORM YOUR OBLIGATIONS RELATING TO THE
    RECEIPT OF THIS MESSAGE.  If the reader of this email (and
    attachments) is not the intended recipient, you are hereby
    notified that any dissemination, distribution or copying of this
    communication is strictly prohibited. Please notify the sender of
    the error and delete the e-mail you received. Thank you.

    *From:* oracle-l-bounce@xxxxxxxxxxxxx
    <mailto:oracle-l-bounce@xxxxxxxxxxxxx>
    [mailto:oracle-l-bounce@xxxxxxxxxxxxx
    <mailto:oracle-l-bounce@xxxxxxxxxxxxx>] *On Behalf Of *Bill Myers
    *Sent:* Wednesday, September 01, 2010 3:24 PM
    *To:* oracle-l@xxxxxxxxxxxxx <mailto:oracle-l@xxxxxxxxxxxxx>
    *Subject:* Why use Pro*C for spooling result sets to flat files
    when you can do the same thing in SQL*Plus?

    In my previous post,
    
http://www.freelists.org/post/oracle-l/Any-valid-security-concerns-using-Data-Pump-over-conventional-expimp,
    I mentioned that the DBAs at my new job use Pro*C to export data
    to flat files. So I asked one of them in a meeting: "why not use
    SQL*Plus instead?" Response after a long awkward stare: "you tell
    me". I have searched the forums and docs to no avail (besides
    maybe some speed advantage).

    So, is there any real advantage to using Pro*C over SQL*Plus to
    spool result sets to flat files? I certainly don't want to become
    a Pro*C expert, so any ideas would be greatly appreciated.

    Thanks in advance.
    Bill
    9i OCA/10g OCP DBA


---------------------------------------------------------------------------------------
This email is intended solely for the use of the addressee and may
contain information that is confidential, proprietary, or both.
If you receive this email in error please immediately notify the
sender and delete the email.
---------------------------------------------------------------------------------------


--
http://www.freelists.org/webpage/oracle-l


Other related posts: