Welcome to the Lounge

Jan Holst Jensen27-Aug-15 1:37

Jan Holst Jensen2

7-Aug-15 1:37

and once you have learned Oracle PL/SQL it will be easy for you to code Postgres plpgsql - almost identical Wink | ;-)

Bruce Patin7-Aug-15 4:15

Bruce Patin

7-Aug-15 4:15

Ask for a pay raise/rise. If your organization/organisation can afford Oracle, they are obviously swimming in cash. My organization could save money by moving in the other direction to PostgreSQL, but fat chance of that.

Sander Rossel7-Aug-15 5:20

7-Aug-15 5:20

As a supplier of Oracle it costs us nothing.
It's our customers who can afford it Wink | ;)

Visit my blog at Sander's bits - Writing the code you need.
Or read my articles at my CodeProject profile.

Simplicity is prerequisite for reliability.
— Edsger W. Dijkstra

Regards,
Sander

Kirk 103898217-Aug-15 5:45

Kirk 10389821

7-Aug-15 5:45

Sander,
It is much harder to go the other way for me.
Utter shock when my coworker hit a breakpoint, and the SQL Server locked up until he continued.
I can HONESTLY SAY I have never seen Oracle do that. Report blocks because user is editing a record. Ouch.

Okay, so after MANY MANY years of doing Oracle. Get a decent tool to work with Oracle.
I use Toad, SQL Navigator (both too expensive), and recently: DevArt dbForge Studio for Oracle.

Straight up, SQL Server is easier to backup/restore since it is built into the tool.
Integration to VS is not as nice.

But with dbForge, you should be able to navigate the DB and write the code you want.
They have a free version! Play with it. The paid version is well worth it (I Pay).
I put the free version out on servers I maintain for quick access.

Oracle suffers because it is NOT a user-driven environment. It was designed for SysAdmins,
and DBAs who had been trained to use it properly.

PL/SQL does not have many limitations. And the best part. I have ported the entire DB from Windows 32 bit to a Linux 64 Bit, without changing a single line of PL/SQL. For many years, PL/SQL was so stable that testing our apps in new versions requires ZERO changes. Just import the DB. Done. (BTW, I feel that importing an OLD DB into a new version of Oracle is much more transparent/easier than learning how to do it in MSSQL Server)

BTW, you lose #TempTables. this sucks, but Oracle supports nested SQL in ways that I never really needed the #TempTables:
Select A., B. from (SELECT something from XXX) A, (SELECT other from ZZZ) B where A.X=B.X;

I have nested them about 7 deep, never had an issue.

Read: AskTom.Oracle.Com
Just about Every question asked is answered in a strict Engineering Approach.
Also, you will learn how to see the internals of what the query does, how, why, and where it is slow.

Google most of your answers with Oracle as the first string: "Oracle create table as"
And you will find a ton of references. CTAS is one of the differences between oracle and T-SQL.
I prefer: Create Table XYZ as Select * from ...;

You must select from a table. There is no "Select 'Hello'" in Oracle.
you do "Select 'Hello' from Dual' (Dual is a simple table with a dummy column and only one row).

You will have more powerful functions and procedures than you get inside of T-SQL (IMO).
Functions can cache results for speed. be tied to table, so updates on a table clear the cache!!!

In general, I feel the difference is:
- Oracle was Designed for Large Databases, with DBAs (sometimes multiple), with an eye on SPEED,
and GUARANTEEING no key violations, and supporting thousands of users with low impact on each other
- SQL Server was gradually upgraded to add features, and is MUCH easier to manage, but behaves more like a desktop DB. I have seen serious issues with scaling, and witnessed duplicate primary keys!

We have single Oracle Boxes that search C1 LIKE 'XXX%' or C2 LIKE 'XXX%'...
Across 60 million rows of data, and average about 8-10ms with 5ms - 20ms response.
WHILE users are deleting 200,000 rows and inserting 200,000 rows at the same time!!!
(20-30% of the DB is completed deleted, re-inserted (ie, updated) every day while being used)
With FULLY CONSISTENT Queries (no dirty reads).

I have NEVER been able to get that kind of performance out of MSSQL. In fact, this started as an MSSQL DB many years ago, and it could not keep up. Had to use dirty reads, which gave "funky" results, and still could not keep up.

Finally, this is REALLY important to understand:
oracle uses UNDO and REDO to give you a CONSISTENT View @ a specific time (the time you ran your query). NOTHING that happens to the DB after you open a query impacts your query. Even dropping the table!

Select * from t; -- Session 1, returns 10 rows
truncate table t; -- Session 2: has no affect on the query, it still has 10 rows to loop over!
drop table t; -- Session 3; has no affect on the query, it still has 10 rows to loop over!

Oracle uses the UNDO/REDO data to make YOUR SESSION (ie, every session) see only committed data that was visible at the time the query was open. This is REALLY AWESOME.

The tools for debugging Oracle exist. Spend some time, play with the tools. Tom Kyte has a couple of books, check them out on Amazon. I think one or more of them may be available as a PDF.

If you ask SPECIFIC questions, I will be willing to share what I know. TOAD is the most popular tool among the professionals, but I find it WAY over priced (I consult, I paid $1,500 for SQL Navigator many years ago, and I loved it, but I felt crippled without it. The choice to go to DevArt was based on having a Free Version that worked the same with fewer features that could be on the Servers I have to support).

I believe Oracle is "harder" to work with, but more accurately reflects how things should be done. AutoIncrement keys are great in a single user DB. Oracles Sequences make a LOT more sense. especially when you have to scale to massive levels.

HTH

Sander Rossel7-Aug-15 12:58

7-Aug-15 12:58

Wow, thanks for the really long answer and all the recommendations.
I use SQL Developer as my main tool now. It makes so much more sense than the internal tool I've been using! Laugh | :laugh:

I'll just be creating some small tables and procedures for now, so no query analyzing, handling massive real-time data and other hard stuff.
I did run into the mandatory FROM TABLE stuff, didn't really need it though so I didn't bother with it. Good to know it's FROM DUAL Smile | :)

Also found out Oracle doesn't know the bit/boolean data type (although PL/SQL does) WTF | :WTF:

I've noticed Oracle has A LOT of features I don't know yet. Seems I'll need to read some Oracle book if I get to do more with it Smile | :)

Visit my blog at Sander's bits - Writing the code you need.
Or read my articles at my CodeProject profile.

Simplicity is prerequisite for reliability.
— Edsger W. Dijkstra

Regards,
Sander

Kirk 103898217-Aug-15 13:21

Kirk 10389821

7-Aug-15 13:21

Sander,
My Pleasure.
I would read Expert oracle by Tom Kyte. You can find it as a PDF probably.
I probably need to read it again... With a bottle of Red Wine... Ahhhh...

The boolean field is really a number(1) which can hold 0-9 (and null). If it is a boolean,
I usually put DEFAULT(0) value on it.
Another gotcha. A Unique constraint allows 1 null key value. That blew my mind.
I would have thought that a unique constraint would prevent. But you need both NOT NULL
and UNIQUE. Keep that in mind. I just fixed someones bug last week over this.

Enjoy!

Sander Rossel7-Aug-15 23:37

7-Aug-15 23:37

Kirk 10389821 wrote:
The boolean field is really a number(1) which can hold 0-9 (and null). If it is a boolean,
I usually put DEFAULT(0) value on it.

Yeah, but just having a bit/bool field would've been so much easier.
My company uses char(1) Y/N by the way Frown | :(

Had to work like that before in SQL Server and all the casting is horrible...

Kirk 10389821 wrote:
Another gotcha. A Unique constraint allows 1 null key value. That blew my mind.

I think that's the same in SQL Server.
What blew my mind in SQL Server is the following:

I have three tables:
Order
- ID PK

OrderLine
- OrderID PK / FK
- LineNumber PK

SomethingElse
- ID PK
- OrderID FK
- LineNumber FK

So notice how I've put a composite Primary Key on OrderLine and a composite Foreign Key from SomethingElse to OrderLine.
I wouldn't usually do this, but I had to work with this database.
Now in SomethingElse either OrderID or LineNumber may be NULL as long as the other value is valid! WTF | :WTF:

That means that even though SomethingElse has Foreign Keys defined it can't guarantee the combined values of OrderID and LineNumber are in fact a valid OrderLine! OMG | :OMG:

Not sure if this also works like that in Oracle.

Visit my blog at Sander's bits - Writing the code you need.
Or read my articles at my CodeProject profile.

Simplicity is prerequisite for reliability.
— Edsger W. Dijkstra

Regards,
Sander

jschell7-Aug-15 11:47

jschell

7-Aug-15 11:47

Sander Rossel wrote:
Say goodbye to good tooling.
Say goodbye to civilized (T-)SQL (I think the PL in PL/SQL means Oracle outsourced the language to some cheap Polish labor... (no offence to Polish people)).
Say hello to steep learning curve.

Could be worse. Way worse. Could be one of the no-sql bandwagon monsters.

Sander Rossel7-Aug-15 12:46

7-Aug-15 12:46

jschell wrote:
no-sql bandwagon monsters.

Actually those are pretty fun! Big Grin | :-D

Visit my blog at Sander's bits - Writing the code you need.
Or read my articles at my CodeProject profile.

Simplicity is prerequisite for reliability.
— Edsger W. Dijkstra

Regards,
Sander

BrainiacV10-Aug-15 4:22

BrainiacV

10-Aug-15 4:22

You might want to start wallpapering your cubicle with Oracle error message translations like I had to.

Errors like "Bad END statement" really means "Label Not Found" (Honestly I wrote better parsers in high school) I'm guessing the parser found the END statement before the label and therefore blamed the END statement.

I had covered half a wall with these translations when I worked at an Oracle shop. I used to tell head hunters NEVER to send me to any place that was using Oracle.

I hoped the A-Team wrote the database engine, but it seemed the F-Troop wrote the user interface.

Psychosis at 10
Film at 11

Those who do not remember the past, are doomed to repeat it.
Those who do not remember the past, cannot build upon it.

Mark-Harrington10-Aug-15 12:01

Mark-Harrington

10-Aug-15 12:01

The only times I've ever had to use Oracle I was very thankful for the tool called "Toad". Here's a link to one of the versions for Toad[^]. Hope that helped.

Mark-Harrington.Where(i => i.Was == "Here")

Sander Rossel10-Aug-15 12:49

WSO FOSW OTD 06. Aug 2015

10-Aug-15 12:49

Looked at Toad and found it to be a little too expensive.
Maybe this freeware version can offer what I need, thanks! Big Grin | :-D

Visit my blog at Sander's bits - Writing the code you need.
Or read my articles at my CodeProject profile.

Simplicity is prerequisite for reliability.
— Edsger W. Dijkstra

Regards,
Sander

HobbyProggy5-Aug-15 22:07

Re: WSO FOSW OTD 06. Aug 2015

5-Aug-15 22:07

I tried to paint something but don't fucs on that 2 much Smile | :)

|=8=/}->=

Hint 1: It's most effective at night!

Rules for the FOSW ![^]

if(this.signature != "")
{
    MessageBox.Show("This is my signature: " + Environment.NewLine + signature);
}
else
{
    MessageBox.Show("404-Signature not found");
}

OriginalGriff5-Aug-15 22:24

Re: WSO FOSW OTD 06. Aug 2015

5-Aug-15 22:24

Telescope?

Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...

super5-Aug-15 22:39

super

5-Aug-15 22:39

If not for KSS rules, my answer would have been little more accurate Smile | :)

cheers,

Super

------------------------------------------
Too much of good is bad,mix some evil in it

HobbyProggy5-Aug-15 22:42

5-Aug-15 22:42

Wow, was it that easy ?

You are up tomorrow

Rules for the FOSW ![^]

if(this.signature != "")
{
    MessageBox.Show("This is my signature: " + Environment.NewLine + signature);
}
else
{
    MessageBox.Show("404-Signature not found");
}

OriginalGriff5-Aug-15 22:48

5-Aug-15 22:48

Oh.

Bugger. It was a guess based on the clue and the pattern - I'm no good at these things!
So now I have to work out how to create one...damn.

Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...

Power Puff Boy5-Aug-15 23:01

Power Puff Boy

5-Aug-15 23:01

...and I thought it was Mega Maid.

If you're having a bad day, stare at your feet and smile.

HobbyProggy5-Aug-15 23:07

5-Aug-15 23:07

OriginalGriff wrote:
I'm no good at these things!

You are good at solving Smile | :)

Rules for the FOSW ![^]

if(this.signature != "")
{
    MessageBox.Show("This is my signature: " + Environment.NewLine + signature);
}
else
{
    MessageBox.Show("404-Signature not found");
}

OriginalGriff5-Aug-15 23:26

5-Aug-15 23:26

Luck, pure and simple I assure you!

I have an idea, but would you mind if I ran it past you and you tell me if it's too simple, too complex (hah!) or just generally rubbish?

Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...

HobbyProggy5-Aug-15 23:29

5-Aug-15 23:29

You could do that Smile | :)

I just don't know how you want to message me Laugh | :laugh:

Rules for the FOSW ![^]

if(this.signature != "")
{
    MessageBox.Show("This is my signature: " + Environment.NewLine + signature);
}
else
{
    MessageBox.Show("404-Signature not found");
}

Mark Parity6-Aug-15 0:27

Mark Parity

6-Aug-15 0:27

Griff will send the message by carrier ~~pigeon~~ sheep.

OriginalGriff6-Aug-15 0:40

6-Aug-15 0:40

We don't send carrier sheep to Europe: either the French set fire to them, or they get eaten by illegal immigrants...

Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...