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)

How to run a Windows or UNIX command using PLSQL code

This can be done using HOST command provided by the PLSQL code:

Example for the same:

DECLARE

v_win_command VARCHAR2 (100);

BEGIN

v_win_command:= 'md c:\temp\myreports';

-- (MD IS THE COMMAND USED IN WINDOWS TO CREATE DIRECTORY/FOLDER)

HOST (v_win_command, no_screen);

END;

How to run the report from forms (using cgicmd.dat):

In Application server:

There will be a cgicmd.dat file in below path of oracle

C:\oracle\FRHome_1\reports\conf

Add below line in the cgicmd.dat file:

EMP_REP_KEY: USERID=USER5/USER5@TESTDB DESFORMAT=PDF DESTYPE=CACHE REPORT='C: \TEMP\REPORTS\EMPDEPT_REP.rdf%*

In Forms:

Call the Report using

web.show_document (:global.report_server_url||'?emp_rep_key&where_condition= where dept_no = 10’);

: GLOBAL.REPORT_SERVER_URL – report server URL

(Ex: http://122.22.222.332:port_no/Reports/rwservlet)

How to run the report from forms (using lexical parameters in reports)

In Reports:
My Query:
“SELECT * FROM EMP
&WHERE_CONDITION;
Here in the above Query “WHERE_CONDITION” is the lexical parameter.
& specifies the Lexical parameter.
In Forms:
Call the Procedure REPORT_PROC
REPORT_PROC (P_WHERE_CONDITION VARCHAR2, P_REP_NAME VARCHAR2, P_FILENAME VARCHAR2 DEFAULT NULL)
P_WHERE_CONDITION is the where condition which you want to pass to the Report.
P_REP_NAME is the report name which you are calling from the form.(Entire path where the report is saved )
P_FILENAME is the file name which is given to report when it gets generated.
For Example:
REPORT_PROC (‘where dept_no = 10’,
’C: \TEMP\REPORTS\EMPDEPT_REP’,
’EMPDEPT_FILE1’)
(NOTE: Form should have the Report object created – here For Example: REPORT_OBJ)
PROCEDURE REPORT_PROC (P_WHERE_CONDITION VARCHAR2, P_REP_NAME VARCHAR2, P_FILENAME VARCHAR2 DEFAULT NULL) IS
REPORT_ID REPORT_OBJECT;
V_REPORT VARCHAR2 (50):= 'REPORT_OBJ';
V_PL_NAME VARCHAR2 (20):= 'REPORT_PARAM_LIST';
V_DUMMY NUMBER;
PL_ID PARAMLIST;
HIDDEN_ACTION VARCHAR2 (2000);
FILE_PATH VARCHAR2 (100);
V_REP VARCHAR2 (200);
REP_STATUS VARCHAR2 (200);
BEGIN
REPORT_ID:= FIND_REPORT_OBJECT (V_REPORT);
IF ID_NULL (REPORT_ID) THEN
SET_ALERT_PROPERTY ('ERROR_ALT', TITLE, 'Failed to run report!');
SET_ALERT_PROPERTY (‘ERROR_ALT', ALERT_MESSAGE_TEXT,'Can not find Report...');
V_DUMMY:= SHOW_ALERT ('ERROR_ALT');
END IF;
--Creating the parameter
PL_ID:= GET_PARAMETER_LIST (V_PL_NAME);
IF ID_NULL (PL_ID) THEN
PL_ID:= CREATE_PARAMETER_LIST (V_PL_NAME);
IF ID_NULL (PL_ID) THEN
MESSAGE ('Error creating parameter list '||V_PL_NAME);
RAISE FORM_TRIGGER_FAILURE;
END IF;
ELSE
MESSAGE ('Parameter list '||V_PL_NAME||' already exists!');
RAISE FORM_TRIGGER_FAILURE;
END IF;
--Adding the Parameters
ADD_PARAMETER (PL_ID, 'WHERE_CONDITION', TEXT_PARAMETER, P_WHERE_CONDITION);
ADD_PARAMETER (PL_ID, 'PARAMFORM', TEXT_PARAMETER, 'NO');
SET_REPORT_OBJECT_PROPERTY (REPORT_ID, REPORT_EXECUTION_MODE, RUNTIME);
SET_REPORT_OBJECT_PROPERTY (REPORT_ID, REPORT_COMM_MODE, SYNCHRONOUS);
SET_REPORT_OBJECT_PROPERTY (REPORT_ID, REPORT_DESTYPE, FILE);
SET_REPORT_OBJECT_PROPERTY (REPORT_ID, REPORT_DESFORMAT,'pdf'); SET_REPORT_OBJECT_PROPERTY (REPORT_ID, REPORT_DESNAME, FILE_PATH||'\'||P_FILENAME);
SET_REPORT_OBJECT_PROPERTY (REPORT_ID, REPORT_FILENAME, P_REP_NAME); SET_REPORT_OBJECT_PROPERTY (REPORT_ID, REPORT_SERVER,:GLOBAL.REP_SERVER);
V_REP:= RUN_REPORT_OBJECT (REPORT_ID, PL_ID);
IF V_REP IS NULL THEN
MESSAGE ('RUN_REPORT_OBJECT is NULL');
MESSAGE ('RUN_REPORT_OBJECT is NULL');
END IF;
REP_STATUS:= REPORT_OBJECT_STATUS (V_REP);
IF REP_STATUS IS NULL THEN
MESSAGE ('REPORT_OBJECT_STATUS is NULL');
MESSAGE ('REPORT_OBJECT_STATUS is NULL');
END IF;
WHILE REP_STATUS IN ('RUNNING','OPENING_REPORT','ENQUEUED')
LOOP
REP_STATUS:= REPORT_OBJECT_STATUS (V_REP);
END LOOP;
IF REP_STATUS = 'FINISHED' THEN
DESTROY_PARAMETER_LIST (PL_ID);
MESSAGE ('Report Generated Successfully');
ELSE
MESSAGE ('Error Generating Report');
END IF;
END;
FILE_PATH – path where you want the report file to get saved.
:GLOBAL.REP_SERVER – Report sever name (ex: rep_A2MD029919991_FRHOME1

Welcome to EmpDept

Entire Oracle learning lies within two tables, namely Emp and Dept. And hence the name of my blog happens to be empdept.blogpot.com