| Run set of procedures automatically [message #609106] |
Sun, 02 March 2014 05:26  |
meteo
Messages: 89
Registered: April 2010
Location: beirut
|
Member |
|
|
i create auto_conn.fmb file that contains
a push button called start
i also create a set of procedures which are well executed
these procedures' are:
p99_1_Exp_Metar_ClimData_Item1;
p99_2_Exp_Metar_ClimData_Item2;
p99_3_Exp_Metar_ClimData_Item3;
my question :
i need to write a trigger when button pressed under the start button so as these mentioned procedures will be executed automatically each 15 minute (one quarter hour) for 2 years.
any help i appreciate it .
thank you
|
|
|
|
|
|
| Re: Run set of procedures automatically [message #609137 is a reply to message #609108] |
Mon, 03 March 2014 03:54  |
meteo
Messages: 89
Registered: April 2010
Location: beirut
|
Member |
|
|
first thank you Mr
i appreciate you help
about the DBMS_SCHEDULER it did not go with Oracle9i Enterprise edition Release 9.2.0.1.0 that i am using, instead i use DBMS_JOB.
when button pressed (start) i write the following block
DECLARE
start_app_auto NUMBER := 0;
BEGIN
DBMS_JOB.SUBMIT(start_app_auto,'p99_9_strat_my_app_man',Current_TimeStamp,'current_timestamp + 3/1440');
-- this job to get the procedure "p99_9_strat_my_app_man" executing each 3 minutes
-- also i would like to declare that, through the mentioned procedure there are 3 other procedures that have been called.
END;
i am getting the following error:
FRM: 40735 - WHEN-BUTTON-PRESSED trigger raised unhandled exception ORA-06550.
can you help me to solve this problem
thank you again for your support;
|
|
|
|
|
|
| Re: Run set of procedures automatically [message #609157 is a reply to message #609139] |
Mon, 03 March 2014 04:41 |
meteo
Messages: 89
Registered: April 2010
Location: beirut
|
Member |
|
|
again thank you for your reply
i try this still the same error, but i confused about the date forma that may be the cause of the error;
first :
from isql plus I execute the following query:
ALTER SESSION SET NLS_DATE_FORMAT = 'dd-MON-YY hh24:mi:ss';
select Current_TimeStamp from dual;
resp := 04-MAR-14 12.14.30.062000 PM -08:00
select current_timestamp + 3/1440 from dual;
resp := 04-MAR-14 12.17.30
commit;
after this:
i went back to my form and modify the trigger when button pressed (start) to be sure about the date forma used in the form
DECLARE
start_app_auto NUMBER := 0;
date_time_start1 TIMESTAMP ;
date_time_start2 TIMESTAMP ;
str_date1 varchar2(100);
str_date2 varchar2(100);
mnt_ch varchar2 (10);
BEGIN
--ALTER SESSION SET NLS_DATE_FORMAT = 'dd-MON-YY hh24:mi:ss';
select to_char(Current_TimeStamp)
INTO str_date1
from dual;
p05_show_alert_message(str_date1 ); -- 04-MAR-14 12.14.30.062000 PM -08:00
select to_char(current_timestamp + 1/1440)
INTO str_date2
from dual;
p05_show_alert_message(str_date2 ); -- 04-MAR-14
DBMS_JOB.SUBMIT (start_app_auto, 'p99_9_strat_my_app_man;', CURRENT_TIMESTAMP, 'current_timestamp + 3/1440');
END;
when i run form and click on the start button. i saw
p05_show_alert_message(str_date1 ); -- 04-MAR-14 12.14.30.062000 PM -08:00
p05_show_alert_message(str_date2 ) -- 04-MAR-14
May the error occur because the two date variable(str_date1, str_date2 ) are in different forma?? if yes how can modify the format of the date through my form ??
thank you.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Run set of procedures automatically [message #609163 is a reply to message #609162] |
Mon, 03 March 2014 05:30 |
meteo
Messages: 89
Registered: April 2010
Location: beirut
|
Member |
|
|
if i try to execute the procedure from isqlplus : exec p_submit_p99_job;
ERROR at line 1:
ORA-06550: line 1, column 93:
PLS-00201: identifier 'P99_9_STRAT_MY_APP_MAN' must be declared
ORA-06550: line 1, column 93:
PL/SQL: Statement ignored
ORA-06512: at "SYS.DBMS_JOB", line 79
ORA-06512: at "SYS.DBMS_JOB", line 136
ORA-06512: at "METEO.P6_SUBMIT_P99_JOB", line 4
ORA-06512: at line 1
i would like to declare when i try to check the procedure
if i try to call the procedure from my form as posted before; i m getting the same error
FRM: 40735 - WHEN-BUTTON-PRESSED trigger raised unhandled exception ORA-06550.
but in spite i m committed the procedure
when I try to execute:
select select * from user_jobs;
the out put : no row selected
i would like to declare from oracle enterprise manage, meteo user, procedures node, i check the procedure created and i found it.
|
|
|
|
|
|
| Re: Run set of procedures automatically [message #609165 is a reply to message #609164] |
Mon, 03 March 2014 05:46 |
meteo
Messages: 89
Registered: April 2010
Location: beirut
|
Member |
|
|
I don't think there in an error in the procedure P99_9_STRAT_MY_APP_MAN because I try it under other button strat_Man
Declare
..
Begin
...
P99_9_STRAT_MY_APP_MAN
..
End;
it is well executed without error and with true result.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Run set of procedures automatically [message #609171 is a reply to message #609170] |
Mon, 03 March 2014 07:18 |
meteo
Messages: 89
Registered: April 2010
Location: beirut
|
Member |
|
|
but in this case i never can use dbms_job to run procedure in schedule time
because the mentioned procedure call many procedures which in turn call many procedures...
in addition all these procedures use a block item in the form, that mean
if i try to recreate all these procedure in
my database using oracle enterprise manage or isqlplus then the compiler always load error message each time an instruction in a procedure use block or item specified in my form call_web.fmb??
is there any solution for this case?? really i appreciate any suggestions you gave me
thank you a lot Mr
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Run set of procedures automatically [message #609176 is a reply to message #609175] |
Mon, 03 March 2014 07:35 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Only if the OP completely rewrites all his code so that it's in the DB rather than in forms, which he doesn't seem to what to do.
Admittedly I'm struggling to think of a case where scheduling forms code to run every 15 mins would be at all useful, hence my question to the OP.
|
|
|
|
| Re: Run set of procedures automatically [message #609177 is a reply to message #609176] |
Mon, 03 March 2014 07:42 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
@meteo - Forms is a tool for creating GUI screens to allow users to interact with a DB.
Processes that need to run every 15 mins, in 99.9% of cases, involve no user interaction.
If your process involves no user interaction then you should rewrite it as a set of stored procedures in the DB that have no references to any forms datablocks/items/builtins and run that from dbms_job (or dbms_scheduler)
If your process does involve user interaction then you need to explain exactly what it does before we can help you further.
|
|
|
|
| Re: Run set of procedures automatically [message #609178 is a reply to message #609173] |
Mon, 03 March 2014 07:57 |
meteo
Messages: 89
Registered: April 2010
Location: beirut
|
Member |
|
|
this is a kind of procedure
PROCEDURE p95_Ins_blk_mtr_tab_mtr IS
-- (p_yr varchar2, p_mn varchar2, p_dy varchar2, p_hr varchar2, p_code varchar2 ) IS
v_year VARCHAR2(10);
v_month VARCHAR2(10);
v_day VARCHAR2(10);
v_hr VARCHAR2(10);
v_code VARCHAR2(10);
v_dd NUMBER;
v_ff NUMBER;
v_tt NUMBER;
v_dp NUMBER;
v_qnh NUMBER;
val_nbr_rec NUMBER;
val_cur_rec NUMBER;
BEGIN
go_block('METARS');
last_record;
val_nbr_rec:= :SYSTEM.cursor_record;
first_record;
val_cur_rec := to_number(name_in('system.cursor_record'));
FOR i in 1 .. (val_nbr_rec) LOOP -- loop through Rel_Prof_Class block
go_record(val_cur_rec);
IF :METARS.year IS NOT NULL AND :METARS.month is not null AND
:METARS.day is not null AND :METARS.hour is not null AND
:METARS.airp_code is not null
THEN
IF Not ( p90_test_found_mtr(:METARS.year, :METARS.month,:METARS.day , :METARS.hour, :METARS.airp_code ) )
THEN
v_code := :METARS.airp_code;
v_day := :METARS.day;
v_hr := :METARS.hour;
v_dd := :METARS.dd;
v_ff := :METARS.ff;
v_tt := :METARS.tt;
v_dp := :METARS.dp;
v_qnh := :METARS.qnh;
v_month := :METARS.month;
v_year := :METARS.year;
INSERT
INTO METARS
VALUES (v_code, v_day, v_hr, v_dd, v_ff, v_tt,v_dp, v_qnh,v_month,v_year );
commit;
next_record;
val_cur_rec:= :SYSTEM.cursor_record;
ELSE
next_record;
val_cur_rec:= :SYSTEM.cursor_record;
END IF;
ELSE
-- p015_show_alert_message('there are error at record: ' || ' ' ||to_char(i)|| ' !! review data in this record and try to insert again' );
next_record;
val_cur_rec:= :SYSTEM.cursor_record;
END IF;
END LOOP;
-- commit;
go_block('METARS');
first_record;
END;
many other procedures like this one ??? it is impossible to create them in the database
[EDITED by LF: removed superfluous empty lines; planned to include [code] tags but they made it look ever worse. Went to format it, but it has syntax errors which made formatting fail]
[Updated on: Mon, 03 March 2014 14:46] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Run set of procedures automatically [message #609182 is a reply to message #609179] |
Mon, 03 March 2014 08:10 |
meteo
Messages: 89
Registered: April 2010
Location: beirut
|
Member |
|
|
there is other procedure which read data from execl file and fetch data into tha block
here is the procedure:
PROCEDURE p77_import_metars_to_block
IS
p_metar varchar2(1000);
v_code varchar2(20);
v_dy varchar2(10);
v_hr varchar2(10);
v_dd number(5) ;
v_ff number(5);
v_tt number(5);
v_dp number(5);
v_qnh number(5);
v_mn varchar2(20);
v_year varchar2 (20);
mtr_cur_rec integer;
htm_cur_rec integer;
htm_nbr_rec integer;
BEGIN
go_block('METARS');
first_record;
mtr_cur_rec:= :SYSTEM.cursor_record;
go_block('PAGEHTML');
last_record;
htm_nbr_rec:= :SYSTEM.cursor_record;
first_record;
htm_cur_rec:= :SYSTEM.cursor_record;
FOR i in 1 .. htm_nbr_rec LOOP
go_block('PAGEHTML');
go_record( htm_cur_rec);
go_item('PAGEHTML.METAR_TEXT');
p_metar := :PAGEHTML.METAR_TEXT;
If p_metar is not null
then
p72_manage_string(p_metar);
end if;
-- If p_metar is not null
-- then
p73_fragment_metar(p_metar , v_code , v_dy , v_hr, v_dd ,v_ff , v_tt , v_dp , v_qnh , v_mn ,v_year );
next_record;
htm_cur_rec:= :SYSTEM.cursor_record;
go_block('METARS');
go_record(mtr_cur_rec);
:METARS.airp_code := v_code;
:METARS.day := v_dy;
:METARS.hour := v_hr;
:METARS.dd := v_dd;
:METARS.ff := v_ff;
:METARS.tt := v_tt;
:METARS.dp := v_dp;
:METARS.qnh := v_qnh;
:METARS.month := v_mn;
:METARS.year := v_year;
next_record;
mtr_cur_rec:= :SYSTEM.cursor_record;
-- else
-- go_block('METARS');
-- next_record;
-- mtr_cur_rec:= :SYSTEM.cursor_record;
-- p05_show_alert_message('field metar is null');
-- go_block('PAGEHTML');
-- next_record;
-- htm_cur_rec:= :SYSTEM.cursor_record;
-- end if;
End loop;
END;
-- ***************************************************
/*PROCEDURE p77_import_metars_to_block(p_block in out block,p_code item , p_dy in varchar2, p_hr in varchar2,
p_dd in number ,p_ff in number, p_tt in number, p_dp in number, p_qnh in number,
p_mn in varchar2,p_year in varchar ) IS*/
|
|
|
|
| Re: Run set of procedures automatically [message #609183 is a reply to message #609179] |
Mon, 03 March 2014 08:15 |
meteo
Messages: 89
Registered: April 2010
Location: beirut
|
Member |
|
|
this is the procedure which get data from excel file into my block
Procedure p70_9i_read_Xls_file_OE
IS
-- Declare handles to OLE objects
application OLE2.OBJ_TYPE;
workbooks OLE2.OBJ_TYPE;
workbook OLE2.OBJ_TYPE;
worksheets OLE2.OBJ_TYPE;
worksheet OLE2.OBJ_TYPE;
cell OLE2.OBJ_TYPE;
args OLE2.OBJ_TYPE;
Check_file text_io.file_type;
no_file exception;
PRAGMA exception_INIT (no_file, -302000);
cell_value varchar2(2000);
eod Boolean := false;
c integer := 1;
BEGIN
-- Check the file can be found, if not exception no_file will be raised
Check_file := TEXT_IO.FOPEN('C:\Climate_File\Metar_files.XLS','R');
TEXT_IO.FCLOSE(Check_file);
--Message('hellllllllllooo');
-- ***********************************
application := OLE2.CREATE_OBJ('Excel.Application');
ole2.set_property(application,'Visible','false');
workbooks := OLE2.GET_OBJ_PROPERTY(application, 'Workbooks');
args := OLE2.CREATE_ARGLIST;
ole2.add_arg(args,'C:\Climate_File\Metar_files.XLS');
workbook := ole2.GET_OBJ_PROPERTY(workbooks,'Open',args);
ole2.destroy_arglist(args); -- create args a list of arg and initiate the arg inside
worksheets := ole2.GET_OBJ_PROPERTY(workbook, 'Worksheets');
worksheet := OLE2.GET_OBJ_PROPERTY(application,'activesheet');
OLE2.SET_PROPERTY(worksheet , 'Value','Sheet1');
-- Get value of cell (k,j) of worksheet Sheet1
go_block('PAGEHTML');
first_record;
-- loop
/* If :system.record_status <> 'NEW' then
create_record;
end if;
exit when eod;*/
for l in 5..160 loop
-- for l in 6..250 loop
If :system.record_status <> 'NEW' then
create_record;
end if;
args:= OLE2.create_arglist;
ole2.add_arg(args, l);
OLE2.add_arg(args, 1);
cell:= OLE2.get_obj_property(worksheet, 'Cells', args);
OLE2.destroy_arglist(args);
cell_value :=oLE2.get_char_property(cell, 'Value');
IF cell_value is not null
THEN
copy(cell_value,name_in('system.cursor_item'));
next_record;
END IF;
end loop;
--j:=1;
--end loop;
go_block('PAGEHTML');
first_record;
-- Release the OLE2 object handles
ole2.release_obj(cell);
ole2.release_obj(worksheet);
ole2.release_obj(worksheets);
ole2.release_obj(workbook);
ole2.release_obj(workbooks);
ole2.invoke(application,'Quit');
ole2.release_obj(application);
-- ASSIGN RETURN VALUE FROM EXCEL TO Text Field
--:PLANETS.pid := cell_value;
EXCEPTION
WHEN no_file THEN
MESSAGE('file not found.');
WHEN OTHERS THEN
MESSAGE(sqlerrm);
PAUSE;
FOR i IN 1 .. tool_err.nerrors LOOP
MESSAGE(tool_err.message);
PAUSE;
tool_err.pop;
END LOOP;
END;
|
|
|
|
|
|
| Re: Run set of procedures automatically [message #609185 is a reply to message #609184] |
Mon, 03 March 2014 08:36 |
meteo
Messages: 89
Registered: April 2010
Location: beirut
|
Member |
|
|
no there is additional steps before inserted reading data from excel file into the database
for example
there is procedure PROCEDURE p77_import_metars_to_block -- read data from exel to my form
after which fragment metars into many element in the block (procedure p73_fragment_metarwhich
finally insert data into tha database (p95_Ins_blk_mtr_tab_mtr)
all these procedure use block item ....
|
|
|
|
|
|
| Re: Run set of procedures automatically [message #609187 is a reply to message #609186] |
Mon, 03 March 2014 09:05 |
meteo
Messages: 89
Registered: April 2010
Location: beirut
|
Member |
|
|
yes, many friends in my job and me, are interested in the weather observation so we need all meteorologically metars message, hourly for many cities, so we use these information in weather subject. manually i achieved it, via 'p99_9_strat_my_app_man', that collect data each time i execute it.
but its butter to make it automatically because if 18 hour passe without executing the mentioned procedure there is missing in the metar messages.
anyway if there is any suggestion about this issue, i will be very grateful for you if you contact me at:
abdmet74@gmail.com
thank you for you support
it was very kind from you to help me.
|
|
|
|
| Re: Run set of procedures automatically [message #609197 is a reply to message #609187] |
Mon, 03 March 2014 14:59 |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
As far as I understood, you get Excel files which contain meteorological data. Then you load that into a form which, finally, inserts data into a table.
How do you get Excel files? Are they result of some Internet pages that are available to public? For example, something like this? If that's so, I'd suggest you to abandon this rather complex way of gathering information and switch to something different: utilize UTL_HTTP package.
Oracle
The UTL_HTTP package makes Hypertext Transfer Protocol (HTTP) callouts from SQL and PL/SQL. You can use it to access data on the Internet over HTTP.
It enables you to create a stored procedure which actually reads data from web pages; you can easily schedule it to run as often as you wish.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
