Oracle JOB scheduling

Job scheduling from Oracle 10g with dbms_scheduler

In Oracle 10g the DBMS_JOB package is replaced by the DBMS_SCHEDULER package. The DBMS_JOB package is now depricated and in Oracle 10g it's only provided for backward compatibility. From Oracle 10g the DBMS_JOB package should not be used any more, because is could not exist in a future version of Oracle.

With DBMS_SCHEDULER Oracle procedures and functions can be executed. Also binary and shell-scripts can be scheduled.

Rights

If you have DBA rights you can do all the scheduling. For administering job scheduling you need the priviliges belonging to the SCHEDULER_ADMIN role. To create and run jobs in your own schedule you need the 'CREATE JOB' privilege. 

With DBMS_JOB you needed to set an initialization parameter to start a job coordinator background process. With Oracle 10g DBMS_SCHEDULER this is not needed any more.

If you want to user resource plans and/or consumer groups you need to set a system parameter:
ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;

Getting started quickly

To quickly get a job running, you can use code like this:

begin

dbms_scheduler.create_job(

job_name => 'DEMO_JOB_SCHEDULE'

,job_type => 'PLSQL_BLOCK'

,job_action => 'begin package.procedure(''param_value''); end; '

,start_date => '01/01/2006 02:00 AM'

,repeat_interval => 'FREQ=DAILY'

,enabled => TRUE

,comments => 'Demo for job schedule.');

end;

/

This schedules a pl/sql block to be executed daily starting 1/1/2006 02:00 AM.
You can schedule things like this, but DBMS_SCHEDULER can reuse components. 

You can build a schedule using components like program, schedule, job, job class and window. We will now discuss these components in detail.

Program

The program component represents program-code that can be executed. This program code can have parameters. Code example

begin

dbms_scheduler.create_program (

program_name => 'DEMO_JOB_SCHEDULE'

,program_type => 'STORED_PROCEDURE'

,program_action => 'package.procedure'

,number_of_arguments => 1

,enabled => FALSE

,comments => 'Demo for job schedule.');

 

dbms_scheduler.define_program_argument (

program_name => 'DEMO_JOB_SCHEDULE'

,argument_position => 1

,argument_name => 'kol1'

,argument_type => 'VARCHAR2'

,default_value => 'default'

);

dbms_scheduler.enable(name => 'DEMO_JOB_SCHEDULE');

end;

/

The parameter program_type can have one of the following values: 'PLSQL_BLOCK', 'STORED_PROCEDURE','EXECUTABLE'.
dbms_scheduler also allows to execute shell scripts (Windows: *.bat files) and executables. 

Schedule

A schedule defines the frequence and date/time specifics of the start-time for the job.
example code

begin

dbms_scheduler.create_schedule(

schedule_name => 'DEMO_SCHEDULE'

, start_date => '01/01/2006 22:00:00'

, repeat_interval => 'FREQ=WEEKLY'

, comments => 'Weekly at 22:00');

END;

/

 

To drop the schedule:

begin

dbms_scheduler.drop_schedule(

schedule_name => 'DEMO_SCHEDULE'

, force => TRUE );

end;

/

Calendar expresions can have one of these values: 'Yearly','Monthly','Weekly','Daily','Hourly','Minutely','Secondely'

Job

A job defines when a specific task will be started. This can be done by assigning a program to one or more schedules (or to a specific date/time). A job can belong to only 1 job class. Code example

begin

dbms_scheduler.create_job(

job_name => 'DEMO_JOB1'

, program_name =>'DEMO_JOB_SCHEDULE'

, schedule_name =>'DEMO_SCHEDULE'

, enabled => FALSE

, comments => 'Run demo program every week at 22:00');

 

dbms_scheduler.set_job_argument_value(

job_name => 'DEMO_JOB1'

, argument_position => 1

, argument_value => 'param1');

 

dbms_scheduler.enable('DEMO_JOB1');

 

commit;

end;

/

 

 

Or start shell script

 

begin

dbms_scheduler.create_job

(

job_name => 'RUN_SHELL1',

schedule_name => 'DEMO_SCHEDULE',

job_type => 'EXECUTABLE',

job_action => '/home/test/run_script.sh',

enabled => true,

comments => 'Run shell-script'

);

end;

/

Monitoring job-scheduling

Jobs can be monitored using Oracle Enterprise Manager 10g. It's also possible to use a number of views that have been created in Oracle 10g. We will discuss some of these views here. 

To show details on job run:

select log_date

, job_name

, status

, req_start_date

, actual_start_date

, run_duration

from dba_scheduler_job_run_details

 

To show running jobs:

select job_name

, session_id

, running_instance

, elapsed_time

, cpu_used

from dba_scheduler_running_jobs;

 

To show job history:

select log_date

, job_name

, status

from dba_scheduler_job_log;

 

 

show all schedules:

select schedule_name, schedule_type, start_date, repeat_interval

from dba_scheduler_schedules;

 

show all jobs and their attributes:

select *

from dba_scheduler_jobs

 

 

show all program-objects and their attributes

select *

from dba_scheduler_programs;

 

show all program-arguments:

select *

from dba_scheduler_program_args;

Create a JOB Scheduler:

Create Job Using DBMS_SCHEDULER:

BEGIN

DBMS_SCHEDULER.CREATE_JOB (

JOB_NAME => 'JOB_CYC_CNT_RQLN',

JOB_TYPE => 'PLSQL_BLOCK',

JOB_ACTION => 'BEGIN PROC_INS_CYC_CNT_RQLN; END;',

START_DATE => SYSTIMESTAMP,

REPEAT_INTERVAL => 'FREQ=DAILY;BYHOUR=10;BYMINUTE=05',

END_DATE => TO_TIMESTAMP ('10-Sep-2099 14:10:10.123000', 'DD-Mon-YYYY HH24:MI:SS.FF'),

ENABLED => TRUE,

COMMENTS => 'CYC_CNT_RQLN');

END;

 

Query to Find the Running Jobs:

select * from user_scheduler_jobs;

 

Query to Find the Status of the Running Query:

SELECT     JOB_NAME,STATUS,

    ERROR#,

    REQ_START_DATE,

    ACTUAL_START_DATE,

    RUN_DURATION,

    ADDITIONAL_INFO

FROM     USER_SCHEDULER_JOB_RUN_DETAILS

WHERE     JOB_NAME LIKE 'JOB_CYC_CNT_RQLN';

 

Query to Drop the Running Jobs:

begin

DBMS_SCHEDULER.DROP_JOB('JOB_CYC_CNT_RQLN');

end;

 

Query to Interrupt the Running Job:

BEGIN

DBMS_SCHEDULER.STOP_JOB ('JOB_CARD_SCHED');

END;

Example For BulkCollect and For all

CREATE OR REPLACE PROCEDURE proc_forecast_copy (

p_bu VARCHAR2,

p_plnt VARCHAR2,

p_fc_no VARCHAR2,

p_user VARCHAR2,

p_out_fc OUT VARCHAR2

)

IS

CURSOR c1

IS

SELECT *

FROM mfg_sales_forecast_hd

WHERE msfhd_bu = p_bu

AND msfhd_plnt = p_plnt

AND msfhd_fcast_no = p_fc_no;

 

cr1 c1%ROWTYPE;

 

TYPE t1 IS RECORD (

msfln_bu mfg_sales_forecast_ln.msfln_bu%TYPE,

msfln_plnt mfg_sales_forecast_ln.msfln_plnt%TYPE,

msfln_fcast_no mfg_sales_forecast_ln.msfln_fcast_no%TYPE,

msfln_seq_no mfg_sales_forecast_ln.msfln_seq_no%TYPE,

msfln_prod_id mfg_sales_forecast_ln.msfln_prod_id%TYPE,

msfln_prod_rev mfg_sales_forecast_ln.msfln_prod_rev%TYPE,

msfln_sales_area mfg_sales_forecast_ln.msfln_sales_area%TYPE,

msfln_terr_id mfg_sales_forecast_ln.msfln_terr_id%TYPE,

msfln_sub_terr_id mfg_sales_forecast_ln.msfln_sub_terr_id%TYPE,

msfln_sales_person mfg_sales_forecast_ln.msfln_sales_person%TYPE,

msfln_bucket mfg_sales_forecast_ln.msfln_bucket%TYPE,

msfln_fcast_qty mfg_sales_forecast_ln.msfln_fcast_qty%TYPE,

msfln_cre_by mfg_sales_forecast_ln.msfln_cre_by%TYPE,

msfln_cre_date mfg_sales_forecast_ln.msfln_cre_date%TYPE,

msfln_upd_by mfg_sales_forecast_ln.msfln_upd_by%TYPE,

msfln_upd_date mfg_sales_forecast_ln.msfln_upd_date%TYPE,

msfln_cust_id mfg_sales_forecast_ln.msfln_cust_id%TYPE

);

 

TYPE t2 IS TABLE OF t1;

 

t3 t2;

v_fc_no mfg_sales_forecast_ln.msfln_fcast_no%TYPE;

BEGIN

SELECT MAX (NVL (msfhd_fcast_no, 0)) + 1

INTO v_fc_no

FROM mfg_sales_forecast_hd

WHERE msfhd_bu = p_bu AND msfhd_plnt = p_plnt;

 

OPEN c1;

FETCH c1 INTO cr1;

 

INSERT INTO mfg_sales_forecast_hd

(msfhd_bu, msfhd_plnt, msfhd_fcast_date, msfhd_fcast_no,

msfhd_name,

msfhd_fcast_level, msfhd_cons_flag,

msfhd_fwd_cons_days, msfhd_bkd_cons_days, msfhd_cre_by,

msfhd_cre_date, msfhd_status, msfhd_upd_by, msfhd_upd_date,

msfhd_date_from, msfhd_date_to, msfhd_name2, msfhd_fc_mthd,

msfhd_fc_prd)

VALUES (p_bu, p_plnt, SYSDATE, v_fc_no,

'Forecast for' || ' ' || TO_CHAR (SYSDATE, 'MON'),

cr1.msfhd_fcast_level, cr1.msfhd_cons_flag,

cr1.msfhd_fwd_cons_days, cr1.msfhd_bkd_cons_days, p_user,

SYSDATE, 'E', NULL, NULL,

SYSDATE, (SYSDATE + 30), cr1.msfhd_name2, cr1.msfhd_fc_mthd,

cr1.msfhd_fc_prd);

 

CLOSE c1;

 

SELECT p_bu,p_plnt,v_fc_no,msfln_seq_no, msfln_prod_id, msfln_prod_rev, msfln_sales_area,

msfln_terr_id, msfln_sub_terr_id, msfln_sales_person,

msfln_bucket, msfln_fcast_qty,p_user,sysdate,null,null, msfln_cust_id

BULK COLLECT INTO t3

FROM mfg_sales_forecast_ln

WHERE msfln_bu = p_bu AND msfln_plnt = p_plnt AND msfln_fcast_no = p_fc_no;

 

FORALL i IN 1 .. t3.COUNT

INSERT INTO mfg_sales_forecast_ln VALUES t3(i);

COMMIT;

END;

Oracle Ascii Table

Dec	Hex	Oct	Char	Description
0	0	000		null
1	1	001		start of heading
2	2	002		start of text
3	3	003		end of text
4	4	004		end of transmission
5	5	005		enquiry
6	6	006		acknowledge
7	7	007		bell
8	8	010		backspace
9	9	011		horizontal tab
10	A	012		new line
11	B	013		vertical tab
12	C	014		new page
13	D	015		carriage return
14	E	016		shift out
15	F	017		shift in
16	10	020		data link escape
17	11	021		device control 1
18	12	022		device control 2
19	13	023		device control 3
20	14	024		device control 4
21	15	025		negative acknowledge
22	16	026		synchronous idle
23	17	027		end of trans. block
24	18	030		cancel
25	19	031		end of medium
26	1A	032		substitute
27	1B	033		escape
28	1C	034		file separator
29	1D	035		group separator
30	1E	036		record separator
31	1F	037		unit separator
32	20	040		space
33	21	041	!
34	22	042	"
35	23	043	#
36	24	044	$
37	25	045	%
38	26	046	&
39	27	047	'
40	28	050	(
41	29	051	)
42	2A	052	*
43	2B	053	+
44	2C	054	,
45	2D	055	-
46	2E	056	.
47	2F	057	/
48	30	060	0
49	31	061	1
50	32	062	2
51	33	063	3
52	34	064	4
53	35	065	5
54	36	066	6
55	37	067	7
56	38	070	8
57	39	071	9
58	3A	072	:
59	3B	073	;
60	3C	074	<
61	3D	075	=
62	3E	076	>
63	3F	077	?

Dec	Hex	Oct	Char
64	40	100	@
65	41	101	A
66	42	102	B
67	43	103	C
68	44	104	D
69	45	105	E
70	46	106	F
71	47	107	G
72	48	110	H
73	49	111	I
74	4A	112	J
75	4B	113	K
76	4C	114	L
77	4D	115	M
78	4E	116	N
79	4F	117	O
80	50	120	P
81	51	121	Q
82	52	122	R
83	53	123	S
84	54	124	T
85	55	125	U
86	56	126	V
87	57	127	W
88	58	130	X
89	59	131	Y
90	5A	132	Z
91	5B	133	[
92	5C	134	\
93	5D	135	]
94	5E	136	^
95	5F	137	_
96	60	140	`
97	61	141	a
98	62	142	b
99	63	143	c
100	64	144	d
101	65	145	e
102	66	146	f
103	67	147	g
104	68	150	h
105	69	151	i
106	6A	152	j
107	6B	153	k
108	6C	154	l
109	6D	155	m
110	6E	156	n
111	6F	157	o
112	70	160	p
113	71	161	q
114	72	162	r
115	73	163	s
116	74	164	t
117	75	165	u
118	76	166	v
119	77	167	w
120	78	170	x
121	79	171	y
122	7A	172	z
123	7B	173	{
124	7C	174	|
125	7D	175	}
126	7E	176	~
127	7F	177	DEL

Import from Excel to Database

DECLARE

application client_ole2.obj_type;

workbooks client_ole2.obj_type;

workbook client_ole2.obj_type;

worksheets client_ole2.obj_type;

worksheet client_ole2.obj_type;

args client_ole2.list_type;

cell client_ole2.obj_type;

v_range client_ole2.obj_type;

num NUMBER;

col_num NUMBER := 64;

mess VARCHAR2(30000);

v_temp VARCHAR2(4000);

var_initiate VARCHAR2(50) := :file_name;

c1 VARCHAR2(1000);

v_cnt NUMBER(10);

j NUMBER;

BEGIN

SELECT COUNT(*)

INTO v_cnt

FROM user_tab_columns

WHERE table_name = :bl1.table_name;

IF :t_columns IS NOT NULL THEN

IF v_cnt <> :t_columns THEN

alert_msg(

'Columns in the Excel Spreadsheet and Table Do not Match',

'S'

);

END IF;

ELSE

alert_msg('No Columns Should not be Null', 'S');

END IF;

IF :table_name IS NULL THEN

alert_msg('Table Name Should not be Null', 'S');

END IF;

IF :file_name IS NULL THEN

alert_msg('File Name Should not be Null', 'S');

END IF;

IF :head_not = 'Y' THEN

num := 1;

END IF;

IF :head_not = 'N' THEN

num := 2;

END IF;

IF (client_ole2.issupported) THEN

application := client_ole2.create_obj('Excel.Application');

client_ole2.set_property(application, 'Visible', 'False');

workbooks := client_ole2.get_obj_property(application, 'Workbooks');

args := client_ole2.create_arglist;

client_ole2.add_arg(args, var_initiate);

workbook := client_ole2.invoke_obj(workbooks, 'Open', args);

client_ole2.destroy_arglist(args);

worksheets := client_ole2.get_obj_property(workbook, 'Worksheets');

args := client_ole2.create_arglist;

client_ole2.add_arg(args, 1);

worksheet := client_ole2.get_obj_property(worksheets, 'Item', args);

client_ole2.destroy_arglist(args);

LOOP

FOR i IN 1 .. :t_columns

LOOP

j := i;

args := client_ole2.create_arglist;

client_ole2.add_arg(args, CHR(col_num + i) || TO_CHAR(

num

));

v_range := client_ole2.get_obj_property(

worksheet,

'Range',

args

);

client_ole2.destroy_arglist(args);

mess := client_ole2.get_char_property(v_range, 'text');

c1 := c1 || ''',''' || mess;

EXIT WHEN mess IS NULL

AND i = 1;

END LOOP;

c1 := LTRIM(c1, '''');

c1 := LTRIM(c1, ',');

v_temp := 'INSERT INTO ' || :table_name || ' VALUES ( ' || c1 || ''' )';

FORMS_DDL(v_temp);

-- MESSAGE(V_TEMP);MESSAGE('');

c1 := NULL;

EXIT WHEN mess IS NULL

AND j = 1;

num := num + 1;

END LOOP;

proc_commit;

alert_msg('Successfully extracted from the excel file', 'N');

client_ole2.RELEASE_OBJ(v_range);

client_ole2.RELEASE_OBJ(worksheet);

client_ole2.RELEASE_OBJ(workbook);

client_ole2.RELEASE_OBJ(workbooks);

--client_ole2.release_obj(application);

client_ole2.invoke(application, 'Quit');

/*

args := CLIENT_OLE2.CREATE_ARGLIST;

CLIENT_OLE2.ADD_ARG(args, 0);

workbook:=CLIENT_OLE2.INVOKE_OBJ(workbook,'Close',args);

CLIENT_OLE2.DESTROY_ARGLIST(args);

CLIENT_OLE2.INVOKE(application,'Quit');

*/

ELSE

alert_msg('OLE2 NOT SUPPORTED', 'S');

END IF;

/*

EXCEPTION

WHEN CLIENT_OLE2.OLE_ERROR THEN

num:=CLIENT_OLE2.last_exception;

message('OLE Error #: ' ||num);

message(' ');

WHEN OTHERS THEN

MESSAGE('Exception');

MESSAGE(error_text);

MESSAGE(dbms_error_text);

MESSAGE(' ');*/

END;

Export to Excel From Oracle Forms

PROCEDURE display_table(

pc$tablename IN VARCHAR2

) IS

inbcols PLS_INTEGER := 0;

inbrows PLS_INTEGER := 0;

inbsheets PLS_INTEGER := 0;

lc$line VARCHAR2(32767);

lc$head VARCHAR2(4000);

lc$type VARCHAR2(4000);

c NUMBER;

d NUMBER;

col_cnt PLS_INTEGER;

rec_tab DBMS_SQL.desc_tab;

v VARCHAR2(4000);

col_num NUMBER;

source_cursor INTEGER;

result INTEGER;

lc$query VARCHAR2(1000);

lc$count VARCHAR2(1000);

lc$where VARCHAR2(1024);

lc$order VARCHAR2(1024);

lc$rowid VARCHAR2(100);

lc$image VARCHAR2(32767);

application client_ole2.obj_type;

workbooks client_ole2.obj_type;

workbook client_ole2.obj_type;

worksheets client_ole2.obj_type;

worksheet client_ole2.obj_type;

cell client_ole2.obj_type;

hfontobj2 client_ole2.obj_type;

v_interior client_ole2.obj_type;

args client_ole2.list_type;

RANGE client_ole2.obj_type;

range_col client_ole2.obj_type;

BEGIN

-- current ROW --

:GLOBAL.current_row := '0';

-- current CELL --

:GLOBAL.current_cell := '0';

IF :bl1.LIMIT IS NOT NULL THEN

lc$query := 'SELECT t.* FROM '

|| pc$tablename

|| ' t WHERE ROWNUM <= '

|| :bl1.LIMIT;

lc$count := 'SELECT Count(*) FROM '

|| pc$tablename

|| ' WHERE ROWNUM <= '

|| :bl1.LIMIT;

ELSE

lc$query := 'SELECT t.* FROM ' || pc$tablename || ' t';

lc$count := 'SELECT Count(*) FROM ' || pc$tablename;

END IF;

IF :bl1.WHERE IS NOT NULL THEN

lc$where := REPLACE(

REPLACE(REPLACE(:bl1.where, 'where', ''), 'WHERE', ''),

'Where',

''

);

lc$query := lc$query || ' AND ' || lc$where;

lc$count := lc$count || ' AND ' || lc$where;

END IF;

IF :bl1.ORDER IS NOT NULL THEN

lc$order := REPLACE(

REPLACE(

REPLACE(:bl1.ORDER, 'order by', ''),

'ORDER BY',

''

),

'Order by',

''

);

lc$query := lc$query || ' ORDER BY ' || lc$order;

lc$count := lc$count || ' ORDER BY ' || lc$order;

END IF;

-- Count the rows --

source_cursor := DBMS_SQL.open_cursor;

DBMS_SQL.parse(source_cursor, lc$count, 1);

DBMS_SQL.define_column(source_cursor, 1, v, 4000);

result := DBMS_SQL.EXECUTE(source_cursor);

IF DBMS_SQL.fetch_rows(source_cursor) > 0 THEN

DBMS_SQL.column_value(source_cursor, 1, v);

inbrows := v;

END IF;

DBMS_SQL.close_cursor(source_cursor);

---for excel--

application := client_ole2.create_obj('Excel.Application');

client_ole2.set_property(application, 'Visible', 'True');

workbooks := client_ole2.get_obj_property(application, 'Workbooks');

workbook := client_ole2.invoke_obj(workbooks, 'Add');

worksheets := client_ole2.get_obj_property(workbook, 'Worksheets');

worksheet := client_ole2.invoke_obj(worksheets, 'Add');

client_ole2.set_property(worksheet, 'Name', :t_name);

-- retrieve the columns of the query --

c := DBMS_SQL.open_cursor;

DBMS_SQL.parse(c, lc$query, 1);

d := DBMS_SQL.EXECUTE(c);

DBMS_SQL.describe_columns(c, col_cnt, rec_tab);

inbcols := 0;

FOR i IN rec_tab.FIRST .. rec_tab.LAST

LOOP

lc$head := rec_tab(i).col_name;

inbcols := inbcols + 1;

args := client_ole2.create_arglist;

client_ole2.add_arg(args, 1);

client_ole2.add_arg(args, inbcols);

cell := client_ole2.get_obj_property(worksheet, 'Cells', args);

client_ole2.destroy_arglist(args);

client_ole2.set_property(cell, 'Value', lc$head);

hfontobj2 := client_ole2.get_obj_property(cell, 'FONT');

client_ole2.set_property(hfontobj2, 'BOLD', TRUE );

client_ole2.set_property(hfontobj2, 'ITALIC', TRUE );

client_ole2.set_property(hfontobj2, 'Name', 'Times New Roman');

client_ole2.set_property(hfontobj2, 'Size', 10);

client_ole2.set_property(hfontobj2, 'ColorIndex', 6); --foreground color

v_interior := client_ole2.get_obj_property(cell, 'Interior'); --background

client_ole2.set_property(v_interior, 'ColorIndex', 10); --background color Number

client_ole2.RELEASE_OBJ(v_interior);

client_ole2.RELEASE_OBJ(hfontobj2);

client_ole2.RELEASE_OBJ(cell);

END LOOP;

DBMS_SQL.close_cursor(c);

--------------------

-- Get the data --

--------------------

source_cursor := DBMS_SQL.open_cursor;

DBMS_SQL.parse(source_cursor, lc$query, 1);

-- Define the columns --

FOR i IN 1 .. rec_tab.LAST

LOOP

DBMS_SQL.define_column(source_cursor, i, v, 4000);

END LOOP;

result := DBMS_SQL.EXECUTE(source_cursor);

-- Fetch the rows from the source query --

inbrows := 1;

inbsheets := 1;

LOOP

IF DBMS_SQL.fetch_rows(source_cursor) > 0 THEN

----------------------------------

-- get column values of the row --

----------------------------------

--if iNbrows >= 2 then

IF inbrows >= 65536 THEN

RANGE := client_ole2.invoke_obj(worksheet, 'UsedRange');

range_col := client_ole2.invoke_obj(RANGE, 'Columns');

client_ole2.invoke(range_col, 'AutoFit');

client_ole2.RELEASE_OBJ(RANGE);

client_ole2.RELEASE_OBJ(range_col);

inbrows := 2;

worksheet := client_ole2.invoke_obj(worksheets, 'Add');

client_ole2.set_property(

worksheet,

'Name',

:t_name || inbsheets

);

c := DBMS_SQL.open_cursor;

DBMS_SQL.parse(c, lc$query, 1);

d := DBMS_SQL.EXECUTE(c);

DBMS_SQL.describe_columns(c, col_cnt, rec_tab);

inbcols := 0;

FOR i IN rec_tab.FIRST .. rec_tab.LAST

LOOP

lc$head := rec_tab(i).col_name;

inbcols := inbcols + 1;

args := client_ole2.create_arglist;

client_ole2.add_arg(args, 1);

client_ole2.add_arg(args, inbcols);

cell := client_ole2.get_obj_property(

worksheet,

'Cells',

args

);

client_ole2.destroy_arglist(args);

client_ole2.set_property(cell, 'Value', lc$head);

hfontobj2 := client_ole2.get_obj_property(

cell,

'FONT'

);

client_ole2.set_property(hfontobj2, 'BOLD', TRUE );

client_ole2.set_property(hfontobj2, 'ITALIC', TRUE );

client_ole2.set_property(

hfontobj2,

'Name',

'Times New Roman'

);

client_ole2.set_property(hfontobj2, 'Size', 10);

client_ole2.set_property(hfontobj2, 'ColorIndex', 6); --foreground color

v_interior :=

client_ole2.get_obj_property(cell, 'Interior'); --background

client_ole2.set_property(

v_interior,

'ColorIndex',

10

); --background color Number

client_ole2.RELEASE_OBJ(v_interior);

client_ole2.RELEASE_OBJ(hfontobj2);

client_ole2.RELEASE_OBJ(cell);

END LOOP;

DBMS_SQL.close_cursor(c);

inbsheets := inbsheets + 1;

ELSE

inbrows := inbrows + 1;

END IF;

FOR i IN 1 .. rec_tab.LAST

LOOP

DBMS_SQL.column_value(source_cursor, i, v);

lc$line := NVL(v, ' ');

args := client_ole2.create_arglist;

client_ole2.add_arg(args, inbrows);

client_ole2.add_arg(args, i);

cell := client_ole2.get_obj_property(

worksheet,

'Cells',

args

);

client_ole2.destroy_arglist(args);

client_ole2.set_property(cell, 'Value', lc$line);

END LOOP;

ELSE

RANGE := client_ole2.invoke_obj(worksheet, 'UsedRange');

range_col := client_ole2.invoke_obj(RANGE, 'Columns');

client_ole2.invoke(range_col, 'AutoFit');

client_ole2.RELEASE_OBJ(RANGE);

client_ole2.RELEASE_OBJ(range_col);

EXIT;

END IF;

END LOOP;

DBMS_SQL.close_cursor(source_cursor);

END;

Example for DBMS_SQL

CREATE OR REPLACE PROCEDURE PROC_EVENT_TAB_VALUE_MAIL

(

P_BU VARCHAR2,

P_ALERT_ID VARCHAR2,

P_QUERY VARCHAR2,

P_MAIL_COL VARCHAR2,

P_REP_FLG VARCHAR2,

P_MAIL_ID OUT VARCHAR2,

P_VALUE OUT VARCHAR2

)

IS

Cursor c1 is

SELECT alrmap_filed_id,alrmap_filed_column

FROM alert_mail_attach_params

WHERE alrmap_bu = P_BU

AND alrmap_alert_id=P_ALERT_ID

AND alrmap_filed_id <> 'BU';

source_cursor NUMBER;

v VARCHAR2(4000);

tab_col VARCHAR2(4000);

rec_tab dbms_sql.desc_tab;

col_cnt PLS_INTEGER;

result NUMBER;

c NUMBER;

d NUMBER;

LC$Query VARCHAR2(4000):=P_QUERY;

LC$Line VARCHAR2(4000);

LC$TAB VARCHAR2(4000);

BEGIN

dbms_output.put_line(LC$Query);

c := dbms_sql.open_cursor;

dbms_sql.parse(c, LC$Query , 1);

d := dbms_sql.EXECUTE(c);

dbms_sql.describe_columns(c, col_cnt, rec_tab);

source_cursor := dbms_sql.open_cursor;

dbms_sql.parse(source_cursor, LC$Query, 1);

FOR i IN 1 .. rec_tab.last LOOP

dbms_sql.DEFINE_COLUMN(source_cursor, i, v,4000);

END LOOP ;

result := dbms_sql.EXECUTE(source_cursor);

LOOP

IF dbms_sql.FETCH_ROWS(source_cursor)>0 THEN

FOR i IN 1.. rec_tab.last LOOP

dbms_sql.COLUMN_VALUE(source_cursor, i, v);

LC$Line :=LC$Line||' '||NVL(v,' ') ;

IF REC_TAB(i).col_name=P_MAIL_COL THEN

P_MAIL_ID:=NVL(v,' ');

END IF;

IF P_REP_FLG='Y' THEN

FOR CR1 IN C1 LOOP

IF REC_TAB(i).col_name=CR1.alrmap_filed_column THEN

UPDATE ALERT_MAIL_ATTACH_PARAMS

SET ALRMAP_COL_VALUE=NVL(v,' ')

WHERE ALRMAP_BU = P_BU

AND ALRMAP_ALERT_ID=P_ALERT_ID

AND ALRMAP_FILED_COLUMN=CR1.ALRMAP_FILED_COLUMN;

END IF;

END LOOP;

END IF;

END LOOP ;

LC$TAB:=LC$TAB||CHR(10)||LC$Line;

ELSE

EXIT;

END IF;

END LOOP;

P_VALUE:=LC$TAB;

dbms_sql.close_cursor(source_cursor);

END;

/