Improving Performance

Improving Performance of Dynamic SQL with Bind Variables

When you code INSERT, UPDATE, DELETE, and SELECT statements directly in PL/SQL,
PL/SQL turns the variables into bind variables automatically, to make the statements work efficiently with SQL. When you build up such statements in dynamic SQL, you need to specify the bind variables yourself to get the same performance.

 In the example below, Oracle opens a different cursor for each distinct value of emp_id. This can lead to resource contention and poor performance as each statement is parsed and cached.  

CREATE PROCEDURE del_employee (emp_id NUMBER) AS 
BEGIN 
EXECUTE IMMEDIATE 'DELETE FROM emp WHERE empno = ' || TO_CHAR(emp_id); 
END;

You can improve performance by using a bind variable, which allows Oracle to reuse the same cursor for different values of emp_id  

CREATE PROCEDURE delt_employee (emp_id NUMBER) AS 
BEGIN 
EXECUTE IMMEDIATE 'DELETE FROM emp WHERE empno = :num' USING emp_id; 
END; 

---- 

Some Good SQL

Query to convert comma separated values into rows 

 with test as (select '1,2,3' col1 from dual) select regexp_substr(col1, '[^,]+', 1, rownum) result1 from test connect by level <= length(regexp_replace(col1, '[^,]+')) + 1 ;

WITH test AS (SELECT 'a,b,c,d,e' temp, 1 slno FROM DUAL UNION SELECT 'f,g' temp, 2 slno FROM DUAL UNION SELECT 'h' temp, 3 slno FROM DUAL) SELECT TRIM(REGEXP_SUBSTR(temp, '[^,]+', 1, level)), slno FROM test CONNECT BY level <= REGEXP_COUNT(temp, '[^,]+') AND PRIOR slno = slno AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL ;
select REGEXP_SUBSTR('A,B,C,D,E','[^,]',1,level) from dual connect by level <= (select regexp_count('A,B,C,D,E',',') from dual);

SELECT EXTRACTVALUE(xt.column_value,'list') AS listitem FROM TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE('' || REPLACE('2,1',',','') || '') ,'/lists/list'))) xt ;


SELECT EXTRACT(VALUE(d), '//row/text()').getstringval() AS DATA FROM (SELECT XMLTYPE('' || REPLACE('1,2', ',', '') || '') AS xmlval FROM DUAL) x, TABLE(XMLSEQUENCE(EXTRACT(x.xmlval, '/rows/row'))) d;

Query to split number and character into two different columns 

with src as (select 'CGJ0000617' col_1 from dual
union all select 'CG0,001442' from dual
union all select 'CGJ0001444' from dual
union all select 'CMOV000GH0200' from dual
union all select 'CXAR00000001' from dual
union all select 'CXAR00000002' from dual)
 select regexp_substr(col_1,'^[A-Za-z]+') part_1 ,regexp_substr(col_1,'[0-9]+$') part_2 from src ;

Second highest salary SQL

select max(sal) from employees where sal not in (select max(sal) from employees) ;
select max(sal) from employees where sal < (select max(sal) from employees ) ;
select sal from (select sal,rownum rn from (select sal from employees order by sal desc)) where rn=2 ;
select sal from (select sal,row_number() over(order by sal desc) rn from employees) where rn=2 ;

Pivot , Unpivot Queries

In the FOR IN clause of PIVOT keyword, we cannot use column names , can only use values 

ORA-56901 : non -constant expression is not allowed in pivot|unpivot values 

 In PIVOT keyword, it is required to provide an aggregate function like COUNT, SUM, AVG,MAX,MIN etc 

ORA-56902 : expect aggregate functions inside pivot operation  

In PIVOT keyword, instead of aggregate function window keyword cannot be used 

ORA-30483 : window functions are not allowed here

Query to Pivot Rows to columns even if aggregate function is not be used (use Max/min with row_number)

SELECT * FROM (
     SELECT column_name, row_number() over(partition by table_name order by table_name) rr FROM user_tab_columns WHERE table_name = 'EMPLOYEES' ) pivot (MAX(column_name) FOR  rr IN (1 C_FIRST, 2 AS C_SECOND, 3 C_THIRD, 4 C_FOURTH, 5 C_FIVE, 6 C_SIX, 7 C_SEV, 8 C_EIGHT, 9 C_NINE, 100 C_HUND));


In HR Schema 

SELECT * FROM ( SELECT column_name,TABLE_NAME,column_id FROM user_tab_columns WHERE table_name ='EMPLOYEES' ) unpivot (column_value FOR column_name IN (table_name,column_name,column_id));

 ORA-01790 expression must have same datatype as corresponding expression 

In unpivot function FOR IN clause data-type with same columns are only allowed . In the above query table_name,column_name are varchar2 but column_id is number .So this results in the above error


SELECT * FROM ( SELECT column_name,TABLE_NAME,column_id FROM user_tab_columns WHERE table_name = 'EMPLOYEES' ) unpivot (column_value FOR column_name IN (table_name,column_name));

SELECT * FROM ( SELECT column_name,table_name,column_id FROM user_tab_columns WHERE table_name = 'EMPLOYEES' ) unpivot (column_value FOR column_name IN (column_name));


SELECT * FROM ( SELECT column_name,TABLE_NAME FROM user_tab_columns WHERE table_name = 'EMPLOYEES' ) unpivot (column_value FOR column_name IN (table_name,column_name));

Try this query for getting more detail about pivot function

SELECT * FROM ( SELECT column_name, row_number() over(partition by table_name order by table_name) rr FROM user_tab_columns WHERE table_name = 'EMPLOYEES' ) pivot (MAX(rr) FOR  rr IN (1 C_FIRST, 2 AS C_SECOND, 3 C_THIRD, 4 C_FOURTH, 5 C_FIVE, 6 C_SIX, 7 C_SEV, 8 C_EIGHT, 9 C_NINE, 100 C_HUND));
    
 SELECT * FROM ( SELECT column_name, row_number() over(partition by table_name order by table_name) rr FROM user_tab_columns WHERE table_name = 'EMPLOYEES' ) pivot (MAX(column_name) FOR  column_name IN (1 C_FIRST, 2 AS C_SECOND, 3 C_THIRD, 4 C_FOURTH, 5 C_FIVE, 6 C_SIX, 7 C_SEV, 8 C_EIGHT, 9 C_NINE, 100 C_HUND));

If there are duplicate records in a query then usage of pivot will removes the duplicate records .For example refer below query


SELECT * FROM (     SELECT column_name, row_number() over(partition by table_name order by table_name) rr FROM user_tab_columns WHERE table_name = 'EMPLOYEES'
     union all
      SELECT column_name, row_number() over(partition by table_name order by table_name) rr FROM user_tab_columns WHERE table_name = 'EMPLOYEES'   ) pivot (MAX(column_name) FOR  rr IN (1 C_FIRST, 2 AS C_SECOND, 3 C_THIRD, 4 C_FOURTH, 5 C_FIVE, 6 C_SIX, 7 C_SEV, 8 C_EIGHT, 9 C_NINE, 100 C_HUND));



 For more examples
 

SQL Injection Part 2

Part 1

---------------------------------------------------------------------------------------------------  
Inband: Retrieve the results of the SQL Injection in the same input (e.g. in the browser). Data can be display in the normal output or in an error message.
Most common techniques for Inband  are
* UNION based attacks
* Error Based

Now we must find out how many columns are used in the first SELECT statement. The most common techniques are the usage of  ORDER BY  or adding NULL values to the second query.

 SELECT * FROM table 
UNION 
SELECT null,null FROM table  

SELECT * FROM table ORDER BY 8 

Out-of-Band: A different channel (e.g. HTTP, DNS) is used to transfer the data from the SQL query. If this is working it is the easiest way to retrieve a large amount of data from the database

Blind: Different timings / results are used to retrieve data from the database.
Oracle offers 2 possibilities to run blind injection. • DECODE • CASE

--------

To get how may records are inserted after import

select table_name, num_rows, blocks, avg_row_len from dba_tables where table_name='EMP';

select table_name, monitoring from dba_tables where owner='SCOTT'  ORDER  BY  2;

Insert into scott.EMP   (EMPNO, ENAME, JOB, MGR, HIREDATE,SAL, COMM, DEPTNO) Values
   (7999, 'SMITH', 'CLERK', 7902, TO_DATE('12/17/1980 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),  800, NULL, 20);
   
COMMIT;

exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();


SELECT * FROM SYS.DBA_TAB_MODIFICATIONS where table_owner='SCOTT' ;