ORACLE DEVELOPMENT: 2011

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;

Saturday, March 5, 2011

Error Handling : Back Trace

PL/SQL offers a powerful and flexible exception architecture. Of course, there is always room for improvement, and in Oracle Database 10g, exception handling takes a big step forward with the introduction of the DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function. This article explores the problem that this function solves and how best to use it.

Who Raised That Exception?

When an exception is raised, one of the most important pieces of information a programmer would like to uncover is the line of code that raised the exception. Prior to Oracle Database 10g, one could obtain this information only by allowing the exception to go unhandled.

Let's revisit the error-handling behavior available to programmers in Oracle9i Database. Consider this simple chain of program calls in Listing 1: procedure proc3 calls proc2 calls proc1 , at which point proc1 raises the NO_DATA_FOUND exception. Notice that there is no error handling in any of the procedures; it is most significantly lacking in the top-level proc3 procedure. If I run proc3 in SQL*Plus, I will see the following results:

ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "SCOTT.PROC1", line 4
ORA-06512: at "SCOTT.PROC2", line 6
ORA-06512: at "SCOTT.PROC3", line 4
ORA-06512: at line 3

Code Listing 1: A stack of procedures

CREATE OR REPLACE PROCEDURE proc1 IS
BEGIN
   DBMS_OUTPUT.put_line ('running proc1');
   RAISE NO_DATA_FOUND;
END;
/
CREATE OR REPLACE PROCEDURE proc2 IS
   l_str VARCHAR2(30) 
         := 'calling proc1';
BEGIN
   DBMS_OUTPUT.put_line (l_str);
   proc1;  
END;
/
CREATE OR REPLACE PROCEDURE proc3 IS
BEGIN
   DBMS_OUTPUT.put_line ('calling proc2');
   proc2;
END;
/

This is the error trace dump of an unhandled exception, and it shows that the error was raised on line 4 of proc1. On the one hand, we should be very pleased with this behavior. Now that we have the line number, we can zoom right in on the problem code and fix it. On the other hand, we got this information by letting the exception go unhandled. In many applications, however, we work to avoid unhandled exceptions.

Let's see what happens when I add an exception section to the proc3 procedure and then display the error information (the simplest form of error logging). Here is the second version of proc3 :

CREATE OR REPLACE PROCEDURE proc3
IS
BEGIN
  DBMS_OUTPUT.put_line ('calling proc2');
  proc2;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE (DBMS_UTILITY.FORMAT_ERROR_STACK);
END;
/

Notice that I call DBMS_UTILITY.FORMAT_ERROR_STACK , because it will return the full error message.

Having compiled the new proc3 , when I run it inside SQL*Plus I see the following output:

SQL> SET SERVEROUTPUT ON
SQL> exec proc3
calling proc2
calling proc1
running proc1
ORA-01403: no data found

In other words, DBMS_UTILITY.FORMAT_ERROR_STACK does not show the full error stack with line numbers; SQLERRM acts in the same manner.

Backtrace to the Rescue

In Oracle Database 10g, Oracle added DBMS_UTILITY.FORMAT_ERROR_BACKTRACE , which can and should be called in your exception handler. It displays the call stack at the point where an exception was raised, even if the function is called in a PL/SQL block in an outer scope from that where the exception was raised. Thus, you can call DBMS_UTILITY.FORMAT_ERROR_BACKTRACE within an exception section at the top level of your stack and still find out where the error was raised deep within the call stack.

Code Listing 2: proc3 rewritten with FORMAT_ERROR_BACKTRACE

CREATE OR REPLACE PROCEDURE proc3
IS
BEGIN
  DBMS_OUTPUT.put_line ('calling proc2');
  proc2;
EXCEPTION
  WHEN OTHERS
  THEN
    DBMS_OUTPUT.put_line ('Error stack at top level:');
    DBMS_OUTPUT.put_line (DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END;
/

And now when I run proc3 , I will see the following output:

SQL> SET SERVEROUTPUT ON
SQL> exec proc3
calling proc2
calling proc1
running proc1
Error stack at top level:
ORA-06512: at "SCOTT.PROC1", line 4
ORA-06512: at "SCOTT.PROC2", line 6
ORA-06512: at "SCOTT.PROC3", line 4

In other words, the information that had previously been available only through an unhandled exception is now retrievable from within the PL/SQL code.

Impact of Multiple RAISEs

An exception often occurs deep within the execution stack. If you want that exception to propagate all the way to the outermost PL/SQL block, it will have to be re-raised within each exception handler in the stack of blocks. Listing 3 shows an example of such an occurrence.

Code Listing 3: Re-raising exceptions to the outermost block in the stack

CREATE OR REPLACE PROCEDURE proc1 IS
BEGIN
   DBMS_OUTPUT.put_line ('running proc1');
   RAISE NO_DATA_FOUND;
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.put_line (
         'Error stack in block where raised:');
      DBMS_OUTPUT.put_line (
         DBMS_UTILITY.format_error_backtrace);
      RAISE;
END;
/
CREATE OR REPLACE PROCEDURE proc2
IS
   l_str VARCHAR2 (30) := 'calling proc1';
BEGIN
   DBMS_OUTPUT.put_line (l_str);
   proc1;
END;
/
CREATE OR REPLACE PROCEDURE proc3 IS
BEGIN
   DBMS_OUTPUT.put_line ('calling proc2');
   proc2;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Error stack at top level:');
      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);
     
END;
/

When I run the code in Listing 3, I see the following output:

SQL> exec proc3
calling proc2
calling proc1
running proc1
Error stack in block where raised:
ORA-06512: at "SCOTT.PROC1", line 4
Error stack at top level:
ORA-06512: at "SCOTT.PROC1", line 11
ORA-06512: at "SCOTT.PROC2", line 6
ORA-06512: at "SCOTT.PROC3", line 4

Program owner = SCOTT
Program name = PROC1
Line number = 11

When I call the backtrace function within the lowest-level program, it correctly identifies line 4 of proc1 as the line in which the error is first raised. I then re-raise the same exception using the RAISE statement. When the exception propagates to the outermost block, I call the backtrace function again, and this time it shows that the error was raised on line 11 of proc1.

From this behavior, we can conclude that DBMS_UTILITY.FORMAT_ERROR_BACKTRACE shows the trace of execution back to the last RAISE in one's session. As soon as you issue a RAISE of a particular exception or re-raise the current exception, you restart the stack that the backtrace function produces. This means that if you want to take advantage of DBMS_UTILITY.FORMAT_ERROR_BACKTRACE , take one of the following two approaches:

Call the backtrace function in the exception section of the block in which the error was raised. This way you have (and can log) that critical line number, even if the exception is re-raised further up in the stack.
Avoid exception handlers in intermediate programs in your stack, and call the backtrace function in the exception section of the outermost program in your stack.

ORACLE DEVELOPMENT

Search This Blog

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.

Saturday, March 5, 2011

Error Handling : Back Trace

PAGE VISIT

Followers