DISABLE & ENABLE all the triggers in the schema
BEGIN
FOR i IN (SELECT trigger_name
FROM user_triggers) LOOP
EXECUTE IMMEDIATE 'ALTER TRIGGER ' || i.trigger_name || ' DISABLE';
EXECUTE IMMEDIATE 'ALTER TRIGGER ' || i.trigger_name || ' ENABLE';
END LOOP;
END;
/
Wednesday, February 17, 2010
Tuesday, February 16, 2010
Create / Export to Excel in Oracle Forms
Create the below procedure in the Program Units & call this procedure (from a button) where you need.
=================================================
PROCEDURE GEN_EXCEL IS
IN_FILE TEXT_IO.FILE_TYPE;
VC_HEAD Varchar2(32000);
vc_file_path Varchar2(50) := :;
BEGIN
IN_FILE := TEXT_IO.FOPEN(vc_file_path||'file_name'||'.CSV','W');
TEXT_IO.PUT_LINE(IN_FILE,'YOUR_TITLE'||chr(10));
VC_HEAD := 'header1,header2,header3,..........';
TEXT_IO.PUT_LINE(IN_FILE,VC_HEAD);
FOR C1 IN ( SELECT cloumn1,
cloumn2,
cloumn3,
------
------
FROM table_name) LOOP
TEXT_IO.PUT_LINE(IN_FILE,C1.cloumn1||','||C1.cloumn2||','||C1.cloumn3||','||..........);
END LOOP;
TEXT_IO.FCLOSE(IN_FILE);
MSG_ALERT('Excel file has been created!','I',FALSE);
EXCEPTION
WHEN Others THEN
TEXT_IO.FCLOSE(IN_FILE);
MSG_ALERT('Error while writing file.'||sqlerrm,'I',FALSE);
END;
END;
===================================================
Thanks
=================================================
PROCEDURE GEN_EXCEL IS
IN_FILE TEXT_IO.FILE_TYPE;
VC_HEAD Varchar2(32000);
vc_file_path Varchar2(50) := :
BEGIN
IN_FILE := TEXT_IO.FOPEN(vc_file_path||'file_name'||'.CSV','W');
TEXT_IO.PUT_LINE(IN_FILE,'YOUR_TITLE'||chr(10));
VC_HEAD := 'header1,header2,header3,..........';
TEXT_IO.PUT_LINE(IN_FILE,VC_HEAD);
FOR C1 IN ( SELECT cloumn1,
cloumn2,
cloumn3,
------
------
FROM table_name) LOOP
TEXT_IO.PUT_LINE(IN_FILE,C1.cloumn1||','||C1.cloumn2||','||C1.cloumn3||','||..........);
END LOOP;
TEXT_IO.FCLOSE(IN_FILE);
MSG_ALERT('Excel file has been created!','I',FALSE);
EXCEPTION
WHEN Others THEN
TEXT_IO.FCLOSE(IN_FILE);
MSG_ALERT('Error while writing file.'||sqlerrm,'I',FALSE);
END;
END;
===================================================
Thanks
Invoking Oracle 6i Reports from Oracle 6i Forms using RUN_PRODUCT
The RUN_PRODUCT builtin is used by Oracle Forms to achieve integration with the
other Developer 2000/6i products. This article looks at the calling of Oracle
Reports from Oracle Forms. There are two methods by which an Oracle Reports
module may be accessed from an Oracle Forms form. These are:
o Passing parameters from the calling application (Forms) to
the called application (Reports).
o Passing data records from the calling application (Forms) to
the called application (Reports). This is more efficient since
it reduces the need for the called product to execute a query
against the database. The Shared Services Layer (SSL) will
detect that data is being passed and replace the query in Oracle
Reports with data from the Oracle Forms record cache (record
group). The names and datatypes of the elements of the select
list in the reports query must match with the query sourcing the
record group.
You cannot pass a record group to a child query in Reports.
Data passing only works for master queries. Child queries in
Reports have a line appended to the 'where' clause containing a
reference to the bind from the master query. Reports would have
no way of qualifying (applying the bind) to the data coming from
the record group from Forms.
The objective of this article will be to create a standard Dept/Emp
Form from which a user may launch an Oracle Reports module via a
button. The report will be based on the 'current department'
displayed in the form. Creation of Oracle Forms objects, such as
default blocks, relations and buttons is not discussed. The article
concentrates on the Forms procedures required in the two scenarios
described above.
The first Oracle Forms procedure documents simply passing a
bind parameter to the report, in this case the current department
number from the dept block. The Reports query then binds this value
in and produces the desired report. The second procedure looks at
the passing of a lexical parameter, a string containing quotes.
The third passes the 'where' clause of the last executed Forms query
and finally, the last, looks at the method by which a record group
is passed to Oracle Reports and actually replaces the Reports query.
The RUN_PRODUCT builtin is adequately documented in the Oracle
Forms reference manual and developers should refer to this for
further information. However, for reference there are two basic
types of parameters for RUN_PRODUCT:
o TEXT_PARAMETERs for passing bind and lexical parameters
o DATA_PARAMETERs for associations between Oracle Forms record
groups and Oracle Reports queries.
1. Passing Bind Parameters
Oracle Reports Component
Create a report based on a single query.
Q_emp: SELECT empno, sal
FROM emp
WHERE deptno = :departmentno
ORDER BY sal
Forms Component
Create the Oracle Forms procedure pass_parameter, outlined below as
a PL/SQL program unit and reference it from a when-button-pressed
trigger. By default, the reports runtime parameter form will appear everytime
the report is run. Pass_parameter will pass two parameters,
one being the department number, the other, a parameter to suppress
the display of the parameter form. The parameters are passed via a parameter
list. Please refer to the Oracle Forms reference manual
for further information about parameter lists.
Oracle Reports Component
Create the lexical parameter MYWHERE and give it an initial value of:
where 1=1
The aim of this example will be to pass from the Form a string:
where loc like 'NEW YORK'
Create a report based on a single query:
Create the Oracle Forms procedure pass_parameter, outlined below as
a PL/SQL program unit and reference it from a when-button-pressed
trigger. By default, the reports runtime parameter form will appear everytime
the report is run. Pass_parameter will pass two parameters,
one being the string for the where clause, the other, a parameter to suppress
the display of the parameter form. The parameters are passed
via a parameter list.
PROCEDURE pass_parameter
IS
plid paramlist;
the_param varchar2(15) := 'tmpdata';
clause varchar2(60);
BEGIN
plid := get_parameter_list(the_param);
/* check if 'tmpdata' exists */
IF NOT id_null(plid) THEN
destroy_parameter_list(plid);
END IF;
/* if it does destroy it */
plid := create_parameter_list(the_param);
/* create it afresh */
clause := '"'||'where loc like '||''''||'NEW YORK'||''''||'"';
add_parameter(plid, 'MYWHERE', TEXT_PARAMETER, clause);
/* associate the param in the form with the param in the report */
add_Parameter(plid, 'PARAMFORM', TEXT_PARAMETER, 'NO');
/* to suppress the parameter form displaying */
run_product(REPORTS, /* product name */
'formrep.rdf', /* Oracle Reports module */
SYNCHRONOUS, /* communication mode */
RUNTIME, /* execution mode */
FILESYSTEM, /* location of the Reports module */
plid, /* handle to the parameter list */
null);
END;
Create the lexical parameter and query outlined below.
Parameter: MYWHERE (initial value is: where 1=2)
Q_emp: SELECT * FROM emp &MY_WHERE
Oracle Forms component
Create the Oracle Forms procedure pass_where, outlined below as
a PL/SQL program unit and reference it from a when-button-pressed
trigger. Pass_parameter will pass two parameters, one being the
where clause obtained from system.last_query, the other, a parameter
to suppress the display of the parameter form. The parameters are
passed via a parameter list as in the example above.
Oracle Reports Component
Create a report based on a single query. No WHERE clause is
included in order to show the true effect of passing the record
group. The record group will contain a restricted list of employees.
Q_emp: SELECT empno, sal
FROM emp
Oracle Forms Component
Create the Oracle Forms procedure outlined below as a PL/SQL program
units and reference it from a when-button-pressed trigger.
PROCEDURE pass_record
IS
repquery varchar2(15) := 'q_emp';
/* name of the reports query */
queryrec varchar2(15) := 'recgroup';
/* name of the record group */
the_param varchar2(15) := 'tmpdata';
plid paramlist;
rgid recordgroup;
the_query varchar2(1000); /* store query in this */
return_cd number;
BEGIN
the_query := 'SELECT empno, sal FROM emp WHERE deptno='||
to_char(:dept.deptno)||' ORDER BY sal';
/* create the query string */
rgid := find_group(queryrec);
/* get handle to record group */
IF id_null(rgid) THEN
rgid := create_group_from_query(queryrec, the_query);
END IF;
/* creating group from query, if it wasnt found */
delete_group_row(rgid,all_rows);
/* empty the record group */
return_cd := populate_group_with_query(rgid,the_query);
/* populate record group using the defined query */
plid := get_parameter_list(the_param);
/* check if 'tmpdata' exists */
IF NOT id_null(plid) THEN
destroy_parameter_list(plid);
END IF;
/* if yes - destroy it */
plid := create_parameter_list(the_param);
/* create it and associate the record group with the query in Reports */
add_parameter(plid, repquery, DATA_PARAMETER, queryrec);
add_Parameter(plid, 'PARAMFORM', TEXT_PARAMETER, 'NO');
/* to suppress the parameter form displaying */
run_product(REPORTS, /* product name */
'formrep.rdf', /* Oracle Reports module */
SYNCHRONOUS, /* communication mode */
RUNTIME, /* execution mode */
FILESYSTEM, /* location of the Reports module */
plid, /* handle to the parameter list */
null);
END;
The error REP-1340 is raised if the Forms procedure passes a
query to Reports containing a mismatch between the columns passed
and those expected by the report.
This will happen if:
o the number of columns is different.
o the parameter list assembled in the form describes a column
differently to that which is expected by the report i.e the
datatype is different.
Calling Reports from Forms results in a small previewer window,
requiring enlargement.
other Developer 2000/6i products. This article looks at the calling of Oracle
Reports from Oracle Forms. There are two methods by which an Oracle Reports
module may be accessed from an Oracle Forms form. These are:
o Passing parameters from the calling application (Forms) to
the called application (Reports).
o Passing data records from the calling application (Forms) to
the called application (Reports). This is more efficient since
it reduces the need for the called product to execute a query
against the database. The Shared Services Layer (SSL) will
detect that data is being passed and replace the query in Oracle
Reports with data from the Oracle Forms record cache (record
group). The names and datatypes of the elements of the select
list in the reports query must match with the query sourcing the
record group.
You cannot pass a record group to a child query in Reports.
Data passing only works for master queries. Child queries in
Reports have a line appended to the 'where' clause containing a
reference to the bind from the master query. Reports would have
no way of qualifying (applying the bind) to the data coming from
the record group from Forms.
The objective of this article will be to create a standard Dept/Emp
Form from which a user may launch an Oracle Reports module via a
button. The report will be based on the 'current department'
displayed in the form. Creation of Oracle Forms objects, such as
default blocks, relations and buttons is not discussed. The article
concentrates on the Forms procedures required in the two scenarios
described above.
The first Oracle Forms procedure documents simply passing a
bind parameter to the report, in this case the current department
number from the dept block. The Reports query then binds this value
in and produces the desired report. The second procedure looks at
the passing of a lexical parameter, a string containing quotes.
The third passes the 'where' clause of the last executed Forms query
and finally, the last, looks at the method by which a record group
is passed to Oracle Reports and actually replaces the Reports query.
The RUN_PRODUCT builtin is adequately documented in the Oracle
Forms reference manual and developers should refer to this for
further information. However, for reference there are two basic
types of parameters for RUN_PRODUCT:
o TEXT_PARAMETERs for passing bind and lexical parameters
o DATA_PARAMETERs for associations between Oracle Forms record
groups and Oracle Reports queries.
1. Passing Bind Parameters
Oracle Reports Component
Create a report based on a single query.
Q_emp: SELECT empno, sal
FROM emp
WHERE deptno = :departmentno
ORDER BY sal
Forms Component
Create the Oracle Forms procedure pass_parameter, outlined below as
a PL/SQL program unit and reference it from a when-button-pressed
trigger. By default, the reports runtime parameter form will appear everytime
the report is run. Pass_parameter will pass two parameters,
one being the department number, the other, a parameter to suppress
the display of the parameter form. The parameters are passed via a parameter
list. Please refer to the Oracle Forms reference manual
for further information about parameter lists.
PROCEDURE pass_parameter
IS
plid paramlist;
the_param varchar2(15) := 'tmpdata';
BEGIN
plid := get_parameter_list(the_param);
/* check if 'tmpdata' exists */
IF NOT id_null(plid) THEN
destroy_parameter_list(plid);
END IF;
/* if it does destroy it */
plid := create_parameter_list(the_param);
/* create it afresh */
add_parameter(plid, 'departmentno', TEXT_PARAMETER,to_char(:dept.deptno));
/* associate the param in the form with the param in the report */
add_Parameter(plid, 'PARAMFORM', TEXT_PARAMETER, 'NO');
/* to suppress the parameter form displaying */
run_product(REPORTS, /* product name */
'formrep.rdf', /* Oracle Reports module */
SYNCHRONOUS, /* communication mode */
RUNTIME, /* execution mode */
FILESYSTEM, /* location of the Reports module */
plid, /* handle to the parameter list */
null
);
END;
2. Passing Lexical Parameters
Oracle Reports Component
Create the lexical parameter MYWHERE and give it an initial value of:
where 1=1
The aim of this example will be to pass from the Form a string:
where loc like 'NEW YORK'
Create a report based on a single query:
Q_emp: SELECT empno, sal
FROM emp
&MYWHERE
Forms Component
Create the Oracle Forms procedure pass_parameter, outlined below as
a PL/SQL program unit and reference it from a when-button-pressed
trigger. By default, the reports runtime parameter form will appear everytime
the report is run. Pass_parameter will pass two parameters,
one being the string for the where clause, the other, a parameter to suppress
the display of the parameter form. The parameters are passed
via a parameter list.
PROCEDURE pass_parameter
IS
plid paramlist;
the_param varchar2(15) := 'tmpdata';
clause varchar2(60);
BEGIN
plid := get_parameter_list(the_param);
/* check if 'tmpdata' exists */
IF NOT id_null(plid) THEN
destroy_parameter_list(plid);
END IF;
/* if it does destroy it */
plid := create_parameter_list(the_param);
/* create it afresh */
clause := '"'||'where loc like '||''''||'NEW YORK'||''''||'"';
add_parameter(plid, 'MYWHERE', TEXT_PARAMETER, clause);
/* associate the param in the form with the param in the report */
add_Parameter(plid, 'PARAMFORM', TEXT_PARAMETER, 'NO');
/* to suppress the parameter form displaying */
run_product(REPORTS, /* product name */
'formrep.rdf', /* Oracle Reports module */
SYNCHRONOUS, /* communication mode */
RUNTIME, /* execution mode */
FILESYSTEM, /* location of the Reports module */
plid, /* handle to the parameter list */
null);
END;
3. Passing the WHERE clause from the last query executed in Oracle Forms
Oracle Report component
Create the lexical parameter and query outlined below.
Parameter: MYWHERE (initial value is: where 1=2)
Q_emp: SELECT * FROM emp &MY_WHERE
Oracle Forms component
Create the Oracle Forms procedure pass_where, outlined below as
a PL/SQL program unit and reference it from a when-button-pressed
trigger. Pass_parameter will pass two parameters, one being the
where clause obtained from system.last_query, the other, a parameter
to suppress the display of the parameter form. The parameters are
passed via a parameter list as in the example above.
PROCEDURE pass_where IS
plid paramlist;
the_param varchar2(15) := 'tmpdata';
the_query varchar2(1000); /* store the query */
i number(4); /* will hold the position of WHERE */
BEGIN
plid := get_parameter_list (the_param);
if not id_null(plid) then
destroy_parameter_list(the_param);
end if;
plid := create_parameter_list(the_param);
the_query := :system.last_query ;
/* find the position of the first instance of the word where */
i := instr(the_query,'WHERE');
/* check, if the query contains a WHERE clause, i=0 if not */
/* update the_query with just the contents of the where clause */
IF i > 0 THEN
the_query := substr(the_query, i, length(the_query) - i + 1) ;
ELSE
the_query := ' ';
END IF;
add_parameter(plid, 'MYWHERE', TEXT_PARAMETER, the_query);
add_Parameter(plid, 'PARAMFORM', TEXT_PARAMETER, 'NO');
run_product(REPORTS,
'formrep.rdf',
SYNCHRONOUS,
RUNTIME,
FILESYSTEM,
plid,
null
);
end;
4. Passing a record group to replace a query defined in Reports
Oracle Reports Component
Create a report based on a single query. No WHERE clause is
included in order to show the true effect of passing the record
group. The record group will contain a restricted list of employees.
Q_emp: SELECT empno, sal
FROM emp
Oracle Forms Component
Create the Oracle Forms procedure outlined below as a PL/SQL program
units and reference it from a when-button-pressed trigger.
PROCEDURE pass_record
IS
repquery varchar2(15) := 'q_emp';
/* name of the reports query */
queryrec varchar2(15) := 'recgroup';
/* name of the record group */
the_param varchar2(15) := 'tmpdata';
plid paramlist;
rgid recordgroup;
the_query varchar2(1000); /* store query in this */
return_cd number;
BEGIN
the_query := 'SELECT empno, sal FROM emp WHERE deptno='||
to_char(:dept.deptno)||' ORDER BY sal';
/* create the query string */
rgid := find_group(queryrec);
/* get handle to record group */
IF id_null(rgid) THEN
rgid := create_group_from_query(queryrec, the_query);
END IF;
/* creating group from query, if it wasnt found */
delete_group_row(rgid,all_rows);
/* empty the record group */
return_cd := populate_group_with_query(rgid,the_query);
/* populate record group using the defined query */
plid := get_parameter_list(the_param);
/* check if 'tmpdata' exists */
IF NOT id_null(plid) THEN
destroy_parameter_list(plid);
END IF;
/* if yes - destroy it */
plid := create_parameter_list(the_param);
/* create it and associate the record group with the query in Reports */
add_parameter(plid, repquery, DATA_PARAMETER, queryrec);
add_Parameter(plid, 'PARAMFORM', TEXT_PARAMETER, 'NO');
/* to suppress the parameter form displaying */
run_product(REPORTS, /* product name */
'formrep.rdf', /* Oracle Reports module */
SYNCHRONOUS, /* communication mode */
RUNTIME, /* execution mode */
FILESYSTEM, /* location of the Reports module */
plid, /* handle to the parameter list */
null);
END;
REP-1340
The error REP-1340 is raised if the Forms procedure passes a
query to Reports containing a mismatch between the columns passed
and those expected by the report.
This will happen if:
o the number of columns is different.
o the parameter list assembled in the form describes a column
differently to that which is expected by the report i.e the
datatype is different.
Previewer Window Size
Calling Reports from Forms results in a small previewer window,
requiring enlargement.
Thursday, February 11, 2010
Flash In Oracle Forms
In Forms:
- Create an ActiveX Control Item (OCX) on your canvas.
- Right click it and select 'Insert Object...'.
- Choose Shockwave Flash Object.
- In the main menu 'Program', choose 'Import OLE Library Interfaces'
- Select ShockwaveFlash.ShockwaveFlash from the list.
- Select both the Method Package(s) and the Event Package(s). Make sure they're highlighted.
- Verify the existance of 5 new objects in your program units:
PACKAGE ShockwaveFlashObject_CONSTANTS (spec only)
PACKAGE ShockwaveFlash_IShockwa_0 (spec and body)
PACKAGE ShockwaveFlash_Shockwav_EVENTS (spec and body)
- Add the following code to your 'when-new-form-instance' trigger:
DECLARE
var oleobj;
BEGIN
var:=:item('block_name.activex_item').interface;
ShockwaveFlash_IShockwa_0.MOVIE(var,'path of swf file');
END;
eg:
DECLARE
var oleobj;
BEGIN
var:=:item('B1.flash1').interface;
ShockwaveFlash_IShockwa_0.MOVIE(var,'C:\TEMP\Music_Midomi_756x252.swf');
END;
or
DECLARE
var oleobj;
BEGIN
var:=:item('B1.flash1').interface;
ShockwaveFlash_IShockwa_0.MOVIE(var,'http://www.nokia-asia.com//NOKIA_ASIA_2/Home/flash/Music_Midomi_756x252.swf');
END;
- Create an ActiveX Control Item (OCX) on your canvas.
- Right click it and select 'Insert Object...'.
- Choose Shockwave Flash Object.
- In the main menu 'Program', choose 'Import OLE Library Interfaces'
- Select ShockwaveFlash.ShockwaveFlash from the list.
- Select both the Method Package(s) and the Event Package(s). Make sure they're highlighted.
- Verify the existance of 5 new objects in your program units:
PACKAGE ShockwaveFlashObject_CONSTANTS (spec only)
PACKAGE ShockwaveFlash_IShockwa_0 (spec and body)
PACKAGE ShockwaveFlash_Shockwav_EVENTS (spec and body)
- Add the following code to your 'when-new-form-instance' trigger:
DECLARE
var oleobj;
BEGIN
var:=:item('block_name.activex_item').interface;
ShockwaveFlash_IShockwa_0.MOVIE(var,'path of swf file');
END;
eg:
DECLARE
var oleobj;
BEGIN
var:=:item('B1.flash1').interface;
ShockwaveFlash_IShockwa_0.MOVIE(var,'C:\TEMP\Music_Midomi_756x252.swf');
END;
or
DECLARE
var oleobj;
BEGIN
var:=:item('B1.flash1').interface;
ShockwaveFlash_IShockwa_0.MOVIE(var,'http://www.nokia-asia.com//NOKIA_ASIA_2/Home/flash/Music_Midomi_756x252.swf');
END;
Subscribe to:
Posts (Atom)