Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!prodigy.com!newsfeed.telusplanet.net!newsfeed.telus.net!newsfeed.bc.tac.net!cyclone.bc.net!newsfeed.stanford.edu!postnews1.google.com!not-for-mail
From: OakRogbak_erPine@yahoo.com (OakRogbak_erPine@yahoo.com   Kill the 2 trees in email address to reply)
Newsgroups: comp.databases.oracle.server
Subject: Dynamic SQL Problems
Date: 7 Jul 2003 14:07:13 -0700
Organization: http://groups.google.com/
Lines: 62
Message-ID: <13fdc9b4.0307071307.5af0279a@posting.google.com>
NNTP-Posting-Host: 169.139.180.100
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1057612033 5942 127.0.0.1 (7 Jul 2003 21:07:13 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: 7 Jul 2003 21:07:13 GMT
Xref: newssvr20.news.prodigy.com comp.databases.oracle.server:237096

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