Re: Dynamic SQL Problems

From: OakRogbak_erPine_at_yahoo.com Kill the 2 trees in email address to reply <OakRogbak_erPine_at_yahoo.com>
Date: 8 Jul 2003 06:26:19 -0700
Message-ID: <13fdc9b4.0307080526.608e17af@posting.google.com>

I have found a solution by modifying an example posted by Thomas Kyte. This is what I am using:

create or replace function countform( p_tname varchar2, value1 VARCHAR2)
return number as
l_cnt number;
statement VARCHAR2(200);
begin

   statement:='SELECT COUNT(formid) FROM ' || p_tname || ' WHERE TID=' || chr(39) || value1 || chr(39) ||' or sid in (SELECT sid FROM users WHERE id=' || chr(39) || value1 || chr(39) ||')';

   Dbms_output.put_line(statement);
   execute immediate statement INTO l_cnt;

• execute immediate 'select count(*) from ' || p_tname INTO l_cnt; return l_cnt; end;
  /
  

Declare

        sReturnValue NUMBER;
Begin

	sReturnValue:=countform ('FORM357','9768');
	Dbms_output.put_line(sReturnValue);

End;
/
Received on Tue Jul 08 2003 - 08:26:19 CDT