6 Replies Latest reply: Feb 11, 2013 4:08 PM by 989009 RSS

    Oracle 10g Insert query performs inconsistent as a query vs procedure and p

    989009
      Database Version: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
      No error messages....

      I am facing a very weird issue that I have a insert statement in a procedure... pretty much

      inert
      into oracle_Table
      SELECT cr.a AS a,
      cr.b AS b,
      cr.c AS c,
      max(d.column_name) as d
      FROM "table 1"@Pmo.World Cr,
      table2@Pmo.World d
      WHERE d."a" = cr."column name"
      GROUP BY cr.a,
      cr.b,
      cr.c
      @Pmo.World is a database link to a MSSQL...

      The problem I am having right now is, every time I run this insert as a query. Everything works as what it should be... However, when I put this insert into a procedure, it inserts nothing...
      Simple test Proceudre:

      declare
      -- Local variables here
      i integer;
      begin
      inert
      into oracle_Table
      SELECT cr.a AS a,
      cr.b AS b,
      cr.c AS c,
      max(d.column_name) as d
      FROM "table 1"@Pmo.World Cr,
      table2@Pmo.World d
      WHERE d."a" = cr."column name"
      GROUP BY cr.a,
      cr.b,
      cr.c
      end;

      Thinking about character conversion issue I changed the procedure to

      inert
      into oracle_Table
      SELECT to_char(cr.a) AS a,
      to_char(cr.b) AS b,
      to_char(cr.c) AS c,
      max(d.column_name) as d
      FROM "table 1"@Pmo.World Cr,
      table2@Pmo.World d
      WHERE d."a" = cr."column name"
      GROUP BY cr.a,
      cr.b,
      cr.c
      Then this Inser works in the procedure... however when I revert it back with the original version that doesnt have to_char... it is working still... then I kept it running for few days... since it runs once per day, it was working for the first 2 days and then stopped working the third day... I verified the source table and every time this procedure runs, source tables were not empty...
      It is so confusing because if I manually run the insert as a query, it worked every time I ran it... however if I put that into a procedure, it works from time to time..

      Any help is highly apprecaited

      Edited by: 986006 on Feb 4, 2013 8:51 AM
        • 1. Re: Oracle 10g Insert query performs inconsistent as a query vs procedure and p
          Paul  Horth
          Welcome to the forum.

          Unfortunately you haven't given enough information: we can't see your procedure.

          I suspect bad exception handling but can't help more without the procedure.

          Please read: {message:id=9360002} and follow the advice there.
          • 2. Re: Oracle 10g Insert query performs inconsistent as a query vs procedure and p
            989009
            Thanks for the hints up... I have updated my post... As I post the test procedure...it is about the exact the same as the insert query... Every time, the insert would actually insert data into the table but every time I run the test procedure, nothing gets inserted in... It sounds unbelievable but it happens... Thanks
            • 3. Re: Oracle 10g Insert query performs inconsistent as a query vs procedure and p
              sb92075
              How can we reproduce what you report?


              How do I ask a question on the forums?
              SQL and PL/SQL FAQ
              • 4. Re: Oracle 10g Insert query performs inconsistent as a query vs procedure and p
                Paul  Horth
                986006 wrote:
                Thanks for the hints up... I have updated my post... As I post the test procedure...it is about the exact the same as the insert query... Every time, the insert would actually insert data into the table but every time I run the test procedure, nothing gets inserted in... It sounds unbelievable but it happens... Thanks
                You obviously haven't read the FAQ, or at least you haven't bothered doing what it asks. Help us to help you.

                Can you recreate the problem with simpler data on your local machine? If so provide create table and insert statements for test data.
                Format your code and place between
                 tags.
                At the very least post the *exact* SQL or PL/SQL you are trying to run: what you've posted isn't even valid SQL.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                • 5. Re: Oracle 10g Insert query performs inconsistent as a query vs procedure and p
                  riedelme
                  Weird problem.

                  First, make sure the issue isn't do to strange privilege issues. If the schemas running the SQL and PL/SQL routines are different it is possible that different objects with different data are being accessed and producing different results. This is unlikely but so is the situation yo9u are describing.

                  Second, the SQL you posted looks strange, particularly the text like
                  FROM "table 1"@Pmo.World Cr,
                  This is bad for a couple of reasons. First, forcing lower case objects using the double quotes is a bit of a pain. It is generally better and easier to store objects in the database in standard upper case to avoid this. You may not have a choice in the matter but this is not a desirable situation.

                  Next, the embedded space "table 1" in the quoted identfier is also problematic. What is intended here? Is the space really part of the identifier? Embedding spaces in identifiers is an even worse idea than storing them in lower case and having to quote them. This practice is all but guaranteed to cause problems eventually

                  Or, did you substitute the lower-case value with the space for what is really there to avoid posting sensitive objects?
                  • 6. Re: Oracle 10g Insert query performs inconsistent as a query vs procedure and p
                    989009
                    I was trying to replace the table name from my work with something as a place holder.. that is why I used like "table 1"... since this is a table from mssql.. and it has a space over their side... that is why I keep it this way...
                    I know this question is weird and sounds unbelievable... however I cant recreate it for now since I cant create a dummy table over other team's database but I am glad to say my problem has been resolved by using materialized view... I set a refresh time before my job call.. I tried normal view, it was not working still even I can just query the data from the view...

                    Edited by: 986006 on Feb 11, 2013 2:08 PM

                    Edited by: 986006 on Feb 11, 2013 2:08 PM