I am writing my first PL/SQL script.
In a batch-processing script I'm writing, I'm inserting data from a "comment" field of Table A into a column of Table B that has various integrity constraints (hence concerns about ORA-12899 - value too long, or ORA-02291 - value not allowed).
If an attempt to insert a value into Table B raises an exception, I'd like to use standard output to report not only that an insertion had to be skipped, but which contents of Table A resulted in a skipped insertion.
I'm aware that I could procedurally loop through Table A's data and essentially call 1 INSERT at a time, reporting out the value of any data that raised an exception to that call. But if possible, I'd like to stick closer to the principle of "letting SQL do its job."
Is there any way to display data from Table A when a straight-SQL "INSERT INTO {Table B} SELECT FROM {Table A}" raises an exception?
Note: I don't have any DDL permissions against the database and know that I can't get any; I only have DML & query like INSERT/UPDATE/SELECT. So solutions like "creating a log table" are out of the question, unfortunately.
Update: Second part to my question: Reading Question 1065829, I'm starting to think that even the skipping of invalid insertions and moving on with the valid insertions isn't possible with straight SQL and no DDL access (which I had presumed would be possible before writing my question above). It looks like I'm going to have to choose between iterating through Table A's rows, doing one INSERT call at a time, and asking for special DDL permissions. Is this correct?
Thank you!