Nov 23, 2014

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; 

---- 

Oct 28, 2014

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
 

Oct 7, 2014

SQL Injection Part 2



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

--------

Aug 3, 2014

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' ;

Interanl execution during Disable versioning a table


Step 1:
SELECT 1  FROM DUAL WHERE EXISTS (SELECT 1 FROM  scott.experiment_lt   WHERE VERSION IN (SELECT VERSION FROM wmsys.wm$version_hierarchy_table 
WHERE workspace != 'LIVE'))
                       
Step 2:       
DELETE FROM experiment_lt  WHERE nextver = ',0,' OR nextver IN ( SELECT next_vers FROM wmsys.wm$nextver_table
WHERE VERSION IN (SELECT VERSION FROM wmsys.wm$version_hierarchy_table  WHERE workspace = 'LIVE')) OR delstatus < 0
           
Step 3:
ALTER TABLE  scott.experiment_lt   ADD CONSTRAINT sys_c0036728 PRIMARY KEY (experiment_id) USING INDEX scott.sys_c0036728

Step 4:
SELECT dtc.column_name FROM dba_constraints dc,dba_cons_columns dcc, dba_tab_columns dtc WHERE dc.owner ='SCOTT' AND dc.table_name ='ADDRESS' || '_LT'
AND dc.constraint_type = 'P' AND dcc.owner = 'SCOTT'  AND dcc.table_name = 'EXPERIMENT'|| '_LT' AND dc.constraint_name = dcc.constraint_name AND
dcc.column_name NOT IN ('VERSION','LTLOCK','DELSTATUS','NEXTVER') AND dcc.column_name NOT LIKE 'WM$%' AND dcc.column_name NOT LIKE 'WM@_%' ESCAPE '@'
AND dtc.owner = 'SCOTT' AND dtc.table_name = 'EXPERIMENT'||'_LT' AND dcc.column_name = dtc.column_name  ORDER BY dtc.column_id

Step 5:
INSERT INTO wmsys.wm$vt_errors_table
     VALUES (:b4, :b3, :b2, :b1, 'DV STEP BEING EXECUTED', NULL)

Step 6:
ALTER TABLE EXPERIMENT_lt DROP COLUMN ltlock;
ALTER TABLE EXPERIMENT_lt DROP COLUMN delstatus;
ALTER TABLE EXPERIMENT_lt DROP COLUMN VERSION;
ALTER TABLE EXPERIMENT_lt DROP COLUMN NEXTVER;

Step 7:

DELETE FROM TABLE (SELECT undo_code FROM wmsys.wm$versioned_tables WHERE owner ='SCOTT' AND table_name ='EXPERIMENT')
WHERE index_type =:b4 AND index_field =:b3

Jul 28, 2014

Oracle Analytic functions

Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. The group of rows is called a window and is defined by the analytic_clause. Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. All joins and all WHERE, GROUP BY, and HAVING clauses are completed before the analytic functions are processed. Therefore, analytic functions can appear only in the select list or ORDER BY clause Analytic functions take 0 to 3 arguments Use OVER analytic_clause to indicate that the function operates on a query result set.