, , ,

Oracle Forms Builder

A typical database schema contains dozens of tables, each with several columns of various data types. We develop applications (forms, reports, menus, etc.) to make the job of manipulating data in those tables easier for users. In Oracle Forms, a form (or data entry form) acts like a window into the database schema. An individual focuses the attention of the user to one or a few of the tables at a time. In addition, a form can give prompts so the user knows what kind of input is expected and how data is to be entered and manipulated.
By default, every form in Oracle Forms has the capability to query existing data in a table, modify existing data and add new data (records) to the table. A form is built up using one or more data blocks that correspond to tables in the database. Fields within the data block correspond to columns in the database table. A data block is similar to a DataWindow object in PowerBuilder. In the following figure, a typical form is shown.
Oracle Developer9i provides various Graphical User Interface (GUI) tools to create functional applications from database definitions, without writing any code. You can use these tools to create, manage, and deploy integrated applications.
Some tools provided by the Oracle Developer9i are:

  1.       The Forms Builder
  2.       The Reports Builder
  3.       The Discoverer Administrator
  4.       The Discoverer Desktop

The Forms Builder
You can use the Forms Builder to easily and quickly construct multi-lingual and interactive database forms, charts, and business logic. Applications developed using Oracle Forms Builder can be instantly deployed over the Internet using the Oracle Internet Application Server.
Some features of the Forms Builder are:
Legacy Application Integration: Enables migration of existing client-server applications to the Internet using rich Java user interfaces.
Developer Productivity: Develop applications quickly with:
            Easy-to-use re-entrant wizards to create blocks and relations.
            Property pallets to set visual attributes.
            Shared libraries and templates to enforce standards.
            Easy PL/SQL scripting for triggers and business logic.
            Debugging across application and database logic.
            PL/SQL code exchange between application and database.
            Live previewers to test your application and version control and impact analysis.
Oracle8i Integration: Enables you to leverage Oracle8i features, such as transaction management, resource management, advanced queuing, and parallel server to share application resources.
Scalability: Enables you to deliver scalable applications that perform across any network inside or outside firewall.
Manageability: Enables you to manage and configure multiple application servers from a single location with Oracle Enterprise Manager.
Internationalization: Enables you to develop applications that can be translated easily with multi-language support.
Enterprise Application Development: Supports application development through a built-in forms server, which receives CGI requests from thin clients, such as browsers and serve the appropriate request.
Interoperability: Is completely interoperable with Java and contains Enterprise Java Beans, Servlets and hosts smart applets. It is also compatible with object technologies, such as CORBA and COM+.
Reduces downtime: Supports previously offline DBA routines, such as database backup, restore, DDL statements through application routines. This reduces the downtime for the Oracle Database Server.
Form Types
There are four main types of forms that can be designed

/by
, , , ,

Defining Alerts in R12


You can create alerts that are as simple or as complex as you need them to be, and you can tailor your alerts in a variety of ways so they perform the kind of exception reporting your organization needs. There are two types of alerts: event and periodic. Both types are defined by a SQL Select statement that you specify.
You can create event alerts that monitor your applications for the exception conditions you specify. You can create periodic alerts that check your database for predefined conditions according to the schedule you determine.
Major Features
Verify SQL
You can verify that your alert’s SQL Select statement runs correctly, and returns the data you specify. You can do this verification directly in Oracle Alert immediately after you enter your Select statement – you don’t have to suspend your Oracle Alert session or navigate to SQL*Plus.
Specify Installations
You can specify which Application installations you want your alert to run against, so you can control which Application installations your alert checks in a database with multiple Application installations.
Information Routing
With Oracle Alert, you can include a file created by another application as part of an alert message. You can also define an alert that distributes an electronic copy of a report, log file, or any other ASCII file.
Dynamic Message Distributions
Oracle Alert lets you define a message distribution list without knowing ahead of time who the actual individuals on the list will be. Oracle Alert can retrieve the appropriate electronic mail IDs from your application tables, and send the message automatically.
Duplicate Suppression
Oracle Alert can automatically determine which action to perform based on whether it locates the same exceptions during a sequence of alert checks. You can have Oracle Alert perform a different action during each alert check that finds the same database exception.
Customizable Inputs by Action Set
You can further customize your alerts by specifying parameters for each set of actions you define. These parameters, or inputs, provide you with extra flexibility in creating your alerts because you can assign specific values to them. For example, if a vendor delivery is overdue, Oracle Alert can notify the purchasing agent when it is two days late and the purchasing manager when it is seven days late. The number of days late is the input; two and seven are distinct input values you assign for each type of recipient.
Distribution Lists
Oracle Alert lets you create an electronic distribution list that you can use on many messages. At any time, you can add or remove names from your lists, or you can make copies of your lists and use the copies to create new lists.
Standard Message Address Formats
Oracle Alert lets you address messages using easily recognizable symbols: to, cc, bcc, just as you would use when writing a memo or sending electronic mail.
Printed Alert Messages
Oracle Alert lets you send messages to people who do not use electronic mail. You can direct a message to a printer with the recipient’s name on the burst page.
Electronic Mail Integration
Oracle Alert leverages the Workflow Notification Mailer to send and receive alert e-mail messages. The notification mailer uses the Simple Mail Transfer Protocol (SMTP) for outbound messages and the Internet Message Access Protocol (IMAP) for inbound messages.
/by
, ,

Alerts in Oracle R12


Oracle Alert is your complete exception control solution. Alerts signal important or unexpected activity in your database. They ensure that you are regularly and quickly informed about critical database events instead of sorting through length reports.
The basic function of alerts includes but not limited to

  1. Keep you informed of critical activity in your database
  2. Deliver key information from your applications, in the format you choose
  3. Provide you with regular reports on your database information
  4. Automate system maintenance, and routine online tasks

Alerts keep a constant check on your database information and prompt you when the specified criteria are met. You can use either an Oracle application or a custom oracle application to define alerts. However, few applications such as purchasing, allow you to simply activate and use alerts supply by default.
You can define one of two types of alerts: an event alert or a periodic alert.
An event alert immediately notifies you of activity in your database as it occurs. When you create an event alert, you specify the following:

  • A database event that you want to monitor, that is, an insert or an update to a specific database table.
  • A SQL Select statement that retrieves specific database information as a result of the database event.
  • Actions that you want Oracle Alert to perform as a result of the database event. An action can entail sending someone an electronic mail message, running a concurrent program, running an operating script, or running a SQL statement script. You include all the actions you want Oracle Alert to perform, in an action set.

A periodic alert, on the other hand, checks the database for information according to a schedule you define. When you create a periodic alert, you specify the following:

  • A SQL Select statement that retrieves specific database information.
  • The frequency that you want the periodic alert to run the SQL statement.
  • Actions that you want Oracle Alert to perform once it runs the SQL statement. An action can entail sending the retrieved information to someone in an electronic mail message, running a concurrent program, running an operating script, or running a SQL statement script. You include all the actions you want Oracle Alert to perform, in an action set

By creating event alerts, you can have an immediate view of the activity in your database, so you keep on top of important or unusual events as they happen. By creating periodic alerts, you can have current measurements of staff and organization performance, so you can zero in on potential trouble spots. You can automate routine transactions, preserving your valuable time for more important issues. Oracle Alert gives you the information you need online, so you do not have to contend with a pile of paperwork.
Workflow vs Alerts
Unlike alerts, workflow is defined in a system to detect a condition and requires user intervention. Every time a response is not recorded during a designated time period, workflow may send a notification to the user’s manager depending upon the workflow definition. It is difficult to accomplish such notification using alerts.

/by
, , , , ,

Basics of PL/SQL

PL/SQL stands for Procedural Language extension of SQL.
PL/SQL is a combination of SQL along with the procedural features of programming languages. It was developed by Oracle Corporation in the early 90’s to enhance the capabilities of SQL.
PL/SQL Environment
PL/SQL is not an Oracle product in its own right; it is a technology used by the Oracle server and by certain Oracle tools. Blocks of PL/SQL are passed to and processed by a PL/SQL engine, which may reside within the tool or within the Oracle server. The engine that is used depends on where the PL/SQL block is being invoked from. When you submit PL/SQL blocks from a Oracle precompiler such as Pro*C or Pro*Cobol program, userexit, iSQL*Plus, or Server Manager, the PL/SQL engine in the Oracle Server processes them. It separates the SQL statements and sends them individually to the SQL statements executor.

A single transfer is required to send the block from the application to the Oracle Server, thus improving performance, especially in a client-server network. PL/SQL code can also be stored in the Oracle Server as subprograms that can be referenced by any number of applications connected to the database.
Many Oracle tools, including Oracle Developer, have their own PL/SQL engine, which is independent of the engine present in the Oracle Server. The engine filters out SQL statements and sends them individually to the SQL statement executor in the Oracle server. It processes the remaining procedural statements in the procedural statement executor, which is in the PL/SQL engine. The procedural statement executor processes data that is local to the application (that is, data already
inside the client environment, rather than in the database). This reduces the work that is sent to the Oracle server and the number of memory cursors that are required.
Advantages of PL/SQL
These are the advantages of PL/SQL.
Block Structures: PL SQL consists of blocks of code, which can be nested within each other. Each block forms a unit of a task or a logical module. PL/SQL Blocks can be stored in the database and reused.
Procedural Language Capability: PL SQL consists of procedural language constructs such as conditional statements (if else statements) and loops like (FOR loops).
Better Performance: PL SQL engine processes multiple SQL statements simultaneously as a single block, thereby reducing network traffic.
Error Handling: PL/SQL handles errors or exceptions effectively during the execution of a PL/SQL program. Once an exception is caught, specific actions can be taken depending upon the type of the exception or it can be displayed to the user with a message.
Architecture
The PL/SQL language is a robust tool with many options. PL/SQL lets you write code once and deploy it in the database nearest the data. PL/SQL can simplify application development, optimize execution, and improve resource utilization in the database.
The language is a case-insensitive programming language, like SQL. This has led to numerous formatting best practice directions. Rather than repeat those arguments for one style or another, it seems best to recommend you find a style consistent with your organization’s standards and consistently apply it. The PL/SQL code in this book uses uppercase for command words and lowercase for variables, column names, and stored program calls
PL/SQL also supports building SQL statements at run time. Run-time SQL statements are dynamic SQL. You can use two approaches for dynamic SQL: one is Native Dynamic SQL (NDS) and the other is the DBMS_SQL package. The Oracle 11g Database delivers new NDS features and improves execution speed. With this release, you only need to use the DBMS_SQL package when you don’t know the number of columns that your dynamic SQL call requires. Chapter 11 demonstrates dynamic SQL and covers both NDS and the DBMS_SQL package.

/by
, , ,

OAF Personalization

In Oracle Trade Management the links ‘About This Page’ & ‘Personalize Page’ links are not enabled on any page of Oracle Trade Management even though the required profile options ‘FND: Diagnostics’, ‘FND: Personalization Region Link Enabled’ ‘,’ Personalize Self-Service Defn’ are set to ‘YES’.
How to proceed further with personalisations/customizations in Oracle Trade Management?
Solution
Oracle Trade Management is built based on 2 different frameworks, OAF and JTF.
So in Oracle Trade Management the links ‘About This Page’ & ‘Personalize Page’ links are not enabled
The w-a is that :
there is another navigation for the Personalization of OA pages to do customizations from here:
Functional Administrator > Personalization Tab > Application Catalog
Search by Application = Trade Management and Document Path. e.g. for Create Special Pricing UI, Document Path =/oracle/apps/ozf/request/webui/SpRequestPartnerCreatePG
Click on Personalize Page UI.
Click on Apply Button.
This will take  Personalization Structure of this UI.
/by