Create an error message for invalid input datatype

Question

I want to create an error message for an column

Suppose I have a column - "Employee Number" with datatype varchar2 in a table "xyz".

Now I'm importing a .csv or .txt file and there is also a column of "employee number" with datatype varchar2 but I need only number data in this column...

Please have a look at minimal reproducible example and then try to edit your question by adding some more informations (table structure, code, sample data, …). As is, it's hard to give you a good answer — Aleksej
– Aleksej, Commented Sep 12, 2018 at 7:19
If you only want numbers in that column, define it as number. Do not store numbers in varchar columns — user330315
– user330315, Commented Sep 12, 2018 at 7:35
actually i want to create my own exception in which it will show my error message for invalid datatype for particular column. — D'Mayal
– D'Mayal, Commented Sep 12, 2018 at 12:15

J. Chomel · Accepted Answer · 2018-09-12 07:45:11Z

1

It depends on how you will import your data.

Oracle `SQL*Loader`

SQL loader can automatically check the input types for you, and raise appropriate error messages.

`PL/SQL`

If you choose to write your own import program in PL/SQL, then you can use Custom exception when you detect the case. It will look like follows:

 declare
    ex_custom EXCEPTION;
    PRAGMA EXCEPTION_INIT( ex_custom, -20001 ); -- here you define a custom exception (by id number)
  begin
    -- here you import...
    if error_detected then
      raise_application_error( -20001, 'Wrong type ' );
    end;
  exception  -- then handle your exception
    when ex_custom
    then
      dbms_output.put_line( sqlerrm || 'error' );
 end;
SQL> /
ORA-20001: Wrong type error

I definitely advise you to choose the sqlloader solution in your case.

answered Sep 12, 2018 at 7:45

J. Chomel

8,44215 gold badges44 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

J. Chomel Over a year ago

Then you may use the second part of the answer.

Collectives™ on Stack Overflow

Create an error message for invalid input datatype

1 Answer 1

Oracle `SQL*Loader`

`PL/SQL`

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

PL/SQL

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related

`PL/SQL`