RE: Force implicit data conversion - REOPENED

From: Michael Rosenblum <mrosenblum_at_dulcian.com>
Date: Mon, 23 Mar 2015 20:11:38 +0000
Message-ID: <17e6037f8e024fdf9be68eb122c340ca_at_Mail.dulcian.local>

Well, not exactly (IMHO). If implicit datatype conversion is the explanation, than why does first PL/SQL block fails, while the second succeeds?

SQL> declare
  2 v_nr number;
  3 begin

  4      select /*+ gather_plan_statistics misha119 */ count(*) into v_nr
  5      from misha_bind where object_name=100;

  6 end;
  7 /
declare
*
ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at line 4

SQL> declare
  2 v_nr number;
  3 begin

  4      select /*+ gather_plan_statistics misha120 */ count(*) into v_nr
  5      from misha_bind where object_id='100';

  6 end;
  7 /

PL/SQL procedure successfully completed.

SQL> The error clearly suggests that Oracle is applying TO_NUMBER to OBJECT_NAME (Case #2), but for the Case #1 doesn’t apply TO_CHAR to OBJECT_ID.

Still puzzled,
Michael

From: Sayan Malakshinov [mailto:xt.and.r_at_gmail.com] Sent: Monday, March 23, 2015 1:55 PM
To: Michael Rosenblum
Cc: Oracle-L Freelists
Subject: Re: Force implicit data conversion - REOPENED

Michael,

This is well described in the documentation: https://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements002.htm#SQLRF00214

--

Best regards,
Sayan Malakshinov
Oracle ACE Associate
http://orasql.org
--

http://www.freelists.org/webpage/oracle-l Received on Mon Mar 23 2015 - 21:11:38 CET