Should TO_CHAR be replaced by high level code with pl/sql calls?

[Ed]

It has been suggested by an "Oracle Expert" that we replace all our
TO_CHAR calls in our select statements into pre or post calls to high
level functions such as C or C++. Can someone please comment as to
whether this is worth the effort.

Thanks In Advance,
Ed Reyes

[Brian Peasland]

I can't imagine suggesting that to someone. The TO_CHAR function does a
pretty good job. What are the reasons for this suggestion? There might
be valid ones, but I can't think of any right now.

Cheers,
Brian

--
===================================================================

Brian Peasland
dba@remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.

"I can give it to you cheap, quick, and good. Now pick two out of
the three"

[Sybrand Bakker]

That's the daftest advice I have seen in ages. Why on earth would you
switch to external function with the associated overhead to call them,
when you have optimized native pl/sql functions?

--
Sybrand Bakker, Senior Oracle DBA

[Daniel Morgan]

Ed wrote:

Fire the "Expert".

Then hire someone that knows what they are talking about. I can only
wonder at the other "advice" your organization has received. I'd get a
second opinion, from someone competent, before I followed any of it.
--
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damo...@x.washington.edu
(replace 'x' with a 'u' to reply)

[mcstock]

what is your coding environment?

if you're using 3GL or anything other than straight PL/SQL, is your
consultant suggesting doing something in the host language prior to issuing
the SQL?

give some examples -- otherwise any response would just be guessing at the
issue the consultant was attempting to address; he may be looney, or he may
be addressing something that is not apparent in your short post

-- mcs

"Ed" <edre...@hotmail.com> wrote in message
news:5bb04c8c.03112...@posting.google.com...

[Pete Finnigan]

Hi,

It may slightly make sense only if your programs are written in C or C++
(OCI, Pro*C, OCCI) and you make many calls to the database with SQL
using TO_CHAR and also depending on how fast your own C conversion is.
The to_char's implemented in $ORACLE_HOME/rdbms/admin/stdspec.sql and
stdbody.sql are either pragma builtin or simply rely on implicit
conversions. I don't know the exact mechanism for the pragma builtin
keyword but the function is almost certainly implemented in C and would
be called in the SQL engine directly so should be reasonably efficient.
We don't know the details of why this is suggested but on balance its
probably not worth the effort.

Kind regards

Pete
--
Pete Finnigan
email:pe...@petefinnigan.com
Web site: http://www.petefinnigan.com - Oracle security audit specialists
Book:Oracle security step-by-step Guide - see http://store.sans.org for details.

[Daniel Morgan]

Pete Finnigan wrote:

> Hi,
>
> It may slightly make sense only if your programs are written in C or C++
> (OCI, Pro*C, OCCI) and you make many calls to the database with SQL
> using TO_CHAR and also depending on how fast your own C conversion is.
> The to_char's implemented in $ORACLE_HOME/rdbms/admin/stdspec.sql and
> stdbody.sql are either pragma builtin or simply rely on implicit
> conversions. I don't know the exact mechanism for the pragma builtin
> keyword but the function is almost certainly implemented in C and would
> be called in the SQL engine directly so should be reasonably efficient.
> We don't know the details of why this is suggested but on balance its
> probably not worth the effort.
>
> Kind regards
>
> Pete

Given what we know of Oracle it could also be implemented in assember.
Either way ... the benefit to writing your own would rank somewhere
around the decrease in performance one would expect to see with the line
voltage to the computer dropped from 120V to 118V. ;-)

[Pete Finnigan]

> the benefit to writing your own would rank somewhere
>around the decrease in performance one would expect to see with the line
>voltage to the computer dropped from 120V to 118V. ;-)

Good evening Daniel,

Thinking along, I did remember once seeing a company do their own date
conversions from DATE to CHAR in C after it had been selected. This was
back in 7.1.6 days though and written before that! and the application
was very date intensive and they said it was faster to read the "raw"
date and then convert it "off-line". Don't know if it really was or not!

Anyway its academic really as i don't really see the point of it all, it
was just interesting to consider the "why"!!, if the voltage dropped to
118v here we would have a much bigger performance problem as we start
with 240v...:-)

kind regards

[DJ]

"Pete Finnigan" <pl...@petefinnigan.com> wrote in message
news:fzCHksAt19v$Qx...@peterfinnigan.demon.co.uk...

> > the benefit to writing your own would rank somewhere
> >around the decrease in performance one would expect to see with the line
> >voltage to the computer dropped from 120V to 118V. ;-)
>
> Good evening Daniel,
>
> Thinking along, I did remember once seeing a company do their own date
> conversions from DATE to CHAR in C after it had been selected. This was
> back in 7.1.6 days though and written before that! and the application
> was very date intensive and they said it was faster to read the "raw"
> date and then convert it "off-line". Don't know if it really was or not!
>
> Anyway its academic really as i don't really see the point of it all, it
> was just interesting to consider the "why"!!, if the voltage dropped to
> 118v here we would have a much bigger performance problem as we start
> with 240v...:-)
>

Im gonna be picky here, but we use 230v in the uk, we went down 10v a few
years ago to be in line with europe:-)