Need Test SQL command

Question

Post reply

Need Test SQL command

RBarryYoung

SSC Guru

Points: 143329
More actions
November 10, 2008 at 4:32 pm

#202604

I need an Oracle SQL command that my code can execute soley to insure that it has a working connection to Oracle.
This command should be trivial, in the sense that is should always work, as long as ORcale is running and I am connected to it. But not meaningless, in the sense that it should always fail if my connection is lost or the Server goes away. On SQL Server, I would use "SELECT GETDATE()", but that command does not work for me on Oracle, so I need something equivalent.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

SELECT SYSDATE FROM DUAL;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

Thanks, Jeff. Will this work for a normal/unprivileged user also?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Mohammad Mazharuddin Ehsan SSCarpal Tunnel Points: 4552 More actions · Answer 3

Thanks, Jeff. Will this work for a normal/unprivileged user also?

This will work if the user has at least the 'CONNECT' role

-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

Mazharuddin is correct. The user won't necessarily be able to do anything... it just verifies that the user can connect. If you want to verify the user can actually do something, you probably need to select a bit of data or a short count from a table or a view...

SELECT COUNT(*) FROM SOMETABLE_OR_VIEW_OR_SYNONYM WHERE ROWNUM =1;

That's usually pretty quick because it counts just one row, whatever it is.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

...and that still won't guarantee that the user can write to the DB. Oracle has it's own "system" views... if you want to see what privs a user has, you may have to query those.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

Thanks, that should do it. 🙂

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]