This discussion is archived
6 Replies Latest reply: Feb 11, 2013 2:08 PM by 989009 RSS

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

989009 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points