Null Within expression

Null within an expression evaluates the complete expression to NULL. ( even on conditional statements )


Example:

declare

   flag1 boolean;

   flag2 boolean;

 begin

  

   flag1 := (null > trunc(sysdate));  --Flag1 evaluates to Null , not to False

  

   flag2 := true;

  

   if  flag1 and flag2 then

     raise_application_error(-20001,'true');

   elsif not flag1 then

     raise_application_error(-20001,'flag1: false , flag2 may be true or false');

   elsif not flag2 then

     raise_application_error(-20001,'flag2: false & flag1 : True');

   else

     raise_application_error(-20001,'case trapped');

   end if;

end;

Hash DataSet

Hashing :

1.  If the same DataSet is Passed to HashGenerator, we get the same Hash value out of it.

2. No two different Input Data Sets result in same Hash Value.


Based on above points, we can check if the process needs to be re-done.

Algorithm :

If HashOfTheProcessChanged then
    redo The Process;
else
    skip The Process;
end if;

           

Code :

 function generatehash (sqlstatement clob)  return varchar2 is

      xmlcontextHandle      DBMS_XMLGEN.CTXHANDLE;

      xml xmltype;

      xmlclob clob;

 begin

      xmlcontextHandle := dbms_xmlgen.newcontext (sqlstatement);

      begin

        xmlclob:=DBMS_XMLGEN.GETXML ( xmlcontextHandle);
      exception
        when others then
           -- close the context to release the resources & close the open cursors
          dbms_xmlgen.closecontext (xmlcontextHandle);
     end;

      dbms_xmlgen.closecontext (xmlcontextHandle);

      return (dbms_crypto.hash (xmlclob, 3));

 exception

    when others then   

         return (null);

 end generatehash;

Note : sqlstatement is the sql string (Can be Dynamic Sql)

          Be sure to use Order by Clause on the SQL Statement.

If Order by Clause not specified the order of the select fetch may change and it makes the hash change

Clob 1 :  (DataSet)

              1, kiran

              2, vishali

Clob 2 :  (DataSet)

              2, vishali

              1, kiran 

Hash on Clob 1 <> Hash on Clob 2

So the Sql Statement must specify the order by Clause to make sure we get the correct hash.

Example :

Select   generatehash  ( ' Select sysdate from dual' ) from dual;

Select   generatehash  ( ' Select ''1A2B3C'' from dual' ) from dual;

Declare 

  sqlStatement varchar2(100):= ' Select sysdate, ''1A2B3C'' from dual' ;

begin

  dbms_output.put_line(  generatehash (sqlStatement) );

end;

Oracle Table Clustering

Packages

Packages :

1. Specification (required)
2. Body (optional)

(i)   Information hiding : Implementation is hidden in the package body.
(ii)  Public and private : Specification (public) & Body (private).
(iii) Initialization:  package is initialized only once per session.
(iv)  Session persistence : If we establish a session and execute a program that assigns a value to a package-level variable,
                            that variable is set to persist for the length of the session, and it retains its value even if the
                            program that performed the assignment has ended.

Example:

create or replace package test_package
as
my_variable1 number;
my_variable2 varchar2(200);
end;
/

Session 1:
Begin
  test_package.my_variable1:=1;
  test_package.my_variable2:='kiran';
End;
/

Begin
  dbms_output.put_line(test_package.my_variable1);
  dbms_output.put_line(test_package.my_variable2);
End;
/

Session 2:
Begin
  dbms_output.put_line(test_package.my_variable1);
  dbms_output.put_line(test_package.my_variable2);
End;
/

Note:
The first time our session uses a package (whether by calling a program defined in the package, reading or writing a variable, or using a locally declared variable TYPE), Oracle initializes that package.
A package may be reinitialized in a session if that package was recompiled since last use
or
if the package state for our entire session was reset.

Oracle Pipelined Table Functions

PIPELINED functions are useful if there is a need for a data source other than a table in a select statement.PIPELINED functions will operate like a table.

Pipelined functions are simply "code you can pretend is a database table"

Pipelined functions give you the ability to : "select * from PLSQL_FUNCTION "

Steps to perform :

1.The producer function must use the PIPELINED keyword in its declaration.

2.The producer function must use an OUT parameter that is a record, corresponding to a row in the result set. 

3.Once each output record is completed, it is sent to the consumer function through the use of the PIPE ROW keyword. 

4.The producer function must end with a RETURN statement that does not specify any return value. 

5.The consumer function or SQL statement then must use the TABLE keyword to treat the resulting rows from the PIPELINE function like a regular table.

Example:

CREATE OR REPLACE TYPE DateListTab AS TABLE OF date;

Create or replace FUNCTION TestPipelinedFunctions RETURN  DateListTab  PIPELINED IS

   fromdate date:= sysdate;

   Todate date:=sysdate+365;

begin

   for datecounter in to_number(to_char(fromdate, 'J')) .. to_number(to_char(Todate, 'J')) 

   loop

          PIPE ROW ( to_date(datecounter, 'J') );

   end loop;          

    RETURN;

end TestPipelinedFunctions;

SELECT * FROM TABLE(TestPipelinedFunctions);

Using Dates in For Loops

 To use dates simply convert the date to a number field using the ‘J’ format mask for dates, ‘J’ gives the number of days since 31 December 4713bc

Example code: 

declare

  fromdate date:= sysdate;

  Todate date:=sysdate+365;

begin

   for datecounter in to_number(to_char(fromdate, 'J')) .. to_number(to_char(Todate, 'J')) 

   loop

        dbms_output.put_line(to_date(datecounter, 'J'));    -- Convert back to date.

   end loop;          

end;

Pinning Tables into SGA ( Performance )

Identify Tables & Indexes need to be pinned onto SGA:

***************************************************************************

  SELECT    'alter table '

         || p.owner

         || '.'

         || p.name

         || ' storage (buffer_pool keep);'

    FROM dba_tables t,

         dba_segments s,

         dba_hist_sqlstat a,

         (SELECT DISTINCT pl.sql_id, pl.object_owner owner, pl.object_name name

            FROM dba_hist_sql_plan pl

           WHERE pl.operation = 'TABLE ACCESS' AND pl.options = 'FULL') p

   WHERE     a.sql_id = p.sql_id

         AND t.owner = s.owner

         AND t.table_name = s.segment_name

         AND t.table_name = p.name

         AND t.owner = p.owner

         AND t.owner NOT IN ('SYS', 'SYSTEM')

         AND t.buffer_pool <> 'KEEP'

  HAVING s.blocks < 50

GROUP BY p.owner,

         p.name,

         t.num_rows,

         s.blocks

UNION

SELECT    'alter index '

       || owner

       || '.'

       || index_name

       || ' storage (buffer_pool keep);'

  FROM dba_indexes

 WHERE owner || '.' || table_name IN

             (  SELECT p.owner || '.' || p.name

                  FROM dba_tables t,

                       dba_segments s,

                       dba_hist_sqlstat a,

                       (SELECT DISTINCT

                               pl.sql_id,

                               pl.object_owner owner,

                               pl.object_name name

                          FROM dba_hist_sql_plan pl

                         WHERE pl.operation = 'TABLE ACCESS'

                               AND pl.options = 'FULL') p

                 WHERE     a.sql_id = p.sql_id

                       AND t.owner = s.owner

                       AND t.table_name = s.segment_name

                       AND t.table_name = p.name

                       AND t.owner = p.owner

                       AND t.owner NOT IN ('SYS', 'SYSTEM')

                       AND t.buffer_pool <> 'KEEP'

                HAVING s.blocks < 50

              GROUP BY p.owner,

                       p.name,

                       t.num_rows,

                       s.blocks);

**********************************************************************