Featured Post

Python Regex: The 5 Exclusive Examples

Image
 Regular expressions (regex) are powerful tools for pattern matching and text manipulation in Python. Here are five Python regex examples with explanations: 01 Matching a Simple Pattern import re text = "Hello, World!" pattern = r"Hello" result = re.search(pattern, text) if result:     print("Pattern found:", result.group()) Output: Output: Pattern found: Hello This example searches for the pattern "Hello" in the text and prints it when found. 02 Matching Multiple Patterns import re text = "The quick brown fox jumps over the lazy dog." patterns = [r"fox", r"dog"] for pattern in patterns:     if re.search(pattern, text):         print(f"Pattern '{pattern}' found.") Output: Pattern 'fox' found. Pattern 'dog' found. It searches for both "fox" and "dog" patterns in the text and prints when they are found. 03 Matching Any Digit   import re text = "The price of the

How to Resolve ORA-01722 Invalid Number Error in Oracle SQL Developer

When working with Oracle SQL Developer, you may come across an error message that reads "ORA-01722: invalid number". This error can be frustrating and difficult to understand, especially if you're new to SQL. In this post, I will explain the cause of this error and provide a solution to resolve it.


Invalid Number Error


Cause of the Error - ORA-01722


Let's consider an example where we have two tables named "my_hr" and "my_dept". The "my_hr" table contains three columns - "name", "pay_id", and "user_id", while the "my_dept" table contains two columns - "name" and "user_id".

CREATE TABLE my_hr (
name CHAR(20),
pay_id NUMBER,
user_id CHAR(6)
);

CREATE TABLE my_dept (
name CHAR(20),
user_id CHAR(6)
);

Now, let's insert some sample data into these tables:

INSERT INTO my_hr VALUES ('Srini', 567, '123456');
INSERT INTO my_dept VALUES ('Srini', '123456');

If we try to execute the following SELECT query to join these two tables using the "name" and "user_id" columns and filter the results based on the "pay_id" column, we will get the "ORA-01722: invalid number" error:

SELECT *
FROM my_hr a, my_dept b
WHERE name = 'Srini'
AND pay_id = '123456' -- This causes the error
AND a.user_id = b.user_id;

The reason for this error is that we are comparing a number column "pay_id" with a string literal value ('123456').

Solution to Resolve the Error:


To fix the error, we need to make sure that we are comparing the "pay_id" column with a number value instead of a string. We can do this by removing the single quotes from the value we are comparing:

SELECT *
FROM my_hr a, my_dept b
WHERE name = 'Srini'
AND pay_id = 123456 -- This is the corrected version
AND a.user_id = b.user_id;

By removing the single quotes from the value of the "pay_id" column, we can compare it with the number column correctly.

Comments

Popular posts from this blog

Explained Ideal Structure of Python Class

6 Python file Methods Real Usage

How to Decode TLV Quickly