, ,

Oracle Forms..An Introduction and Brief History

Oracle Forms…What is it?
•A software product for creating screens that interact with an Oracle Database.
•Has a typical IDE including an object navigator, property sheet and code editor that uses PL/SQL.
•Primary focus of Forms is to create data entry systems that access an Oracle database.
•Originally sold as standalone product , now bundled into a package suite called Oracle Developer Suite.
History
•Interactive Application Facility (IAF)
-Block Mode
-Oracle Database 2
•FastForms
-Character Mode
-Oracle Database 4
•SQL*Forms version 2, 2.3
-Character Mode
-Oracle Database 5
•SQL*Forms 3
-Character Mode
-Oracle Database 6
-New IDE, PL/SQL
•Oracle Forms 4.0/4.5
-4.0 was the first “true” GUI based version
-Oracle Database 6/7
-Optimized for client server.
-4.0 interface was slow, buggy and un-popular with client base.
-4.5 was a popular one with enhanced GUI.
•Oracle Forms 5
-Oracle Database 7
•Oracle Forms 6/6i
-Forms server and web forms were introduced.
-Oracle Database 8/8i
•Forms 7 to 8 did not exist. These numbers were jumped over in order to allow the Oracle Forms version number to match the database version.
•Oracle Forms 9i
-Oracle Database 9i
-Stable one with many bug fixes.
-Three-tier, browser based delivery
•Oracle Forms 10g
-Oracle Database 10g
-Actually a Forms release (9.0.4.0.19)
•Oracle Forms 11g
-Oracle Database 11g
-Latest One.
/by
, , ,

SRW Package in Oracle Report

SRW (Sql Report Writer) Package is a built in package in Oracle Reports Builder. It is a collection of PL/SQL constructs that include many functions, procedures, and exceptions you can reference in any of your libraries or reports.
The PL/SQL provided by the SRW package enables you to perform such actions as change the formatting of fields, run reports from within other reports, create customized messages to display in the event of report error, and execute SQL statements. There are nearly 70 functions, procedures, and exceptions are there in this package. Here I am giving brief information and uses of few important functions, procedures, and exceptions.
SRW.MESSAGE:
It is a Procedure that displays a message with the message number and text that you specify. It is mainly used to debug a report in Reports Builder.
SRW.MESSAGE(msg_number NUMBER, msg_text CHAR);
Example:
function foo return boolean is
begin
  if :sal < 0 then
    SRW.MESSAGE(100, 'Found a negative salary. Check the EMP table.');
    raise SRW.PROGRAM_ABORT;
  else
    :bonus := :sal * .01;
  end if;
  return(true);
end;




SRW.PROGRAM_ABORT:


This exception stops the report execution and raises the following error message: REP-1419: PL/SQL program aborted. SRW.PROGRAM_ABORT stops report execution when you raise it.


SRW.DO_SQL:


This procedure executes the specified SQL statement from within Reports Builder. The SQL statement can be DDL (statements that define data), or DML (statements that manipulate data). DML statements are usually faster when they are in PL/SQL, instead of in SRW.DO_SQL.


Since you cannot perform DDL statements in PL/SQL, the SRW.DO_SQL packaged procedure is especially useful for performing them within Reports Builder.


Example:




FUNCTION CREATETABLE RETURN BOOLEAN IS
BEGIN
  SRW.DO_SQL('CREATE TABLE TEST_EMP (EMPNO NUMBER NOT NULL
    PRIMARY KEY, SAL NUMBER (10,2)) PCTFREE 5 PCTUSED 75');
    RETURN (TRUE);
   EXCEPTION
    WHEN SRW.DO_SQL_FAILURE  THEN
    SRW.MESSAGE(100, 'ERROR WHILE CREATING TEST_EMP TABLE.');
    RAISE
    SRW.PROGRAM_ABORT;
END;






SRW.DO_SQL_FAILURE:


Reports Builder raises this exception when the SRW.DO_SQL packaged procedure fails. This exception stops the report execution and raises the following error message:


REP-1425: Error running DO_SQL package – REP-msg ORA-msg.


SRW.GET_REPORT_NAME:


This function returns the file name of the report being executed.


SRW.GET_REPORT_NAME (report_name);


Example:




function AfterPForm return boolean is
    my_var varchar2(80);
  BEGIN
    SRW.GET_REPORT_NAME (my_var);
    SRW.MESSAGE(0,'Report Filename = '||my_var);
   RETURN (TRUE);
  END;






SRW.RUN_REPORT:


This procedure synchronously executes the specified report within the context of the currently running report.


SRW.RUN_REPORT (“report=test.rdf … “)


SRW.SET_FIELD:


This procedure sets the value of a character, number, or date field. This is useful when you want to conditionally change a field’s value.


SRW.SET_FIELD (object_id, text CHAR | number NUM | date DATE);


Example:


Suppose you want to conditionally change the number of a field, based on each employee’s salary. In the format trigger for the field, you could type the following:




FUNCTION CHGFIELD RETURN BOOLEAN IS
TMP NUMBER;
 BEGIN
  if :sal >= 5000 then
  tmp := :sal * 1.10;
  srw.set_field (0, tmp);
 else
  srw.set_field (0, 4000);
 end if;
 RETURN (TRUE);
END;





SRW.SET_FIELD should be used only to change the contents of a field’s datatype, not change the field to a different datatype.


Others in Brief:


  • SRW.SET_FONT_FACE: This procedure specifies font face for a CHAR, DATE, or NUMBER field. SRW.SET_FONT_FACE(‘arial’);

  • SRW.SET_FONT_SIZE: This procedure specifies font size for a CHAR, DATE, or NUMBER field. SRW.SET_FONT_SIZE(10);

  • SRW.SET_FONT_STYLE: This procedure specifies font style for a CHAR, DATE, or NUMBER field. SRW.SET_FONT_STYLE(SRW.ITALIC_STYLE);

  • SRW.SET_FORMAT_MASK: This procedure specifies the format mask for the DATE or NUMBER field. SRW.SET_FORMAT_MASK(‘mask’);

  • SRW.SET_TEXT_COLOR: This procedure specifies the global text color of the CHAR, DATE, or NUMBER field. SRW.SET_TEXT_COLOR(‘color’);






/by 
, , , 
Oracle Reports:An Introduction


Oracle Reports:An Introduction


Oracle Reports, the high-fidelity enterprise reporting tool, enables businesses to give immediate access to information to all levels within and outside of the organization in a scalable and secure environment. Using Oracle Reports, you can publish information from any data source, in any format (PDF, HTML, printed, XML, etc.), to any destination (Web, Portal, e-mail, file, etc.) in a scalable, efficient manner.


The main features are:


    

  • You can access data from any data source
    • 

  • You can obtain the report data in a graphical representation by a query builder
    • 

  • You can use default report templates or you can customize it if needed
    • 

  • A live editor that allows you to modify paper report layouts in WYSIWYG (“what you see is what you get”) mode
    • 

  • The ability to add dynamic report output to an HTML page by embedding custom JavaServer Page (JSP) tags within an HTML document
    • 

  • You can graphically represent report data with the help of  integrated chart builder
    • 

  • You can use various web publishing tools that dynamically generate Web pages based on your data
    • 

  • You can show the report data in various formats such as HTML, HTMLCSS, XML, PDF, PCL (Printer Control Language), Postscript, and ASCII
    • 

  • Support for run-time customization
    • 

  • You can execute dynamic SQL statements within PL/SQL procedures
    • 

  • Seamless integration of Oracle Reports with OracleAS Portal for administering report security
    • 

  • The ability to publish report output to portlets
    • 



Designing Reports


Before you create a report using any report-writing software, you must first consider the type of report that you are being asked to produce. You will have a specification of the needs, required output, and the expected publishing medium, but you also need to know the underlying structure that supports the requirement and the most efficient way to retrieve data.




Common report styles


The majority of report requirements fall into the following categories:


    

  • Tabular: One group
    • 

  • Master-detail: Master-detail hierarchy (may be several levels)
    • 

  • Master and multiple details: Two or more detail groups at the same level
    • 

  • Matrix: Two masters, one detail
    • 



Running a Report


There are many ways of running a report, depending on the application design. You can call a report from:


    

  • A Web browser
    • 

  • An OracleAS Portal application
    • 

  • The command line, using the Start > Run option in Windows
    • 

  • The OracleAS Reports Queue Manager
    • 

  • A Java application
    • 

  • A database trigger
    • 

  • A customized menu in a Forms application
    • 

  • A button in a Forms application
    • 



Supported File Types


    

  • RDF: Report Definition File: binary file containing source code
    • 

  • REP: Report: binary file without source code
    • 

  • JSP: JavaServer Page format
    • 

  • HTML: HyperText Markup Language
    • 

  • XML: Extensible Markup Language
    • 



Reports Builder Modules


The Reports Builder interface enables you to create a number of different types of modules, and it provides a Report Editor in which you can view the structure and objects in a report module. The Reports Builder module types are:


    

  • Report: A report definition
    • 

  • Template: A skeleton definition containing common style and standards
    • 

  • PL/SQL Library: A stand-alone library containing PL/SQL program units—procedures, functions, packages—that can be called from multiple reports
    • 



Report Data and Layout


A report definition defines two main parts of a report and brings them together in the output.


    

  • Data: Data structure and data to be displayed
    • 

  • Layout: Formatting information about how the data appears in the output
    • 



Each report module can have a data model, a paper layout, and a Web layout. The data model, as well as program units, can be shared by the paper and Web layouts.


A report can consist of:


    

  • A data model and a paper layout.
    • 

  • A data model and a Web layout.
    • 

  • A data model, a paper layout, and a Web layout.
    • 



Reports Builder Components


Object Navigator


The Object Navigator is a hierarchical browsing and editing interface that enables you to locate and manipulate application objects quickly and easily.


Report Editor


The Report Editor contains different views to help you handle the data objects and layout objects for Web and paper reports.


Property Inspector


All objects in a module, including the module itself, have properties that you can see and modify in the Property Inspector.


PL/SQL Editor


The PL/SQL Editor enables you to create and compile program units such as procedures, functions, and packages within the current report.


Wizards in Reports Builder


Wizards provide an easy step-by-step interface for commonly performed tasks. The wizards in Reports Builder are:


    

  • Report Wizard: The Report Wizard guides you through the steps to create a basic paper report. Each page of the wizard asks you for information to help you create your initial report.
    • 

  • Data Wizard: This wizard helps you quickly define or modify a query for a multiquery data models.
    • 

  • Graph Wizard: You can add a variety of charts and graphs, including true 3-dimensional graphs, to a report using the Graph Wizard. Charting is implemented in Reports Builder with the Oracle BI graph bean.
    • 

  • Report Block Wizard: This wizard enables you to quickly create a JSP report by embedding report data into a Web page using Reports custom JSP tags.
    • 





/by 
, , 
Flex mode and Confine mode


Flex mode and Confine mode


Confine mode:


On: child objects cannot be moved outside their enclosing parent objects.
Off: child objects can be moved outside their enclosing parent objects.


Flex mode:


On: parent borders “stretch” when child objects are moved against them.
Off: parent borders remain fixed when child objects are moved against
them.




/by 
, , , 
SQL*Loader Environment


SQL*Loader Environment


SQL*Loader takes as input a control file, which controls the behavior of SQL*Loader, and one or more datafiles. Output of the SQL*Loader is an Oracle database (where the data is loaded), a log file, a bad file, and potentially a discard file.




Execution Steps:


Step1    Executes from the command prompt or parameter file.


Step2    Reads the control file.


Step3    Reads the data from the control file or from one or more datafiles.


Step4    Loads the data in the database and logs the information in the log file.


Step5    Places the rejected records in the bad file.


Step6    Places the discarded records in the discard file.


1] The parameter file:


The command line information can be saved in a parameter file. This parameter file is executed from the command prompt. A parameter file has a .par extension. Following is a sample parameter file.


Steps to create a parameter file:


    

  • Open a text editor.
    • 

  • Type in each parameter with its corresponding value as shown in the parameter file.
    • 

  • Save the file with the .par extension.
    • 



How do you execute the parameter file?




At the command prompt type sqlldr PARFILE = <parfile name>.


2] The Control File:


    

  • The control file is a text file written in a language that SQL*Loader understands.
    • 

  • The control file describes the task that the SQL*Loader is to carry out. The control file tells SQL*Loader where to find the data, how to parse and interpret the data, where to insert the data, and more.
    • 

  • It also contains the names and locations of the bad file and the discard file.
    • 

  • Some of above information (such as name and location of the input file) can also be passed to SQL*Loader as command-line parameters.
    • 

  • It’s also possible for the control file to contain the actual data to be loaded. This is sometimes done when small amounts of data need to be distributed to many sites, because it reduces (to just one file) the number of files that need to be passed around.
    • 



 A sample control file is given below:




In general, the control file has three main sections, in the following order:


A] Session-wide information:


The session-wide information contains the names of the input/output files for the data load session. Apart from this, other SQL*Loader parameters can also be listed in this section.


    

  • The LOAD DATA statement is required at the beginning of the control file.
    • 

  • INFILE * specifies that the data is found in the control file and not in an external data file.
    • 

  • BADFILE ‘example1.bad’ indicates that all erroneous records must be stored in the file example1.bad.
    • 

  • DISCARDFILE ‘example1.dsc’ indicates that all discarded records must be stored in the file example1.dsc.
    • 



B] Table and Field_List Information:


The INTO TABLE statement specifies the table into which data should be loaded. In this case it is the dept table. By default, SQL*Loader requires the table to be empty before it inserts any records.


FIELDS TERMINATED BY specifies that the data is terminated by commas, but can also be enclosed by quotation marks. Data types for all fields default to CHAR.


The names of columns to load are enclosed in parentheses. Because no data type or length is specified, the default is type CHAR with a maximum length of 255.


C] Input Data:


BEGINDATA specifies the beginning of the data. The data to be loaded is present below the BEGINDATA command.


3] Input Datafiles:


    

  • The data to be loaded is contained in one or more datafiles if it is not contained in the control file.
    • 



    

  • The data in the datafile can be in the fixed length format, variable length format, or in the stream record format.
    • 



A] Fixed Length Format:


A file is in the fixed record format when all the records in the datafile have the same byte length. This format is not flexible but offers very good performance.then the syntax for the INFILE command is – INFILE student.dat “fix 15″


The syntax for letting SQL*Loader know that the data is in the fixed length format is:


INFILE datafile_name “fix n”


Here INFILE datafile_name refers to the file that contains the data to be loaded. “fix n” implies that each record in the datafile has a fixed byte length of n.


For example if the name of the following datafile is student.dat and the byte length of a record is 15 bytes
0001, —–Rina, 0002, —-Harry, 0003,—–Sudha


B] Variable Length Format:


A file is in the variable record format when the length of each record varies. The length of each record is included at the beginning of the record in the datafile. This format provides some added flexibility over the fixed record format and a performance advantage over the stream record format.
For example, you can specify a datafile that is to be interpreted as being in variable record format as follows:


INFILE “datafile_name” “var n”


Here n specifies the number of bytes in the record length field. If n is not specified, SQL*Loader assumes a length of 5 bytes. If n is specified larger than 40 it results in an error. The following datafile is random.dat and the value for n is 3.


009hello,cd,010world,im,
012my,name is,


SQL*Loader reads the first 3 bytes to gather the length of the record. Here the first record is 9 bytes long. After SQL*Loader has read 9 bytes, it reads the next 3 bytes to find the size of this record which is 10 bytes long. It reads the next 10 bytes of the record and then finds the third record is 12 bytes long and so on.


C] Stream Record Format:


A file is in the stream record format when the records are not specified by size; instead SQL*Loader forms records by scanning for the record terminator. Stream record format is the most flexible format, but there can be a negative effect on performance. The syntax for specifying the stream record format is as follows:


INFILE datafile_name [“str terminator_string”]


The terminator_string can be a ‘char_string’  which is a string of characters enclosed in single or double quotation marks or a ‘hex_string’ which is a byte string in hexadecimal format.


4] The Log File:


The log file is a record of SQL*Loader’s activities during a load session. It contains information such as the following:


    

  • The names of the control file, log file, bad file, discard file, and data file
    • 

  • The values of several command-line parameters
    • 

  • A detailed breakdown of the fields and datatypes in the data file that was loaded
    • 

  • Error messages for records that cause errors
    • 

  • Messages indicating when records have been discarded
    • 

  • A summary of the load that includes the number of logical records read from the data file, the number of rows rejected because of errors, the number of rows discarded because of selection criteria, and the elapsed time of the load
    • 



Always review the log file after a load to be sure that no errors occurred, or at least that no unexpected errors occurred. This type of information is written to the log file, but is not displayed on the terminal screen.


5] The Bad File:


Whenever you insert data into a database, you run the risk of that insert failing because of some types of error. Integrity constraint violations undoubtedly represent the most common type of error. However, other problems, such as the lack of free space in a tablespace, can also cause insert operations to fail. Whenever SQL*Loader encounters a database error while trying to load a record, it writes that record to a file known as the bad file.


    

  • If one or more records are rejected, the bad file is created and the rejected records are logged.
    • 

  • If no records are rejected, then the bad file is not created.
    • 



6] The Discard File:


While SQL*Loader is being executed it creates a discard file for records that do not meet any of the loading criteria. The records contained in this file are called discarded records. Discarded records do not satisfy any of the WHEN clauses specified in the control file. These records differ from rejected records. Discarded records do not necessarily have any bad data. A discarded record is never inserted into the Oracle table.


A discard file is created according to the following rules:


    

  • You have specified a discard filename and one or more records fail to satisfy all of the WHEN clauses specified in the control file. (If the discard file is created, it overwrites any existing file with the same name, so be sure that you do not overwrite any files that you want to retain.)
    • 

  • If no records are discarded, then a discard file is not created.
    • 





/by