ORACLE DEVELOPMENT

Sunday, March 11, 2012

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.

Wednesday, September 14, 2011

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

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;

Thursday, August 18, 2011

Issues with Implicit conversion

Implicit conversion does only if the operands data type differs

‘01’ ( varchar ) < 6 (int)

Then implict casting will be done as 1 < 6

If operands are of same data type no conversion is needed.

‘01’ = ‘1’

Strings are compared character by character ( ASCII value ).

Comparisions must always be done in numeric format

Example:

comparison done in string format

‘01/08/2011’ > ‘01/02/2012’

This evaluate to true.

Comparison done in the correct data type format

To_date( ‘01/08/2011’,’mm/dd/yyyy’) > To_date ( ‘01/02/2012’,’mm/dd/yyyy’)

Thumb rule : Don’t trust on Implicit conversions.

Tuesday, August 2, 2011

Alter session Set Current_Schema=UserXYZ

Using this we can refer to the objects in "SchemaUserX" without any Synonyms or schema reference.

All the objects will be referenced as SchemaUserX.Objectname

Example : select * from tablename

will be transformed to

Select * from SchemaUserX.tablename;

Statement to execute:

ALTER SESSION SET CURRENT_SCHEMA = SchemaUserX

This will be equivalent to Logging into SchemaUserX and then executing the sql / pl/sql blocks.

Sunday, April 17, 2011

SYS_CONTEXT

In Oracle/PLSQL, the sys_context function can be used to retrieve information about the Oracle environment.

sys_context( namespace, parameter)

NameSpace : Is an Oracle namespace that has already been created.

If the namespace of 'USERENV' is used, attributes describing the current Oracle session can be returned.

Parameter : Is a valid attribute that has been set using the DBMS_SESSION.set_context procedure.

USERENV is the context (namespace) provided by oracle.

User defined NameSpaces:

Imagine as we are setting the session wide attributes ( like packaged variables ) and those are used within the session. ( ROW LEVEL Security Implementation )

Steps :

1. Create a namespace

   CREATE OR REPLACE CONTEXT security_context USING security.pkg_security ACCESSED GLOBALLY;

2. Enclose the assignment of name/value pair in a package ( ex:security.pkg_security)

3. Assign (Name/Value) pair using DBMS_SESSIOn.SET_CONTEXT

   DBMS_SESSION.SET_CONTEXT(NAMESPACE => 'security_context'
   ,ATTRIBUTE => empid
   ,VALUE => 420
   );

4. Attach the package to a trigger (DATABASE Level Trigger): Trigger needs to fire at the time of user login.

Example:

Create Or Replace package pkg_security
As
procedure set_attributes ;
end pkg_security;
/

Create Or Replace package body pkg_security
as
   Procedure set_attributes
   is
   Begin
   Dbms_Session.Set_Context('security_context','empid',420);
   Dbms_Session.Set_Context('security_context','sid',Sys_Context('userenv','sid'));
   Dbms_Session.Set_Context('security_context','ssn',123456789);
   end set_attributes;
End pkg_security;
/

create or replace trigger set_session_trigger after logon on database
Begin
Security.pkg_security.Set_Attributes;
end set_session_trigger;
/

login to a user:

select sys_context('security_context','empid),
   sys_context('security_context','sid'),
   sys_context('security_context','ssn')
from dual;

Wednesday, April 6, 2011

Using Objects & Nested tables.

1. Create an Object at database level

-- Can be Imagined as a record ( composite fields with datatypes )

2. Create a Nested table on above Created Object.

3. Use the Nested table type within the Pl/SQL Code.

This helps to avoid to iterate through the Collections and those can be directly used within the sql statements as table.

Examples: ( @ SQL Level )

Demo 1:

CREATE or replace TYPE phone AS TABLE OF NUMBER;

CREATE or replace TYPE phone_list AS TABLE OF phone;

SELECT t.COLUMN_VALUE

FROM TABLE(phone(1,2,3)) t;

Demo 2:

create or replace type emp_obj as object

( empno number,

deptno number,

sal number);

create or replace type emp_tab is table of emp_obj;

SELECT empno, deptno, sal

FROM TABLE (emp_tab (emp_obj (1, 10, 1000), emp_obj (2, 20, 2000)));

Examples : ( @ PL/SQL Level )

CREATE OR REPLACE TYPE emp_temp AS OBJECT

(EMPNO NUMBER,

ENAME VARCHAR2 (10),

SAL NUMBER,

DEPTNO NUMBER);

CREATE OR REPLACE TYPE emp_temp_tab IS TABLE OF emp_temp;

DECLARE

emp_table emp_temp_tab;

BEGIN

SELECT emp_temp (EMPNO,

ename,

sal,

deptno)

BULK COLLECT

INTO emp_table

FROM emp;

DBMS_OUTPUT.put_line (emp_table.COUNT);

-- Access the collection within the sql. ( No looping )

INSERT INTO emp_out

SELECT * -- column names can be specified

FROM TABLE (emp_table);

COMMIT;

END;

ORACLE DEVELOPMENT

Search This Blog

Sunday, March 11, 2012

DBMS_ALERT

Wednesday, September 14, 2011

Email Package : UTL_SMTP

Continue Statement. (11g)

Thursday, August 18, 2011

Issues with Implicit conversion

Tuesday, August 2, 2011

Alter session Set Current_Schema=UserXYZ

Sunday, April 17, 2011

SYS_CONTEXT

Wednesday, April 6, 2011

Using Objects & Nested tables.

PAGE VISIT

Followers