Learn SQL & PLSQL

Oracle Database uses two engines to run PL/SQL blocks and subprograms. The PL/SQL engine runs procedural statements, while the SQL engine runs SQL statements. During execution, every SQL statement causes a context switch between the two engines, resulting in performance overhead. Performance can be improved substantially by minimizing the number of context switches required to run a particular block or subprogram. When a SQL statement runs inside a loop that uses collection elements as bind variables, the large number of context switches required by the block can cause poor performance. Collections include the following: Varrays Nested tables Index-by tables Host arrays Binding is the assignment of values to PL/SQL variables in SQL statements. Bulk binding is binding an entire collection at once. Bulk binds pass the entire collection back and forth between the two engines in a single operation. Typically, using bulk binds improves performance for SQL statements that affect four...

If you would like to create a user, we generally use the following syntax. CREATE USER SCOTT1 IDENTIFIED BY TIGER1; Once the user is created the privileges list will be empty and hence we wont be able to log in to the schema. In order to be able to lo-gin we need to explicity grant certain privileges as shown Grant Create Session, Resource to SCOTT1; There is a combined syntax available. You can now create a New User with the GRANT Statement as shown below. GRANT Create Session, Resource to SCOTT1 identified by TIGER1;

As a PL/SQL developer, I find myself leaning toward using associative arrays as a first instinct.  Why is this? They involve the least amount of coding. You don't have to initialize or extend them. They have historically been the most efficient collection type (although this distinction will probably fade over time). However, if you want to store your collection within a database table, you cannot use an associative array. The question then becomes: nested table or VARRAY? I have listed down a few guidelines which might help you choose. If you do not intend to store data into database use associative arrays If your PL/SQL application requires negative subscripts, you also have to use associative arrays. If you want to enforce a limit to the number of rows stored in a collection, use VARRAYs. If you intend to store large amounts of persistent data in a column collection, your only option is a nested table. Oracle will then use a separate table behind the scenes to hol...

Oracle 10g introduced the ability for user-defined quote characters. Well, prior to Oracle 10g, if you wanted to include quotes in text, you had to ‘escape’ the quote with another quote such as: Select 'My Name is Alwyn D''Souza having '' after D' from dual; This technique becomes inconvenient when you write a very long dynamic sql string.You miss one 'escape' character and the code runs into error during run-time and we waste lot of time finding the missing 'escape' character. Now with Oracle 10g, you can re-write this as: Select q ' { My Name is Alwyn D'Souza having ' after D } ' from dual; Select Q ' { My Name is Alwyn D'Souza having ' after D } ' from dual; Note that the quoted strings starts with the letter q/Q, followed by a single quote and the new quote character.  It ends with the new quote character and a single quote.  I’ve used a flower bracket ({) as my quote character, but you can use other ...

 In most of the article on PLSQL performance it is always recommended to use PLS_INTEGER as a  numeric data-type whenever  there is any arithmetic calculation. Have you even wondered why does PLS_INTEGER perform much better when compared to other PLSQL numeric datatype ? It is because when compared to other data-types Its value requires less storage space than other numeric data-types (represented internally as 22-byte Oracle numbers) . PLS_INTEGER operations use machine arithmetic Here is an example that demonstrates the difference. --Code taken from www.oracle-base.com SET SERVEROUTPUT ON DECLARE   l_number1          NUMBER := 1;   l_number2          NUMBER := 1;   l_integer1         INTEGER := 1;   l_integer2         INTEGER := 1;   l_pls_integer1   ...