6 Replies Latest reply: Jan 31, 2013 4:57 PM by rp0428 RSS

    Log tables

    969952
      Hi All,

      We have an existed Package which is created based on combination some applications ( data based on different schemas around 5).

      This Package is comparing the data as per the requirement and Updating and Inserting the data into respective tables. After that we can see the status of the data like ,

      If it is updated correctly then it will display the status as "U" if it's update wrongly status is "NU" -- (not Updated)
      If it is Inserted correctly then it will display the status as "I" if it's Inserted wrongly status is "NI" -- (Not Inserted)

      Now my requirement is I want to add a code for it the data is not updated/inserted correctly that particular data should store in some other table with the owner name.

      It would be easy for me to figure out the issue and resolve it. So can you please let me know the code for the above requirement.

      Thanks.
        • 1. Re: Log tables
          sb92075
          How do I ask a question on the forums?
          SQL and PL/SQL FAQ
          • 2. Re: Log tables
            969952
            May I know what's wrong here?
            • 3. Re: Log tables
              sb92075
              966949 wrote:
              May I know what's wrong here?
              how can we write code when we do not know table name or column names?
              • 4. Re: Log tables
                rp0428
                >
                We have an existed Package which is created based on combination some applications ( data based on different schemas around 5).

                This Package is comparing the data as per the requirement and Updating and Inserting the data into respective tables. After that we can see the status of the data like ,

                If it is updated correctly then it will display the status as "U" if it's update wrongly status is "NU" -- (not Updated)
                If it is Inserted correctly then it will display the status as "I" if it's Inserted wrongly status is "NI" -- (Not Inserted)

                Now my requirement is I want to add a code for it the data is not updated/inserted correctly that particular data should store in some other table with the owner name.

                It would be easy for me to figure out the issue and resolve it. So can you please let me know the code for the above requirement.
                >
                Depending on the datatypes your table uses you could use DML ERROR LOGGING
                http://docs.oracle.com/cd/B28359_01/server.111/b28310/tables004.htm
                >
                Inserting Data with DML Error Logging
                When you load a table using an INSERT statement with subquery, if an error occurs, the statement is terminated and rolled back in its entirety. This can be wasteful of time and system resources. For such INSERT statements, you can avoid this situation by using the DML error logging feature.

                To use DML error logging, you add a statement clause that specifies the name of an error logging table into which the database records errors encountered during DML operations. When you add this error logging clause to the INSERT statement, certain types of errors no longer terminate and roll back the statement. Instead, each error is logged and the statement continues. You then take corrective action on the erroneous rows at a later time.

                DML error logging works with INSERT, UPDATE, MERGE, and DELETE statements. This section focuses on INSERT statements.
                • 5. Re: Log tables
                  969952
                  Hi,

                  Thanks for the explanation. But my question here is If am trying to load and update the data using ( suquerying ) multiple tables, some records will update/insert properly. for those records it should update the status as " U/I" but for the records which are not Updated/Inserted properly those records will store in some other log table ( need to create). which will contains the Schema name as well. based on that we can figure out why the records are not loaded properly.

                  For this requirement I wan tot add some in the middle of existing procedure. Can you please provide me any sample code for the above requirement.
                  • 6. Re: Log tables
                    rp0428
                    >
                    Thanks for the explanation. But my question here is If am trying to load and update the data using ( suquerying ) multiple tables, some records will update/insert properly. for those records it should update the status as " U/I" but for the records which are not Updated/Inserted properly those records will store in some other log table ( need to create). which will contains the Schema name as well. based on that we can figure out why the records are not loaded properly.

                    For this requirement I wan tot add some in the middle of existing procedure. Can you please provide me any sample code for the above requirement.
                    >
                    Well I'm totally lost. The doc I gave you the link to HAS an example for an INSERT.

                    You just create a log table (manually or use the Oracle package) and then do your DML like you normally would. Records that have errors will get inserted into the log table.

                    Maybe these examples from Oracle-base will make it clearer.
                    http://www.oracle-base.com/articles/10g/dml-error-logging-10gr2.php

                    You should actually TRY the examples in the article. Just reading about it won't help you learn it as well.