Re: Why use Pro*C for spooling result sets to flat files when you can do the same thing in SQL*Plus?
Date: Thu, 02 Sep 2010 10:17:58 +1000
Message-ID: <4C7EED36.6030500_at_tpg.com.au>
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_at_HOME1] SQL> create table test ( a varchar2(4000) , b clob );
Table created.
10:05:32 [OPS$LINGENT_at_HOME1] SQL> insert into test values( lpad('a', 4000, 'b' ) , lpad('a', 4000, 'b' ) );
1 row created.
10:06:02 [OPS$LINGENT_at_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_at_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_at_xifin.com
> <mailto:mdinh_at_xifin.com>> 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_at_freelists.org
> <mailto:oracle-l-bounce_at_freelists.org>
> [mailto:oracle-l-bounce_at_freelists.org
> <mailto:oracle-l-bounce_at_freelists.org>] *On Behalf Of *Bill Myers
> *Sent:* Wednesday, September 01, 2010 4:15 PM
> *To:* Michael Dinh
> *Cc:* oracle-l_at_freelists.org <mailto:oracle-l_at_freelists.org>
> *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_at_xifin.com
> <mailto:mdinh_at_xifin.com>> 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_at_freelists.org
> <mailto:oracle-l-bounce_at_freelists.org>
> [mailto:oracle-l-bounce_at_freelists.org
> <mailto:oracle-l-bounce_at_freelists.org>] *On Behalf Of *Bill Myers
> *Sent:* Wednesday, September 01, 2010 3:24 PM
> *To:* oracle-l_at_freelists.org <mailto:oracle-l_at_freelists.org>
> *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-lReceived on Wed Sep 01 2010 - 19:17:58 CDT