SEO

#1 (**permalink**) 02-24-2008, 07:43 AM

Hi,

Does anyone know the PL/SQL code behind SQL Single Row Character Function,
for example, UPPER()?

#2 (**permalink**) 02-24-2008, 07:43 AM

"Mario King" <hliji@yahoo.com> wrote in message
news:a3f90977.0409240944.5a131be@posting.google.co m...
| Hi,
|
| Does anyone know the PL/SQL code behind SQL Single Row Character Function,
| for example, UPPER()?

um, why?

the upper() function and other standard functions are likely written in C,
certainly not PL/SQL -- they've existed in Oracle long before PL/SQL existed

what are you trying to learn or accomplish?

++ mcs

#3 (**permalink**) 02-24-2008, 07:43 AM

On 24 Sep 2004 10:44:09 -0700, hliji@yahoo.com (Mario King) wrote:

>Hi,
>
>Does anyone know the PL/SQL code behind SQL Single Row Character Function,
>for example, UPPER()?

These functions are in the standard package and they are not written
in PL/SQL. Oracle doesn't provide the code, and reverse engineering is
expressly prohibited by your license.

--
Sybrand Bakker, Senior Oracle DBA

#4 (**permalink**) 02-24-2008, 07:43 AM

"Mario King" <hliji@yahoo.com> a écrit dans le message de
news:a3f90977.0409240944.5a131be@posting.google.co m...
> Hi,
>
> Does anyone know the PL/SQL code behind SQL Single Row Character Function,
> for example, UPPER()?

If you want to create your own aggregate function, have a look at:
http://www.dba-village.com/dba/villa...ils?TipId=1774

--
Regards
Michel Cadot

#5 (**permalink**) 02-24-2008, 07:50 AM

I'm trying to write a function long2ch() to convert
table's LONG column to varchar2 so that I could run
select * from tab where long2ch(long_col) like '%MY String%'.
-- I want to make long2ch() similar to upper(), trim()...

"Mark C. Stock" <mcstockX@Xenquery .com> wrote in message news:<td6dnS1tV7o1_MncRVn-vQ@comcast.com>...
> "Mario King" <hliji@yahoo.com> wrote in message
> news:a3f90977.0409240944.5a131be@posting.google.co m...
> | Hi,
> |
> | Does anyone know the PL/SQL code behind SQL Single Row Character Function,
> | for example, UPPER()?
>
> um, why?
>
> the upper() function and other standard functions are likely written in C,
> certainly not PL/SQL -- they've existed in Oracle long before PL/SQL existed
>
> what are you trying to learn or accomplish?
>
> ++ mcs

#6 (**permalink**) 02-24-2008, 07:50 AM

Mario King wrote:
> I'm trying to write a function long2ch() to convert
> table's LONG column to varchar2 so that I could run
> select * from tab where long2ch(long_col) like '%MY String%'.
> -- I want to make long2ch() similar to upper(), trim()...

Sorry, but that's just not going to happen:

SQL> create or replace function long2ch( p_long in long ) return
varchar2 is
2 begin
3 return p_long;
4 end;
5 /

Function created.

SQL> select * from user_views
2 where long2ch(text) like '%EMP%';
where long2ch(text) like '%EMP%'
*
ERROR at line 2:
ORA-00997: illegal use of LONG datatype

The problem is that you can't pass the LONG value INTO the function at
all!

Maybe you could be using CLOB instead?

#7 (**permalink**) 02-24-2008, 07:51 AM

On 30 Sep 2004 08:27:51 -0700, hliji@yahoo.com (Mario King) wrote:

>I'm trying to write a function long2ch() to convert
>table's LONG column to varchar2 so that I could run
>select * from tab where long2ch(long_col) like '%MY String%'.
>-- I want to make long2ch() similar to upper(), trim()...

If at all possible, get rid of the LONG column(s) once and for all.

Oracle has worked hard to eliminate the beast, and if you are on 9i+,
it's as simple as

SQL> create table foo(c1 long);
SQL> alter table foo modify c1 clob;

- Kenneth Koenraadt

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

HELP- Code for SQL Single Row Character Function?