Learn SQL & PLSQL

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