Re: Converting a stored procedure from Sybase

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 15 Dec 1999 07:50:59 -0500
Message-ID: <pl2f5scd6fp3ggikjn5qptlq6bqa43hf7f@4ax.com>

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 ) return

dbms_sql.varchar2s
2 as

  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;

14 end;
15 /

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;

9 end;
10 /
How
Now
Brown
Cow

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 ) return

myTableType
2 as

  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;

15 end;
16 /

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

2 from THE ( select cast( test( 'How,Now,Brown,Cow' ) as mytableType ) from dual ) a
3 /

VAL

How
Now
Brown
Cow

tkyte_at_8i> 
tkyte_at_8i> 
tkyte_at_8i> REM Example 3, works in Oracle8i, release 8.1 and up.  Uses a

temporary table
tkyte_at_8i> REM and ref cursors (ref cursors are available from 7.2 up). Temporary tables

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 commit

delete rows;
Table Created.

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;

15 end;
16 /

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

How
Now
Brown
Cow

--
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