SQL & PL/SQL

I've got a function that reads a temp table and creates the bind variables for a select statement. However, when a bind value (vData) is NULL the Select statement doesn't fine the correct entry from the table. IT returns nothing. I know its there because the next call after running the function "GETTABLEID" is to insert the information if GETTABLEID returns nothing. I've checked the Table to make sure the information exist and then I run the procedure again and it INSERTS the data again. I'm sure I'm doing something stupid but I can't figure it out. Maybe some new eyes on it may see it. Thanks in advance

FUNCTION GETTABLEID
(
TABLENAME IN VARCHAR2
, getVALUE IN VARCHAR2
) RETURN NUMBER AS

-- Declare DBMS_SQL variables.
cvar Integer := dbms_sql.open_cursor;
fdbk INTEGER;
a_null CHAR(1);

-- Declare local variables.
counter NUMBER := 1;
column_names DBMS_SQL.VARCHAR2_TABLE;
item_ids DBMS_SQL.NUMBER_TABLE;
item_type DBMS_SQL.VARCHAR2_TABLE;
v_Data DBMS_SQL.VARCHAR2_TABLE;
stmt VARCHAR2(2000);
substmt VARCHAR2(2000) := '';
TABLEID number;
num number;
vdate date;

BEGIN

--dbms_output.put_line('TABLENAME = ' || TABLENAME);

FOR i IN (SELECT COLUMNNAME, COL_DATA, DATATYPE, rownum FROM TEMP_COLUMNNAME where table_name = TABLENAME) LOOP
column_names(counter) := i.COLUMNNAME;
v_Data(counter) := i.col_data;
item_ids(counter) := i.rownum;
item_type(counter) := i.datatype;
counter := counter + 1;
END LOOP;

dbms_output.put_line('counter = ' || counter);
-- Dynamically create substatement.
IF item_ids.COUNT = 1 THEN

substmt := 'WHERE '|| column_names(1) ||'= :1';

ELSE

substmt := 'WHERE ';
FOR i IN 1..item_ids.COUNT LOOP
--dbms_output.put_line('v_Data(i) = ' || v_Data(i));
IF i = 1 THEN
substmt := substmt || column_names(i) ||'= :'||i;
ELSE
substmt := substmt ||' and '|| column_names(i) ||' = :'||i;
END IF;
END LOOP;

END IF;

-- Set statement.
stmt := 'SELECT '|| getValue
|| ' FROM ' || TABLENAME || ' '
|| substmt;

dbms_output.put_line(' stmt = ' || stmt);
-- Parse the statement with DBMS_SQL
dbms_sql.parse(cvar,stmt,dbms_sql.native);

dbms_sql.define_column(cvar,1, tableid); --define OUT mode variable

--Bind the bind variable name and value.
FOR i IN 1..item_ids.COUNT LOOP
if v_Data(i) is NULL then
dbms_sql.bind_variable(cvar,':'||item_ids(i), a_null);
else

if (upper(item_type(i))= upper('Number')) then
num := v_Data(i);
dbms_sql.bind_variable(cvar,':'||item_ids(i), num);
elsif (upper(item_type(i))= upper('date')) then
dbms_sql.bind_variable(cvar,':'||item_ids(i), to_date(v_Data(i), 'mm/dd/yyyy'));
else
dbms_sql.bind_variable(cvar,':'||item_ids(i),v_Data(i));
end if;
end if;

END LOOP;

-- Execute using DBMS_SQL.
fdbk := dbms_sql.execute_and_fetch(cvar);

dbms_sql.column_value(cvar,1,tableID); --copy query column to variable

dbms_output.put_line('tableID = ' || tableID);

return tableID;

END;

Edited by: 839575 on Feb 24, 2011 9:08 AM