Saturday, May 12, 2012


Optimize Oracle Query
We can optimized/tune a query by following way
SELECT * FROM emp e, dept d WHERE e.deptno = d.deptno AND e.ename = 'SMITH';
The DBMS_XPLAN.DISPLAY function can accept 3 optional parameters
SELECT /*+ FIRST_ROWS(10) */ * FROM employees;=>First 10 rows in employee table

How to bind array in oracle

Oracle Procedure contain Associative Array Call from Dot Net

Looping Associative array In Oracle
    How to Defined an array IN Oracle    ==>

       TYPE prod_name IS RECORD(product_id varchar2(500),product_desc varchar2(500),product_brand varchar2(500));
type prod_prod_grp_1_assoc is table of prod_name index by binary_integer;
type prod_prod_grp_2_assoc is table of prod_name index by binary_integer;

prod_product_group_1 prod_prod_grp_1_assoc;

   prod_product_group_1(1).product_id := 'ProductID1';
   prod_product_group_1(1).product_desc := 'Product Description';
   prod_product_group_1(1).product_brand := 'Product Brand'; 
   prod_product_group_1(2).product_id := 'ProductID2';
   . . . . . . . . . . . . . . .   
   prod_product_group_1(3).product_id := 'ProductID3';
   . . . . . . . . . . . . . . .
   prod_product_group_1(4).product_id := 'ProductID4';
   . . . . . . . . . . . . . . .
   prod_product_group_1(5).product_id := 'ProductID5';
    . . . . . . . . . . . . . . .
   prod_product_group_1(6).product_id := 'ProductID6';
    . . . . . . . . . . . . . . .

   for i in 1 .. 6 LOOP
       dbms_output.put_line (prod_product_group_1(i).product_id);
       dbms_output.put_line (prod_product_group_1(i).product_desc);
       dbms_output.put_line (prod_product_group_1(i).product_brand);

How to defiend procedure in oracle.==>

In the scope of the package defined the t_associative_id

type t_associative_id is table of VARCHAR2(4000) index by binary_integer;

NOTE: Here one thing we need to remember that if we pass associative array as VARCHAR2[i.e => t_associative_id .OracleDbType = OracleDbType.Varchar2;] from .net code then we need to defiened the associative array type as above but if we pass associative array as Number[i.e:=> t_associative_id.OracleDbType = OracleDbType.Int64/Int32;] then we need to defiend the associative array type as following
type t_associative_id is table of Number index by binary_integer;

PART 1:Signature of the procedure
PROCEDURE testExample(v_id IN v_table.v_id%type, v_age IN NUMBER, v_name IN VARCHAR2, v_array_id IN t_associative_id, v_retcode OUT NUMBER, v_reterror OUT VARCHAR2) AS

PART2:Defined variable of the procedure

v_var1 VARCHAR2(50); =>Defiend a varchar type variable. v_var2 NUMBER(5);=>Defiend a number type variable. v_tmp_id v_table.v_id%type;=>Defiened a type which are equivalent to the type of v_id field of v_table table. c_select SYS_REFCURSOR;=>Defiend a cursor. v_query VARCHAR2(1000);

PART3:Body of the procedure

v_retcode :=0; v_reterror :=NULL;

--Now looping through associative array

FOR I in .. t_associative_id LOOP=>Looping through the associative array.
v_query:=”SELECT fieldval1,filedval2,filedval3 FROM v_table WHERE v_id=”|| t_associative_id (i);

OPEN c_select FOR v_query;=>Looping through the cursor.
FETCH c_select
INTO fieldval1,fieldval2,fieldval3;
INSERT INTO t_table(field1,field2,field3)
CLOSE c_select;=>Close the cursor.
v_retcode :=SQLCODE;=>Return the exception sql code.
V_reterror :=SQLERRM;=>Return the exception sql message.
ROLLBACK;=>Rollback the transaction.
END testExample=>End the procedure.

How to test the ORACLE PROCEDURE
To test the oracle procedure we need to right click on the procedure and click on the test link. Now we need to pass the parameter value which is required to call the procedure.

We can pass the associative array value in the following way :

t_associative_id (1):='12';=>First array element .
t_associative_id (2) :='23';=>Second array element.
Timestamp Conversion In oracle

1>For converting timezone format
SELECT TO_TIMESTAMP_TZ('2012-02-25 03:15:04.062827 +00:00', 'yyyy-mm-dd HH24:MI:SS.FF6 TZH:TZM') FROM dual;

2>For converting millisecond format SELECT To_timestamp('2012-02-25 03:15:04.062827', 'yyyy-mm-dd HH24:MI:SS.FF6') FROM dual;

For Converting milliseconds format

Oracle Troubleshooting
ora-01031 insufficient privileges while creating a view
1>Open the command prompt and type=>sqlplus / as sysdba
3>grant create view to scott;

ORA-28000: the account is locked
After oracle installation we may get the account locked error.TO resolve this error we need to logging as database administrator and unlock the user account.
1>Open the command prompt and type=>sqlplus / as sysdba
2>alter user scott account unlock;
3>grant connect,resource to scott;
ORACLE Few Required query
select * from dba_all_tables
select * from dba_users
select * from all_objects
select * from V$DATAFILE
select * from V$session
select * from v$
select * from gv$session s
select * from gv$process

How to Use Telerik Control

Telerik control are very useful in web application to make the application more pleasant and good look and feel.To use Telerik control we need to do the following things..

1>First open the visual studio and create a Web Application
2>Now choose the ASP.NET Web Application
3>Now reference the telerik related dll
4>Now register the telerik control
5>Added the script manager right way.Here FIG 1 is the right way and FIG 2 is wrong way..

6>Now check the script manager.
7>Now Add smart tag.
8>Now add the telerik web resource
9>When the telerik web resource is added succefully then we get the following message
10>When telerik web resources added successfully then config file also will be updated.
Telerik Error
If we not provide the design file in web.config then ti will return error.
We need to add the design file into web.config file.

Version Information for ScriptReferenceBase: "Supported in: 3.5 SP1". It's a new class for Service Pack 1. It doesn't exist in .NET 3.5 and the documentation for ScriptReference.So we need to install 2008 Service pack 1 and the problem will be solved.

Saturday, February 25, 2012

How to create windows service & Setup file

Windows service is a service which enable us to create a long running executable application.Sometimes it is needed that one process will be executed in daily basis in that situation we can use windows service.To create a windows service we need to follow the following steps..
1>Create a windows service application by navigating ..
File->New->Project[See FIG-1]

2>Rename the service1 to appropiate service name.

3>Now open the service in design view.
4>Click on properties window.
5>Change the Name and service name property with the desired name.

6>Now open the service in design mode.
7>Right click on the design mode and click on Add Installer.

8>Two file[serviceProcessInstaller1,serviceInstaller] will be created.

9>Right click on serviceProcessInstaller and click on properties and change the account to LocalSysytem.

10>Now right click on serviceInstaller and change the SratType Property to Automatic and DisplayName to Service name

Now to create the setup file we need to follow the following steps..
1>Create a setup project in the same solution.

2>We can see Output file system structure by navigating the steps
Right CLick on Setup Project->View->File Sysytem

3>From the File system structure right click on the Application Folder->
Add->Project Output-Select Primary output->OK

4>Rightclick on set up project->View->Custom Action

5>In custom action interface Right click on Install->Add Custom Action
->Double click on application folder->Select The Primary output file

6>DO the step 5 for Uninstall
In custom action interface Right click on Uninstall->Add Custom Action
->Double click on application folder->Select The Primary output file
7>Open the ProjectInstaller.cs and add following line

8>Now build the project and build the set up project
Now Add code to test the service.
1>Open the service by right click on it and select view code.

2>Add the following code to test the service.
3>Right click on setup->Install->Specify Install Directory->Next->Service will be install into the windows service
4>To view the service RUN->Type->Services.msc
we can find our service