Member Avatar for saideepak89

hi.. i know little bit about the casting in java ie (from "float"to "int" or/etc) like wise my question is how the oracle converts the data in condition this is my sample query

branch -t
branch_bnk_code number3
branch_brn_code number3
branch_city_code number3

mybranch -t
mybranch_bnk_code varchar2
mybranch_brn_code varchar2
mybranch_loc_code varchar2

by this 2 tables

my query

select * from branch,mybranch
where branch_bnk_code = mybranch_bnk_code
and branch_brn_code = mybranch_brn_code

here which is type casted to which , what is the prioritised one whether the data type or the conditioned column (rignt column value or left column value) or the both.

and u can say u,i have to cast it by to_number.
i am using 11g, having the two hosts, one is running properly and another throwing excep
"invalid number"


thanks in advance.

  • 3 Contributors
  • 5 Replies
  • 204 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by Member #647493

Dani AI

Generated

Short answer: Oracle decides conversions by data type, not by left/right position. When a NUMBER is compared with a character value Oracle will try to convert the character value to NUMBER (not the other way round), and that implicit conversion will fail with ORA‑01722 if any string is not a valid numeric literal. (docs.oracle.com)

Why the two hosts behave differently: either the stored text differs (some rows contain non‑numeric characters), or the optimizer/SQL execution plan forces an attempt to convert a non‑numeric row before other predicates can filter it out. Predicate ordering in source SQL is not a safe protection against conversion errors because evaluation can be reordered or done by indices/filters. This explains the “works on one server, fails on the other” symptom. (stackoverflow.com)

Safe approaches and a practical pattern:

  • Prefer storing numeric data in NUMBER columns so no conversion is required.
  • If the text column must stay VARCHAR2, make conversions explicit and guarded. Filter the text rows to numeric format first, then cast inside a query block so conversion only runs on cleaned rows. For example:
SELECT b.*
FROM branch b
JOIN (
  SELECT mybranch_bnk_code, mybranch_brn_code
  FROM mybranch
  WHERE regexp_like(mybranch_bnk_code, '^[0-9]+$')
    AND regexp_like(mybranch_brn_code, '^[0-9]+$')
) mb
  ON b.branch_bnk_code = CAST(mb.mybranch_bnk_code AS NUMBER)
 AND b.branch_brn_code   = CAST(mb.mybranch_brn_code   AS NUMBER);

Guarding the conversion in a subquery or preprocessing step makes the intent explicit and avoids plan-dependent failures. (docs.oracle.com)

Extra notes: use REGEXP_LIKE or similar checks to find offending strings; check NLS settings (decimal separator, grouping characters) because different sessions/hosts can have different NLS parameters that affect conversion; and avoid implicit conversion on indexed columns (it can prevent index use). gave the right high‑level advice to convert types; was also right that many conversions are implicit — both points are consistent with the behavior above. ()

Recommended Answers

All 5 Replies

Member Avatar for debasisdas

If all the data contains number you can you to_number on the varchar field else use to_char on the number field.

Member Avatar for saideepak89

thats ok....i did that.. but you did not given me the ans for my question.."the way of casting and preference "

Member Avatar for Member #647493

Casting, in Oracle, is usually implicit (It knows how to do it).
Sometimes, Oracle can NOT cast implicitly. You will have to cast explicitly.
Follow debasisdas's method.
And, yes... he did answer your question.

Edited by Member #647493 because: n/a
Member Avatar for saideepak89

then can u give me the thing in the oracle implicit casting......

Member Avatar for Member #647493

Your request doesn't make any sense.
You do understand what "implicit" means, don't you?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.