Home » SQL & PL/SQL » SQL & PL/SQL » Character to number conversion error
Character to number conversion error [message #312317] Tue, 08 April 2008 09:50 Go to next message
callimaco0082
Messages: 18
Registered: March 2008
Junior Member
Hi all.

I'm trying to execute the following code:

declare
fsUser varchar2(50) := 'user1@domain.com';
fsCondition varchar2(30) := 'APS_ORA_OK';
fnNumber integer;

begin

fnNumber := bar_mail.FindCodeUser(fsUser,fsCondition);

end;

Function FindCodeUser(fsUser in varchar2, fsCondition in varchar2) return INTEGER is
lnCodeUser integer;
begin
select fsCondition into lnCodeUser from TMP_MAIL_USER where UTENTE = fsUser;

return lnCodeUser;


end;

Every time the same error is generated:

ORA-06502: PL/SQL: numeric or value error: character to number conversion error

Data in tables are inserted correctly with rigth types. Which conversion fails?
Thanks to all will help!
Re: Character to number conversion error [message #312318 is a reply to message #312317] Tue, 08 April 2008 09:53 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You select the VARCHAR2 fsCondition into the NUMBER lnCodeUser.
Re: Character to number conversion error [message #312320 is a reply to message #312317] Tue, 08 April 2008 09:57 Go to previous messageGo to next message
callimaco0082
Messages: 18
Registered: March 2008
Junior Member
Thanks Thomas, but if it is so I've another problem.
fsConditionis the name of teh column I need to select.

This column contains integer data that I have to store in lnCodeUser variable...

How to indicate a column name through a variable?

Thanks!
Re: Character to number conversion error [message #312322 is a reply to message #312320] Tue, 08 April 2008 10:01 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Check out the documentation for Dynamic SQL
Re: Character to number conversion error [message #312328 is a reply to message #312317] Tue, 08 April 2008 10:21 Go to previous messageGo to next message
callimaco0082
Messages: 18
Registered: March 2008
Junior Member
Great! Using dynamic SQL it works succesfully.

In the function I used the line:

execute immediate 'select '||fsCondition||' from TMP_MAIL_USER where UTENTE = '''||fsUser||'''' into lnCodeUser;

No more problems (for today...)
Re: Character to number conversion error [message #312334 is a reply to message #312328] Tue, 08 April 2008 10:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
No more problems (for today...)

But you will problem in future. Use bind variables and not constant in your query.

Regards
Michel
Re: Character to number conversion error [message #312351 is a reply to message #312334] Tue, 08 April 2008 11:16 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:
Use bind variables and not constant in your query.


You can use bind variables for column names?

It seems with the column name you are stuck with the constant.

SQL> SET serverout ON;
SQL>
SQL> CREATE TABLE test (col1 VARCHAR2(20), col2 VARCHAR2(20));

Table created.

SQL>
SQL> INSERT INTO test VALUES ('COL1_VALUE','COL2_VALUE');

1 row created.

SQL>
SQL> declare
  2
  3  v_col    VARCHAR2(20);
  4  v_where  VARCHAR2(20);
  5  v_result VARCHAR2(20);
  6
  7  BEGIN
  8
  9  v_where := 'COL2_VALUE';
 10  v_col   := 'COL1';
 11
 12  execute immediate 'select :1 from test where col2 = :2'
 13      into v_result USING v_col, v_where;
 14  Dbms_Output.put_line('RES1 : ' || v_result);
 15
 16  execute immediate 'select ' || v_col || '  from test where col2 = :1'
 17      into v_result USING v_where;
 18  Dbms_Output.put_line('RES2 : ' || v_result);
 19
 20  END;
 21  /

RES1 : COL1
RES2 : COL1_VALUE

PL/SQL procedure successfully completed.


So at least that angle will still be open for runtime bugs and SQL injections.

And since the column name basically is the only reason for the dynamic SQL at that point, I would suggest looking into avoiding the dynamic SQL by adjusting the data model.

Do you really have that many different columns you have to check via the "FindCodeUser" function that the column name has to be a parameter?

Re: Character to number conversion error [message #312355 is a reply to message #312351] Tue, 08 April 2008 11:42 Go to previous message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
You can use bind variables for column names?

No, I was talking about this part:
Quote:
UTENTE = '''||fsUser||''''


Regards
Michel
Previous Topic: Nested Tables
Next Topic: odd characters in dba_tab_cols
Goto Forum:
  


Current Time: Wed Dec 07 08:34:43 CST 2016

Total time taken to generate the page: 0.33632 seconds