Oracle-Connection hangs on executing a stored proc...

[iKnowItsLame]

Hi Oracle-Lovers,

this one has me quite stumped.

powershell-script simplified for repro-purposes:

   $conn = New-Object System.Data.Odbc.OdbcConnection;
   # PROD-DB: working
   $conn.ConnectionString = "Dsn=system_dsn_name;Uid=db_user;Pwd=db_password";

   # DEV-DB: hangs...
   # $conn.ConnectionString = "Dsn=other_system_dsn_name;Uid=db_user;Pwd=db_password";

   $conn.open();

   $cmd = New-object System.Data.Odbc.OdbcCommand;
   $cmd.CommandText = "call someProcedure(?, ?)";

   $cmd.Connection = $conn;
   $cmd.CommandType = [System.Data.CommandType]::StoredProcedure;

   $p1 = $cmd.CreateParameter();
   $p1.OdbcType = [System.Data.Odbc.OdbcType]::VarChar;
   $p1.Size = 64;
   $p1.Direction = [System.Data.ParameterDirection]::Input;
   $p1.Value = "value_param_1";
   $cmd.Parameters.Add($p1);

   $p2 = $cmd.CreateParameter();
   $p2.OdbcType = [System.Data.Odbc.OdbcType]::Numeric;
   $p2.Direction = [System.Data.ParameterDirection]::InputOutput;
   $p2.Value = -1;
   $cmd.Parameters.Add($p2);

   $cmd.ExecuteNonQuery();

   Write-Host "returnValue: $($p2.Value)" -ForegroundColor Yellow;

   $conn.close()

Database: 11g R2

someProcedure is an sProc with 2 parameters, the first one IN, the second one OUT.

If I execute this script as is against DatabaseOne "it's 4 o'clock and all is well"; commenting out the first ...ConnectionString and enabling the second one (different Server / Instance etc) the thing just hangs and eventually runs into a timeout.

The -not quite interested- DBA shrugs it off with "so if your newfangled powershell thingy doesn't work, huh? Then extend your <existing thing>, case closed."

Difficulty: the <existing thing> is a frankenstein worth its own which reared its head ~2005 and I absolutely do NOT want to touch.

Things I've checked:

• ODBC-DSNs: no difference except the used TNSNAME
• tnsnames.ora: no difference except host- / instancename for the respective entry
• everything else (firing a SELECT | INSERT | UPDATE) works on both DSNs, it's just barfs on exec'ing a sProc
  /cannae touch the db-server and DBA ain't interested :-(

Any ideas anyone?

[Luhmann]

@iKnowItsLame said in Oracle-Connection hangs on executing a stored proc...:

Hi Oracle-Lovers

sounds like a @boomzilla alt

[loopback0]

@iKnowItsLame Does the queryprocedure just take too long on Dev?

What happens if you run the procedure using sqlplus or SQL Developer or whatever?

[PleegWat]

Do both servers have the same version of the stored proc (and any other procs it invokes, directly or indirectly)?

Do they run the same version of the database?

[iKnowItsLame]

@loopback0

it works of course...
the sProc isn't anything new, the one thing that has changed is how I talk to the database (powershell vs. frankenstein .net v?); both via ODBC.

The one thing I've found within <frankenstein> is that this thing never calls the sProc directly and does a "insert into <blah> select sProc, whatever from dual" instead.

Still: this doesn't explain why it works on <thisdatabase> and doesn't on <thatdatabase>

[iKnowItsLame]

@PleegWat yes (can't doublecheck right now but DBA says yes)

EDIT: the sProcs are the identical, that I can confirm 'cos it's mine.

[boomzilla]

@iKnowItsLame said in Oracle-Connection hangs on executing a stored proc...:

The one thing I've found within <frankenstein> is that this thing never calls the sProc directly and does a "insert into <blah> select sProc, whatever from dual" instead.

1. Does a select sProc...from dual (or the same insert statement as <frankenstein>, if not) work in PS?
2. Can you call other sProcs directly from PS?
3. Can you call a sProc directly from <frankenstein>?

[iKnowItsLame]

@boomzilla said in Oracle-Connection hangs on executing a stored proc...:

@iKnowItsLame said in Oracle-Connection hangs on executing a stored proc...:

The one thing I've found within <frankenstein> is that this thing never calls the sProc directly and does a "insert into <blah> select sProc, whatever from dual" instead.

1. Does a select sProc...from dual (or the same insert statement as <frankenstein>, if not) work in PS?
   not tested yet, also see #3
2. Can you call other sProcs directly from PS?
   nope
3. Can you call a sProc directly from <frankenstein>?

can't|won't check because I DO NOT WANT to touch this beast (lost sourcecode, decompiled, etc...) and since it does a boatload of <other things which can't be rolled back> I won't go this route, ever.

[loopback0]

@iKnowItsLame said in Oracle-Connection hangs on executing a stored proc...:

@loopback0
it works of course...

In a reasonable amount of time?

[iKnowItsLame]

@loopback0 instant, nothing like > 0.01 sec

[boomzilla]

@iKnowItsLame said in Oracle-Connection hangs on executing a stored proc...:

@boomzilla said in Oracle-Connection hangs on executing a stored proc...:

@iKnowItsLame said in Oracle-Connection hangs on executing a stored proc...:

The one thing I've found within <frankenstein> is that this thing never calls the sProc directly and does a "insert into <blah> select sProc, whatever from dual" instead.

1. Does a select sProc...from dual (or the same insert statement as <frankenstein>, if not) work in PS?
   not tested yet, also see #3
2. Can you call other sProcs directly from PS?
   nope
3. Can you call a sProc directly from <frankenstein>?

can't|won't check because I DO NOT WANT to touch this beast (lost sourcecode, decompiled, etc...) and since it does a boatload of <other things which can't be rolled back> I won't go this route, ever.

OK. That's #3. What about 1 or 2?

[PleegWat]

How large is the risk that the work you are doing now will eventually be rejected in review because you're not using frankenstein and nobody else knows (or wants to know) PS?

[loopback0]

@iKnowItsLame said in Oracle-Connection hangs on executing a stored proc...:

@loopback0 instant, nothing like > 0.01 sec

It doesn't sound like Oracle being Oracle, at least.

[iKnowItsLame]

@boomzilla
for #1 I will have to figure out <decompiled source> actually does and then try to parrot this with PS... augh

2 is already answered I thing: nope, no sProc (even a stupid one which just parrots input-param to output-param) works on DB1 but does on DB2...

[boomzilla]

@iKnowItsLame said in Oracle-Connection hangs on executing a stored proc...:

@boomzilla
for #1 I will have to figure out <decompiled source> actually does and then try to parrot this with PS... augh

Well...I mean...can't you just put it inside a sql statement to see if that works?

# 2 is already answered I thing: nope, no sProc (even a stupid one which just parrots input-param to output-param) works on DB1 but does on DB2...

Ah. I guess I missed that. Yeah, this points to some weird configuration thing to my mind. Which is all of Oracle configuration, to my understanding, which is meager.

[iKnowItsLame]

@loopback0 said in Oracle-Connection hangs on executing a stored proc...:

@iKnowItsLame said in Oracle-Connection hangs on executing a stored proc...:

@loopback0 instant, nothing like > 0.01 sec

It doesn't sound like Oracle being Oracle, at least.

• select <single column from a table with ~500 entries>
• increment column
• update <row>
  ... one would buttume that even oracle can do this instant
  

difficulty: on the DB refusing this I'm alone, on the working DB are ~100 users at any given time.

[MathNerdCNU]

@iKnowItsLame Have you considered writing a quick and dirty version of your Powershell script as a Java app using the JDBC driver? As long as your sqlnet.ora is fairly vanilla you shouldn't have to dig to far into the documentation.

I only ask because it sounds like your bum-for-a-DBA can't be arsed to go check the alert log/listener log on the DEV-DB. That would be work and we can't have any of that!

Only other thing that jumps to mind is the DEV-DB is on a different patch level than the PROD-DB and the DEV-DB patch level requires clients to be patched up to a higher level than you are currently at.

[iKnowItsLame]

@MathNerdCNU

That would be work and we can't have any of that!

.. same here: estimated: ~5hrs; actually spent: 15hrs (with ~95% done, just the effin' sProc-call fails)
-> I can't have any of that

Edit: this is not a "do my homework, please"-thing, it just really really irks me is happening here
-> will check re patch-level, if there's any difference my fellow wtf-readers already know what to expect...

[loopback0]

@MathNerdCNU said in Oracle-Connection hangs on executing a stored proc...:

your bum-for-a-DBA can't be arsed to go check the alert log/listener log on the DEV-DB. That would be work and we can't have any of that!

The DBA runs an explain plan for all work they're asked to do and refuses anything with a cost > 1.

[iKnowItsLame]

@loopback0
"explain plan" == cost > 0 therefore WONTEXECUTE

[pie_flavor]

@iKnowItsLame said in Oracle-Connection hangs on executing a stored proc...:

@boomzilla
for #1 I will have to figure out <decompiled source> actually does and then try to parrot this with PS... augh

2 is already answered I thing: nope, no sProc (even a stupid one which just parrots input-param to output-param) works on DB1 but does on DB2...

[Carnage]

Some strange configuration that blocks some clients on the dev dB perhaps?

[Tsaukpaetra]

Something trying to lock the whole database when executing?

[iKnowItsLame]

just to follow up / "close" this one:

-) I'm really sorry bout the and cannot explain how I manged to do that, everything looked fine in preview.

-) no I was not able to fix the timeouts on Dev-DB
... I just winged it: since the sProc in question creates/reserves PKs for <table submitted as parameter#1> (parameter#2 is the output I have to use; don't ask) I just pre-created a "free range" of PKs, used this area for test/debug and just hot-deployed this thing to Prod

-) as it is: no, no risk re "we're gonna reject this 'cos <raisins>", got a "happy customer"-feedback and I would've ignored anything else anyway.

/followup: my script (read <directory>, do some mangling with the filenames, look up in <database> and INSERT some rows according to <lookup> or not) went live with ~5 files provided by <their admin> -> all was well.

so... they decided to flip the switch on the <providing end> too:
-> the 3rd-party-thing plonked about 10K files into "my" directory; of course this happened ~5mins before <my script> was scheduled to run and while <admin> was frantically cleaning up the mess (~2K files were utter garbage) of course my script also fired... worked as expected (INSERTs got rolled back, complain in logfile and plod on...)

Summary: "works", until Prod-DB doesn't decide to ape Dev-DB's behavior I won't care about <Topic> any more. If -and when- that problem pops up my script will be the frankenstein and -hopefully- someone else's problem

//not bad for a ~6line-Spec where I had to decide the rest on my own anyway

[boomzilla]

@iKnowItsLame said in Oracle-Connection hangs on executing a stored proc...:

creates/reserves PKs...I just pre-created a "free range" of PKs

This generates so many more questions.

[loopback0]

@boomzilla said in Oracle-Connection hangs on executing a stored proc...:

@iKnowItsLame said in Oracle-Connection hangs on executing a stored proc...:

creates/reserves PKs...I just pre-created a "free range" of PKs

This generates so many more questions.

Some of the questions are presumably pre-created.

[boomzilla]

@loopback0 nah...I'm free ranging today.

[iKnowItsLame]

it's quite simple:

sProc (P1 varchar INPUT, P2 number OUTPUT)...
select <col> from counter_table where table_name = P1
set P2 = <col> + 1
update counter_table set <col> = P2 where table_name = P1
return P2

/that's not PL-SQL, I know but CBA to look up the syntax used; I'm not that good at orcl-lingo and much more at home with MSSQL.

so I just exec'd this sProc a few times via SQLDeveloper and just used the PKs returned for testing; of course I had to DELETE ... them before the next run of my script but...

[loopback0]

If only Oracle had some sort of new-fangled feature to handle this for you.

(FWIW one of the applications I look after is built on a COTS product which ignores the database support for both primary and foreign keys and handles them itself, so I'm used to this retardedness - this one even has a table to store the PKs its generated but ended up not using so they can be recycled)

[dkf]

@loopback0 said in Oracle-Connection hangs on executing a stored proc...:

ignores the database support for both primary and foreign keys and handles them itself

[iKnowItsLame]

@loopback0 said in Oracle-Connection hangs on executing a stored proc...:

If only Oracle had some sort of new-fangled feature to handle this for you.

this one I can explain:
-) <Database> is "owned" by <product> with "Enterprise" in its name (hint?)
-) aforementioned <product> currently supports SQL Server >2005 and Oracle >10 as backend
-) <product> once also supported Informix and Access (or at least .mdb whateverthisshitis; not sql-server, of that I'm sure)
-> everything via ODBC, at least as far I can be bothered to remember; joined <Solution Provider> for <Vendor of product> just at the time where the last Informix-Customer left; this was ~2005 or so.

TL; DR: think of it as a CRU-Framework "...just like Oracle Forms..."; the D omitted 'cos usually one cannot (e.g. would not want to, really) "D"elete records and it's usually sufficient to just flip the table.row.deleted-column to Y.

That being said I absolutely understand the approach re "we don't want to use/support N code-paths re PK|FK and roll our own instead, our <service> handles this just fine".
... and it does; it's just when one has to leave <vendors embrace> because of (feel free to ask!) and here the aforementioned sProc comes into play.

The sProc just mimics <method_that_would_be_available_if_one_used_vendors_framework> (but can't be used 'cos nothing that came out after VB5 is officially supported, remember "Enterprise"?) and works just fine (we've implementations for MSSQL and Oracle); it's just at this one customer where the DB-Engine barfed, DEV-Instance only...

Anyway, it's quite nice to work with <Framework> once one is familiar with the ever growing list of limitations/workarounds/bugs (use <froob>, except with Builds N_to_Q -> only on Tuesdays; everything newer use <frotz> except when it doesn't work then use <froob>, YMMV; but I'm preaching to the choir here, I am posting on reddit, right?)

PS: I'm not sure if Stockholm-Syndrome applies here or if it's just "that's just like it is", my experience in this field -since 2005- points to the latter one and I've seen quite a number of <other vendors> where glossy "can do!"-papers came out as not-so-nice-"well shite, you have to code xml by hand because that's what they expect and since powers-that-be signed off, just do it"

[loopback0]

@iKnowItsLame said in Oracle-Connection hangs on executing a stored proc...:

this one I can explain:

Enterprise.

@iKnowItsLame said in Oracle-Connection hangs on executing a stored proc...:

-) <Database> is "owned" by <product> with "Enterprise" in its name (hint?)

At least having "Enterprise" in the name means you're not using the same product as we are in my example above.

@iKnowItsLame said in Oracle-Connection hangs on executing a stored proc...:

-) aforementioned <product> currently supports SQL Server >2005 and Oracle >10 as backend

Yup. Ours too.

@iKnowItsLame said in Oracle-Connection hangs on executing a stored proc...:

Access (or at least .mdb whateverthisshitis; not sql-server, of that I'm sure)

Access, alas.

[iKnowItsLame]

@loopback0

so we're brothers in Stockholm, huh?

[loopback0]

@iKnowItsLame said in Oracle-Connection hangs on executing a stored proc...:

@loopback0

so we're brothers in Stockholm, huh?

FTFY.

[iKnowItsLame]

@loopback0 said in Oracle-Connection hangs on executing a stored proc...:

@iKnowItsLame said in Oracle-Connection hangs on executing a stored proc...:

@loopback0

so we're brothers in Stockholm, huh?

FTFYAgain for citizens in countries with beers worth their name, otherwise you're wouldathushtwritesome gibberigobbledygook, JA?

/drink and post responsibly
//one of three ain't so bad, huh?

hic :-)

[iKnowItsLame]

@iKnowItsLame said in Oracle-Connection hangs on executing a stored proc...:

@loopback0 said in Oracle-Connection hangs on executing a stored proc...:

@iKnowItsLame said in Oracle-Connection hangs on executing a stored proc...:

@loopback0

so we're brothers in Stockholm, huh?

FTFYAgain for citizens in countries with beers worth their name, otherwise you're wouldathushtwritesome gibberigobbledygook, JA?

/drink and post responsibly
//one of three ain't so bad, huh?

hic :-)

... or should I be supposed to grok some rules here (2 normal, rest of it green, subtracting something or not)?
-> NEIN!

Either you're drinking responsibly or you aren't; go on posting anyway

[loopback0]

@iKnowItsLame said in Oracle-Connection hangs on executing a stored proc...:

... or should I be supposed to grok some rules here (2 normal, rest of it green, subtracting something or not)?

Nah. Just Enterprise = more drinking.

@iKnowItsLame said in Oracle-Connection hangs on executing a stored proc...:

Either you're drinking responsibly or you aren't

The important thing is you're drinking.

@iKnowItsLame said in Oracle-Connection hangs on executing a stored proc...:

go on posting anyway

Unless you're @lucas1.

[iKnowItsLame]

@loopback0
so, to sum it up: drink.
if one has to deal with <Enterprise> just drink more.

sounds allright to me

do let's close this, thanks y'all for the grins.

PS: TRWTF is the forum
the editor say <@>loopback0
live preview shows: <@>boomzilla
... at least in FF 66.0.1 64bit / Linux

[PleegWat]

@iKnowItsLame said in Oracle-Connection hangs on executing a stored proc...:

@loopback0
so, to sum it up: drink.
if one has to deal with <Enterprise> just drink more.

sounds allright to me

do let's close this, thanks y'all for the grins.

PS: TRWTF is the forum
the editor say <@>loopback0
live preview shows: <@>boomzilla
... at least in FF 66.0.1 64bit / Linux

Check your calendar.

[iKnowItsLame]

@PleegWat
yeah... noticed it just a minute after submit.

NodeBB.contains("Enterprise"), so I'll just drink some more.

I do think that @blakeyrat deserves admin-equiv anyway.

[iKnowItsLame]

@iKnowItsLame
and now you've gone and done it and I really did confused Morbs with Blakey.

I miss them both (one just tries -and fails- to be an asshole most of the time, the other one seems to be on the spectrum and the last one belongs to our herd of cats) and therefore DEMAND that everyone gets to be Moderator.

/or else
//NO CATCARRIER

[PleegWat]

@iKnowItsLame Don't we all strive to kneel among the warthogs.

[HardwareGeek]

@PleegWat No, striving sounds like far too much effort.

[iKnowItsLame]

@HardwareGeek

you will kneel in front of Maggie (18?), Viola (15), Tinkerbell & Mika (7).

Otherwise Mika will call out to RALPH while he just happens to be in front of you shoes.
/you have been warned, strike one.

[pie_flavor]

@iKnowItsLame you read like @SpectateSwamp.

[hungrier]

@iKnowItsLame said in Oracle-Connection hangs on executing a stored proc...:

Otherwise Mika will call out to RALPH while he just happens to be in front of you shoes.

One time my friend got hammered at a bar, and he called out to RALPH all over some guy's shoes

[iKnowItsLame]

you know, this thingy got something spun up here and I can't stop thinking about how "Love you even more" would sound if one applies a clbuttic regex to it, e.g. s/love\ you/drink/g in sed-parlor.

the more I think about this the faster the wheels are spinning: windmills of your mind, i hear them ;-)

EDIT: re "love you even more": atm I only find references to somebody called "Prince Malik" from 2013 but i'm quite sure that this is quite a bit older....

Q: do you know how to diagnose Alzheimer?
A: no, how?
Q: Do you know his first name?
A: ..umm..
Q: there you go -> diagnose came out positive