Wednesday, March 24, 2010

.NET: How to prevent master page flickering



Finally I have found a simply great solution to prevent the master page flickering every time the user makes a selection off of the menu.

This does not imply preventing the master page from reloading after post back, but only stops master page flickering.

Add this magical code in between your <Head> </Head> tags of your "MasterPage.Master".
Please do not ask me how the below code works, because I am not sure either, but it works like a gem.

<meta http-equiv="Page-Enter" content="blendTrans(Duration=0)">
<meta http-equiv="Page-Exit" content="blendTrans(Duration=0)">



Enjoy …!


ASP.NET Page Life Cycle


Introduction

I have found the following article on the net which I feel is worth reading to understand the ASP.NET page life cycle. So I am just posting the article "AS IS".


When an ASP.NET page runs, the page goes through a life cycle in which it performs a series of processing steps. These include initialization, instantiating controls, restoring and maintaining state, running event handler code, and rendering.
It can be difficult to remember exactly what happens and when during the ASP.NET page lifecycle. That in mind, often the easiest way to make sense out of a series of steps is to create an acronym. Microsoft shows us the basic steps of the ASP.NET page lifecycle below.
  1. Page Request
  2. Start
  3. Page Initialization
  4. Load
  5. Validation
  6. Postback event handling
  7. Rendering
  8. Unload
Putting together an acronym for these is easy enough. Since the Page Request technically isn't a part of the life cycle (it only indicates whether we actually will start the cycle or load a cached page) we won't include it in the acronym.
  • S - Start
  • I - Initialize
  • L - Load
  • V - Validate
  • E - Event Handling
  • R - Render
That gives us "SILVER", which is very easy to remember. However, it is important remember that the last part of the cycle is unload. You can remember it as "SILVER-U" or "SILVER-YOU" if that helps (but it just didn't quite fit into our acronym!). Now that it's easy to remember the order of steps for the page lifecycle, we'll summarize exactly what happens and what events are pertinent to each stage.

1. Start

This is where page properties such as Request, Response, IsPostBack and UICulture are set. As a developer, you most likely won't really need to do anything in this stage of the cycle. If you need to access or override behavior for this step, use the PreInit method to create or re-create dynamic controls, set a master page or theme or read or set profile property values. It is important to note that if the request is a postback, the values of the controls have not yet been restored from view state. If you set a control property at this stage, its value might be overwritten in the next event.

2. Initialize

This stage can be very important to developers. Here, themes are applied, and unique ids are generated and set for controls. Developers have access to the Init, InitComplete and PreLoad methods in this stage. Microsoft's recommended usage for these methods is as follows:
  • Init This event is raised after all controls have been initialized and any skin settings have been applied. Use this event to read or initialize control properties.
  • InitComplete This event is raised by the Page object. Use this event for processing tasks that require all initialization be complete.
  • PreLoad - Use this event if you need to perform processing on your page or control before the Load event. After the Page raises this event, it loads view state for itself and all controls, and then processes any postback data included with the Request instance.

3. Load

This stage is perhaps the most utilized by developers. In this stage controls are loaded with information retrieved from view and control states. The OnLoad is the event method that fires during this stage. This is where you will want to set properties for all of the server controls on your page, request query strings, and establish database connections.

4. Validation

If you have controls that require validation, they are validated here and you can now check the IsValid property of the control. The event associated with this is Validate, which contains one overloaded method that accepts a validation group string. The overloaded method instructs the controls in the specified group to validate.

5. Event Handling

The event handling for server controls occurs during this stage. This means that events such as Click, SelectedIndexChanged, etc are applied to your server controls, and, in the case of a postback, these event handlers are fired by the control. The accessible events of note in this stage are as follows:
  • LoadComplete At this step, all of the controls for the page have been loaded.
  • PreRender A few things of import happen here. First, the page object will call EnsureChildControls for each control, and finally for the page. Additionally, any data bound control that has a DataSourceID set will call its DataBind method. It is important to note that the PreRender event occurs for each control on the page. At the conclusion of this event, ViewState will be saved for the page and all of the controls.
  • SaveStateComplete ViewState has been saved. If you have actions that do not require changes to controls but require ViewState to have been saved, you can handle the SaveStateComplete event.

6. Render

Render is not really an event. Rather, the page object calls this method on each control, which in turn writes out the HTML markup for the control to the browser. This stage is keenly important to developers who create custom controls, because the standard approach is to override the Render method for the control in order to output the custom markup. If your control inherits from a standard ASP.NET server control, you probably won't need to override the Render method unless you want to exhibit a different behavior than the control's default. This is outside the scope of this document, but for more reading, you can reference Microsoft's Developing Custom ASP.NET Server Controls. (http://msdn2.microsoft.com/en-us/library/zt27tfhy.aspx)

7. Unload

This final event occurs first for each control, then, finally, for the page. At this point, all controls have been rendered to the output stream and cannot be changed. During this event any attempt to access the response stream will result in an exception being thrown. This event is primarily for cleanup routines such as closing open database connections and open file streams, or, event logging and other tasks.

Methods

The following methods (which can all be overridden) occur in order during the lifecycle of an ASP.NET page. Please realize that some of these methods are called recursively, and multiple times depending on the content of the page. This list is the generalized order in which methods fire when a page loads. You can test this by creating a default ASP.NET application, overloading each of the below methods, and setting a breakpoint on each.
  1. Construct
  2. ProcessRequest
  3. FrameworkInitialize
  4. InitializeCulture

  5. If child controls are present:
    1. AddParsedSubObject
    2. CreateControlCollection
    3. AddedControl
    4. ResolveAdapter
  6. DeterminePostBackMode
  7. OnPreInit
  8. OnInit
  9. TrackViewState
  10. OnInitComplete
  11. OnPreLoad
  12. OnLoad
  13. OnLoadComplete

  14. EnsureChildControls
    1. CreateChildControls
  15. OnPreRender
  16. OnPreRenderComplete
  17. SaveViewState
  18. OnSaveStateComplete
  19. CreateHtmlTextWriter
  20. RenderControl

  21. Render
    1. RenderChildren
    2. VerifyRenderingInServerForm
  22. OnUnload
  23. Dispose

Tuesday, March 23, 2010

.NET: Activator Class (Create dynamic instance of an object)


In my recent developments, I had a situation to create a dynamic instance of a Crystal Report where the Crystal Report File name is dynamically derived from the table settings.
In general scenarios, without knowing the class name it is almost impossible to construct an instance of that class at runtime. In order to achieve the above, use "Activator.CreateInstance"

Activator Class

Contains methods to create types of objects locally or remotely, or obtain references to existing remote objects.

Activator.CreateInstance

The CreateInstance method creates an instance of a type defined in an assembly by invoking the constructor that best matches the specified arguments. If the instance is created locally, a reference to that object is returned. If the instance is created remotely, a reference to a proxy is returned.

Example:

Public ReadOnly Property GetCrystalReportInstance
(Optional
ByVal pReportName As
String = "") As ReportClass
Get
    Dim crReport As ReportClass = Nothing
    Dim sReportClassName As String = String.Empty    
    Dim rpttype As Type
Try
'NOTE : Report Name should always be a fully qualified name prefixed with your Namesspce.
sReportClassName = pReportName.Replace(".rpt", "")
rpttype = Type.GetType(sReportClassName, True, True)
crReport = CType(Activator.CreateInstance(rpttype),ReportClass)
Catch ex As Exception
'ignore errors and return crReport

End Try

Return crReport
End Get
End Property

.NET: JavaScript Object Notation (JSON)


JSON is a lightweight format for exchanging data between the client and server. It is often used in Ajax applications because of its simplicity and because its format is based on JavaScript object literals.

JSON is built on two structures:

JSON Array:

An array is an ordered collection of values. An array begins with [ (left bracket) and ends with ] (right bracket). Values are separated by , (comma).
Array literals are created with square brackets as shown below:

var Beatles = ["Paul","John","George","Ringo"];//This is the equivalent of:
var Beatles = new Array("Paul","John","George","Ringo");

JSON Object:

An object is an unordered set of name/value pairs. An object begins with { (left brace) and ends with } (right brace). Each name is followed by : (colon) and the name/value pairs are separated by , (comma).

Object literals are created with curly brackets:

var Beatles = {
           "Country" : "England",

           "YearFormed" : 1959,
           "Style" : "Rock'n'Roll"

              }

Accessing JSON Object:

alert(Beatles.Style); //Dot Notation

alert(Beatles["Style"]); //Bracket Notation

Little Complex Example of Accessing JSON Object:
var menu = {"menu":
{"id": "file",
"value": "File",

"popup": {

    "menuitem": [

      {"value": "New", "onclick": "CreateNewDoc()"},
      {"value": "Open", "onclick": "OpenDoc()"},
      {"value": "Close", "onclick": "CloseDoc()"}
                ]
          }
}
};

function json (){
   alert(menu.menu.popup.menuitem[0].value); //Return "New"
   return  false;
   }

   

Monday, March 22, 2010

.NET: Singleton Pattern

What is Singleton pattern?

Singleton pattern is a strategy for ensuring that one and only one instance of an object will be created.
In other words a singleton pattern is a design pattern that is used to restrict instantiation of a class to one object.

Implementation of a singleton pattern requires a mechanism to access the singleton class member without creating a class object and a mechanism to persist the value of class members among class objects.

1. The first step is to ensure that every constructor in the class implementing the Singleton pattern is non-public. All constructors must be protected or private.
2. Implement a public/shared method-that creates just one instance of the class.

Example:

Public NotInheritable Class cSinglton
 
Private Shared _CreateInstance As cSinglton = Nothing


Private Sub New()
End

Sub
Public Shared ReadOnly Property CreateInstance() As cSinglton
Get

   If (_CreateInstance Is Nothing) Then

      _CreateInstance = New cSinglton()
   End  If
  Return _CreateInstance
End Get
End Property
End Class

The private shared property _CreateInstance is used to store the reference to the instance of the Singleton class.  Consumer will never able to create a direct instance of the class using "New" keyword because the constructor-Sub New-is defined as private.


Consumer:

Consume should always read the shared property CreateInstance in order to create a new instance of the class which indeed ensures that only one instance of this class will be returned.

Dim clsSinglton As cSinglton
clsSinglton = cSinglton.CreateInstance

Following error message will be display in the event of "Dim clsSinglton As new cSinglton" declaration.
Error: cSinglton.Private Sub New() is not accessible in this context because it is 'Private'.




Wednesday, March 17, 2010

Oracle: Generating XML Using XMLFOREST

XMLForest produces forest of XML elements from its arguments, which are expressions to be evaluated, with optional aliases.
Each of the value expressions is converted to XML.

Syntax:      XMLFOREST ( value_expr AS c_alias)

To understand more about XMLForest in a clear way, Let us see one simple example to create your XML document using a SQL query that could give you some sense to it.

Following example creates a XML document per each employee record without using XMLFOREST:
NOTE: Casting to XMLTYPE is optional here:

SELECT XMLTYPE
 (XMLELEMENT ("ROOT",
         XMLELEMENT ("REC",
         XMLELEMENT ("EMPNO", empno),

         XMLELEMENT ("ENAME", ename),

         XMLELEMENT ("JOB", job)

             )
 ).getclobval () ) AS "RESULT"
FROM EMP

WHERE ROWNUM<3

Output:
Row1:

<ROOT>
<REC >
<EMPNO>7369</EMPNO>
<ENAME>SMITH</ENAME>
<JOB>CLERK</JOB>
</REC>
</ROOT>
Row2:
<ROOT>
<REC EMPNO="7499">
<EMPNO>7499</EMPNO>
<ENAME>ALLEN</ENAME>
<JOB>SALESMAN</JOB>
</REC>
</ROOT>

Achieve the same above result by simply using XMLFOREST: Instead of creating XMLELEMENT for each coulmn you could simply use XMLFOREST to do the job.

SELECT XMLElement("ROOT",
       XMLForest(empno, ename, job, mgr, hiredate, sal, comm, deptno)

   ) AS "RESULT"
FROM EMP 

Output:
Row1:

<ROOT>
<EMPNO>7369</EMPNO>
<ENAME>SMITH</ENAME>
<JOB>CLERK</JOB>
<MGR>7902</MGR>
<HIREDATE>1980-12-17</HIREDATE>
<SAL>800</SAL>
<DEPTNO>20</DEPTNO>
</ROOT>
Row2:
<ROOT>
<EMPNO>7499</EMPNO>
<ENAME>ALLEN</ENAME>
<JOB>SALESMAN</JOB>
<MGR>7698</MGR>
<HIREDATE>1981-02-20</HIREDATE>
<SAL>1600</SAL>
<COMM>300</COMM>
<DEPTNO>30</DEPTNO>
</ROOT>


An alternate Query by using column alias:
SELECT XMLELEMENT ("ROOT",
       XMLFOREST (empno AS "EMP",
                  ename AS "NAME",
                  job AS "JOB",
                  mgr AS "MANAGER",
                  hiredate AS "HIRE_DATE",
                  sal AS "SALARY",
                  comm AS "COMMISSION",
                  deptno AS "DEPR_NO"
                 ) ) AS "RESULT"
FROM emp

WHERE ROWNUM < 3






Oracle: Generating XML Using DBMS_XMLGEN

Package DBMS_XMLGEN now supports hierarchical queries. PL/SQL package DBMS_XMLGEN creates XML documents from SQL query results.
It retrieves an XML document as a CLOB or XMLType value.

Converts the query results from the passed in SQL query string to XML format, and returns the XML as a CLOB.

Syntax:
FUNCTION DBMS_XMLGEN.getXML
(
     sqlQuery IN VARCHAR2,
     dtdOrSchema IN NUMBER := NONE
) RETURN CLOB;

Converts the query results from the passed in SQL query string to XML format, and returns the XML as a XMLTYPE.

 Syntax:
FUNCTION DBMS_XMLGEN.getXMLType
(

     sqlQuery IN VARCHAR2,
     dtdOrSchema IN NUMBER := NONE
) RETURN XMLType;

Package DBMS_XMLGEN also provides options for changing tag names for ROW, ROWSET, and so on.
setRowTag()         : Sets the name of the element separating all the rows. The default name is ROW.
setRowSetTag()     : Sets the name of the document root element. The default name is ROWSET

Example1:
SELECT dbms_xmlgen.getxml('select * from emp where rownum<6') data FROM dual --RETURNS CLOB

Example2:
SELECT dbms_xmlgen.getxmltype ('select * from emp where rownum<6') data FROM dual --RETURNS XMLTYPE

Output:
<ROWSET>
<ROW>
<EMPNO>7369</EMPNO>
<ENAME>SMITH</ENAME>
<JOB>CLERK</JOB>
<MGR>7902</MGR>
<HIREDATE>17-Dec-1980</HIREDATE>
<SAL>800</SAL>
<DEPTNO>20</DEPTNO>
</ROW>
<ROW>
<EMPNO>7499</EMPNO>
<ENAME>ALLEN</ENAME>
<JOB>SALESMAN</JOB>
<MGR>7698</MGR>
<HIREDATE>20-Feb-1981</HIREDATE>
<SAL>1600</SAL>
<COMM>300</COMM>
<DEPTNO>30</DEPTNO>
</ROW>
</ROWSET>

For More Info : DBMS_XMLGEN





Saturday, March 13, 2010

.NET: What’s New in C# 3.0

Implicitly Typed Local Variables
  • Local variables can be declared as type 'var' which means compiler to determine the actual type based on the data by which its is initialized.
  • var i = 10; // i is created of type int
  • var name = "MyName" ; // name is created of type string
  • can only be used when declared and initialized in same statement.
  • Cannot be initialized to null.
  • Cannot be used as class members.
  • Mostly used to store anonymous types as in LINQ based programming.

Object & Collection Initializers
  • Allow assigning values to any accessible members or properties of a type at the time of initiation without invoking the constructor with parameters.
  • The default constructor gets executed before assigning the values.
  • E.g. Coordinate c1 = new Coordinate {x=1 , y=2};
  • Used in LINQ query expressions along with anonymous types.
  • Collection Initializers use Object Initializers to specify multiple elements of collection without calling Add method multiple times.

Extension Methods
  • Allows adding new methods to existing types without modifying the existing type.
  • Are special kind of static methods but are called as if they are instance methods.
  • The first parameter passed to Extension methods specifies to which type they operate on preceded by 'this' keyword.
  • They cannot access the private variables of type which they are extending.
  • Extension Methods need to defined in a non-nested and non-generic static class.
  • Instance methods take priority over extension methods in case they have same signature.

Anonymous Types
  • Are of class types which can have only public read-only properties as their members. No other class members like methods are allowed.
  • They are of reference types and are derived from 'Object' class.
  • Internally compiler gives them the name but its not accessible by application code.
  • They have a method scope.
  • Can be initiated directly e.g. new { property1=1, property2="Hello World"};

Lambda Expressions
  • Very similar to anonymous methods introduced in C# 2.0.
  • Its an inline expression or statement block which can be used to pass arguments to method call or assign value to delegate.
  • All lambda expression use lambda operator => where the left side denotes result and right contains statement block or expression.

Auto-Implemented Properties
  • Helps in simplifying property declaration in cases where there is no custom logic required in accessors methods.
  • E.g. public int Price {get; set;};
  • Internally compiler creates an anonymous field for assigning values.

Friday, March 12, 2010

Oracle: Generate a XML document with your SQL Query


Oracle provides excellent built in packages to handle XML documents in much easier way than before.
You could generate a simple XML documents by passing your SQL query. The output of the query can be simply converted to a XML document without much overhead.

Following function will return a CLOB data (which is your output XML document) by passing a SQL query. The Result of the query is simply converted to a XML document.

FUNCTION generate_xml
(
   par_query CLOB,
   par_record VARCHAR2 DEFAULT 'RECORD',
   par_root VARCHAR2 DEFAULT 'ROOT'
) RETURN CLOB
IS
  qryCtx DBMS_XMLGEN.ctxHandle;
  result CLOB;
BEGIN
  IF NOT par_query IS NULL THEN
  qryCtx := dbms_xmlgen.newContext(par_query);
  -- set the ROOT element Name
  DBMS_XMLGEN.setrowsettag(qryCtx, par_root);
  -- set the row header Name
  DBMS_XMLGEN.setRowTag(qryCtx,par_record);
  -- now get the result
  result := DBMS_XMLGEN.getXML(qryCtx);
  --close context
  DBMS_XMLGEN.closeContext(qryCtx);
  RETURN result;
ELSE
   RETURN NULL;
END IF;
EXCEPTION
  WHEN others THEN
   DBMS_XMLGEN.closeContext(qryCtx);
    RETURN NULL;
END generate_xml ;


Example : SELECT GENERATE_XML ('SELECT * FROM user_objects WHERE ROWNUM <=100') result FROM DUAL;

Monday, March 8, 2010

Oracle: Flashback Technology – Recovering dropped objects


Oracle Database 10g extended the Flashback Technology to provide fast and easy recovery at the database, table, row, flashback Table.
When you drop a table by default it is stored in oracles recyclebin like windows. And you can restore the table with flashback command easily;

You can use the flashback technology at the database, table, and transaction levels:
• Flashback database enables you to take the entire database to a past point in time (using flashback logs).
• Flashback drop lets you retrieve accidentally dropped tables and indexes (using the recycle bin).
• Flashback table lets you recover a table to a time in the past (using undo data).
• Flashback query lets you query and restore data rows to a point in time (using undo data).

What is the Recycle Bin?

The Recycle Bin is a virtual container where all dropped objects reside Dropped tables and any associated objects such as indexes, constraints, nested tables, and other dependant objects are not moved, they are simply renamed with a prefix of BIN$$.

Flashback Drop:

Dropping of objects by accident has always been a problem for users and DBAs alike. Users soon realize their mistake but then it's too late and historically there is no easy way to recover those dropped tables, indexes, constraints, triggers, etc. Flashback Drop provides a safety net when dropping objects in Oracle Database 10g. When a user drops a table, Oracle automatically places it into the Recycle Bin.

--Create a dummy table for testing purpose
CREATE TABLE test_flashback
AS
SELECT object_name ,object_type
FROM user_objects
WHERE ROWNUM <=100
ORDER BY dbms_random.value
/
SELECT * FROM test_flashback
/
--Drop the table
DROP TABLE test_flashback
/
SELECT * FROM test_flashback
/
--Following ERROR will be displayed
--ORA-00942: table or view does not exist
/
--Search in RECYCLEBIN
SELECT * FROM RECYCLEBIN
WHERE upper(original_name) = 'TEST_FLASHBACK' ;
/
--To recover the recently dropped version of the table from flashback memory
FLASHBACK TABLE test_flashback TO BEFORE DROP;

The most recently dropped table with that original name is retrieved from the recycle bin, with its original name.
/
FLASHBACK TABLE test_flashback TO BEFORE DROP;
/
SELECT * FROM test_flashback
/
--You can retrieve the table and assign a new name using a RENAME TO clause
FLASHBACK TABLE test_flashback TO BEFORE DROP RENAME TO test_flashback_version1;
/
SELECT * FROM test_flashback_version1
/
-- The following command recovers the test_flashback table version to 2:33 PM on July 7.
FLASHBACK TABLE test_flashback TO TIMESTAMP ('JUL-07-2003, 02:33:00')
/

For More information on Oracle Flashback please visit : Oracle Flaback Technology

Oracle: Bind Variables


What are Bind Variables?

Bind variables are so called SQL query performance improvement catalysts. The simple definition of a Bind variable is "Passing a value by reference". Bind variables are 'substitution' variables that are used in place of literals.
Using bind variable is your dynamic queries could potentially improve your SQL query performance by reusing the execution plan that the statement is previously used.

NOTE: Important thing to remember is that you can't substitute object names (tables, views, columns etc) with bind variables. You could only substitute literals with the bind variables.

NOTE: "Using Duplicate Placeholders":
Placeholders in a dynamic SQL statement are associated with bind arguments in the USING clause by position, not by name. So, if the same placeholder appears two or more times in the SQL statement, each appearance must correspond to a bind argument in the USING clause.

For example: following query is defined using the identical names to bind variables, but all the placeholder are being replaced with a proper value.


DECALRE
v_empno number := 100;
v_name varchar2(100) := 'srinivas sreeramoju';
v_sal     number := 5000;
BEGIN
v_sql := 'INSERT INTO emp (empno,fname,lname,sal) VALUES (:x, :y, :y, :x)';

EXECUTE IMMEDIATE v_sql USING v_empno, v_name, v_name, v_sal;
END;

How will they work?

Just analyze the following example:
SELECT f_name, l_name, sal FROM customers WHERE empno = 100;
SELECT f_name, l_name, sal FROM customers WHERE empno = 101;
SELECT f_name, l_name, sal FROM customers WHERE empno = 102;
SELECT f_name, l_name, sal FROM customers WHERE empno = 103;

Each time the query is submitted, Oracle first checks for a matching statement in the shared pool. if found, the execution plan that this statement previously used is retrieved, and the SQL is executed.

If the statement cannot be found in the shared pool, Oracle has to go through the process of parsing the statement, working out the various execution paths and coming up with an optimal access plan before it can be executed.

In the above example, the "empno" column predicate value changes to each query, so you'll never get a match, and every statement you submit will need to be hard parsed.

So the best way to get Oracle to reuse the execution plans for these statements is to use bind variables.

SELECT f_name, l_name, sal FROM customers WHERE empno = :emp_no;
SELECT f_name, l_name, sal FROM customers WHERE empno = :emp_no;
SELECT f_name, l_name, sal FROM customers WHERE empno = :emp_no;
SELECT f_name, l_name, sal FROM customers WHERE empno = :emp_no;

Thursday, March 4, 2010

Oracle: Creating Nested PL/SQL Subprograms


You can declare subprograms in any PL/SQL block.
Subprograms must go at the end of the declarative section (i.e. between IS and BEGIN statements)
The Scope of Subprogram is always within the main Program.

FUNCTION GROSSPAY (input NUMBER) RETURN NUMBER
IS
iBonus NUMBER;


/**BEGIN : Local program units **

The Scope of CalcBonus function is within the GROSSPAY function.
It cannot be directly accessed outside of GROSSPAY function.
NOTE : All the nested function must be created within IS and BEGIN statements.
*/
FUNCTION CalcBonus RETURN NUMBER; --Optional declarative section

FUNCTION CalcBonus RETURN NUMBER

IS
dBonus NUMBER ;

BEGIN
CASE
  WHEN input BETWEEN 2000 and 4999 THEN
   dBonus := (input * 10)/100;

  WHEN input BETWEEN 5000 and 6999 THEN

   dBonus := (input * 15)/100;

  WHEN input BETWEEN 7000 and 1000 THEN

   dBonus := (input * 20)/100;

  WHEN input > 7000 THEN

   dBonus := (input * 25)/100;
END CASE;

RETURN dbonus;

END CalcBonus;
/**END : Local program units ***/
BEGIN
   iBonus := input + CalcBonus ; RETURN iBonus;END GrossPay;

Wednesday, March 3, 2010

Oracle: How to read UNIX ASCII file contents with a SQL Query

Following example illustrate the way to read the UNIX system ASCII files with a Single SQL Query.
With the function, now you do not have hazels to login to your UNIX box to view the contents of your ASCII log files.

Create the following function and compile it. Then use the following SQL query to read your log file.

SQL> : SELECT read_file(‘/home/export/auditlog.txt’,'DIR_ALIAS’) AS data FROM DUAL;

NOTE: Return value would be a CLOB datatype. use TO_CHAR to cnvert CLOB data to a VARCHAR2 type.

CREATE FUNCTION read_file (pFileName VARCHAR2 , pDirAlias VARCHAR2) RETURN CLOB
IS
oBFile BFILE;
oCLob CLOB;
v_file_exists NUMBER;
bValue BOOLEAN := FALSE;
v_error VARCHAR2(4000);

BEGIN
-- Purpose: Reads the UNIX Log files
-- SRINIVAS Sreeramoju 02/21/2010 Initial Creation
-- --------- ------ ------------------------------------------
DBMS_LOB.CREATETEMPORARY(oCLob,true);
oBFile := BFILENAME(pDirAlias,pFileName);
v_file_exists := DBMS_LOB.fileexists(oBFile);

IF v_file_exists = 1 THEN
-- Open the file
  DBMS_LOB.fileOpen(oBFile,DBMS_LOB.file_readonly);
 DBMS_LOB.loadFromFile
  (  dest_lob => oCLob,
     src_lob => oBFile,
     amount => DBMS_LOB.getLength(oBFile)
 );

IF DBMS_LOB.ISOPEN(oBFile) = 1 THEN
  DBMS_LOB.fileclose(oBFile); --Close file
END IF ;

IF oCLob IS NULL THEN
  RETURN ' ';
ELSE
  RETURN oCLob;
END IF;

END IF;

EXCEPTION

WHEN others THEN
   v_error := SQLCODE '-' SQLERRM ;
   DBMS_OUTPUT.Put_Line( v_error );

END read_file;

Oracle: How to Invoke a Java class in PL/SQL


The advantage of using Java class for accessing systems files is for richer set of file IO Capabilities.
Oracle's UTL_FILE package provides a limited functionality for accessing system files.
However, Java has a far richer set of File IO capabilities, allowing developers to remove files, add directories, and so on.

The following example illustrates some simple steps to invoke a Java class in Oracle PL/SQL.

Objective of this example is to get list of all files under a specified directory and insert the data into an Oracle Table.

Step 1#. Create a Java Class in Oracle which could access system files.--Following Java class is used to read through a directory for Files
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "javaReadFolder"AS import java.io.*;
import java.sql.*;

  public static void getList(String pdirectory)
  throws SQLException
{
--pdirectory : Full physical path of the directory
File path = new File( pdirectory );

String[] list = path.list();

String element;

int jobrunid;

jobrunid = 0;
--EXECUTE a SQL statement: Get runid

#sql { SELECT seq_job_runid.NEXTVAL into :jobrunid from dual};

--Loops through all the files from the specified directory and insert the file rows in the table.


for(int i = 0; i < list.length; i++)

{
  element = list[i];
  --EXECUTE a SQL statement

  #sql { INSERT INTO tb_load_files (runid, filename,processed)

  VALUES (:jobrunid, :element,'Y') };
}

}
}
/
Using a Java stored procedure it is possible to manipulate operating system files from PL/SQL: Get the Java File Handling class :
http://www.oracle-base.com/articles/8i/FileHandlingFromPLSQL.php

Step 2#. Create Oracle PL/SQL Procedure wrapper which could invoke the Java Class
PROCEDURE GET_DIR_LIST( p_directory in VARCHAR2) AS LANGUAGE JAVA
NAME 'javaReadFolder.getList(java.lang.String)' ;

Step 3#. Execute the Oracle Proceduer
EXEC GET_DIR_LIST('/home/xml/export')
public class javaReadFolder

{

Oracle: Creating a XML File


Following example illustrate a simple way to create a XML file on your UNIX box.
The UTL_FILE package can be used to read or write file from operating system. The UTL_FILE package has different subprograms which will help to read and write file from/to OS. The first and foremost step is to create a Directory path for your XML files folder. Follow the steps to create a directory alias.

CREATE
DIRECTORY:

In Oracle, the list of accessible directories as will be configured in ALL_DIRECTORIES view. Together, the file location and name must represent a legal filename on the system, and the directory must be accessible. A subdirectory of an accessible directory is not necessarily also accessible; it too must be specified using a complete path name matching an ALL_DIRECTORIES object.
CREATE DIRECTORY "XML_DIR_ALIAS" AS '/xml/export';
GRANT READ ON DIRECTORY "XML_DIR_ALIAS" TO <USER>;


Note that XML_DIR_ALIAS name is the alias of the physically existing directory of '/xml/export'. So there must exists export directory. And to create directory the user must have DBA role or create directory privilege.





CREATE OR REPLACE PROCEDURE CreateXMLFile


IS


v_filename VARCHAR2(255);


v_xml_file UTL_FILE.file_type;


v_emp_no VARCHAR2(255);


v_fname VARCHAR2(255);


v_lname VARCHAR2(255);


v_sal NUMBER ;


v_dob DATE ;




CURSOR emp_cursor is

SELECT t.emp_no, t.fname, t.lname, t.sal , t.dob

FROM emp t;


BEGIN


v_filename := TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MI') || '.xml';


v_xml_file := UTL_FILE.fopen('XML_DIR_ALIAS', v_filename, 'W'); --XML_DIR_ALIAS is a Directory alias



--Add Version Tag

UTL_FILE.put_line(v_xml_file, '<?xml version="1.0" encoding="UTF-8"?>');




--Add top level node


UTL_FILE.put_line(v_xml_file, '<EMP>');




--Open the EMP cursor


OPEN emp_cursor;




--Loop through the emp cursor.


LOOP

FETCH emp_cursor

INTO v_emp_no, v_fname, v_lname, v_sal , v_dob;




EXIT WHEN emp_cursor%NOTFOUND;




UTL_FILE.put_line(v_xml_file, ' <EMP_RECORD >');


UTL_FILE.put_line(v_xml_file, ' <EMP_NO>' || v_emp_no || '</EMP_NO>');


UTL_FILE.put_line(v_xml_file, ' <FIRST_NAME>' || v_fname || '</FIRST_NAME>');


UTL_FILE.put_line(v_xml_file, ' <LAST_NAME>' || v_order_mode || '</LAST_NAME>');


UTL_FILE.put_line(v_xml_file, ' <SALARY>' || v_order_total || '</SALARY>');


UTL_FILE.put_line(v_xml_file, ' <DOB>' || TO_CHAR (v_dob,'YYYY-MM-DD') || '</DOB>');


UTL_FILE.put_line(v_xml_file, ' </EMP_ RECORD>');


END LOOP


--Close the EMP cursor


CLOSE emp_cursor;




UTL_FILE.put_line(v_xml_file, '</EMP>');


UTL_FILE.fclose(v_xml_file);




EXCEPTION

WHEN OTHERS THEN

raise_application_error(-22300,'Filed to Open :' || v_filename ||', Error:' || sqlcode ||':' || sqlerrm);

END CreateXMLFile;


UTL_FILE Package Exceptions:

Exception NameDescription
INVALID_PATHFile location is invalid.
INVALID_MODEThe open_mode parameter in FOPEN is invalid.
INVALID_FILEHANDLEFile handle is invalid.
INVALID_OPERATIONFile could not be opened or operated on as requested.
READ_ERROROperating system error occurred during the read operation.
WRITE_ERROROperating system error occurred during the write operation.
INTERNAL_ERRORUnspecified PL/SQL error
CHARSETMISMATCHA file is opened using FOPEN_NCHAR, but later I/O operations use nonchar functions such as PUTF or GET_LINE.
FILE_OPENThe requested operation failed because the file is open.
INVALID_MAXLINESIZEThe MAX_LINESIZE value for FOPEN() is invalid; it should be within the range 1 to 32767.
INVALID_FILENAMEThe filename parameter is invalid.
ACCESS_DENIEDPermission to access to the file location is denied.
INVALID_OFFSETCauses of the INVALID_OFFSET exception:
  • ABSOLUTE_OFFSET = NULL and RELATIVE_OFFSET = NULL, or
  • ABSOLUTE_OFFSET < 0, or
  • Either offset caused a seek past the end of the file
DELETE_FAILEDThe requested file delete operation failed.
RENAME_FAILEDThe requested file rename operation failed.