Oracle 11g – PL SQL – New features — Reference


I found a nice article, contain reference DocID from Metalink to know latest and new features of Oracle Database 11g PL/SQL

All the new features available with 11.1 release of oracle are listed together in
Note 869084.1 : New PL/SQL Features for 11g Release 1 (11.1).
The contents of the article is pasted below.

New PL/SQL Features for 11g Release 1 (11.1)

  • PL/SQL Native Compilation
    In Oracle 11g, PL/SQL native compilation requires no C compiler, no DBA intervention and is fully supported in a RAC environment. By setting the PLSQL_CODE_TYPE to a value of NATIVE , rather than the default value of INTERPRETED , code is compiled directly to machine code . When the code is called, it is loaded into shared memory, making it accessible for all sessions in that instance. The %_PLSQL_OBJECT_SETTINGS views include the current PLSQL_CODE_TYPE setting for each PL/SQL object.
    Note 462449.1 : PL/SQL Native Compilation on Oracle 11G

 

  • PL/SQL Function Result Cache
    The cross-session PL/SQL function result cache provides a simple way to boost the performance of PL/SQL functions by saving the results of function calls for specific combinations of input parameters in the SGA. These results can be reused by any session calling the same function with the same parameters. This can result in a significant performance boost when functions are called for each row in an SQL query, or within a loop in PL/SQL. This article describes the usage and administration of the function result cache.
    Note 430887.1  : 11g New Feature PL/SQL Function Result Cache

 

  • PL/SQL Fine-Grained Dependencies
    Prior to Oracle11g, modifying the structure of a table would make dependent views invalid even thought the change to table has nothing to do with view.That is why starting from Oracle11g a change to table that does not effect view logically, will NOT make view invalid. So, if you add a column to table T1 then because of fine grained dependency tracking system applied by Oracle 11g, it doesn’t make view invalid.
    Note 430725.1 : 11g New Feature Fine-Grained Dependencies

 

  • PL/SQL Continue Statement
    In Oracle Database 11g PL/SQL has a new construct called CONTINUE , which is used in a loop. The statement moves the logic to the end of the loop and then to the beginning of the loop.The CONTINUE statement jumps out of the current loop iteration and starts the next one. It can be used on its own, or as part of a CONTINUE WHEN statement. This type of processing has always been possible using IF statements either on their own or with exceptions or GOTO statements, but the CONTINUE statement is neater and brings PL/SQL in line with other languages.
    Note 469584.1 : 11g New Feature PLSQL CONTINUE Statement

 

  • PL/SQL Automatic Subprogram Inlining
    Every call to a procedure or function causes a slight, but measurable, performance overhead, which is especially noticeable when the subprogram is called within a loop. Avoiding procedures and functions is not an option, as it goes against the concept of modular programming, making programs bulky and difficult to manage. Automatic subprogram inlining can reduce the overheads associated with calling subprograms, whilst leaving your original source code in its normal modular state. This is done by replacing the subprogram calls with a copy of the code in the subprogram at compile time.
    The process of subprogram inlining is controlled by the PLSQL_OPTIMIZE_LEVEL parameter and the INLINE pragma. When PLSQL_OPTIMIZE_LEVEL=2 (the default), the INLINE pragma determines whether the following statement or declaration should be inlined or not. When PLSQL_OPTIMIZE_LEVEL=3, the optimizer may inline code automatically. In this case the INLINE pragma can turn it off inlining for a statement, or increase the likelihood that the optimizer will choose to inline a statement.
    http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/tuning.htm#i53930

 

  • PL/SQL SIMPLE_INTEGER, SIMPLE_FLOAT, and SIMPLE_DOUBLE Data Types
    The SIMPLE_INTEGER, SIMPLE_FLOAT, and SIMPLE_DOUBLE data types are predefined subtypes of PLS_INTEGER, BINARY_FLOAT, and BINARY_DOUBLE, respectively. Each subtype has the same range as its base type and has a NOT NULL constraint.
    SIMPLE_INTEGER differs significantly from PLS_INTEGER in its overflow semantics, but SIMPLE_FLOAT and SIMPLE_DOUBLE are identical to their base types, except for their NOT NULL constraint.
    You can use SIMPLE_INTEGER when the value will never be NULL and overflow checking is unnecessary. You can use SIMPLE_FLOAT and SIMPLE_DOUBLE when the value will never be NULL. Without the overhead of checking for nullness and overflow, these subtypes provide significantly better performance than their base types when PLSQL_CODE_TYPE=’NATIVE’, because arithmetic operations on SIMPLE_INTEGER values are done directly in the hardware. When PLSQL_CODE_TYPE=’INTERPRETED’, the performance improvement is smaller.
    Note 433091.1 : 11g New Features SIMPLE_INTEGER Datatype

  • Named and Mixed Notation in PL/SQL Subprogram Invocations
    Before Release 11.1, a SQL statement that invoked a PL/SQL subprogram had to specify the actual parameters in positional notation. As of Release 11.1, named and mixed notation are also allowed. This improves usability when a SQL statement invokes a PL/SQL subprogram that has many defaulted parameters, and few of the actual parameters must differ from their default values.
    Note 433070.1 : 11g New Feature Using Named and Mixed notation in PL/SQL subprograms

 

  • Enhancements to Regular Expression Built-in SQL Functions
    The regular expression built-in functions REGEXP_INSTR and REGEXP_SUBSTR have increased functionality. A new regular expression built-in function, REGEXP_COUNT, returns the number of times a pattern appears in a string. These functions act the same in SQL and PL/SQL.

 

  • Sequences in PL/SQL Expressions
    The pseudocolumns CURRVAL and NEXTVAL make writing PL/SQL source code easier for you and improve run-time performance and scalability. You can use sequence_name.CURRVAL and sequence_name.NEXTVAL wherever you can use a NUMBER expression.
    Note 433068.1 : Sequences in PL/SQL Expressions
  • Dynamic SQL Enhancements
    Both native dynamic SQL and the DBMS_SQL package have been enhanced.Native dynamic SQL now supports a dynamic SQL statement larger than 32 KB by allowing it to be a CLOB.
    In the DBMS_SQL package:
    • All data types that native dynamic SQL supports are supported.
    • The DBMS_SQL.PARSE function accepts a CLOB argument, allowing dynamic SQL statements larger than 32 KB.
    • The new DBMS_SQL.TO_REFCURSOR Function enables you to switch from the DBMS_SQL package to native dynamic SQL.
    • The new DBMS_SQL.TO_CURSOR_NUMBER Function enables you to switch from native dynamic SQL to the DBMS_SQL package.
    Note 435251.1   : How Native Dynamic Sql Supports Statements > 32K Characters
    Note 567856.1 : New Feature in 11g , DBMS_SQL.TO_REFCURSOR and DBMS_SQL.TO_CURSOR_NUMBER.

 

  • Compound Trigger
    A compound trigger allows different blocks within a trigger to be executed at different timing points. It has a declaration section and a section for each of its timing points. All of these sections can access a common PL/SQL state.
    Note 430847.1 : 11g NEW PL/SQL COMPOUND Trigger

Oracle Interview Questions – Oracle Forms – Custom Development – FAQ


What is the ICX: Forms Launcher system profile option for?

This profile option is used by the Self Service Web Applications Personal Home Page (also known as ICX) to determine the base URL needed to launch an application, which in this case is a Forms application.

What should ICX: Forms Launcher be set to?

ICX: Forms Launcher –is set to ?http://machine_name:port/dev60cgi/f60cgi?. In Apps release 11i you can add some parameters to this URL to enable some Forms features like tracing.

What is FORMS60_TIMEOUT?
FORMS60_TIMEOUT is an environment setting that determines the maximum idle time (in minutes) before f60webmx shuts down.  Note that it will only terminate an idle middle tier process, i.e. one which is waiting for the "next message" from the client. If the middle tier process is running a transaction or waiting for a query to complete it will not have take effect.

How do you enable Forms Runtime Diagnostics (FRD) in Apps?
To enable Forms Runtime Diagnostics (FRD):

– login to Apps as sysadmin
– click on Profile/System
– query ICX%FORMS%LAUNCHER
– add the following to the end of its value at user level:
?record=collect&log=/tmp/forms_frd.log

Note that like any Apps profiles you can do this on various levels e.g. site, user, etc. so that you target your FRD accordingly. Also be aware that FRD incurs significant overhead to Apps processing so disable it when not in use

How do you generate Apps Forms modules or FMB files?
Oftentimes as part of upgrading Forms or modifying a Form module or applying a patch modifying a Form module, you would encounter an issue that would necessitate manually regenerating the Forms module executable or FMX file. To do this you issue the following command:

f60gen module=form_name.fmb userid=apps/apps output_file=form_name.fmx module_type=form batch=yes compile_all=special

How do you generate Apps Forms library modules or PLL files?
To do this you issue the following command:

f60gen module=library_name.pll userid=apps/apps module_type=library batch=yes compile_all=special

How do you generate Apps Forms menu modules or MMB files?
Note that there is only one menu for Apps so it is rare that you need to regenerate the menu. To do this you issue the following command:

f60gen module=FNDMENU.mmb userid=apps/apps output_file=FNDMENU.mmx module_type=menu batch=yes compile_all=special

What is the sequence in which Forms resolves program unit calls?
Forms will attempt to resolve a program unit call in the following sequence:

Program Units node
Forms Library
Database

Where can you find the source Forms modules for Apps?
Apps places all its source (FMB) files in the $AU_TOP/forms/<language> directory whereas the FMX files reside in their respective $PRODUCT_TOP/forms/<language> directory. On the other hand, all menu (MMB, MMX) and library (PLL and PLX) files are copied to the $AU_TOP/resource directory.

How can you find the version of Forms in Apps?
Once you are in an Apps form …

click on Help
click on About Oracle Applications

information is under the Current Form section. The Form Name corresponds to the FMB and FMX file for that form.  The Form Version corresponds to the release level of that form.

How can I open an Apps form in Forms Builder

If you want to open a product specific form, make sure you have all its associated forms and libraries copied over.  Since it is quite difficult to pinpoint exactly what this is, its best to just copy over the entire contents of $AU_TOP/forms/<language> and $AU_TOP/resource.

How can you confirm the version of the FMX or FMB file?
Oftentimes, once you determine the version and name of the current form you want to peek into the internal workings of the actual form itself. To ensure that you are peeking at the right release of the FMB file you can do the following:

$adident Header FNDSCSGN.fmb

If you simply copy the form over to a a machine that does not have Apps installed, you will run into is a lot of FRM-18108: "Failed to load the following objects" errors.  This is because an Apps form is usually dependent on a host of other forms, libaries and menus.

If you want to open the TEMPLATE.fmb form, make sure you have the following forms, libraries, copied over as well from the $AU_TOP/forms/<language> and $AU_TOP/resource directories:

APPSTAND.fmb
APPCORE.pll
APPCORE2.pll
APPDAYPK.pll
CUSTOM.pll
FNDSQF.pll
GHR.pll
GLOBE.pll
GSM.pll
JA.pll
JE.pll
JL.pll
VERT.pll
OPM.pll
PQH_GEN.pll
PSA.pll
PSAC.pll
PSB.pll
IGILUTIL.pll
IGILUTIL2.pll

What is a Forms Builder node?

    A Forms Builder node refers to the leaves and branches of its Object Navigator. The Object Navigator provides a hierarchical display of the objects in a Form modules.

    +Forms
       +Module
       +Trigger
       +Alerts
       +Attached Libraries
       +Data Blocks
       +Canvases
       +Editors
       +LOVs
       +Object Groups
       +Parameters
       +Pop Up Menus
       +Program Units
       +Property Classes
       +Record Groups
       +Reports
       +Visual Attributes
       +Windows
    +Menu
    +PL/SQL Libraries
    +Object Libraries
    +Built-In Packages
    +Database Objects

    Objects are grouped under their respective nodes. For example, all of the windows defined in a form module appear under the Windows node; all the LOVs defined in the form module appearunder the LOVs node and all the program units appear under the Program Units node.  It is important to familiarize yourself with this in case you need to navigate within an Apps form during the course of debugging a problem.

     

     

How To Make All The Responsibilities Read Only For A User


Here is a sample code could be used to make all the responsibilities read only for a specific user. I am sure we can use it in different variations.

1. Open the Custom.pll in the Form Builder.
2.Add the following sample code in the Procedure Event for the event WHEN-NEW-FORM-INSTANCE
3.Compile the Custom.pll and place under the directory $AU_TOP/resource

BEGIN
IF event_name = ‘WHEN-NEW-FORM-INSTANCE’ THEN
IF FND_PROFILE.VALUE(‘USER_NAME’)=’<USER_NAME>‘ THEN
BEGIN
COPY(‘Entering app_form.query_only_mode.’,’global.frd_debug’);
COPY(‘YES’, ‘PARAMETER.QUERY_ONLY’);
APP_MENU2.SET_PROP(‘FILE.SAVE’, ENABLED,PROPERTY_OFF);
APP_MENU2.SET_PROP(‘FILE.ACCEPT’, ENABLED,PROPERTY_OFF);
formname := NAME_IN(‘system.current_form’);
blockname := GET_FORM_PROPERY(formname, FIRST_BLOCK);
WHILE (blockname is not null) LOOP
IF (GET_BLOCK_PROPERTY(blockname, BASE_TABLE) is not NULL) THEN
SET_BLOCK_PROPERTY(blockname, INSERT_ALLOWED, PROPERTY_FALSE);
SET_BLOCK_PROPERTY(blockname, UPDATE_ALLOWED, PROPERTY_FALSE);
SET_BLOCK_PROPERTY(blockname, DELETE_ALLOWED, PROPERTY_FALSE);
END IF;
blockname := GET_BLOCK_PROPERTY(blockname, NEXTBLOCK);
END LOOP;
END query_only_mode;
END;

The above code will make all the Responsibilities read only for a specific user.

It will fundamentally make all the form functions to behave as passing the parameter QUERY_ONLY.

Oracle E-Business Suite Diagnostics Overview


  • Oracle E-Business Suite Diagnostics is a free tool provided by Oracle to ease the gathering and analyzing of information from your E-Business Suite specific to a existing issue or setup
  • Formatted output displays the information gathered, the findings of the analysis and appropriate actions to take if necessary
  • This tool is easy to use and is designed for both the functional and technical user

Oracle E-Business Suite Diagnostics are designed to improve:

  • Problem Avoidance – resolving configuration and data issues that would cause processes to fail
  • Self Service Resolution – resolving problems without the need to contact Oracle Support
  • Reduction in Resolution Time – minimizing the time spent to resolve an issue by increasing support engineer efficiency

Key items to note:

  • E-Business Suite Diagnostics do not alter the data or setup in your system
  • E-Business Suite Diagnostics are organized into one of the following groups:
  • Setup Diagnostic tests are designed to assist in resolving product setup issues
  • Activity Diagnostic tests gather information about data and configuration relevant to a particular functionality / issue, examine that information, provide feedback, and suggest appropriate actions
  • Data Collection tests gather information relevant to a particular product / functionality / issue
  • Functional Categories typically align with the product menu structure and include both activity and data collection tests
  • E-Business Suite Diagnostics are designed to run in Oracle Diagnostics and are available via the E-Business Suite Diagnostics Patch

Release 11i and Release 12 both use the Role-based Access Control (RBAC) security model.
For a basic understanding of RBAC and how it relates to diagnostics, please click here

New to Release 12.0.6 security:

  • user must have a diagnostics role assigned – 3 seeded roles are delivered that can be used without additional setup
  • the ‘Application Diagnostics’ responsibility is used to execute tests in the diagnostic framework

Additionally, output logs from previously executed tests can only be reviewed by the user who executed them or the system administrator.

More information is available on Metalink / Oracle MySupport

How to trace Web Session in Oracle Applications


This process can be used to trace any action, anywhere in Oracle Applications and be very useful for the Self-Service Web Applications as there is no utility defined like in the Forms.

a. First make sure the necessary profile has the proper permissions.
1. Log onto the Applications Forms with the Application Developer Responsibility
2. Navigate to the Profile menu
3. Query up the profile name “FND_INIT_SQL”
4. In the bottom block of the form, make sure that ALL checkboxes are checked Typically, you will have to enable the checkboxes under “User Access” to make it “Visible” and “Updatable”.

b. Now switch to the System Administrator Responsibility
1. Navigate to – Profile – System
2. On the “Find System Profile Values” form, make sure the checkboxes for “User” and “Profiles with no Values” are checked
3. Beside the “User” checkbox, use the LOV to select the user who’s activity you need to trace
4. In the “Profile” field, enter the following profile and click the button: ‘Initialization SQL Statement – Custom’
5. In the “System Profile Values” form, enter the following in the User Field: (This is one line and all single quotes)

begin fnd_ctl.fnd_sess_ctl(”,”,’TRUE’,’TRUE’,’LOG’,’ALTER SESSION SET EVENTS=”10046 TRACE NAME CONTEXT FOREVER, LEVEL 12” tracefile_identifier=”spurohit” max_dump_file_size=”unlimited”’); end;

6. DO NOT SAVE THE PROFILE YET

7. In another browser window, login as the user you are going to trace and prepare to reproduce the problem
8. Once you are ready to reproduce the problem, go back to the Applications Forms and Save the profile change
9. Reproduce the problem
10. Back in the Applications form, set profile to null so it does not trace anymore and Save the change
11. The trace will be located in the user_dump_dest. To find location run the following in SQL*Plus: select value from v$parameter where name = ‘user_dump_dest’;
12. The trace file will have current date/time and can be identified with the word spurohit in it.

— To know all steps in details and with picture, do refer below url

http://oracle-apps-dba.blogspot.com/2009/01/how-to-enable-trace-for-crm-session.html

thanks – Shiv