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   ...

Without proper safeguards, applications are vulnerable to various forms of security attack. One particularly pervasive method of attack is called SQL injection. Best example of Sql Injection .  I think he was trying to inject SQL through the traffic camera and drop the table from the database which host the application. So BEWARE , SQL INJECTION CAN HAPPEN FROM UNKNOWN WAYS. Hackers can try numerous unknown ways to bring down your database. One of the best exhaustive article that i have read recently which was published in Oracle Magazine provides the detailed explanation on SQL Injection and different ways to handle it and make the application secure.   How to write SQL injection proof PL/SQL Types of SQL Injection Attacks The impact of SQL injection attacks may vary from gathering of sensitive data to manipulating database information, and from executing system-level commands to denial of service of the application. The impact als...

In Oracle 11g, PL/SQL native compilation requires no C compiler, no DBA intervention and is fully supported in a RAC environment. By setting the PLSQL_CODE_TYPE to a value of NATIVE , rather than the default value of INTERPRETED , code is compiled directly to machine code . When the code is called, it is loaded into shared memory, making it accessible for all sessions in that instance. The %_PLSQL_OBJECT_SETTINGS views include the current PLSQL_CODE_TYPE setting for each PL/SQL object.  SQL> CREATE OR REPLACE PROCEDURE test_speed AS   2    v_number  NUMBER;   3  BEGIN   4    FOR i IN 1 .. 10000000 LOOP   5      v_number := i / 1000;   6    END LOOP;   7  END;   8  / Procedure created. SQL> select name,type,plsql_code_type from user_plsql_object_settings where name ='TEST_SPEED'; NAME      TYPE  ...