oracle DB workflows

Malcolm
Posts: 32040
Joined: Fri May 21, 2004 1:04 pm
Location: Minneapolis

Post by Malcolm »

TheCatt wrote:Every environment has Windows + Linux, yet you design on Windows, and run on Linux.
Because our linux install has zero GUI support. They aren't moving the workflow to Windows any time soon and they aren't putting in a linux distro with a windows manager any time soon, either. Another reason why I want to kick it to the curb.
Diogenes of Sinope: "It is not that I am mad, it is only that my head is different from yours."
Arnold Judas Rimmer, BSC, SSC: "Better dead than smeg."
Malcolm
Posts: 32040
Joined: Fri May 21, 2004 1:04 pm
Location: Minneapolis

Post by Malcolm »

Solution:

Removing kitchen, java, bash, and linux. Move the workflow to a Windows box. Use Oracle to parse the XML from an external table (living in a NAS), in batches. Death to the motherfucker that gave XML namespaces.
Diogenes of Sinope: "It is not that I am mad, it is only that my head is different from yours."
Arnold Judas Rimmer, BSC, SSC: "Better dead than smeg."
Malcolm
Posts: 32040
Joined: Fri May 21, 2004 1:04 pm
Location: Minneapolis

Post by Malcolm »

Another Oracle question. What's the general strategy for testing one's Oracle packages? Starting point: we currently have neither testing nor strategy.
Diogenes of Sinope: "It is not that I am mad, it is only that my head is different from yours."
Arnold Judas Rimmer, BSC, SSC: "Better dead than smeg."
TheCatt
Site Admin
Posts: 53729
Joined: Thu May 20, 2004 11:15 pm
Location: Cary, NC

Post by TheCatt »

Many people use PL Unit. Some people use Toad. A few people use things like DBFit
It's not me, it's someone else.
Malcolm
Posts: 32040
Joined: Fri May 21, 2004 1:04 pm
Location: Minneapolis

Post by Malcolm »

We already pay for Toad, so will definitely check that. We had a meeting with the dudes from the data warehouse today. Asked them if they had any strategies besides custom everything. Nope. Nothing.
Diogenes of Sinope: "It is not that I am mad, it is only that my head is different from yours."
Arnold Judas Rimmer, BSC, SSC: "Better dead than smeg."
TheCatt
Site Admin
Posts: 53729
Joined: Thu May 20, 2004 11:15 pm
Location: Cary, NC

Post by TheCatt »

Malcolm wrote:We already pay for Toad, so will definitely check that. We had a meeting with the dudes from the data warehouse today. Asked them if they had any strategies besides custom everything. Nope. Nothing.
There are different flavors of Toad, iirc, some include the testing modules, some do not. Toad is ridiculously expensive.
It's not me, it's someone else.
Malcolm
Posts: 32040
Joined: Fri May 21, 2004 1:04 pm
Location: Minneapolis

Post by Malcolm »

TheCatt wrote:
Malcolm wrote:We already pay for Toad, so will definitely check that. We had a meeting with the dudes from the data warehouse today. Asked them if they had any strategies besides custom everything. Nope. Nothing.
There are different flavors of Toad, iirc, some include the testing modules, some do not. Toad is ridiculously expensive.
Going to check on our licenses for that.
Diogenes of Sinope: "It is not that I am mad, it is only that my head is different from yours."
Arnold Judas Rimmer, BSC, SSC: "Better dead than smeg."
Malcolm
Posts: 32040
Joined: Fri May 21, 2004 1:04 pm
Location: Minneapolis

Post by Malcolm »

This is for catt, I guess.

We've just gotten all our code bases onto this fucking batshit insane loser platform at work using outdated DLLs from 3+ years ago.

The Players:

Oracle 11g database
ODAC/ODP 11.2 Release 4
.Net 4 (there's a chance I can swing 4.5)
MS Visual Studio 2013

The Game:
Get a fucking oracle data provider to connect to the oracle DB so I can make some attempt use .Net Entity Framework < 6 to make a DB-first model.

The Problem:
Fucking ODAC/ODP installer won't recognize my version of VS. I have data providers for Oracle 7-10, and Oracle 12 isn't supported by the shitty platform, so I can't get the latest and greatest.

New theory:
FUCK YOU, ORACLE.
ODAC (11.2.0.3.0) Installation Instructions, Setup, and Notes ... Additional Notes on Requirements:

Oracle Developer Tools for Visual Studio requires a minimum of Visual Studio 2010 or Visual Studio 2008 SP 1. Entity Framework tools are only supported with Visual Studio 2010.

Hopefully the data providers can be had without them ... looks like not. The data providers are .Net-dependent, but the VS dev tools look like the only way to get at a 11g version. Fucking oracle is going to make me install VS2010.




Edited By Malcolm on 1440186585
Diogenes of Sinope: "It is not that I am mad, it is only that my head is different from yours."
Arnold Judas Rimmer, BSC, SSC: "Better dead than smeg."
Malcolm
Posts: 32040
Joined: Fri May 21, 2004 1:04 pm
Location: Minneapolis

Post by Malcolm »

Oh my fucking god. FUCK ORACLE.

I'm retooling some DB tables, adding one new column to about 6-8 tables so a job can come along and use them later. I get a fatal error. Fucking awesome. Even better, I had to guess for a few hours because the informative message presented is the equivalent of "call Oracle's support team" which means I can expect an answer in about a week. However, because I'm fucking amazing, I figure the root cause myself. Oracle tries to "optimize" the new columns but since mine are a certain sort (not at all uncommon), they're rendered 100% worthless for future usage. The workaround is to drop the columns, specifically DISABLE the optimization, then add columns again. FUCK YOU ORACLE, but at least there's a way to proceed.

Now I'm getting fatal errors trying to drop columns. FUCK YOU, ORACLE.
Diogenes of Sinope: "It is not that I am mad, it is only that my head is different from yours."
Arnold Judas Rimmer, BSC, SSC: "Better dead than smeg."
TheCatt
Site Admin
Posts: 53729
Joined: Thu May 20, 2004 11:15 pm
Location: Cary, NC

Post by TheCatt »

Oracle's awesome.
It's not me, it's someone else.
Malcolm
Posts: 32040
Joined: Fri May 21, 2004 1:04 pm
Location: Minneapolis

Post by Malcolm »

One more useless error message and a core dump file later...

Had to copy the table data, disable all the refs, truncate the fucker, FINALLY drop the fucking stupid fucking bugged column, reinsert data, reenable refs. FUCK YOU, ORACLE. Why can't you handle a 1.7GB/6.8M row table better than Boyziel handles a football offense?




Edited By Malcolm on 1454625399
Diogenes of Sinope: "It is not that I am mad, it is only that my head is different from yours."
Arnold Judas Rimmer, BSC, SSC: "Better dead than smeg."
Malcolm
Posts: 32040
Joined: Fri May 21, 2004 1:04 pm
Location: Minneapolis

Post by Malcolm »

This is the setting we used to fix the problem without a performance hit. Hey catt, why in the name of jeebus would a MV-specific tweek have any impact on an update statement involving only tables?
Diogenes of Sinope: "It is not that I am mad, it is only that my head is different from yours."
Arnold Judas Rimmer, BSC, SSC: "Better dead than smeg."
TheCatt
Site Admin
Posts: 53729
Joined: Thu May 20, 2004 11:15 pm
Location: Cary, NC

Post by TheCatt »

Cuz Oracle sucks?
It's not me, it's someone else.
Malcolm
Posts: 32040
Joined: Fri May 21, 2004 1:04 pm
Location: Minneapolis

Post by Malcolm »

Finding that magic setting was also devoid of real deduction or intuition.

DBA: Here, try this alter session command.
Malcolm: Nope, didn't work.
[repeat a dozen times]
...
M: Holy shit. It worked.
DBA: Wtf? You're kidding.

It was like playing a goddamn game of Battleship.
Diogenes of Sinope: "It is not that I am mad, it is only that my head is different from yours."
Arnold Judas Rimmer, BSC, SSC: "Better dead than smeg."
Malcolm
Posts: 32040
Joined: Fri May 21, 2004 1:04 pm
Location: Minneapolis

Post by Malcolm »

Virtual columns in Oracle 11g .. supported since:
Oracle Database 11g Release 1
When queried, virtual columns appear to be normal table columns...

My motherfucking ass. As long as you don't want to use them in any select queries ever, yeah, they're supported. Otherwise, the query "optimizer" bends you over and fucks you in the ass, ORA-00600-style.

One day, we'll upgrade to 11.2.




Edited By Malcolm on 1457994802
Diogenes of Sinope: "It is not that I am mad, it is only that my head is different from yours."
Arnold Judas Rimmer, BSC, SSC: "Better dead than smeg."
TheCatt
Site Admin
Posts: 53729
Joined: Thu May 20, 2004 11:15 pm
Location: Cary, NC

Post by TheCatt »

Duuuuuddddeeeeee... 11gR1? Seriously? That shit is 9 years old. Extended support ended a year ago.
It's not me, it's someone else.
Malcolm
Posts: 32040
Joined: Fri May 21, 2004 1:04 pm
Location: Minneapolis

Post by Malcolm »

TheCatt wrote:Duuuuuddddeeeeee... 11gR1? Seriously? That shit is 9 years old. Extended support ended a year ago.

Yeah. I'd invade Mordor singlehandedly for an upgrade. But it pairs great with the java 6 legacy jobs we got.




Edited By Malcolm on 1458007695
Diogenes of Sinope: "It is not that I am mad, it is only that my head is different from yours."
Arnold Judas Rimmer, BSC, SSC: "Better dead than smeg."
Malcolm
Posts: 32040
Joined: Fri May 21, 2004 1:04 pm
Location: Minneapolis

Post by Malcolm »

The upgrade stack trace:

We can't upgrade Oracle versions because the OS on the boxes is too old. We can't get a newer OS because the hardware is out of warranty and they ain't installing shit on anything of that sort. We can't get new hardware because there's "no money budgeted." There's no money budgeted because FUCK YOU.
Diogenes of Sinope: "It is not that I am mad, it is only that my head is different from yours."
Arnold Judas Rimmer, BSC, SSC: "Better dead than smeg."
TheCatt
Site Admin
Posts: 53729
Joined: Thu May 20, 2004 11:15 pm
Location: Cary, NC

Post by TheCatt »

Malcolm wrote:The upgrade stack trace:

We can't upgrade Oracle versions because the OS on the boxes is too old. We can't get a newer OS because the hardware is out of warranty and they ain't installing shit on anything of that sort. We can't get new hardware because there's "no money budgeted." There's no money budgeted because FUCK YOU.
OMG, I love your job.
It's not me, it's someone else.
Malcolm
Posts: 32040
Joined: Fri May 21, 2004 1:04 pm
Location: Minneapolis

Post by Malcolm »

TheCatt wrote:
Malcolm wrote:The upgrade stack trace:

We can't upgrade Oracle versions because the OS on the boxes is too old. We can't get a newer OS because the hardware is out of warranty and they ain't installing shit on anything of that sort. We can't get new hardware because there's "no money budgeted." There's no money budgeted because FUCK YOU.

OMG, I love your job.

I think my new title is officially "Oracle internal error wrangler."




Edited By Malcolm on 1458080867
Diogenes of Sinope: "It is not that I am mad, it is only that my head is different from yours."
Arnold Judas Rimmer, BSC, SSC: "Better dead than smeg."
Post Reply