How Do You Know What Column Is What Plsql Error Report

How to discover the exact cavalcade proper noun for the PL/SQL exception

Howdy,

Need to find the verbal column name for the PL/SQL exception. Say for example, I have an fault ORA-01722: invalid number, I want to know in brandish along with the mistake message, the table proper name and the column proper noun in which the mistake has occured. How tin I attain this.

Thank you, Anand

Answers

• 

  I don't remember pl/sql can practise that. At max you lot can put DBMS_UTILITY.FORMAT_ERROR_BACKTRACE to identify the verbal row where y'all faced the effect

• 

  Hi Nimish,

  One of my colleague told that there was selection to extend the SQLERRM to display these details by changing some data base level parameters. But non sure what are the parameters or how to alter these. Take you come across any info like this?

  Thank you,
  Anand

• 

  SQLERRM is merely the fault message which comes where whatever ora-error

  await at following example for meliorate understanding

  SQL> set serveroutput on SQL> declare   2     l_num number(ten);   3  brainstorm   4     l_num := 'nimish';   v  exception   6     when others so   7             dbms_output.put_line('sqlcode:sqlerrm' || sqlcode || ':' || sqlerrm);   8             dbms_output.put_line('DBMS_UTILITY.FORMAT_ERROR_BACKTRACE' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);   ix  stop; 10  / sqlcode:sqlerrm-6502:ORA-06502: PL/SQL: numeric or value mistake: character tonumber conversion error DBMS_UTILITY.FORMAT_ERROR_BACKTRACEORA-06512: at line iv                  

• 

  Hullo,

  Are you referring to "dbms_utility.wait_on_pending_dml"

  - Thanks

  Pavan Kumar N

• 

• 

  Hello,

  Every bit OP mentioned, information technology would property list of tables during exception, I just tot, he would be referring to that bundle.

  Since OP, is not specific with request.

  -Thanks

  Pavan Kumar N

• 

  Need to find the exact column proper noun for the PL/SQL exception. Say for example, I have an error ORA-01722: invalid number, I want to know in display along with the mistake message, the tabular array name and the cavalcade name in which the error has occured. How tin I achieve this.

  Deplorable - there is NO such adequacy.

  Many exceptions, including that 1, tin can be acquired by in a number of ways.

  select to_number('abc') from dual  ORA-01722: invalid number                  

  Tin YOU tell us 'the table name and the column name in which the error has occured'?

  The conversion above is NOT due to data in any table but nevertheless gives the exception.

  Unfortunately, for some data issues y'all need to manually examine the data AND know what conversions are being performed on it. Sometimes you can try inserting the data into a tabular array and apply DML error logging to grab the errors. Other times you only have to do it the hard mode.

• 

  My Scenario goes like this.

  I take 100+ tables and each table is having more than xx columns. I get data in csv formats. I load the data to staging tables which will have all columns in varchar2 format to ensure there are no errors while loading. From staging table, I do majority insert into the actual table which volition take the exact format for each column. The PLSQL logic takes intendance of to_date, to-number conversions wherever required. If by any risk, the data provided is junk, the data is going to fail load. In that case, the mistake is captured through exception and stored in another table. It will hard task to write validation for each and every column. Hence I am looking for ways(if any) to include the column name in the mistake message, so that I tin can pick that item cavalcade and ready the fault.

  Promise I clarified.

  Thanks,

  Anand

• 

  hi,

  Not sure the architecture of your project.But i believe yous should utilize SQL Loader utility of Oracle,before loading information into staging table write SQL Loader for which your csv file will be input file and if any fault occurs than appropriately Loader will generate its bad file/discard file/Log file.If you are using UNIX in your project than y'all can view log file( which will clearly show you where and what sort of error occurred.I hope this might help you lot out.

  Regards,

  Mohit

• 

  Thanks for providing the additonal info. But in lite of what you say below information technology isn't clear at all why y'all don't already know what rows/columns are causing problems.

  I have 100+ tables and each table is having more than than 20 columns. I go information in csv formats. I load the data to staging tables which volition have all columns in varchar2 format to ensure there are no errors while loading. From staging tabular array, I do majority insert into the bodily table which will have the verbal format for each column. The PLSQL logic takes care of to_date, to-number conversions wherever required. If by any adventure, the data provided is junk, the data is going to fail load. In that case, the mistake is captured through exception and stored in another table. It will difficult task to write validation for each and every column. Hence I am looking for ways(if any) to include the column proper noun in the fault message, so that I can pick that particular column and ready the fault.

  Ok - I've washed tons of that for 25+ years and still don't understand why you are having any problem telling where the errors are coming from.

  If the 'PLSQL logic takes care of ..' the conversions then that code should already know what column information technology is converting - so why aren't you capturing that info when the conversion fails?

  What 'bulk insert' are you talking about? If you have PL/SQL logic involved is this the procedure you are using:

  If the raw VARCHAR2 data is in a staging tabular array and you do a 'bulk insert' into the bodily table where does the PLSQL logic come into play?

  Sounds like you are doing something like

  1. PL/SQL read from staging and doing TO_DATE (and other) conversions saving information into collections

  2. using FORALL to bulk insert the collection data into the actual table.

  Explicate the actual order of events. As well explain how the PLSQL logic is doing the conversions without knowing what table/row/column information technology is converting.,

  Something is missing from your explanation,

  The architecture I use adds a line_number to EVERY row in EVERY staging table. That line_number is so replicated to other staging tables as needed. That provides a fashion to track that row through every operation and dorsum to the original source. Error reporting tin then be done past row, column and error.

  The error log table tin can have multiple errors even for the same cavalcade and then that ALL information cleansing issues are captured in one laissez passer.

  Except we prefer to utilize external tables to avoid actually storing all data as VARCHAR2.

This discussion has been airtight.

romanoron1989.blogspot.com

Source: https://community.oracle.com/tech/developers/discussion/3732623/how-to-find-the-exact-column-name-for-the-pl-sql-exception

Share this post