Oracle

The TRUNCATE command may be useful in certain instances for rapid row removal. Unlike the DELETE command, triggers are bypassed with TRUNCATE. Also, ROLLBACKs are not allowed as rows are immediately committed. Foreign keys referencing the table must be disabled prior to the operation. See below for basic syntax:

TRUNCATE TABLE EMPLOYEES;

Oracle provides the SYS_CONTECT function to return numerous attributes about the current session. The syntax is:

SYS_CONTEXT('USERENV',parameter,[length])

Length is optional and defaults to 256. There are a large number of available parameters, ones we've found useful include:

• SESSION_USER - Oracle user's name
• DB_NAME - Name of the database
• HOST - Name of the host machine of client connection
• IP_ADDRESS - IP ofthe host machine of client connection
• OS_USER - The user name of the operating system user from client machine
• See Oracle documentation for full list

A source of confusion for SQL developers is the difference between WHERE and HAVING. First, HAVING is used exclusively with the GROUP BY feature. HAVING is essentially a WHERE applied after the grouping is complete. The example below shows how it can be used.

SELECT employee_id, SUM(payment_amount) FROM employee_payroll GROUP BY employee_id HAVING SUM(payment_amount) > 1000;

The query would return only employees who have been paid more than $1000. It could include any number of rows that were totally via the SUM function as long as the total for all rows with the same employee_id exceeded 1000.

Oracle offers two very useful functions for replacing a NULL with a value easier to work with. This is particularly useful when evaluating columns using CASE or IF statements that would otherwise require multiple conditions.

To replace a NULL value with another use NVL. In the example below, if a spouse exists, the name will be reported and if NULL, it will instead report 'Not Married'.

SELECT NVL(employee_spouse,'Not Married') FROM employees;

To assign one value for a NULL value and another for a non-NULL value, use NVL2. The same query will be reworked to return 'Yes' if married and 'No' if not. The non-NULL option is listed first.

SELECT NVL2(employee_spouse,'Yes','No') FROM employees;

The TRUNC function is used to truncate the undesired portion of a date, whether it be the time component or reducing to the beginning of a month, quarter, year, etc.

To remove the time component (which actually sets it to midnight):

SELECT TRUNC(SYSDATE,'DD') FROM DUAL;

Other options that can replace 'DD' include:

• YEAR - Returns first day of year
• Q - Returns first day of quarter
• MM - Returns first day of month
• DAY - Returns first day of week

Oracle does not support the Identity Column or AutoNumber features as such. It requires the use of a sequence object and a trigger.

To create a sample sequence:

CREATE SEQUENCE <sequence_name> CYCLE ORDER NOCACHE MAXVALUE 999999999 MINVALUE 1 INCREMENT BY 1 START WITH 1;

• CYCLE - If cycle is specified, then at the end of the run (MAXVALUE) it restarts at the beginning (MINVALUE) value.
• ORDER - Forces the sequence to run in order. If not used the numbers can come from any point in the sequence.
• CACHE - Caches the specified number of values in memory. Allows some performance gain, but can result in omitted values (which may not be desirable depending on the implementation).
• INCREMENT BY - The number that the sequence skips after generating a value. If this number is negative, then the sequence is run in descending order.
• The rest are pretty much self explanatory.

To create the trigger necessary to implement the sequence for its table:

CREATE OR REPLACE TRIGGER <trigger_name> BEFORE INSERT
ON <table_name> FOR EACH ROW BEGIN

SELECT <sequence_name>.NEXTVAL INTO :NEW.<id_columnname> FROM DUAL;

END;
/

In certain instances, I like to use the NVL to give the flexibility to assign IDs manually if specified and via the sequence if left null:

CREATE OR REPLACE TRIGGER <trigger_name> BEFORE INSERT
ON <table_name> FOR EACH ROW BEGIN

SELECT NVL(:NEW.<id_columnname>,<sequence_name>.NEXTVAL) INTO :NEW.<id_columnname> FROM DUAL;

END;
/

Be sure to grant SELECT privileges on the sequence if other users/schemas need to use it.

Oracle's locking strategy does a marvelous job of preventing concurrent modifications to the database. However, it is not very kind about informing you it is doing so. Often, the only symptom is a hung statement/process/click.

To find the culprit behind a Blocking Session (if you know the schema and/or table name use them to reduce the result set):

SELECT * FROM v$lock
WHERE id1 = (SELECT object_id FROM all_objects WHERE owner =<schema_name> AND object_name =<table_name>);

Next, you must find additional information required to kill the session:

SELECT serial# FROM v$session WHERE sid =<above query result>;

Finally, you have enough information to remove the block:

ALTER SYSTEM KILL SESSION '<sid>,<serial#>';

To combine multiple queries into a single result set, Oracle has the UNION and UNION all commands. Each query to be combined must have the same number of data elements and the matched elements from the queries must be of the same type. The UNION command is used if duplicates are to be combined and the UNION ALL retrieves all rows regardless of duplicates.

SELECT last_name, first_name, employee_id
FROM employees
UNION
SELECT last_name, first_name, 0
FROM customers;

 

TNSNAMES may seem like something to set once and forget, but system maintenance for another application running on our users' workstations killed that notion some time back.

In a Windows install, the default location for TNSNAMES.ORA is in %ORACLE_HOME%\network\admin. Other potential methods of assigning a TNSNAMES location are:

• TNS_ADMIN Environment Variable
• HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\TNS_ADMIN Registry Key

In the case metioned above the system admin set the TNS_ADMIN environment variable and it killed our application which had its TNSNAMES in the default location. The order in which Oracle evaluates the possible locations is:

1. TNS_ADMIN Environment Variable
2. TNS_ADMIN Registry Key
3. %ORACLE_HOME%\network\admin

When it is desirable to get an ordinal row association in a query, the rownum pseudocolumn can be useful. However, there are some traps to be avoided. The following statement is an example:

SELECT last_name, rownum FROM employees ORDER BY last_name;

While it might seem that it should return a list of last names, in alpha order and sequentially numbered, that is not guaranteed. Oracle applies the rownum when retrieving the rows, which occurs prior to ordering them. The following method fixes that issue.

SELECT last_name, rownum FROM (SELECT last_name FROM employees ORDER BY last_name);

The subquery, sometimes known as top-N reporting forces the evaluation of rownum after the ordering of rows.

Another useful alternative is the ROW_NUMBER analytic function.

Invalid objects may be recompiled for a database (all schemas) using the following:

SQL>@%oracle_home%\RDBMS\ADMIN\UTLRP.SQL

To view remaining invalid objects:

SQL>SELECT OWNER, OBJECT_TYPE, OBJECT_NAME FROM DBA_OBJECTS WHERE STATUS LIKE 'INV%';

If you want to restrict to certain schemas:

SQL>SELECT OWNER, OBJECT_TYPE, OBJECT_NAME FROM DBA_OBJECTS WHERE STATUS LIKE 'INV%' AND OWNER IN ('SCHEMA1','SCHEMA2');

This is very useful tool to see detailed data on the communication between Oracle and your application/users.

In the sqlnet.ora file place the following settings:

For server side tracing:

trace_level_server=16
trace_directory_server=folder
trace_file_server=filename

For client side tracing:

trace_level_client=16
trace_directory_client=folder
trace_file_client=filename
trace_file_unique_client=on

Use TNSPing to determine if the client is able to communicate with the server.  From the command prompt type:

tnsping sid [n]  - where sid is the Oracle System ID and n is the number of times to ping, see below for example

>tnsping oseb 5

LENGTH(TRIM(TRANSLATE(TRIM(string1),' 0123456789','A          ')))

string1 is the string value that you are testing.

This function will return a null value if string1 is numeric. It will return a value "greater than 0" if string1 contains any non-numeric characters (including spaces). You may also want to add ".", "-" and/or "+" to the list of numeric characters depending on your use.

Date columns in Oracle have a time component. So, if you are comparing two dates from two sources and the times are different, you will never get a match. To remedy this we had been converting the dates into string using TO_CHAR in the 'YYYYMMDD' format. A better way to do this is the TRUNC function.

A common way we might use it is:

SELECT  *
FROM     elections
WHERE  Trunc(:din_election,'DD') = Trunc(election_date,'DD') 

It is important to note that applying the function in this manner may bypass the use of an index on the date column and may negatively affect performance.