Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01006 w/no binded variables (Windows 2003, 11.1.0.7..)
ORA-01006 w/no binded variables [message #442976] Thu, 11 February 2010 15:33 Go to next message
db33
Messages: 21
Registered: July 2009
Junior Member
Hi,

Can anyone help me debug this function. I keep getting:
  ORA-01006: bind variable does not exist
  ORA-06512: at "STE.NEXT_BATCH", line 11

when I execute. I've stripped out most of the code and am now down to this:
CREATE OR REPLACE TYPE sub_batch AS OBJECT
       (pid int,
        minstamp timestamp,
	c int);
/
CREATE OR REPLACE TYPE sub_batch_table AS TABLE OF sub_batch;
/
CREATE OR REPLACE FUNCTION next_batch(maxrows IN Number) 
  RETURN sub_batch_table PIPELINED IS
my_pid	     integer;
my_stamp     timestamp;
my_count     integer;
rowcount     integer;
TYPE         ref0 IS REF CURSOR;
cur0         ref0;
out_rec      sub_batch := sub_batch(NULL,NULL,NULL);
BEGIN
OPEN cur0 FOR
  'select 1,2,3 from dual'
USING my_pid,my_stamp,my_count;
  LOOP
    FETCH cur0 INTO out_rec.pid, out_rec.minstamp, out_rec.c;
        EXIT WHEN cur0%NOTFOUND;
    PIPE ROW(out_rec);
  END LOOP;
  CLOSE cur0;
RETURN;
END next_batch;
/


But I am still getting the error on 'select * from table(next_batch(10))'.
Thanks.
Re: ORA-01006 w/no binded variables [message #442977 is a reply to message #442976] Thu, 11 February 2010 15:47 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
>But I am still getting the error on 'select * from table(next_batch(10))'.

I disagree.

error occurs on
>OPEN cur0 FOR 'select 1,2,3 from dual' USING my_pid,my_stamp,my_count;
Re: ORA-01006 w/no binded variables [message #442979 is a reply to message #442976] Thu, 11 February 2010 16:51 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Your problem is in this line:
OPEN cur0 FOR
  'select 1,2,3 from dual'
USING my_pid,my_stamp,my_count;


When Oracle sees the USING.... construct in an piece of dynamic code like this, it scans the SQL or Pl/Sql looking for as many bind variables as you have parameters after the USING.

Bind variables are specified with a leading : so this should work:
OPEN cur0 FOR
  'select :p1,:p2,:p3 from dual'
USING my_pid,my_stamp,my_count;
Re: ORA-01006 w/no binded variables [message #442988 is a reply to message #442976] Thu, 11 February 2010 21:36 Go to previous message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Read this also.

regards,
Delna
Previous Topic: DBMS_SCHEDULER.RUN_JOB -> ORA-02800: Requests timed out
Next Topic: Auto Generate User Names through particular column (merged by CM)
Goto Forum:
  


Current Time: Mon Sep 26 14:35:54 CDT 2016

Total time taken to generate the page: 0.10127 seconds