Re: Why use Pro*C for spooling result sets to flat files when you can do the same thing in SQL*Plus?
- From: Bill Myers <bwmyers@xxxxxxxxx>
- To: Michael Dinh <mdinh@xxxxxxxxx>
- Date: Wed, 1 Sep 2010 17:06:31 -0700
"id||id" is 4000 chars concatenated with another 4000 chars, so shouldn't
linesize=8000 in that case?
On Wed, Sep 1, 2010 at 4:40 PM, 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
>
>
>
Other related posts:
