Dealing with apostrophes/single quotes in strings

Now it is first important to remember that in Oracle, you enclose strings in single quotes. The first quote denotes the beginning of the string and the second quote denotes the termination of the string.

If you need to deal with apostrophes/single quotes in strings, your solution depends on where the quote is located in the string.

We’ll take a look at 4 scenarios where you might want to place an apostrophe or single quote in a string.

Apostrophe/single quote at start of string

When the apostrophe/single quote is at the start of the string, you need to enter 3 single quotes for Oracle to display a quote symbol. For example:

SELECT ”’Hi There’
FROM dual;

would return

‘Hi There

Apostrophe/single quote in the middle of a string

When the apostrophe/single quote is in the middle of the string, you need to enter 2 single quotes for Oracle to display a quote symbol. For example:

SELECT ‘He’’s always the first to arrive’
FROM dual;

would return

He’s always the first to arrive

Apostrophe/single quote at the end of a string

When the apostrophe/single quote is at the end of a string, you need to enter 3 single quotes for Oracle to display a quote symbol. For example:

SELECT ‘Smiths”’
FROM dual;

would return

Smiths’

Apostrophe/single quote in a concatenated string

If you were to concatenate an apostrophe/single quote in a string, you need to enter 4 single quotes for Oracle to display a quote symbol. For example:

SELECT ‘There’ || ”” || ’s Henry’
FROM dual;

would return

There’s Henry

http://www.techonthenet.com/oracle/questions/quotes.php

QREF: NULL Comparisons

This is a quick reference note describing the outcome of
various Null Comparison operations:

  NB: WHERE clauses only return information if result is TRUE

        Expression      Result          Expression      Result
        ----------      ------          ----------      ------
        null = null       null          null != null    null
        null = 1          null          null != 1       null
        1    = 1          TRUE          1    != 1       FALSE
        1    = 0          FALSE         1    != 0       TRUE

        NOT(null)         null
        null OR  null     null          null OR  TRUE   TRUE
        null OR  FALSE    null
        null AND null     null
        null AND TRUE     null          null AND FALSE  FALSE           

        null IS NULL      TRUE
        null IS NOT NULL  FALSE

        1 in (null,0)     null   }
        1 not in (null,0) null   }  a IN ( b, c ) is the same as
        1 in (null,1)     TRUE   }  a=b OR a=c
        1 not in (null,1) FALSE  }

  In a where clause a "null" result from an expression will not return data
  as it is not a TRUE result.
        Eg: select * from dual where null=null;
            -> select * from dual where 'null'
            -> No rows returned

SELECT

START WITH Clause

Specify a condition that identifies the row(s) to be used as the root(s) of a hierarchical query. Oracle Database uses as root(s) all rows that satisfy this condition. If you omit this clause, then the database uses all rows in the table as root rows. The START WITH condition can contain a subquery, but it cannot contain a scalar subquery expression.

CONNECT BY Clause

Specify a condition that identifies the relationship between parent rows and child rows of the hierarchy. The connect_by_condition can be any condition as described in Chapter 7, “Conditions”. However, it must use the PRIOR operator to refer to the parent row.

Restriction on the CONNECT BY Clause The connect_by_condition cannot contain a regular subquery or a scalar subquery expression.

NULL in Oracle

http://www.51cto.com/art/200511/10681.htm

http://www.adp-gmbh.ch/ora/misc/null.html

http://www.adp-gmbh.ch/ora/sqlplus/null.html

Avoid Updating the Entire Table when Adding a Column Default

If you add a column with a default, Oracle dutifully goes back and updates every single existing row. (Which might include a nice trip to row migration heaven!)Can we avoid this? Yes, just don’t do it “all at once”…

SQL> create table T
  2  as select * from all_objects
  3  ;

Table created.

SQL> set timing on
SQL> alter table T add NEW_COL1 number default 10;

Table altered.

Elapsed: 00:00:03.92

Adding a new column with a default value takes considerable time. Oracle not only creates the column but also updates it with the default value; this will generate redo/undo information.

SQL> alter table T add NEW_COL2 number;

Table altered.

Elapsed: 00:00:00.01
SQL> alter table T modify NEW_COL2 default 10;

Table altered.

Elapsed: 00:00:00.01

SQL> select count(new_col1), count(new_col2) from T;

COUNT(NEW_COL1) COUNT(NEW_COL2)
--------------- ---------------
          50330               0

1 row selected.

Elapsed: 00:00:00.01

From http://www.quest-pipelines.com/pipelines/dba/tips.htm#october

Outer join query in Oracle using the (+) sign

How to create outer join query in Oracle using the (+) sign rather than LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN clause, since Oracle 8 didn’t know them. Assume we have two tables (T1 and T2) with the structure is shown below:

D:\oracle\product\10.2.0\db_1\BIN>set ORACLE_SID=WEB

D:\oracle\product\10.2.0\db_1\BIN>sqlplus test/test

SQL> create table T1 ( A int, R int );
SQL> insert into T1 (A, R) values (1, 1);
SQL> insert into T1 (A, R) values (3, 3);
SQL> select * from T1;
         A          R
---------- ----------
         1          1
         3          3 

SQL> create table T2 ( R int, B int );
SQL> insert into T2 (R, B) values (2, 2);
SQL> insert into T2 (R, B) values (3, 4);
SQL> insert into T2 (R, B) values (4, 4);
SQL> select * from T2;
       R          B
---------- ----------
         2          2
         3          4
         4          4

I. LEFT OUTER JOIN Here is the script to create left outer join query.

SQL> select * from T1, T2
  2  where T1.R = T2.R (+);

         A          R          R          B
---------- ---------- ---------- ----------
         3          3          3          4
         1          1
The same result will be produced when we use the following script (in Oracle 9i)

select * from T1
left outer join T2
on T1.R = T2.R;

II. RIGHT OUTER JOIN Here is the script to create right outer join query.

select * from T1, T2
where
T1.R (+) = T2.R;
         A          R          R          B
---------- ---------- ---------- ----------
         3          3          3          4
                               4          4
                               2          2

The same result will be produced when we use the following script (in Oracle 9i)

select * from T1
right outer join T2
on T1.R = T2.R;

III. FULL OUTER JOIN To create full outer join using (+) sign, we need combine two queries above using UNION, like this:

SQL> select * from T1, T2
  2  where T1.R = T2.R (+)
  3  UNION
  4  select * from T1, T2
  5* where T1.R (+) = T2.R
         A          R          R          B
---------- ---------- ---------- ----------
         1          1
         3          3          3          4
                               2          2
                               4          4

In Oracle 9i, we can do it with the following script.

select * from T1
full outer join T2
on T1.R = T2.R;

From http://www.dbapool.com/articles/010406.html

Cheatsheet

Oracle PL/SQL Cheatsheet

http://www.yagc.ndo.co.uk/cheatsheets/plsql_cheatsheet.html

Oracle Relational SQL Cheatsheet

http://www.yagc.ndo.co.uk/cheatsheets/sql_cheatsheet.html

Best Practices to Improve SQL Performance

Being developers we often see clients complaining about database performance. Performance problems arise when we ignore good practices. In this article I briefly list some of the best practices one should follow while working with SQL.

1. The database design should be simple and clear. It should be user friendly. It should have flexible structure so that depending on the future requirements the database can easily be expanded.
2. Store relevant and necessary information in the database.
3. Use normalized tables in the database. It is better to use small multiple tables instead of using one large table.
4. Use denormalization as the last option for your database.
5. Use database look up tables. It will help you maintain data integrity of your database.
6. Use numeric or small width primary key for your database tables. Processing small data takes less time.
7. Do not store images in the database. Store URLs of images instead.
8. Do not display SQL errors to users. Most of the attackers get benefit of error messages.
9. Make an ERROR table in the database. Storing errors in the ERROR table will help you keep track of bugs.
10. Assign proper data types to the fields in the database. It is better to use TINYINT for age rather than VARCHAR (45).
11. Use database field names in SELECT, INSERT, UPDATE commands. Avoid using SELECT * or INSERT INTO TABLE VALUES (value1, value2…).
12. Use LIKE clause properly. Improper usage may cause performance problems in your database.
13. Use database server’s timestamp instead of web server’s timestamp.
14. Use alias in the SQL statements which require multiple tables.
15. Write SQL commands in capital letters. It helps in readability.
16. Use nested queries as minimum as possible. Use JOIN statement instead.
17. Avoid query running in the loop.
18. Use stored procedures. They are fast and help in maintainability and security of the database.
19. Avoid CURSOR, GOTO and IF statements. Prefer SWITCH CASES.
20. Use comments.
21. Do proper indexing. It will improve the speed of operations in the database tables. Improper or too much indexing affects performance of the database.
22. Use inet_aton() function to store IPs as integers in the database. The inet_aton() function converts the string, in the Internet standard dot notation, to a network address, and stores the address in the structure provided.
23. Retrieve the IPs with inet_ntoa() function. The inet_ntoa() function converts the specified Internet host address to a string in the Internet standard dot notation.
24. Always encrypt the sensitive data.
25. Do not rely on server configurations. Always write the most portable code possible.
26. Do proper documentation of your application.
27. Always do testing of your database application. Make cases based on requirements and maintain a checklist of the tests.

From http://www.quest-pipelines.com/pipelines/plsql/tips.htm#NOVEMBER

SQL OUTER JOIN

Starting with Oracle9i, the confusing outer join syntax using the ‘(+)’ notation has been superseded by ISO 99 outer join syntax.  As we know, there are three types of outer joins, left, right, and full outer join.  The purpose of an outer join is to include non-matching rows, and the outer join returns these missing columns as NULL values.

The syntax for performing an outer join in SQL is database-dependent. For example, in Oracle, we will place an “(+)” in the WHERE clause on the other side of the table for which we want to include all the rows.

author.author_key = book_author.author_key(+)

Notice the (+) in the WHERE clause.  This indicates a left outer join.  If we were using a right outer join, the WHERE clause would be:

author.author_key(+) = book_author.author_key

Table with (+) sign returns those missing columns as NULL values.

An outer join uses a (+) on the side of the operator (which in this case happens to be the equality operator) where we want to have nulls returned if no value matches.