Re: Why use Pro*C for spooling result sets to flat files when you can do the same thing in SQL*Plus?
- From: Steve Baldwin <stbaldwin@xxxxxxxxxxxxxxxx>
- To: mdinh@xxxxxxxxx
- Date: Thu, 2 Sep 2010 09:48:03 +1000
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> 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] *On Behalf Of *Bill Myers
> *Sent:* Wednesday, September 01, 2010 4:15 PM
> *To:* Michael Dinh
> *Cc:* 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> 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] *On Behalf Of *Bill Myers
> *Sent:* Wednesday, September 01, 2010 3:24 PM
> *To:* 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,
> //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.
---------------------------------------------------------------------------------------
Other related posts:
