Re: Generic function to test for a returned value in a select statement?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 05 Nov 1999 14:40:31 -0500
Message-ID: <ZTIjOEGvSk=i7xPtcjJ+F7tmdxiK@4ax.com>

A copy of this was sent to johng_at_mm.com (TurkBear) (if that email address didn't require changing) On Fri, 05 Nov 1999 18:38:51 GMT, you wrote:

>Hi,
>I am attempting to create a function that will return a value based on the
>results on a select statement within the function...I would like it to be
>'generic', that is I want to be able to pass the table name, the value to check
>for and the value to check against to the function...like this
>
>------------------------------------------------------------------------------------------------------------------------------------
>Function TESTFLAG
> ( tblname IN varchar,
> testval IN varchar,testkey IN varchar)
> RETURN varchar IS
>
> retval1 varchar2(1);
>BEGIN
> select 'Y' into retval1 from dual where exists
> (select testkey from tblname where testkey = testval) ;
> if retval1 = 'Y' then
> RETURN 'True';
> else
> RETURN 'False' ;
> end if;
>END;
>
>----------------------------------------------------------------------------------------------------------------------------
>
>When I try to compile this it fails because tblname is not a table name ( since
>I have not passed it a value ) ...
>
>Am I on the wrong track here ? Can this be done...
>
>Thanks for any help, or pointers ...
>
>John Greco
>
>

Need dynamic sql. 2 versions below. first is for 8.0 and before. second is Oracle8i release 8.1 and up (and much shorted..)

tkyte_at_8i> create or replace function testflag( tblname IN varchar,

  2                                        testval IN varchar,
  3                                        testkey IN varchar )

  4 return varchar2
  5 as

  6      exec_cursor     integer default dbms_sql.open_cursor;
  7      rows_processed  number  default 0;
  8      l_stmt          varchar2(2000);
  9      l_return_value  varchar2(30);
 10  begin
 11      l_stmt := 'select decode( count(*), 1, ''True'', ''False'' )
 12                   from dual
 13                  where exists ( select 1
 14                                   from ' || tblName || '
 15                                  where ' || testKey || ' = :x )';
 16  
 17      dbms_sql.parse(exec_cursor, l_stmt, dbms_sql.native );
 18      dbms_sql.define_column( exec_cursor, 1, l_return_value, 30 );
 19      dbms_sql.bind_variable( exec_cursor, ':x', testVal );
 20      rows_processed :=  dbms_sql.execute(exec_cursor);
 21  
 22      if ( dbms_sql.fetch_rows(exec_cursor) > 0 )
 23      then
 24          dbms_sql.column_value( exec_cursor, 1, l_return_value );
 25      else
 26          dbms_output.put_line( 'bummer ' || rows_processed );
 27      end if;
 28      dbms_sql.close_cursor( exec_cursor );
 29  
 30      return l_return_value;

 31 end;
 32 /

Function created.

tkyte_at_8i>
tkyte_at_8i> begin
  2 dbms_output.put_line( 'King is in emp: ' || testflag( 'emp', 'KING', 'ename' ) );
  3 dbms_output.put_line( 'Barney is in emp: ' || testflag( 'emp', 'BARNEY', 'ename' ) );
  4 end;
  5 /
King is in emp: True
Barney is in emp: False

PL/SQL procedure successfully completed.

tkyte_at_8i>
tkyte_at_8i> create or replace function testflag( tblname IN varchar,

  2                                        testval IN varchar,
  3                                        testkey IN varchar )

  4 return varchar2
  5 as
  6 l_return_value varchar2(30);
  7 begin
  8

  9      execute immediate
 10                'select decode( count(*), 1, ''True'', ''False'' )
 11                   from dual
 12                  where exists ( select 1
 13                                   from ' || tblName || '
 14                                  where ' || testKey || ' = :x )'
 15              into l_return_value using testval;
 16  
 17      return l_return_value;

 18 end;
 19 /

Function created.

tkyte_at_8i>
tkyte_at_8i> begin
  2 dbms_output.put_line( 'King is in emp: ' || testflag( 'emp', 'KING', 'ename' ) );
  3 dbms_output.put_line( 'Barney is in emp: ' || testflag( 'emp', 'BARNEY', 'ename' ) );
  4 end;
  5 /
King is in emp: True
Barney is in emp: False

PL/SQL procedure successfully completed.

>
> -----------== Posted via Newsfeeds.Com, Uncensored Usenet News ==----------
> http://www.newsfeeds.com The Largest Usenet Servers in the World!
>------== Over 73,000 Newsgroups - Including Dedicated Binaries Servers ==-----

--
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 Fri Nov 05 1999 - 13:40:31 CST