Temporary Tables Concept

http://www.codeproject.com/Articles/42553/Quick-Overview-Temporary-Tables-in-SQL-Server-2005

How to Alter V$PARAMETER

ALTER SYSTEM set utl_file_dir = 'D:\temp' SCOPE = SPFILE;

Script to grant permission to execute Java code in

GRANT JAVASYSPRIV SCOTT;

Max size of Varchar2 in SQL and PLSQL

In SQL max size of VARCHAR2 - 1..4000bytes

In PLSQL max size of VARCHAR2 - 1 .. 32767.

Creating a new Excel sheet/append contents in existing Excel sheet using OLE2 package

Package specification:

PACKAGE Export2Excel IS
Application ole2.obj_type;
WorkBooks ole2.obj_type;
Workbook ole2.obj_type;
Worksheets ole2.obj_type;
Worksheet ole2.obj_type;
V_Border ole2.obj_type;
ExcelFont ole2.obj_type;
Cell ole2.obj_type;
Arg ole2.list_type;
ccol ole2.obj_type;
rrow ole2.obj_type;
Filename Varchar(100);

procedure OpenApplication(Filename varchar default 'New' );
Procedure OpenWorksheet(WorksheetName Varchar default 'New' );
procedure WriteIntoCell(rowno number,colno number,Fvalue varchar,typ varchar,border boolean,font boolean);
procedure MergeCells(Rstarting char,Rending char,Cstarting char,Cending char);
function ReadFromCell(rowno number,colno number,dtype Char) Return Varchar;
Procedure VisibleApps(Val Boolean);
Procedure SaveFile(Filename varchar);
Procedure ReleaseWorksheet(Filename varchar default 'New');
procedure CloseApplication;

END;

Package Body

PACKAGE BODY Export2Excel IS
Procedure OpenApplication(Filename varchar default 'New') Is
Begin
Application := ole2.create_obj('Excel.Application');
Workbooks := ole2.get_obj_property(Application,'Workbooks');

--Open Exisiting Excel File
If Filename != 'New' Then
Arg := ole2.create_arglist;
ole2.add_arg(Arg,Filename);
Workbook := ole2.get_obj_property(Workbooks,'Open',Arg);
ole2.destroy_arglist(arg);
Else
Workbook := ole2.invoke_obj(workbooks,'Add');
End If;
End OpenApplication;

Procedure OpenWorksheet(Worksheetname varchar default 'New') is
Begin
--Open Exisiting Excel Sheet
If Worksheetname != 'New' Then
Arg := ole2.create_arglist;
ole2.add_arg(Arg,Worksheetname);
WorkSheet := ole2.get_obj_property(Workbook,'WorkSheets',Arg);
ole2.destroy_arglist(arg);
Else
WorkSheets := ole2.get_obj_property(Workbook,'WorkSheets');
Worksheet := ole2.invoke_obj(Worksheets,'Add');
End If;
End OpenWorksheet;


Procedure WriteIntoCell(rowno number,colno number,Fvalue varchar,typ varchar,border boolean, Font Boolean) Is
Begin
Arg := ole2.create_arglist;
ole2.add_arg(Arg,rowno);
ole2.add_arg(Arg,colno);
Cell := ole2.get_obj_property(WorkSheet,'Cells',Arg);
ole2.destroy_arglist(arg);



If typ = 'CHAR' Then
If Substr(Fvalue,1,1) = '0' Then
ole2.Set_property(Cell,'Value',''''||Fvalue);
Else
ole2.Set_property(Cell,'Value',Fvalue);
End if;
ole2.Set_property(Cell,'NumberFormat','@');
Else
If typ = 'NUMBER' Then
ole2.Set_property(Cell,'Value',Fvalue);
ole2.Set_property(Cell,'NumberFormat','####0.00');
Else
ole2.Set_property(Cell,'Value',Fvalue);
End if;
End if;

If substr(Fvalue,3,1) in ('-','/','.') and substr(Fvalue,7,1) in ('-','/','.') Then
ole2.Set_property(Cell,'NumberFormat','d-mmm-yyyy');
End If;



If Font Then
ExcelFont := ole2.get_obj_property(Cell,'Font');
ole2.Set_property(ExcelFont,'Bold','True');
ole2.Set_property(ExcelFont,'Size',10);
ole2.release_obj(ExcelFont);
End If;

If Border Then
V_border := ole2.get_obj_property(Cell,'Borders');
ole2.Set_property(V_border,'LineStyle',1);
ole2.release_obj(V_border);
End If;

arg := ole2.create_arglist;
ole2.add_arg(arg,colno);
ccol := ole2.get_obj_property(worksheet,'Columns', arg);
ole2.destroy_arglist(arg);
ole2.invoke(ccol,'Autofit');


ole2.release_obj(cell);
End WriteIntoCell;

Procedure MergeCells (Rstarting char,Rending char,Cstarting char,Cending char) is
temp1 varchar2(20);
Begin
arg := ole2.create_arglist;
ole2.add_arg(arg,Cstarting||':'||Cending);
ccol := ole2.get_obj_property(worksheet,'Columns', arg);
ole2.destroy_arglist(arg);

arg := ole2.create_arglist;
ole2.add_arg(arg,Rstarting||':'||Rending);
rrow := ole2.get_obj_property(ccol, 'Rows', arg);
ole2.destroy_arglist(arg);
ole2.invoke(rrow, 'Merge');

Arg := ole2.create_arglist;
ole2.add_arg(Arg,Rstarting);
rrow := ole2.get_obj_property(WorkSheet,'Rows',Arg);
ole2.destroy_arglist(arg);
ole2.set_property(rrow,'RowHeight',30);

Arg := ole2.create_arglist;
ole2.add_arg(Arg,Rstarting);
ole2.add_arg(Arg,Cstarting);
Cell := ole2.Get_Obj_Property(Worksheet,'Cells',Arg);
ole2.destroy_arglist(arg);
ole2.set_property(Cell,'Wraptext','true');
ole2.set_property(Cell,'HorizontalAlignment',-4108);
ole2.set_property(Cell,'VerticalAlignment',-4108);

End;

Function ReadFromCell(rowno number,colno number,dtype char) Return Varchar is
Begin
Arg := ole2.create_arglist;
ole2.add_arg(Arg,rowno);
ole2.add_arg(Arg,colno);
Cell := ole2.get_obj_property(WorkSheet,'Cells',Arg);
If dtype = 'NUMBER' Then
Return(ole2.get_num_property(Cell,'Value'));
Elsif dtype = 'CHAR' Then
Return(ole2.get_char_property(Cell,'Value'));
Else
Return(ole2.get_char_property(Cell,'Text'));
End If;
End ReadFromCell;


Procedure VisibleApps(Val Boolean) Is
Begin
ole2.Set_property(Application,'Visible',val);
End;

Procedure SaveFile(Filename varchar) Is
Begin
Arg := ole2.create_arglist;
ole2.add_arg(Arg,Filename);
ole2.set_property(workbook,'Save',Arg);
ole2.destroy_arglist(arg);
End SaveFile;

Procedure ReleaseWorksheet(Filename varchar default 'New') Is
Begin
ole2.release_obj(Worksheet);
If Filename = 'New' Then
ole2.release_obj(WorkSheets);
End If;
end;

Procedure CloseApplication Is
Begin
ole2.release_obj(Workbook);
ole2.release_obj(Workbooks);
ole2.invoke(Application,'Quit');
ole2.release_obj(Application);
End CloseApplication;
END;

Procedure to Write new Excel sheet

PROCEDURE WRITENEW IS
BEGIN
Export2Excel.OpenApplication(
'D:\myforms\excelexport.xls'--Existing xls file name with complete path
);
Export2Excel.OpenWorksheet(
'Sheet1'--Existing worksheet name in the excel file
);
Export2Excel.WriteIntoCell(
1,--rowno
1,--colno
'character type value',--Fvalue
'CHAR',--typ
false,--border
false--Font
);
Export2Excel.WriteIntoCell(
1,--rowno
2,--colno
'character type value - with border',--Fvalue
'CHAR',--typ
true,--border
false--Font
);
Export2Excel.WriteIntoCell(
1,--rowno
3,--colno
'character type value - with border & font set',--Fvalue
'CHAR',--typ
true,--border
true--Font
);
Export2Excel.WriteIntoCell(
2,--rowno
1,--colno
'Number type value',--Fvalue
'NUMBER',--typ
false,--border
false--Font
);
Export2Excel.WriteIntoCell(
3,--rowno
1,--colno
'27-MAY-2011',--Fvalue
'DATE',--typ
false,--border
false--Font
);
Export2Excel.SaveFile(
'D:\myforms\excelexport.xls'--Existing xls file name with complete path
);
Export2Excel.ReleaseWorksheet(
'D:\myforms\excelexport.xls'--Existing xls file name with complete path
);
Export2Excel.CloseApplication;
END;

Procedure to Append contents to existing Excel sheet

PROCEDURE WRITEAPPEND IS
L_rowno NUMBER := 1;
L_colno NUMBER := 1;
BEGIN
Export2Excel.OpenApplication(
'D:\myforms\excelexport.xls'--Existing xls file name with complete path
);
Export2Excel.OpenWorksheet(
'Sheet1'--Existing worksheet name in the excel file
);
WHILE Export2Excel.ReadFromCell(L_rowno ,L_colno ,NULL) != ' '
LOOP
MESSAGE(Export2Excel.ReadFromCell(L_rowno ,L_colno ,NULL));MESSAGE('');
L_rowno := L_rowno + 1;
END LOOP;
L_rowno := L_rowno + 1;
Export2Excel.WriteIntoCell(
L_rowno,--rowno
1,--colno
'character type value',--Fvalue
'CHAR',--typ
false,--border
false--Font
);
Export2Excel.WriteIntoCell(
L_rowno,--rowno
2,--colno
'character type value - with border',--Fvalue
'CHAR',--typ
true,--border
false--Font
);
Export2Excel.WriteIntoCell(
L_rowno,--rowno
3,--colno
'character type value - with border & font set',--Fvalue
'CHAR',--typ
true,--border
true--Font
);
L_rowno := L_rowno + 1;
Export2Excel.WriteIntoCell(
L_rowno,--rowno
1,--colno
'Number type value',--Fvalue
'NUMBER',--typ
false,--border
false--Font
);
L_rowno := L_rowno + 1;
Export2Excel.WriteIntoCell(
L_rowno,--rowno
1,--colno
'27-MAY-2011',--Fvalue
'DATE',--typ
false,--border
false--Font
);
Export2Excel.SaveFile(
'D:\myforms\excelexport.xls'--Existing xls file name with complete path
);
Export2Excel.ReleaseWorksheet(
'D:\myforms\excelexport.xls'--Existing xls file name with complete path
);
Export2Excel.CloseApplication;
END;

INTERVIEW QUESTIONS - PART 2

9. Find out nth highest salary from emp table
SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B
WHERE a.sal<=b.sal);
Enter value for n:
2 SAL --------- 3700

10. Explicit Cursor attributes
There are four cursor attributes used in Oracle cursor_name%Found, cursor_name%NOTFOUND, cursor_name%ROWCOUNT, cursor_name%ISOPEN

11. Implicit Cursor attributes
Same as explicit cursor but prefixed by the word SQL SQL%Found, SQL%NOTFOUND, SQL%ROWCOUNT, SQL%ISOPEN Tips : 1. Here SQL%ISOPEN is false, because oracle automatically closed the implicit cursor after executing SQL statements. 2. All are Boolean attributes.

12.Display the number value in Words
SQL> select sal, (to_char(to_date(sal,'j'), 'jsp'))
from emp;
the output like,
SAL (TO_CHAR(TO_DATE(SAL,'J'),'JSP'))
--------- -----------------------------------------------------
800 eight hundred
1600 one thousand six hundred
1250 one thousand two hundred fifty
If you want to add some text like,
Rs. Three Thousand only.
SQL> select sal "Salary ",
(' Rs. ' (to_char(to_date(sal,'j'), 'Jsp')) ' only.'))
"Sal in Words" from emp
/

13. Display Odd/ Even number of records
Odd number of records:
select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);
1
3
5
Even number of records:
select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp)
2
4
6

Interview Questions

1. What is difference between a PROCEDURE & FUNCTION?
a) A function can be called from SQL statements and queries while procedure can be called in a begin end block only.
b) In case of function, it must have return type. In case of procedure, it may or may not have return type.
c) Functions and Procedures both can return values using OUT parameter.But Functions has return clause to return value and Procedures does not have return statement.
d) Function mainly used for calculation purpose. Procedure used to perform DML operation.

2. State the difference between implicit and explicit cursors.
a) Implicit cursor: It is automatically created by oracle for all sql dml statements including the query that returns one row.
b) Explicit cursor: These are created and managed by the user and also used for multi row select statement.

3. Explian rowid,rownum?What are the pseduocolumns we have?
a) ROWID : Every record in a database is uniquely identified by system generated value called Rowid. It Is a 18 character hexma decimal value. These Rowid's are physically existence.
b) ROWNUM: It is a pseduocolumn which generates the sequence of numeric values based on the position of the records in the output. These ROWNUM'S logically generated.
c) NEXTVAL,CURRVAL,SYSDATE,LEVEL ARE PSEDUOCOLUMNS

4. What are advantages of Stored Procedures?
a) Easy maintenance
b) Improved data security and integrity
c) Improved performance
d) Modularity, Reusability, one time compilation.
e) Faster in execution: As Procedure is a complied schema object and stored in database, hence it takes less time to execution.
f) By writing procedure redundant coding can be avoid, increasing productivity.
g) The amount of memory used reduces as SP has shared memory capability. Once a copy of a SP is opened in the oracle engine's memory, other user who has appropriate permission may access it when required.

5. What are the two parts of a procedure
a) Declaration and execution (Exception Handling).

6. What is difference between a Cursor declared in a procedure and Cursor declared in a package specification?
A cursor declared in a package specification is global and can be accessed by other procedures or functions in a package.A cursor declared in a procedure is local to the procedure that cannot be accessed by other procedures.

7. Name the tables where characteristics of Package, procedure and functions are stored?
a) USER_OBJECTS
b) USER_SOURCE
c) USER_ERROR.
d) USER_PROCEDURES


8. What is Overloading of procedures?
a) Same procedure name with different parameters called procedure overloading; parameters may different by their data types, sequence and position.eg1:get_employee(Emp_id number);get_employee(Emp_id Varchar(20));eg2:get_employee(ID number, name char(20))get_employee(ID Number, name char(20), dept char(20))get_employee(name char(20), dept char(20),ID Number)