Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Dynamic SQL Problems

Re: Dynamic SQL Problems

From: Jim Kennedy <kennedy-down_with_spammers_at_comcast.net>
Date: Tue, 08 Jul 2003 03:02:16 GMT
Message-ID: <YuqOa.2347$sY2.2415@rwcrnsc51.ops.asp.att.net>


I don't know the answer but try changing the following line: from:
  DBMS_SQL.BIND_VARIABLE(v_Cursor, ':tempcount',tempcount,20); to:
 DBMS_SQL.BIND_VARIABLE(v_Cursor, 'tempcount',tempcount,20);

see if that helps
Jim

<OakRogbak_erPine_at_yahoo.com> wrote in message news:13fdc9b4.0307071307.5af0279a_at_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 - 22:02:16 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US