Re: Converting a stored procedure from Sybase

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 22 Dec 1999 08:13:51 -0500
Message-ID: <jij16sk6tu3sd6h3u1vrrs0pqnghg9pq1t@4ax.com>

A copy of this was sent to /jc <johnchewter_at_my-deja.com> (if that email address didn't require changing) On Wed, 22 Dec 1999 12:06:47 GMT, you wrote:

>I've got the code down to the following but it never finds any entries,
>even if the table names are passed in as part of the argument.
>
>I've checked that the string is parsed ok into the PL/SQL table and
>this all seems fine, it's just that the IN part of the select statement
>doesn't seem to pick up the values.
>
>Any ideas?
>
>tia,
>/jc
>
>
>create or replace package test_pkg
>AS
> TYPE usertables_refcursor IS REF CURSOR RETURN user_tables%ROWTYPE;
> TYPE param_table_type IS TABLE OF varchar2(50) INDEX BY
>BINARY_INTEGER;
>
> FUNCTION getUserTables ( p_str IN VARCHAR2 ) RETURN
>usertables_refcursor;
>
>END test_pkg;
>
>--
>
>create or replace package body test_pkg
>AS
>
>FUNCTION getUserTables ( p_str IN VARCHAR2 ) RETURN
>usertables_refcursor
>IS
> tmp_rc usertables_refcursor;
> l_str long default p_str || ',';
> l_n number;
> l_data param_table_type;
>
>BEGIN
> loop
> l_n := instr( l_str, ',' );
> exit when (nvl(l_n,0) = 0);
> l_data ( l_data.COUNT + 1 ) := ltrim(rtrim(substr(l_str,1,l_n-
>1)));
> l_str := substr( l_str, l_n+1 );
> end loop;
>
> l_n := l_data.COUNT;
> OPEN tmp_rc FOR SELECT * FROM USER_TABLES where TABLE_NAME in
> ( SELECT l_data(rownum) from all_objects where rownum <= l_n );
> RETURN tmp_rc;
>END getUserTables;
>
>END test_pkg;
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

You've got to expose the plsql table via a function -- not directly. It will not work as you have above as the array l_data is consider a bind variable, not a function to SQL. The package could be:

tkyte_at_8.0> create or replace package test_pkg   2 AS
  3 TYPE usertables_refcursor IS REF CURSOR RETURN user_tables%ROWTYPE;   4
  4 FUNCTION getUserTables ( p_str IN VARCHAR2 ) RETURN usertables_refcursor;   5
  5 function get_data( n in number ) return varchar2;   6 pragma restrict_references( get_data, wnds );   7
  7 function get_max_data return number;   8 pragma restrict_references( get_max_data, wnds, rnds, wnps );   9
  9 END test_pkg;
10 /

Package created.

tkyte_at_8.0> --
tkyte_at_8.0> 
tkyte_at_8.0> create or replace package body test_pkg
  2  AS
  3    TYPE     param_table_type IS TABLE OF varchar2(50) INDEX BY

BINARY_INTEGER;
  4
  4 g_data param_table_type;
  5 g_count number;
  6
  6
  6
  6 function get_data( n in number ) return varchar2   7 is
  8 begin
  9 return g_data(n);
10 end;
11
11 function get_max_data return number 12 is
13 begin
14 return g_count;
15 end;
16
16
16 FUNCTION getUserTables ( p_str IN VARCHAR2 ) RETURN usertables_refcursor 17 IS
18 tmp_rc usertables_refcursor;

 19    l_str    long default p_str || ',';
 20    l_n      number;
 21    l_data   param_table_type;
 22  BEGIN
 23      g_data := l_data; -- empty the table and then fill it...
 24      loop
 25        l_n := instr( l_str, ',' );
 26        exit when (nvl(l_n,0) = 0);
 27        g_data ( g_data.COUNT + 1 ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
 28        l_str := substr( l_str, l_n+1 );
 29      end loop;
 30     g_count := g_data.COUNT;
 31  
 31     OPEN tmp_rc FOR
 32       SELECT *
 33         FROM USER_TABLES
 34        where TABLE_NAME in ( SELECT test_pkg.get_data(rownum)
 35                                from all_objects
 36                               where rownum <= ( select test_pkg.get_max_data
from dual ) );
 37     RETURN tmp_rc;

38 END getUserTables;
39
39 END test_pkg;
40 /

Package body created.

tkyte_at_8.0> 
tkyte_at_8.0> 
tkyte_at_8.0> variable rc refcursor
tkyte_at_8.0> exec :rc := test_pkg.getUserTables( 'EMP, DEPT' )

PL/SQL procedure successfully completed.

tkyte_at_8.0> print rc

TABLE_NAME                     TABLESPACE_NAME                CLUSTER_NAME
------------------------------ ------------------------------
------------------------------
IOT_NAME                         PCT_FREE   PCT_USED  INI_TRANS  MAX_TRANS

INITIAL_EXTENT

------------------------------ ---------- ---------- ---------- ----------

NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS LOG B NUM_ROWS
----------- ----------- ----------- ------------ ---------- --------------- --- - ----------

BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS
---------- ------------ ---------- ---------- -----------

NUM_FREELIST_BLOCKS DEGREE     INSTANCES  CACHE TABLE_LO SAMPLE_SIZE LAST_ANAL
PAR IOT_TYPE     T
------------------- ---------- ---------- ----- -------- ----------- ---------

--- ------------ -
NES BUFFER_
--- -------

DEPT                           TOOLS
                                       10         40          1        255
40960
      40960           1         505           50          1               1 YES

                             1          1     N ENABLED
NO               N

NO DEFAULT

EMP                            TOOLS
                                       10         40          1        255
40960
      40960           1         505           50          1               1 YES

                             1          1     N ENABLED
NO               N

--
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 22 1999 - 07:13:51 CST