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