ORA-01722 for bind variable in dbms_sql [message #659749] |
Sun, 29 January 2017 07:10 |
|
myadi26
Messages: 7 Registered: December 2012 Location: india
|
Junior Member |
|
|
I am getting ORA-01722: invalid number for the below code snippet as from p_audit_tab(rec1.seq_no).column_value for rec1.seq_no = 1 value may be number or character datatype . Any suggestion please .
a:= DBMS_SQL.OPEN_CURSOR;
FOR rec IN cur_view
LOOP
FOR rec1 IN cur_get_column_mapping (rec.view_name)
LOOP
IF rec1.seq_no = 1
THEN
l_sql := 'SELECT 1 FROM '|| rec.view_name || ' WHERE '||rec1.column_name||'= :1';
ELSE
l_sql := l_sql ||' AND '||rec1.column_name||'= :1';
END IF;
DBMS_SQL.PARSE(a, l_sql , DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE(a,'1',p_audit_tab(rec1.seq_no).column_value );
END LOOP;
END LOOP;
rows_processed := dbms_sql.execute(a);
IF dbms_sql.fetch_rows(a) > 0 THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
b := dbms_sql.execute(a);
if dbms_sql.fetch_rows(a) > 0 then
dbms_sql.close_cursor(a);
return true;
else
dbms_sql.close_cursor(a);
return false;
end if;
*BlackSwan added {code} tags. Please do so yourself in the future.
How to use {code} tags and make your code easier to read
[Updated on: Sun, 29 January 2017 07:13] by Moderator Report message to a moderator
|
|
|
|
|
|
|
|
Re: ORA-01722 for bind variable in dbms_sql [message #659756 is a reply to message #659752] |
Sun, 29 January 2017 07:55 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
What is wrong is that you parse the query N times.
The snippet is:
1/ build the query
2/ parse the query
3/ loop to bind the query variables
4/ execute the query
5/ loop to fetch and within this loop loop to get each result column value
6/ close the cursor
|
|
|