by ORA : 1006 : Bind variable does not exist
I have a procedure of 7 parameters and on the basis of the values of these
parameters I build
my SQL statement.
I type in my browser
http://........./List3?One=5&Two=480&Three=&Four=&Five=&Six=&Seven=
The problem is that " OPEN c FOR SqlStr USING ......"
expects all the parameters , but in input some of them are null
when i call this procedure, I get the error "ORA : 1006 Bind Variable does
not exist",
anyone knows the cause of the error ?? and how to solve it ??
thanks Simone
This is a sample of code.....
CREATE OR REPLACE PROCEDURE list3
one IN NUMBER ,
two IN NUMBER,
three IN VARCHAR2 DEFAULT NULL,
four IN VARCHAR2 DEFAULT NULL,
five IN VARCHAR2 DEFAULT NULL,
six IN VARCHAR2 DEFAULT NULL,
seven IN NUMBER DEFAULT NULL) AS
/************************************************************
************************************************************/
res1 NUMBER;
res2 VARCHAR2(100);
res3 VARCHAR2(100);
res4 VARCHAR2(100);
res5 NUMBER;
res6 NUMBER;
res7 VARCHAR2(100);
res8 VARCHAR2(100);
res9 VARCHAR2(100);
res10 NUMBER;
Ris NUMBER;
SqlStr VARCHAR2(2000);
Rows INTEGER;
TYPE cur_typ IS REF CURSOR;
c cur_typ;
BEGIN
HTP.HTMLOPEN;
SqlStr := ' SELECT B.CODE, A.NAME, A.SOC, A.ID, A.CODE,C.GRAF, B.TYPE,
B.COLUMN, B.NUMBER, B.CURSOR
FROM TAB1 A, TAB2 B, TAB3 C
WHERE A.CODE = B.CODE
AND B.TYPE = '''|| X ||'''
AND B.CODE = C.CODE ';
IF Two=5 THEN
SqlStr :=SqlStr ||' AND B.CODE1= :One';
ELSIF Two=7 THEN
SqlStr :=SqlStr ||' AND B.CODE2= :Two';
END IF;
IF Three IS NOT NULL THEN
SqlStr :=SqlStr ||' AND A.COG LIKE :Three ||''%''';
END IF ;
IF Four IS NOT NULL THEN
SqlStr :=SqlStr ||' AND A.SOC LIKE :Four ||''%''' ;
END IF ;
IF Five IS NOT NULL THEN
SqlStr :=SqlStr ||' AND A.COLUMN LIKE :Five ||''%''' ;
END IF ;
IF Six IS NOT NULL THEN
SqlStr :=SqlStr ||' AND A.TYPE LIKE :Six ||''%''' ;
END IF ;
IF Seven IS NOT NULL THEN
SqlStr:=SqlStr || ' AND B.CURSOR= :Seven ' ;
END IF;
SqlStr :=SqlStr ||' ORDER BY A.CODE' ;
OPEN c FOR SqlStr USING One,Two,Three,Four,Five,Six,Seven;
LOOP
FETCH c INTO re1,res2,res3,res4,res5,res6,res7,res8,res9,res10;
IF c%ROWCOUNT=0 THEN
RAISE NO_DATA_FOUND;
END IF;
EXIT WHEN c%NOTFOUND;
-- process row here
END LOOP;
CLOSE c;
HTP.HTMLCLOSE;
EXCEPTION
WHEN OTHERS THEN
htp.p (SQLCODE||SQLERRM);
END;