Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01722 for bind variable in dbms_sql (Oracle 10g)
ORA-01722 for bind variable in dbms_sql [message #659749] Sun, 29 January 2017 07:10 Go to next message
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 #659751 is a reply to message #659749] Sun, 29 January 2017 07:17 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
[oracle@vbgeneric dbs]$ oerr ora 1722
01722, 00000, "invalid number"
// *Cause: The specified number was invalid.
// *Action: Specify a valid number.

only NUMERIC DIGITS are allowed
Re: ORA-01722 for bind variable in dbms_sql [message #659752 is a reply to message #659749] Sun, 29 January 2017 07:21 Go to previous messageGo to next message
myadi26
Messages: 7
Registered: December 2012
Location: india
Junior Member
so, for p_audit_tab(rec1.seq_no).column_value value should only be numeric ? please correct me if I am wrong
Re: ORA-01722 for bind variable in dbms_sql [message #659753 is a reply to message #659752] Sun, 29 January 2017 07:25 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
myadi26 wrote on Sun, 29 January 2017 05:21
so, for p_audit_tab(rec1.seq_no).column_value value should only be numeric ? please correct me if I am wrong

What results when you implement this requirement?
Re: ORA-01722 for bind variable in dbms_sql [message #659754 is a reply to message #659753] Sun, 29 January 2017 07:30 Go to previous messageGo to next message
myadi26
Messages: 7
Registered: December 2012
Location: india
Junior Member
I got the correct sql syntax as a result of this statement DBMS_SQL.PARSE(a, l_sql , DBMS_SQL.NATIVE) . Value from p_audit_tab(rec1.seq_no).column_value may be 'xyz' or may it be 22 for rec1.seq_no = 1
Re: ORA-01722 for bind variable in dbms_sql [message #659755 is a reply to message #659754] Sun, 29 January 2017 07:41 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
"SELECT 1 FROM " seems to be somewhat useless.
I do not understand what the code is supposed to accomplish.
Please explain & elaborate.

When trying to use dynamic SQL it is a Good Thing to DBMS_OUTPUT.PUT_LINE(l_sql) to verify syntax prior to executing it.
Re: ORA-01722 for bind variable in dbms_sql [message #659756 is a reply to message #659752] Sun, 29 January 2017 07:55 Go to previous message
Michel Cadot
Messages: 68624
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

Previous Topic: cursor leak and open cursors
Next Topic: Procedure out of statements
Goto Forum:
  


Current Time: Thu Mar 28 09:35:14 CDT 2024