0

I have a table type input+output(IO) variable in oracle(with 5 columns), i read values from it in a loop and and also perform validations, if there's an validation fired i put an error message in fifth column. if the fifth column is null i insert values in table. I have working code for this. But what i need is ,suppose i have 5 rows to be inserted, and if there's a validation fired for any row then data should not be inserted for other rows as well. I cannot raise exception as i still need error message for other rows.

I have 2 solutions for this. First..Perform validations first(without insert), if fifth column is empty then will again run loop and insert in table. Second..Insert values in a temporary table, if no validation fired till last row then will select from temp table and insert in main table.

Is there a better way to handle this?

sssophkc
  • 19
  • 3
  • What do you think is wrong with your first suggested approach? It would help if you included sample data and code, including the type definition for your table type. You might be able to use `forall` instead of a second explicit loop but it isn't clear. – Alex Poole Dec 14 '18 at 13:12
  • Check `error logging clause` in Oracle Documentation. – Dr Y Wit Dec 14 '18 at 13:23
  • I knew it will be difficult to explain my problem here.. But thanks anyways, i will try forall as it has better performance – sssophkc Dec 15 '18 at 14:08

1 Answers1

0

You can try to validate all rows before inserting and then use the command "INSERT SELECT" to insert all your rows into the table with the fifth column filled with error message or not (based on the prior validation you made).

Example:

BEGIN
    --DO SOME VALIDATION

    INSERT INTO <TABLE_NAME>
    SELECT COLUMN1, COLUMN2, COLUMN3, COLUMN4, 'ERROR OR NOT ERROR' COLUMN5
      FROM <OTHER_TABLE>;
END;

This way you wouldn't need a second loop, but you need to see if it suits your needs.

Thalles Ribeiro
  • 46
  • 1
  • 6
  • Yes this would help, but i will have to create another table of same type.. Any alternare of creating a new table? – sssophkc Dec 15 '18 at 14:05