Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Converting a stored procedure from Sybase
A copy of this was sent to johnchewter_at_my-deja.com
(if that email address didn't require changing)
On Wed, 15 Dec 1999 10:13:36 GMT, you wrote:
>I've got the following stored procedure in a Sybase database and now
>need to do the same using Oracle:
>
>create proc test( @P varchar(255) )
>as
>declare @w varchar(20)
>declare @c int
>select @w as "UserID" into #res where 0 = 1
>select @c = charindex(',', @P)
>while (@c<>0) begin
> select @w = substring(@P, 1, @c-1)
> select @P = substring(@P, @c+1, 200)
> insert into #res values (ltrim(rtrim(@w)))
> select @c = charindex(',',@P)
>end
>insert into #res values (ltrim(rtrim(@U)))
>select UserID from Users where UserID in (select UserID from #res)
>return
>
>any help appreciated.
>John
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
Ok, you are passing a comma delimited string and want that as a result set. 3 ways depending on version/needs:
tkyte_at_8i> REM Example 1, works in 7.3 and up. Uses the dbms_sql.varchar2s tkyte_at_8i> REM so we don't have to create our own 'table of varchar2'. In 7.2 tkyte_at_8i> REM and before, you would create your own type. tkyte_at_8i> tkyte_at_8i> create or replace function test( p_str in varchar2 ) returndbms_sql.varchar2s
3 l_str long default p_str || ','; 4 l_n number; 5 l_data dbms_sql.varchar2s; 6 begin 7 loop 8 l_n := instr( l_str, ',' ); 9 exit when (nvl(l_n,0) = 0); 10 l_data( l_data.count+1 ) := ltrim(rtrim(substr(l_str,1,l_n-1))); 11 l_str := substr( l_str, l_n+1 ); 12 end loop; 13 return l_data;
Function created.
tkyte_at_8i> tkyte_at_8i> tkyte_at_8i> declare 2 l_data dbms_sql.varchar2s; 3 begin 4 l_data := test( 'How,Now,Brown,Cow' ); 5 6 for i in 1 .. l_data.count loop 7 dbms_output.put_line( l_data(i) ); 8 end loop;
PL/SQL procedure successfully completed.
tkyte_at_8i> tkyte_at_8i> REM Example 2, works in 8.0 and up. Uses an object type (new SQL tkyte_at_8i> REM Type) to let you "select * from plsql function". Instead tkyte_at_8i> REM of calling function to return a result set, use a result tkyte_at_8i> REM set to call a function tkyte_at_8i> tkyte_at_8i> create or replace type myTableType as table of varchar2(20);2 /
Type created.
tkyte_at_8i> tkyte_at_8i> tkyte_at_8i> create or replace function test( p_str in varchar2 ) returnmyTableType
3 l_str long default p_str || ','; 4 l_n number; 5 l_data myTableType := myTabletype(); 6 begin 7 loop 8 l_n := instr( l_str, ',' ); 9 exit when (nvl(l_n,0) = 0); 10 l_data.extend; 11 l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1))); 12 l_str := substr( l_str, l_n+1 ); 13 end loop; 14 return l_data;
Function created.
tkyte_at_8i> tkyte_at_8i> REM here we go... selecting from it: tkyte_at_8i> tkyte_at_8i> select a.column_value val
VAL
tkyte_at_8i> tkyte_at_8i> tkyte_at_8i> REM Example 3, works in Oracle8i, release 8.1 and up. Uses atemporary table
tkyte_at_8i> REM are new to 8i tkyte_at_8i> tkyte_at_8i> tkyte_at_8i> create global temporary table tmp_foo ( data varchar2(20) ) on commitdelete rows;
tkyte_at_8i>
tkyte_at_8i> create or replace package types
2 as
3 type rc is ref cursor;
4 end;
5 /
Package created.
tkyte_at_8i> tkyte_at_8i> tkyte_at_8i> create or replace function test( p_str in varchar2 ) return types.rc 2 as 3 l_str long default p_str || ','; 4 l_n number; 5 l_rc types.rc; 6 begin 7 loop 8 l_n := instr( l_str, ',' ); 9 exit when (nvl(l_n,0) = 0); 10 insert into tmp_foo values ( ltrim(rtrim(substr(l_str,1,l_n-1))) ); 11 l_str := substr( l_str, l_n+1 ); 12 end loop; 13 open l_rc for select * from tmp_foo; 14 return l_rc;
Function created.
tkyte_at_8i> tkyte_at_8i> variable rc refcursor tkyte_at_8i> tkyte_at_8i> exec :rc := test( 'How,Now,Brown,Cow' );
PL/SQL procedure successfully completed.
tkyte_at_8i> print rc
DATA
--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Dec 15 1999 - 06:50:59 CST
![]() |
![]() |