Re: HELP : Store Procedure Question ?

I think that you may be getting confused with the difference between SQL functions and PL/SQL functions.

In broad terms, SQL functions are those which are part of the RDBMS and are part of the ISO/ANSI definition of what an RDBMS database must supply as basic functionality. Within this context are functions such as INSTR() and SUBSTR(). There is also the PL/SQL language which also has versions of INSTR() and SUBSTR() that work in an identical manner except that they are not associated with a cursor.

So, if you are writing a PL/SQL program and write a cursor such as:

cursor c1 is
select substr(first_name,1,5)
from address_book;

The substring() function that is used is part of the core rdbms code.

However, if you write something like:

declare

    first_name varchar2(20) := 'Alexander';     temp varchar2(5) := null; begin

    temp := substr(first_name,1,5);
end;

you will be using the pl/sql version of the function.

Luckily, the functionality of the two different versions is exactly the same.

"Chow Hoi Ka, Eric" wrote:

> Hi,
>
> Would you please to teach me howw to use INSTR and SUBSTR ??
>
> select INSTR(Name, 'O', 1, 1) from data;
>
> the above query can work well in Oracle, right ?
>
> But, how can I use INSTR and SUBSTR not in a query but in a Store
> Procdure ?
>
> Such as,
>
> declare
> tmpStr varchar2(50);
> begin
>
> tmpStr := INSTR('Hello Oracle', 'ra', 1, 1);
> end;
>
> Is it possible ? Please teach me how to implement INSTR and SUBSTR ????
>
> Best regards,
> Eric
>
> --
> _ _
> / ) |~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| ( \
> / / | | \ \
> _( /_ | _ Chow Hoi Ka, Eric _ | _) )_
> (((\ \> |/ ) ( \| </ /)))
> (\\\\ \_/ / E-Mail : d951686_at_sftw.umac.mo \ \_/ ////)
> \ / \ /
> \ _/ http://www.sftw.umac.mo/~d951686/ \_ /
> / / |____________________________________________| \ \
> / / \ \
Received on Mon Jun 21 1999 - 07:31:29 CDT