i have an Oracle PL/SQL anomoly (UNIX server)

[Cartoon Corpse]

im running a stored procedure from one server updating/inserting (depending on existence on the other server of the record im looking at on the source server) an identical Oracle table on another (sqlnet) server. commits after each one.

the program appears to be updating/inserting (according to our db viewing tool Artisan) and reports successful counts for all records it tried to process and NO errors.

however nothing appears to be getting into the target table!

the link (ie insert into table@xxxx) is properly configured according to DBA, and i have indeed been successful at manually (sqlplus prompt) inserting and updating a test record from the source to the target server for that same table.

anyone know of what's going on here? i've never encountered this type of situation (no error, but no result either)

 

[Cartoon Corpse]

i have now converted the remote link version spu to a local update/insert version of the stored procedure and it works perfectly.

insert into tablex

instead of

insert into tablex@xxxx

still no errors and NO results on remote link version.

DBA insists no problems with network.

 

[Cartoon Corpse]

i also tried another table...it didn't work either.


the DBA (confidentially, i've been offered her job) is claiming it's my code....sheesh..i hate pat answer people.

turns out she created a synonym for the tablex@xxxx link. and it now 'magically' works.

i think im not getting the whole story....she's pulling a CHA (cover her ass) move.

no reason you have to use a synonym for a remote link. i don't do that elsewhere on the network in other apps.

so you people don't do grunt computer geek work? no responses?

 

[Ferrari_pro]

Have you tried commiting the insert


Just kidding... Do you not have a Metalink account? You could always raise a TAR.

 

[Zaphod]

Sounds like a transaction issue to me. Atocommit or explicit commit? I miss the most basic things myself sometimes...

 

[Cartoon Corpse]

Have you tried commiting the insert


Just kidding... Do you not have a Metalink account? You could always raise a TAR.

at our shop ONLY our dba can initiate a tar. i've requested. several times yesterday.

bottom line (IMO) is if i have a program inserting data with no errors, indeed showing successful counts (using sql%rowcount) in my run log, it's NOT my code.

yet i have to wrangle for an entire day...

then it gets fixed in this nonsensical way (she creates a synonym of the link and use that in my code). and i have no clue what happened what was wrong, etc. bad DBA, no cookie for you!


i think the thing is she isn't a developer, so has no idea what makes sense in terms of the story she offers. wasted a whole day.

 

[Cartoon Corpse]

Sounds like a transaction issue to me. Atocommit or explicit commit? I miss the most basic things myself sometimes...

you can bet i triple checked that stuff.

i commit each record as i go. it's transaction based...checks for existence...updates, if so, otherwise insert.

i know, i know, usually it is 'pilot error' but that's for noobs. i do this type of (and other types) of db/network data sharing all the time. every one of my interfaces is modeled after the others (ease of use, etc). i've been doing it for years. and it's pretty much bullet proof.

the only issues i ever have with my production end is someone filled up my disk, or failure to extend segments (resource issues).

yet when im testing new things...the DBA always says it's me.

oh well, i can take her job (comes with free dba training!) if i want next week (my deadline to decide). but im wondering what im inheriting a little.

it seems all the tech support people around here are self taught/hobbiests (no degrees...up through the ranks). i really hate 'debating' with them.