Re: Converting a stored procedure from Sybase

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 16 Dec 1999 16:52:50 -0500
Message-ID: <4bni5ssfno727uif9hn83bp604n10q8jrb@4ax.com>

A copy of this was sent to /jc <johnchewter_at_my-deja.com> (if that email address didn't require changing) On Thu, 16 Dec 1999 14:51:34 GMT, you wrote:

>In article <pl2f5scd6fp3ggikjn5qptlq6bqa43hf7f_at_4ax.com>,
> tkyte_at_us.oracle.com wrote:
>
>Thomas,
>
>thanks for the response, the only one that is possible for me to use is
>the first as we are running on v8 without support for objects.
>
>Whilst this provides a breakdown of the comma delimited list as a
>result set I can't find a way of coercing the varchar2s into an
>argument for the select in clause.
>
>e.g. something like select UserID from Users where UserID in ( test
>('jim,bob,mike,fred'));
>
>cheers,
>john
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

for that you need the objects stuff.

You can sort of do it with plsql tables like this:

tkyte_at_8.0> create or replace package demo_pkg   2 as

  3      function getval( i in number ) return varchar2;
  4      pragma restrict_references( getval, rnds, wnds, wnps );
  5  
  5      function getmaxval return number;
  6      pragma restrict_references( getmaxval, rnds, wnds, wnps );

  7
  7
  7 procedure init( p_str in varchar2 );   8 end;
  9 /

Package created.

tkyte_at_8.0>
tkyte_at_8.0> create or replace package body demo_pkg   2 as
  3

  3  g_table     dbms_sql.varchar2s;
  4  g_cnt        number;

  5
  5
  5 procedure init( p_str in varchar2 )   6 as

  7      l_str   long default p_str || ',';
  8      l_n        number;
  9  begin
 10      loop
 11          l_n := instr( l_str, ',' );
 12          exit when (nvl(l_n,0) = 0);
 13          g_table( g_table.count+1 ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
 14          l_str := substr( l_str, l_n+1 );
 15      end loop;
 16      g_cnt := g_table.count;

 17 end;
 18
 18
 18 function getval( i in number ) return varchar2  19 as
 20 begin
 21 return g_table(i);
 22 end;
 23
 23 function getmaxval return number
 24 as
 25 begin
 26 return g_cnt;
 27 end;
 28
 28 end;
 29 /

Package body created.

tkyte_at_8.0> show errors
No errors.
tkyte_at_8.0>
tkyte_at_8.0> exec demo_pkg.init( 'how,now,brown,cow' )

PL/SQL procedure successfully completed.

tkyte_at_8.0>
tkyte_at_8.0> select demo_pkg.getval(rownum)   2 from all_objects
  3 where rownum <= ( select demo_pkg.getmaxval from dual )   4 /

DEMO_PKG.GETVAL(ROWNUM)

---

how
now
brown
cow

Just make sure the table you use (i used all-objects) has more rows then you'll ever put in that table.

--
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 Thu Dec 16 1999 - 15:52:50 CST