Home » SQL & PL/SQL » SQL & PL/SQL » How to get warnings for implicit type conversions? (SQLDeveloper 3.1.07/Oracle 11.1.0.1/Linux)
How to get warnings for implicit type conversions? [message #622653] Thu, 28 August 2014 05:07 Go to next message
Justin Schoeman
Messages: 8
Registered: February 2008
Junior Member
Hi, I am hoping someone can help me with this.

I need to change a lot of code from VARCHAR to CLOB. Mostly, just changing the type definition works, but there is one issue with implicit type conversion of numbers:

declare
  v_i number;
  v_c clob;
begin
  v_i := 1;
  v_c := 'foo';
  loop
    v_c := 1234567890||v_c;
    so_log(1, 'tst', ''||length(v_c));
    v_i := v_i + 1;
    exit when v_i > 10000;
  end loop;
end;
/


This code bombs out with

Quote:
ORA-06512: at line 8
06502. 00000 - "PL/SQL: numeric or value error%s"
*Cause:
*Action:


the first iteration after length(v_c) exceeds 8192.

However, this code works perfectly:

v_c := to_char(1234567890)||v_c;


So something strange happens with the implicit type conversion of the number. With explicit type conversions, everything works perfectly.

Now for the real problem - there are roughly 40,000 lines of PL/SQL, with many different authors and many uses of the || operator, and with some non-obvious variable types. So manually searching for every implicit numeric type conversion is a daunting task.

So basically I have two questions:
1) is it somehow possible to convince SQLDeveloper to provide warnings of implicit type conversions, or
2) is there some other way to handle the concatenation of numbers to strings?

Thanks,
Justin
Re: How to get warnings for implicit type conversions? [message #622661 is a reply to message #622653] Thu, 28 August 2014 06:06 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2447
Registered: May 2013
Location: World Wide on the Web
Senior Member
Justin Schoeman wrote on Thu, 28 August 2014 15:37

1) is it somehow possible to convince SQLDeveloper to provide warnings of implicit type conversions, or

Since it is implicit, you won't know unless it throws an error and you dig into the code to reach there.

At the first place, you should avoid depending on implicit conversions. You can never depend on them, and cannot hold Oracle responsible if anything bizarre happens.

Quote:

2) is there some other way to handle the concatenation of numbers to strings?


A number is a number data type and strings are character type in Oracle. When you concatenate them, Oracle considers it as character type. So, you need to use to_char(like you did) to avoid the implicit conversion.


Regards,
Lalit
Re: How to get warnings for implicit type conversions? [message #622672 is a reply to message #622661] Thu, 28 August 2014 06:48 Go to previous messageGo to next message
Justin Schoeman
Messages: 8
Registered: February 2008
Junior Member
Thanks Lalit.

I agree that the way it was done is wrong - the question is: 'How do I fix it'?

What tools/facilities are there in Oracle/SQLDeveloper that I can use to fix the problem?

There are at ~ 10,000 occurrences of || in the code I need to review. Each of these has two operands. Each of the operands needs to be reviewed to see if it has a numeric type.

Doing this by hand is next to impossible and will result in errors. But most sane software environments have tools/options to help identify issues like this, so that they can be reliably repaired. Are there any such tools/options in Oracle/SQLDeveloper?
Re: How to get warnings for implicit type conversions? [message #622676 is a reply to message #622672] Thu, 28 August 2014 07:08 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2447
Registered: May 2013
Location: World Wide on the Web
Senior Member
Justin Schoeman wrote on Thu, 28 August 2014 17:18

I agree that the way it was done is wrong - the question is: 'How do I fix it'?

What tools/facilities are there in Oracle/SQLDeveloper that I can use to fix the problem?

Are there any such tools/options in Oracle/SQLDeveloper?


You are wishing for a magic wand, just like Harry Potter. The bad news is, AFAIK, there isn't any tool to automatically fix your problem. As far as Oracle is concerned, it throws an error with as many details as you need to fix the issue. I would suggest, start fixing your code as soon as possible, else, with course of time you will run into deeper trouble. Use this opportunity as a leaning experience, and going forward never depend on implicit conversions. Ask other developers to stop this bad practice, else it's NOW OR NEVER.
Re: How to get warnings for implicit type conversions? [message #622681 is a reply to message #622653] Thu, 28 August 2014 07:41 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2049
Registered: January 2010
Senior Member
Justin Schoeman wrote on Thu, 28 August 2014 06:07
So basically I have two questions:
1) is it somehow possible to convince SQLDeveloper to provide warnings of implicit type conversions, or
2) is there some other way to handle the concatenation of numbers to strings?


1. Unfortunately Oracle doesn't provide a way to raise warnings in case of implicit conversions.
2. Term "strings" is a broad term. You need to follow Implicit Type Conversion Matrix. Error message is misleading. There is no implicit conversion between NUMBER and CLOB. Don't ask me why, that's Oracle's decision. There is implicit conversion between VARCHAR2 and CLOB, that's why to_char(1234567890)||v_c works.

SY.
Re: How to get warnings for implicit type conversions? [message #622682 is a reply to message #622681] Thu, 28 August 2014 07:52 Go to previous messageGo to next message
Justin Schoeman
Messages: 8
Registered: February 2008
Junior Member
Gack. So basically there is a broad class of very bad errors that Oracle provides no way to warn you about...

Does anybody perhaps know why the following doesn't work:

CREATE OR REPLACE FUNCTION ov_concat_cn(a CLOB, b NUMBER) RETURN CLOB AS
BEGIN
  RETURN a||'x'||to_clob(b);
END;
/

CREATE OR REPLACE OPERATOR "||"
   BINDING (CLOB, NUMBER) RETURN CLOB
   USING ov_concat_cn;
   
select to_clob('foo')||12345 from dual;


Nothing returns an error, but it still uses the system || operator, instead of the overloaded one. If I can overload the clob,number and number,clob variants of the operator, then it should provide a reasonable work-around.

Only other option I can think of is to export all the PL/SQL, and write an external lexical parser to find all bogus implicit conversions...
Re: How to get warnings for implicit type conversions? [message #622685 is a reply to message #622682] Thu, 28 August 2014 08:02 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
o basically there is a broad class of very bad errors that Oracle provides no way to warn you about...

You can kick some of them using:
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';

See Database PL/SQL Language Reference
Chapter 11 PL/SQL Error Handling
Section Compile-Time Warnings

[Updated on: Thu, 28 August 2014 08:03]

Report message to a moderator

Re: How to get warnings for implicit type conversions? [message #622747 is a reply to message #622685] Fri, 29 August 2014 01:06 Go to previous messageGo to next message
_jum
Messages: 490
Registered: February 2008
Senior Member
BTW Your operator works:
CREATE OR REPLACE FUNCTION ov_concat_cn(a CLOB, b NUMBER) RETURN CLOB AS
BEGIN
  RETURN a||'x'||to_clob(b);
END;
/

CREATE OR REPLACE OPERATOR "||"
   BINDING (CLOB, NUMBER) RETURN CLOB
   USING ov_concat_cn
/   
   
SELECT "||"('foo',12345) opc
  FROM dual;

OPC               
------------------
foox12345         
1 row selected.

DROP  OPERATOR "||";

[Updated on: Fri, 29 August 2014 01:06]

Report message to a moderator

Re: How to get warnings for implicit type conversions? [message #622765 is a reply to message #622747] Fri, 29 August 2014 03:29 Go to previous messageGo to next message
Justin Schoeman
Messages: 8
Registered: February 2008
Junior Member
Ah - so it creates a new prefix operator, rather than overloading the existing infix operator. Any idea how to overload the existing operator? The documentation on Oracle's web site is dismal to say the least...
Re: How to get warnings for implicit type conversions? [message #622766 is a reply to message #622682] Fri, 29 August 2014 03:36 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2447
Registered: May 2013
Location: World Wide on the Web
Senior Member
Justin Schoeman wrote on Thu, 28 August 2014 18:22

   
select to_clob('foo')||12345 from dual;


If I can overload the clob,number and number,clob variants of the operator, then it should provide a reasonable work-around.


Well it will take the same effort and not a reasonable workaround since the syntax will change while using user-defined operator. So what you thought to do as a quick workaround to create || as user-defined operator so that the existing code 'foo'||12345 works, won't happen in reality. Therefore you need to manually modify the code.
Re: How to get warnings for implicit type conversions? [message #622767 is a reply to message #622765] Fri, 29 August 2014 03:46 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2447
Registered: May 2013
Location: World Wide on the Web
Senior Member
Justin Schoeman wrote on Fri, 29 August 2014 13:59
Any idea how to overload the existing operator?


AFAIK, you can't.
Re: How to get warnings for implicit type conversions? [message #622768 is a reply to message #622766] Fri, 29 August 2014 03:47 Go to previous message
Justin Schoeman
Messages: 8
Registered: February 2008
Junior Member
It would be a reasonable workaround if operator overloading worked correctly. This is exactly what operator overloading is designed for.

I am down to my last option now. Writing a PL/SQL parser in perl to locate every variable/function passed as an argument to the || operator, and create a list of every instance with a numeric argument.
Previous Topic: First business day of a given month
Next Topic: Excel created using utl_file is larger than manual file creation
Goto Forum:
  


Current Time: Fri Oct 24 16:51:01 CDT 2014

Total time taken to generate the page: 0.30401 seconds