Dynamic SQL Problems

From: OakRogbak_erPine_at_yahoo.com Kill the 2 trees in email address to reply <OakRogbak_erPine_at_yahoo.com>
Date: 7 Jul 2003 14:07:13 -0700
Message-ID: <13fdc9b4.0307071307.5af0279a@posting.google.com>

Please help me with this Dynamic SQL, I have been working on it most of the day and trying to follow exams in several Oracle books:

Basically I need to call a function by passing it a tablename and another value. The function needs to return the results of a 'Select count(*)...' type query to the calling statement.

Here's my Function:
create or replace FUNCTION testproc (tabname VARCHAR2, value1 VARCHAR2)
RETURN NUMBER IS
formcount NUMBER;
tempcount NUMBER;
statement VARCHAR2(200);
v_Cursor NUMBER;
v_dummy INTEGER;
Begin

   v_Cursor:=DBMS_SQL.OPEN_CURSOR;
   statement:='SELECT COUNT(formid) into :tempcount FROM ' || tabname || ' 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);
   DBMS_SQL.PARSE(v_Cursor,statement, DBMS_SQL.native);
   DBMS_SQL.BIND_VARIABLE(v_Cursor, ':tempcount',tempcount,20);

   v_dummy:=DBMS_SQL.EXECUTE(v_Cursor);
   Dbms_output.put_line('v_dummy='||v_dummy);    Dbms_output.put_line('tempcount='||tempcount);    formcount:=tempcount;
   DBMS_SQL.CLOSE_CURSOR(v_Cursor);
   RETURN formcount;
End;
/

Here's what I am using to call it:
Declare

        sReturnValue NUMBER;
Begin

	sReturnValue:=testproc ('FORM357','999999');
	Dbms_output.put_line(sReturnValue);

End;
/

I am getting errors on the bind variable portion. With the "into :tempcount" I get the errors
ERROR at line 1:

ORA-01006: bind variable does not exist
ORA-06512: at "SYS.DBMS_SYS_SQL", line 844
ORA-06512: at "SYS.DBMS_SQL", line 52
ORA-06512: at "ESSS.TESTPROC", line 13
ORA-06512: at line 4

and with out it, my Select statement looks good, but it doesn't return the right value.
SELECT COUNT(formid) into :tempcount FROM FORM357 WHERE TID='999999' or sid in (SELECT sid FROM users WHERE id='999999') v_dummy=0
tempcount=

Thanks,Roger Received on Mon Jul 07 2003 - 16:07:13 CDT