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

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

dbms_Shared_pool ( Performance )

dbms_shared_pool

Imagine a large object has to be loaded into the shared pool.

The database has to search for free space for the object.

If it cannot get enough contiguous space, it will free many small objects to satisfy the request.

If several large objects need to be loaded, the database has to throw out many small objects in the shared pool.

Finding candidate objects and freeing memory is very costly.  These tasks will impact CPU resources.

One approach to avoiding performance overhead and memory allocation errors is to keep large PL/SQL objects in the shared pool at startup time.This process is known as pinning.

This loads the objects into the shared pool and ensures that the objects are never aged out of the shared pool. If the objects are never aged out, then that avoids problems with insufficient memory when trying to reload them.


Pinning an object :  exec dbms_shared_pool.keep('owner.object');

View Pinned objects : select owner,name,type,sharable_mem from v$db_object_cache where kept='YES';


How to Identify candidates that should be kept in the shared pool:

Step 1 :
select owner||'.'||name  Name ,
           type,
           sharable_mem,
           loads,
           executions,
           kept
from v$db_object_cache
where type in ('TRIGGER','PROCEDURE','PACKAGE BODY','PACKAGE') and executions >0
order by executions desc,loads desc,sharable_mem desc;

Step 2:

select * from x$ksmlru;

The x$ksmlru table keeps track of the current shared pool objects and the corresponding number of objects flushed out of the shared pool to allocate space for the load. These objects are stored and flushed out based on the Least Recently Used (LRU) algorithm.

KSMLRNUM  shows the number of objects that were flushed to load the large object
KSMLRISZ shows the size of the object that was loaded (contiguous memory allocated).

Analyze the x$ksmlru output to determine if there are any large allocations that are flushing other objects.
If this is the case, analyze the v$db_object_cache to identify the objects with high loads or executions.  These should be kept in the shared pool.

DBMS_ALERT

DBMS_ALERT supports asynchronous notification of database events (alerts).

DBMS_ALERT.REGISTER (name IN VARCHAR2);  -- Name of the alert & it is case insensitive

This procedure lets a session register interest in an alert.


DBMS_ALERT.SIGNAL (name IN  VARCHAR2,message IN  VARCHAR2);

This procedure signals an alert.
The effect of the SIGNAL call only occurs when the transaction in which it is made commits.
If the transaction rolls back, SIGNAL has no effect.

All sessions that have registered interest in this alert are notified.
If the interested sessions are currently waiting, they are awakened.
If the interested sessions are not currently waiting, they are notified the next time they do a wait call.


DBMS_ALERT.WAITANY (name      OUT  VARCHAR2,
                                                 message   OUT  VARCHAR2,
                                                 status    OUT  INTEGER,
                                                 timeout   IN   NUMBER DEFAULT MAXWAIT);

Call this procedure to wait for an alert to occur for any of the alerts for which the current session is registered.
An implicit COMMIT is issued before this procedure is executed.

status:
0 - alert occurred
1 - timeout occurred

message:
This is the message provided by the SIGNAL call.
If multiple signals on this alert occurred before WAITANY, the message corresponds to the most recent SIGNAL call. Messages from prior SIGNAL calls are discarded.


DBMS_ALERT.WAITONE (name      IN   VARCHAR2,
                                                 message   OUT  VARCHAR2,
                                                 status    OUT  INTEGER,
                                                 timeout   IN   NUMBER DEFAULT MAXWAIT);

This procedure waits for a specific alert to occur. An implicit COMMIT is issued before this procedure is executed.


DBMS_ALERT.SET_DEFAULTS (sensitivity  IN  NUMBER); -- sensitivity,in seconds, to sleep between polls. The default interval is five seconds


DBMS_ALERT.REMOVE (name  IN  VARCHAR2);
This procedure enables a session that is no longer interested in an alert to remove that alert from its registration list.

DBMS_ALERT.REMOVEALL;
This procedure removes all alerts for this session from the registration list.


Example:

Session 1:


DECLARE
   status       NUMBER;
   MESSAGE      VARCHAR2 (200);
   signalname   VARCHAR2 (30);
BEGIN
   DBMS_ALERT.REGISTER ('testsignal1');
   DBMS_ALERT.REGISTER ('testsignal0');
   DBMS_OUTPUT.put_line ('waiting for a single');
   DBMS_ALERT.WAITANY (signalname,
                                              MESSAGE,
                                              status,
                                              1);
   DBMS_OUTPUT.put_line (signalname);
   DBMS_OUTPUT.put_line (status);
   DBMS_OUTPUT.put_line (MESSAGE);
END;



Session 2:


begin
DBMS_ALERT.Signal('testsignal1','Job 1 done');
commit;
end;


Session 3:


begin
DBMS_ALERT.Signal('testsignal0','Job 0 done');
commit;
end;




Session 1 registered for two alerts. Session 2 and Session 3 signal alert completion.

Email Package : UTL_SMTP

UTL_SMTP : Package is used to send out an email via PL/SQL

Sample Code:

DECLARE

   c             UTL_SMTP.CONNECTION;

   datasetclob   CLOB;

   FUNCTION generatexml (sqlstatement CLOB)

      RETURN CLOB

   IS

      xmlcontextHandle   DBMS_XMLGEN.CTXHANDLE;

      xml                XMLTYPE;

      xmlclob            CLOB;

      XSLT_str_w_css VARCHAR2 (4000)

            := '<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><xsl:template match="/"><HTML><HEAD><style type="text/css">body{ background-color:#FFFFFF;} h1{color:black;text-align:center;}    td{background-color:#FFCC80;font-family:"Helvetica"; font-size:14px; color:black; text-align:left;}</style>        </HEAD><BODY><xsl:apply-templates/></BODY></HTML></xsl:template><xsl:template match="/*"><TABLE BORDER="1"><TR><xsl:for-each select="*[position() = 1]/*"><TD><xsl:value-of select="local-name()"/></TD></xsl:for-each></TR><xsl:apply-templates/></TABLE></xsl:template><xsl:template match="/*/*"><TR><xsl:apply-templates/></TR></xsl:template><xsl:template match="/*/*/*"><TD><xsl:value-of select="."/></TD></xsl:template></xsl:stylesheet>';

      XSLT               XMLTYPE;

   BEGIN

      xmlcontextHandle := DBMS_XMLGEN.newcontext (sqlstatement);

      DBMS_XMLGEN.setRowsetTag (xmlcontextHandle, 'Data');

      DBMS_XMLGEN.setRowTag (xmlcontextHandle, 'DataRow');

      xmlclob := DBMS_XMLGEN.GETXML (xmlcontextHandle);

      DBMS_XMLGEN.closecontext (xmlcontextHandle);

      XSLT := XMLType (XSLT_str_w_css);

      xml := XMLType.transform (xmltype (xmlclob), XSLT);

      xmlclob := XML.getCLOBVal;

      RETURN xmlclob;

      

   END generatexml;

   

   

BEGIN

   datasetclob := generatexml ('select sysdate currentdate,sysdate+1 nextday from dual union select sysdate-1,sysdate from dual');

   DBMS_OUTPUT.put_line (datasetclob);

   c := UTL_SMTP.OPEN_CONNECTION ('webmail.nrgenergy.com'); -- open a connection with smtp server

   

   UTL_SMTP.HELO (c, 'webmail.nrgenergy.com'); -- Handshaking with the server (Ping)

   UTL_SMTP.MAIL (c, 'kkodava1@reliant.com');                          -- from

   UTL_SMTP.RCPT (c, 'kkodava1@reliant.com');                            -- To

   UTL_SMTP.RCPT(c, 'kiran.kodavati@gmail.com'); -- To

   

   UTL_SMTP.OPEN_DATA (c);                             -- Open data connection

   

   UTL_SMTP.WRITE_DATA (C, 'Subject: ' || 'my test subject'); -- Set the subject

   UTL_SMTP.WRITE_DATA (C, UTL_TCP.CRLF);

   UTL_SMTP.WRITE_DATA (C, 'To: ' || 'kiran,vijay');                -- To list

   UTL_SMTP.WRITE_DATA (C, UTL_TCP.CRLF);

   

   -- Send email as HTML : Start

   UTL_SMTP.write_data (c, 'MIME-Version: 1.0');

   UTL_SMTP.write_data (c, UTL_TCP.CRLF);

   UTL_SMTP.write_data (c, 'Content-Type: text/html');

   UTL_SMTP.write_data (c, UTL_TCP.CRLF);

   UTL_SMTP.write_data (c, 'Content-Transfer-Encoding: 7bit;');

   UTL_SMTP.write_data (c, UTL_TCP.CRLF);

   -- Send email as HTML : End

   

   UTL_SMTP.WRITE_DATA (c, UTL_TCP.CRLF || 'Hello, world!');          -- email message

   UTL_SMTP.WRITE_DATA (c, UTL_TCP.CRLF || 'kiran testmsg 1');      -- email message

   UTL_SMTP.WRITE_DATA (c, UTL_TCP.CRLF || 'kiran testmsg 2');      -- email message

   

   UTL_SMTP.WRITE_DATA (c, UTL_TCP.CRLF || datasetclob);  -- SQL output in Tabular format 

   

   UTL_SMTP.CLOSE_DATA (c);                           -- Close data connection

   UTL_SMTP.QUIT (c);                                             -- Close the

END;

Continue Statement. (11g)

Continue - Specifies to skip the iteration in a loop and move on to next iteration.



Example code:

begin

   for i in 1 .. 100

   loop

      dbms_output.put_line ('Iteration:' || i || 'Start');

      if mod (i, 2) = 0

      then

         dbms_output.put_line (

            'skip and move on to next record-Condition 1 satisfied');

         continue;

      end if;

      dbms_output.put_line ('Condition 1 failed:' || i);

      if mod (i, 5) = 0

      then

         dbms_output.put_line (

            'skip and move on to next record-Condition 2 satisfied');

         continue;

      end if;

      dbms_output.put_line ('Condition 2 failed:' || i);

      if mod (i, 11) = 0

      then

         dbms_output.put_line (

            'skip and move on to next record-Condition 3 satisfied');

         continue;

      end if;

      dbms_output.put_line ('Condition 3 failed:' || i);

      dbms_output.put_line ('Iteration:' || i || 'End');

   end loop;

end;