Generic code for Oracle forms calling reports

PROCEDURE CALL_REPORT(rptname varchar2,paramval varchar2 default null,paraform varchar2 default 'N') IS
plid paramlist;
a_paralist varchar2(4000);
report_id Report_Object;
report_job_id VARCHAR2(100);
report_status VARCHAR2(100);
vc_use_report_server char(1) := 'N';
vs_report_server varchar2(256) := 'GEOREPSRV';
vs_report_server_url varchar2(256) := 'dev60cgi/rwcgi60';
vs_report_format VARCHAR2(100) := 'pdf';
BEGIN
dbms_application_info.set_module('GEORPT',rptname);
if paramval is null then
a_paralist := 'PARAMFORM=NO';
else
a_paralist := 'PARAMFORM=NO' paramval;
end if;
-- vincent
-- replace spaces with %20 for web.show_document
a_paralist := replace(a_paralist,' ','%20');
--

plid := buildparamlist(a_paralist);

if instr(UPPER(a_paralist),'COPIES=') > 0 then
delete_parameter(plid,'COPIES');
end if;
if instr(UPPER(a_paralist),'DESFORMAT=') > 0 then
vs_report_format := substr(a_paralist,(10 + instr(UPPER(a_paralist),'DESFORMAT=')),(instr(UPPER(a_paralist),'',instr(UPPER(a_paralist),'DESFORMAT=')) - instr(UPPER(a_paralist),'DESFORMAT=') - 10));
delete_parameter(plid,'DESFORMAT');
end if;
if instr(UPPER(a_paralist),'BACKGROUND=') > 0 then
delete_parameter(plid,'BACKGROUND');
end if;
if instr(UPPER(a_paralist),'CURRENCY=') > 0 then
delete_parameter(plid,'CURRENCY');
end if;
if instr(UPPER(a_paralist),'DECIMAL=') > 0 then
delete_parameter(plid,'DECIMAL');
end if;
if instr(UPPER(a_paralist),'DESNAME=') > 0 then
delete_parameter(plid,'DESNAME');
end if;
if instr(UPPER(a_paralist),'DESTYPE=') > 0 then
delete_parameter(plid,'DESTYPE');
end if;
if instr(UPPER(a_paralist),'MODE=') > 0 then
delete_parameter(plid,'MODE');
end if;
if instr(UPPER(a_paralist),'ORIENTATION=') > 0 then
delete_parameter(plid,'ORIENTATION');
end if;
if instr(UPPER(a_paralist),'PRINTJOB=') > 0 then
delete_parameter(plid,'PRINTJOB');
end if;
if instr(UPPER(a_paralist),'THOUSANDS=') > 0 then
delete_parameter(plid,'THOUSANDS');
end if;

begin
select REF_STRING into vc_use_report_server from SYS_REF_TABLE where ref_code = 'USEREPSRV';
if vc_use_report_server not in ('Y','N') then
vc_use_report_server := 'N';
end if;
exception
when no_data_found then
insert into SYS_REF_TABLE (REF_CODE,REF_TITLE,REF_STRING) values ('USEREPSRV', 'Use Report Server','N');
commit;
vc_use_report_server := 'N';
when others then
vc_use_report_server := 'N';
end;



begin
select REF_STRING into vs_report_server from SYS_REF_TABLE where ref_code = 'GEOREPSRV';
vs_report_server := nvl(vs_report_server,'GEOREPSRV');
exception
when no_data_found then
insert into SYS_REF_TABLE (REF_CODE,REF_TITLE,REF_STRING) values ('GEOREPSRV', 'GeO Report Server','GEOREPSRV');
commit;
vs_report_server := 'GEOREPSRV';
when others then
vs_report_server := 'GEOREPSRV';
end;
begin
select REF_STRING into vs_report_server_url from SYS_REF_TABLE where ref_code = 'GEOREPSRVURL';
vs_report_server_url := nvl(vs_report_server_url,'GEOREPSRVURL');
exception
when no_data_found then
insert into SYS_REF_TABLE (REF_CODE,REF_TITLE,REF_STRING) values ('GEOREPSRVURL', 'GeO Report Server URL','dev60cgi/rwcgi60');
commit;
vs_report_server_url := 'dev60cgi/rwcgi60';
when others then
vs_report_server_url := 'dev60cgi/rwcgi60';
end;
report_id:= find_report_object('REPORT1');
if GET_APPLICATION_PROPERTY(USER_INTERFACE) <> 'WEB' then
vc_use_report_server := 'N';
END IF;
if vc_use_report_server = 'N' then
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,SCREEN);
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_FILENAME,rptname);
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_DESFORMAT,vs_report_format);
report_job_id:=run_report_object(report_id,plid);
else
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,CACHE);
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_DESFORMAT,vs_report_format);
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_FILENAME,rptname);
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_SERVER,vs_report_server);
report_job_id :=run_report_object(report_id,plid);
report_status := report_object_status(report_job_id);

if report_status = 'FINISHED' THEN
IF UPPER(vs_report_format) = 'RTF' THEN
Web.Show_Document(vs_report_server_url '/' url_encode2('getjobid' substr(report_job_id,length(vs_report_server) + 2)) '?' url_encode2('server') '=' url_encode2(vs_report_server) '&mimetype=application/msword', '_blank');
ELSE
Web.Show_Document(vs_report_server_url '/' url_encode2('getjobid' substr(report_job_id,length(vs_report_server) + 2)) '?' url_encode2('server') '=' url_encode2(vs_report_server), '_blank');
END IF;
else
msg('Report failed with message ' report_status chr(13) substr(a_paralist,instr(UPPER(a_paralist),'P_USERNAME'),30) );
end if;
end if;
dbms_application_info.set_module('','');
END;

How to setup Oracle Forms 10g IAS

How to setup Oracle Forms 10g IAS

Login to "Enterprise Manager"
URL : http://localhost:18100/
Default userid = ias_admin

Setting Form servers
Adding user defined icons.jar into d:\oracle\oas10g\forms\java

Try testing the website thru config
URL : http://localhost:7777/forms/frmservlet?config=geo

Setting Report servers
Check the current environment
http://localhost:7777/reports/rwservlet/showenv

Check what options you have
http://localhost:7777/reports/rwservlet

At Application server
Change the conf file
D:\oracle\OAS10G\reports\conf.conf
Uncomment the sourcedir and add the geo directory where the reports is located

At GEO
10200 - System Reference Table
GEOREPSRV =
GEOREPSRVURL = /reports/rwservlet
USEREPSRV = Y

Export data on webForm using DAD

How to export data on webform using DAD?
Using DAD to export data on Oracle webform without using javabean . You need to setup DAD on the Application server first.

Code in the calling oracle forms
if GET_APPLICATION_PROPERTY(USER_INTERFACE) = 'WEB' then
select REF_STRING into a_url from SYS_REF_TABLE where ref_code = 'DAD';
Web.Show_Document(a_url '/stkmas_export?v_org_no=' :global.orgno '&v_stk_flg=S' , '_blank');

Code in the store procedure

PROCEDURE STKMAS_EXPORT AS
cursor c1 is select STK_FLG,STK_C,NAME from stk_mas where org_no = v_org_no and stk_flg = v_stk_flg order by org_no,stk_c;
BEGIN
owa_util.mime_header('text/plain');
for c1rec in c1 loop
htp.p('STK_FLG^STK_C^NAME'); for c1rec in c1 loop htp.p(c1rec.STK_FLG '^' c1rec.STK_C '^' translate(c1rec.name,chr(10),'') '^');
end loop;
END STKMAS_EXPORT;

How to set up Developer reports Server 6.0 on NT

This is a step by step guide that helps you in setting up the Reports Server
6.0 on the NT platform

Set-up Procedure

1. Add the Reports Server to the network configuration file.
   Add a line of the following form to
   
   ORACLE_HOME/net80/admin/tnsnames.ora: = ( ADDRESS= (PROTOCOL=TCP) (HOST=) (PORT=))
   
   where is the name of the Reports Multi-Tier Server instance. If you are using a sqlnet.ora file and default_domain is specified, remember to fully qualify the Reports Multi-Tier Server TNS name with the default domain. For example, if the default_domain is world, the repserver.world would be a valid TNS name. is the IP-adress or the hostname of the Application Server machine, and is the port number to which the Reports Multi-Tier Server is listening (such as, 1949).
   
2. Install the Reports Multi-Tier Server as a NT-service
   Run the following command to install the RMTS as a service (Start - Run):
   
   rwmts60 -install tcpip
   
3. Verify the settings of the service
   For the Reports Multi-tier server to run properly, it must have access to printers. Note that by default, the SYSTEM user does not have access to printers. Therefore, you must either set up a separate user to run the Reports Server or give the SYSTEM user access to printers. Since the latter can prove difficult, it is best to set up a separate user to run the Reports Server. In order to change the start-up account of the service, go to the Control Panel - Services.
   
   Look for the entry "Oracle Reports Server ." Click on Startup. From this startup dialog, select "This Account" on the Log On As section, and type in your operating system username and password. This specifies that the Reports Multi-tier Server is run as you. In addition, you can also set the service to be started automatically (when the system is booted up) or manually. If you install the service to run under a user other than SYSTEM, make sure that the user account:
   · Has the Password Never Expires option selected in the User Manager. · Has membership in the appropriate groups to run the Reports Multi-tier Server and access to the report files. · Has at least print permission to a default printer. · Can log on to a service. Go to Administration Tools User Manager, Policies, User Rights. Check Show Advanced User Rights; choose Log on a Service, and click the Add button.
   
4. Starting/Monitoring/Stopping the service
   To start the service, go to the Control Panel, Services, and select the service, optionally enter command line arguments in Startup Parameters, and click on the Start button. If the service fails to start, check the Reports Server logfile for more information. The file is ORACLE_HOME\report60\ server\.log.
   In order to see if your Reports Server is running, simply go to Task manager, Processes, and check to see if the process ‘rw60mts.exe’ is listed.
   To stop the service, go to the Control Panel, Services, and select the service. Click on the Stop button.
   
5. The Reports Server Configuration file
   A configuration file is created when the Reports Multi-tier Server is first started. The file is ORACLE_HOME\report60\server\.ora. Note that if you manually edit the file, the changes are not picked up by the Reports Multi-Tier Server until you shut it down and restart it. The four optional server command line arguments (minengine, maxengine, initengine, and maxidle) can be specified in the Startup Parameters in the Control Panel Services window to override the settings in the configuration file.
   
   So for the set-up, you can repeat steps 2 through 6 to set up and run multiple Reports Multi-tier Servers on one machine. Make sure that you specify different port numbers.
   
   NOTE: To uninstall the Reports Multi-tier Server service, go to the Control Panel, Services and stop the service. Then run the following command line:
   
   rwmts60 -uninstall
   
   NOTE: When running the Reports Server as a service, there is a known problem with Windows NT not sending report output to mail (DESTYPE=MAIL). Microsoft plans to fix this in Microsoft Exchange Server 5.0. In the meantime, you can get around this problem by running the Reports Server not a service.
   
   

2.2.2 Installing the Reports Server as a non-service

1. Be sure the PATH contains ORACLE_HOME/bin.
   Go to the Control Panel, System. and verify the value of the system variable PATH contains the ORACLE_HOME/bin directory.
   
2. Add the Reports Server to the network configuration file.
   Add a line of the following form to ORACLE_HOME/net80/admin/tnsnames.ora: = ( ADDRESS= (PROTOCOL=TCP) (HOST=) (PORT=))
   where is the name of the Reports Multi-Tier Server instance. If you are using a sqlnet.ora file and default_domain is specified, remember to fully qualify the Reports Multi-Tier Server TNS name with the default domain. For example, if the default_domain is world, the repserver.world would be a valid TNS name. is the IP-adress or the hostname of the application server machine, and is the port number to which the Reports Multi-Tier Server is listening (such as, 1949).
   
3. Start the Reports Multi-tier Server
   Run the following command to start the reports server manually.
   Rwmts60 -listen name= [minengine= maxengine= initengine= maxidle=]
   You can use Task manager, Processes to ensure that the reports server is running.
   4. Stopping the Reports Multi-tier Server
   If the listener is running, the Task Manager will display a process called RWMTS60.EXE. Select RWMTS60.EXE and click End Process.
   
   2.2.3 The Reports Server Configuration File
   The configuration file specifies runtime parameters for the Reports Multi-tier Server. When the Reports Multi-tier Server is started, it gets a TNS name from the installation or the command line. The server will listen to this TNS address for RPC calls. The server then looks for a file that is named after the TNS name with .ora as the file extension and that is located in the server subdirectory in ORACLE_HOME\REPORT60\SERVER. If the configuration file is not present, a default one is created. Decide for yourself if this file requires modification. Note that your changes only take effect if you restart the Reports Server. Note that because of its importance, you should restrict access to the configuration file.
   The format of the configuration file is:
   identifier=
   maxconnect=
   sourcedir=
   cachedir=
   tempdir=
   cachesize=
   minengine=
   maxengine=
   initengine=
   maxidle=
   security=
   englife=
   
   If two servers are running on the same Windows machine, they will have to share the same ORACLE_HOME, REPORTS60_PATH, and REPORTS60_TMP. However, each server listens to a different TNS name and therefore, has a unique configuration file, that can specify different sourcedir, cachedir, and tempdir settings, optionally on different drives. The minengine, maxengine, maxidle, and cachesize settings can be viewed and changed from the queue manager. The queue administrator userid and password can also be changed. The configuration file can be overwritten by the server process when these settings change or when it needs to. Any optional arguments on the command line override the settings in the configuration file.
   
   Some remarks on the syntax in the configuration file: strings must be quoted if they contain spaces, and do not put spaces around the equal sign. (Follow the rules of an Oracle Reports command file.)