Oracle Database Discussions

Prefix your identifiers with colon, ie

:EMPLOYEE_ID

to_date(:TASK_DATE)

Create a transformation rule in Data Loading ...

Hai Scott,

Thanks for your quick response!

I have one more doubt in Transformation Rule,

why cant i return boolean and display my error message when it return False condition ?

Thanks in advance

Regards

Balaji Madhavan

I'm not sure that's the place for such an action. Transformation rules are just there to massage data.

If you want a record to fail because a value is incorrect, I think that's where you have a lookup definition with 'insert new value' as No.

how can i validate the data in the lookup definition using a PLSQL code ??

Would you need pl/sql? A lookup against a table not complex enough for your needs?

Maybe this sort of process needs to happen outside the declarative data loading process?

Okay Scott,

Let me explain my requirement, I have a spreadsheet file with data in it and all i need is to perform a validation before inserting it into table. I am inserting the data through data load wizard process, how can i validate and restrict the wrong data.

Please guidee me .

That's the purpose of a 'table lookup'. If you define one against a column, and the lookup table is the list of valid values, you can have the record rejected if 'insert new value' is No, and the value doesn't match.

This will work well for values with small number of values, but I'm not sure if you can make it conditional to other data in the row.

Yes, Scott, you're right, I have created a Lookup validation to validate a particular column if it has values matching in the Master table ie, lookup table, I have done that

Now i need to validate the Task_hours column according to my condition and restrict the failed records, how can i do that ??

Balaji Madhavan wrote:
Okay Scott, 
Let me explain my requirement, I have a spreadsheet file with data in it and all i need is to perform a validation before inserting it into table. I am inserting the data through data load wizard process, how can i validate and restrict the wrong data.
Please guidee me .

I have never seen those two items ( "validation before inserting it into table" and "data load wizard" ) work nicely together.

IMHO - You are using the wrong tool for the job.

What you need is a Data Extract, Transform, and Load Wizard [ETL].  "Validation before inserting it into table" is usually a part of the process also.

There is no "APEX ETL Wizard", you have to use a "DIY ETL Wizard".  (DIY === Do It Yourself)

Recent thread went over the Extract portion of a DIY ETL Wizard:  Best way to load CSV files over 100 columns into database from Apex Application

In that thread, Scott posted a link to a nice blog that listed out the components for creating a proper "DIY ETL Wizard"

https://jeffkemponoracle.com/2018/11/load-spreadsheet-data-into-apex/

My $0.02

MK