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

In Oracle Database 11g PL/SQL has a new construct called CONTINUE , which is used in a loop. The statement moves the logic to the end of the loop and then to the beginning of the loop.The CONTINUE statement jumps out of the current loop iteration and starts the next one. It can be used on its own, or as part of a CONTINUE WHEN statement. This type of processing has always been possible using IF statements either on their own or with exceptions or GOTO statements, but the CONTINUE statement is neater and brings PL/SQL in line with other languages.  Sample Code : SQL> SET SERVEROUTPUT ON SQL> DECLARE   2    l_itr    NUMBER := 0;   3  BEGIN   4    FOR i IN 1 .. 100 LOOP   5      CONTINUE WHEN MOD(i,5) = 0;   6      l_itr := l_itr + 1;   7    END LOOP;   8   9    DBMS_OUTPUT.put_line('Loop Inter...

There are different packages using which you can check whether a file exists on the database / application server. The different ways are 1. Using UTL_HTTP from Web Server 2. Using UTL_FILE from Database Server 3. Using DBMS_LOB from Database Server 1. You can also use UTL_HTTP package to check whether a file exists on a web server from the database. SET SERVEROUTPUT ON DECLARE   url       VARCHAR2(256) := 'http://www.oracle.com/index1.html';   username  VARCHAR2(256);   password  VARCHAR2(256);   req       UTL_HTTP.REQ;   resp      UTL_HTTP.RESP; BEGIN   req := UTL_HTTP.BEGIN_REQUEST(url);   IF (username IS NOT NULL) THEN     UTL_HTTP.SET_AUTHENTICATION(req, username, password);   END IF;   resp := UTL_HTTP.GET_RESPONSE(req);   DBMS_OUTPUT.PUT_LINE('response -->' || resp.status_code); END; / If the f...

The PLS-00436 restriction has been removed from 11g, and now we can reference each element of the collection object when used with FORALL statement.Each element can now be referenced using SET and WHERE clauses of a DML statement in a FORALL construct. Sample program : conn scott/tiger create table demo_plsql_436 as select * from emp where 1=2; /* The procedure "display_demo_plsql_436" will display the data of  table demo_plsql_436 */ create or replace procedure display_demo_plsql_436 as    cursor c1 is select ename, sal from demo_plsql_436;    emp_name demo_plsql_436.ename%type;    emp_sal  demo_plsql_436.sal%type; begin    open c1;    loop       fetch c1 into emp_name, emp_sal;    exit when c1%notfound;    dbms_output.put_line(rpad (emp_name,10,' ')|| '     ' || rpad(emp_sal,7,' '));    end loop;    close c1; end display_d...

There might be cases when we would like to aggregate the data into a single row for the set of records for a particular value, for example say i have following data in my table FILTERID               CRITERIA            ---------------------- -------------------- 28900                  CRIT1               28901                  CRIT1               28902                  CRIT1               28903...

The DBMS_PARALLEL_EXECUTE package enables you to incrementally update the data in a large table in parallel, in two high-level steps: 1. Group sets of rows in the table into smaller chunks. 2. Apply the desired UPDATE statement to the chunks in parallel, committing each time you have finished processing a chunk. This technique is recommended whenever you are updating a lot of data. Its advantages are: o You lock only one set of rows at a time, for a relatively short time, instead of locking the entire table. o You do not lose work that has been done if something fails before the entire operation finishes. o You reduce rollback space consumption. o You improve performance Different Ways to Spilt Workload : 1. CREATE_CHUNKS_BY_NUMBER_COL: Chunks the table associated with the given task by the specified column. 2. CREATE_CHUNKS_BY_ROWID : Chunks the table associated with the given task by ROWID 3. CREATE_CHUNKS_BY_SQL : Chunks the table associated with the given task by me...