June 2015, SQL Simantics, Page 2

Gotchas with the Oracle DATE Datatype

Randy Sims Oracle

DATE is a common datatype. Most transactions in a database have a date associated with them for tracking purposes. When was the order made? When is the order expected to be delivered? When was it actually delivered? And so on. Querying dates, however, takes some attention to detail as there are many details to consider […]

Null Parameters in WHERE Conditions

Randy Sims Oracle

When building a procedure, I want to be able to pass a parameter or pass a NULL if I don’t want to search by a parameter. Some people will tell you to accomplish this that you should write a dynamic SQL statement so you can add or remove parameters as needed. I avoid Dynamic SQL […]

Reading Linux’s TOP Screen

Randy Sims Linux

The top command in Linux provides information about the top CPU processess and provides performance information. The processes displayed are sorted by CPU usage with the information updating by default every 3 seconds. Top also displays information about memory and CPU waiting for I/O. Below is a line-by-line explanation of the top screen.

INTERVAL: Clock Math

Randy Sims SQL Fundamentals I

INTERVAL is a function that provides a way to add, subtract or just convert. SELECT INTERVAL ‘300’ MONTH, INTERVAL ’54-2′ YEAR TO MONTH, INTERVAL ’11:12:10.1234567′ HOUR FROM DUAL; Which is the correct output of the above query? A. +25-00, +54-02, +00 11:12:10.123457 B. +00-300, +54-02,+00 11:12:10.123457 C. +25-00,+00-650,+00 11:12:10.123457 D. +00-300,+00-650,+00 11:12:10.123457

Setting the SESSIONS Parameter

Randy Sims Oracle

The SESSIONS parameter determines the maximum number of sessions that can be created in a system. So, it is basically the number of users that can be logged into the system at one time. BUT, it also needs to include the number of background processes running because they run in sessions, as well. Let’s see […]

Oracle Set Operators: UNION, INTERSECT and MINUS

Randy Sims SQL Fundamentals I

Did you know you can do set-based math with Oracle? With the UNION, INTERSECT and MINUS set operators, you can compare one result set to another and add or subtract results, called a compound query. This can be a handy tool for your toolbox.

Ending a Transaction

Randy Sims SQL Fundamentals I

QUESTION: Which three statements/commands would cause a transaction to end? (Choose three.) A. COMMIT B. SELECT C. CREATE D. ROLLBACK E. SAVEPOINT

Transactions and SAVEPOINT

Randy Sims SQL Fundamentals I

QUESTION: Which two statements describe the consequence of issuing the ROLLBACK TO SAVE POINT a command in the session?

Load a Photo or BLOB for Testing

Randy Sims Oracle

If you work with an application where you have to save BLOBs, such as an ID card app, you’ll run into this situation. You’ve just made a procedure that needs testing, but you need a BLOB to test it. In a few simple steps, you can have that BLOB.