Re: Converting a stored procedure from Sybase

From: /jc <johnchewter_at_my-deja.com>
Date: Wed, 22 Dec 1999 12:06:47 GMT
Message-ID: <83qesn$ffs$1@nnrp1.deja.com>

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. Received on Wed Dec 22 1999 - 06:06:47 CST