How To Create a Text File report from oracle Forms/Reports

Creating a text file and save it on server through Oracle forms:-


declare

 file_path varchar2(2000);
 V_file_str varchar2(2000);
 cursor rec1 is
 SELECT  emp_name,emp_code
  FROM employee_master;
 first_flag char :='F';
BEGIN
file_path:='D:\ORACLE-PROJECTS\files1\'||to_char(sysdate,'dd_mm_yyyy_hh-mi-ss')||'.TXT';

v_file_str:='LIST OF EMPLOYEES'||TO_CHAR(SYSDATE,'DDMMYY')||CHR(10);
SaveTextToFile(v_file_str, file_path, 'w');
             for rec in REC1          
             loop
                           v_file_str:='01'||REC.EMP_CODE||REC.EMP_NAME;
              SaveTextToFile(v_file_str, file_path, 'a');
             end loop;
ENd;

Note:-where savetexttofile is a procedure:-

PROCEDURE SaveTextToFile(p_Text in varchar2, p_FilePath in varchar2, p_mode in varchar2) IS
 v_TextFile text_io.file_type;
 e_FileOpenError exception;
BEGIN
 begin
  v_TextFile := text_io.fopen(p_FilePath, p_mode);
 exception
  when others then
    LAST_ERROR := 'Cannot open file';
   raise e_FileOpenError;
 end;

 text_io.put(v_TextFile, p_Text);

 text_io.fclose(v_TextFile); 
END;
---------------------------


creating a text file through report:-

define a key in cgicmd file

textreport: userid=system/sys@test destype=file desname='c:\test_report.txt' desformat=delimited

create a report and give the desired formatting,

In the form use the following syntax while calling the report:-

rep_url:=/*server path*/||'/reports/rwservlet?textreport&module='|report_name;
             
  WEB.SHOW_DOCUMENT(rep_url,'_blank');

Then the text file will be save in the c: directory.

Procedure To Run Report From Oracle 10g Forms:-

The following procedure shows how to print report from oracle form.

Web.Show_Document() Built-in to call Oracle Reports on the Web is an alternative to the Run_Report_Object() Built-in.

Web.Show_Document(URL, Target);

1. url – The URL is passed as a string in a variable, or as a combination of both. If the target Web page is located on the same server that runs Forms Services,relative addressing could be used.

2.target  – Definition of the target where the addressed Web page should be displayed. Values must be single-quoted. Possible target values are ‘_blank’ to show the Reports output in an extra browser window, ‘_self’ to replace the Forms

application with the Reports output, ‘<frame name>’ to load the Reports output into a named frame of the multi frame HTML page.

A complete syntax example to run Reports from a browser looks like this:-

http://<server>:<port>/reports/rwservlet? keyname&report=<report>.rdf&desformat=[htmlcss|pdf|xml|delimited|]&destype=cache&paramform=[no|yes]

Report – the name of the Reports module to execute

desformat – the output format of the returned Reports result set. Desformat can be

htmlcss, html, pdf, xml, rtf and delimited. For Reports run from Forms pdf and

htmlcss are the most commonly used options

destype – determines where the Reports output gets written to. “Cache” specifies

that the Reports output gets streamed to the requesting browser. ‘

paramform – determines if Reports should display a HTML parameter form before

executing the request. The parameter form can be used for the user to further filter

the expected Reports result set. Valid values are ‘yes’ and ‘no’.

To reduce the length of the Reports request URL, you can create a key entry in the

Reports cgicmd.dat configuration file to store command line parameters that don’t

change from one Report to the other. In this case the first argument in a Reports

Web request, right after the question mark, must be the key name2.

Keyname refers to a command line listed under a unique header (the key name) in the cgicmd.dat file. Note that this works differently for JSP files, which use the keyword/value pair cmdkey=value to specify key names for command lines that are stored in the cgicmd.dat file.

DECLARE

REP_NAME varchar2 (2000); 

rep_url varchar2 (4000);

BEGIN

                                                               

                                REP_NAME:='d:\ PROJECTS\ code\'||'MYFIRSTREPORT.rdf';                                       

                                rep_url:='http://localhost:8890'||'/reports/rwservlet?employee&module='||REP_NAME                          

                                ||'&p_emp_code='||:BLOCK_NAME.EMP_CODE;

                                                                                                                                                                                                                               

                                WEB.SHOW_DOCUMENT(rep_url,'_blank');

END;

How a deadlock situation occur in Oracle database?

Create a procedure and call a sub procedure within it which is a pragma Autonomous_transaction. Don't Commit or rollback the autonomous procedure.This will creat a deadlock situation.

The reason for this is if you declare any procedure as pragma autonomous_transaction then whatever changes are committed are within the scope of this procedure. If you don't commit the insert statement within this procedure it creats lock.

How to create a Oracle form?

Prerequisites:-

Install Oracle Developer Suite 10g.

Oracle Developer Suite 10g includes a standalone version of Oracle 10g Containers for J2EE (OC4J), which you can use as a Web listener and a servlet container to test Forms applications. To get ready to build and test a form, perform the following steps:

1.From the Windows Start menu, select Programs | Oracle Developer Suite iDS10g | Forms Developer | Start OC4J Instance.

From the Windows Start menu, select Programs | Oracle Developer Suite -Devsuite home1 | Forms Developer | Forms Builder.

When Forms Builder starts, an empty form is automatically created named Module1. Select the Module1 name, then click it again to change the name of the form. Type in the name Employee and press enter.

Click Save . By default, Forms saves files in the \bin directory of your Developer Suite installation. You may want to save it in a different directory; if desired, you can create a new directory called proects in which to save the application.

Connect to the database as the HR user by clicking Connect.

Building a data-block:-

1. Select the Data Blocks node in the Object Navigator, then click Create .

2. In the New Data Block dialog, ensure that Use the Data Block Wizard is selected, then click OK.

3. In the Table or view field, type EMP_MASTER for the table name, then click Refresh. Click the right double arrow button to move all available columns to the Database Items to be included in the block, then click Next >.

4. Accept the default name for the block, which is the same as the table name, and click Next >.

5. On the final page of the Data Block Wizard, click Finish to create the data block and invoke the Layout Wizard.

If the Welcome to the Layout Wizard page displays, click Next >.

Accept the defaults and click Next > to lay out the items on a new content canvas.

Display the EMP_CODE and EMP_NAME and click Next>

Click Next > to accept the default item prompt, width, and height for both items.

Click Next > to accept the Form layout.

On the final page of the Layout Wizard, click Finish to create the canvas and open it in the Layout Editor.

Click Save .

Click Run Form to test it (be sure that you have started OC4J -- see Step 1).

Click Exit Form to exit the application, then close the browser window.

What is Oracle Developer Suite?

Oracle Developer Suite is a suite of development tools . The principal components are Oracle forms,Oracle reports and jdeveloper.

Oracle Forms is a software product for creating screens that interacts with oracle database.It has an IDE that includes object navigator,property sheet and plsql editor for writing oracle related code.You can create procedures and functions in the forms.The source form (*.fmb) is compiled into an "executable" (*.fmx), that is run (interpreted) by the forms runtime module. The form is used to view and edit data in database-driven applications. Various GUI elements, such as buttons, menus, scrollbars, and graphics can be placed on the form.

Oracle Reports is a tool for developing reports for the data stored in oracle database.
There are various output formats text,pdf,spreadsheet,xml,rtf,html.

JDeveloper is a  IDE .It offers features for development in sql/plsql. JDeveloper covers the full development lifecycle from design through coding, debugging, optimization and profiling to deploying.

Friends
No one can become expert in any particular computer language or database,especially like oracle(Its an RDBMS).Its a very vast topic and in this blog I'll try to solve the oracle related issues as much as possible and will also try to share my knowledge with all of you.