, , , , ,

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
, , , ,

Basics of SQL

PL/SQL is a procedural language that Oracle developed as an extension to standard SQL to provide a way to execute procedural logic on the database.

SQL, SQL*Plus, PL/SQL: What’s the Difference?
This question has bedeviled many people new to Oracle. There are several products with the letters “SQL” in the title, and these three, SQL*Plus, SQL, and PL/SQL, are often used together. Because of this, it’s easy to become confused as to which product is doing the work and where the work is being done. This section briefly describes each of these three products.

SQL
SQL stands for Structured Query Language. This has become the lingua franca of database access languages. It has been adopted by the International Standards Organization (ISO) and has also been adopted by the American National Standards Institute (ANSI). When you code statements such as SELECT, INSERT, UPDATE, and DELETE, SQL is the language you are using. It is a declarative language and is always executed on the database server. Often you will find yourself coding SQL statements in a development tool, such as PowerBuilder or Visual Basic, but at runtime those statements are sent to the server for execution.

PL/SQL
PL/SQL is Oracle’s Procedural Language extension to SQL. It, too, usually runs on the database server, but some Oracle products such as Developer/2000 also contain a PL/SQL engine that resides on the client. Thus, you can run your PL/SQL code on either the client or the server depending on which is more appropriate for the task at hand. Unlike SQL, PL/SQL is procedural, not declarative. This means that your code specifies exactly how things get done. As in SQL, however, you need some way to send your PL/SQL code up to the server for execution. PL/SQL also enables you to embed SQL statements within its procedural code. This tight-knit relationship between PL/SQL, SQL, and SQL*Plus is the cause for some of the confusion between the products.

SQL*Plus
SQL*Plus is an interactive program that allows you to type in and execute SQL statements. It also enables you to type in PL/SQL code and send it to the server to be executed. SQL*Plus is one of the most common front ends used to develop and create stored PL/SQL procedures and functions.

What happens when you run SQL*Plus and type in a SQL statement? Where does the processing take place? What exactly does SQL*Plus do, and what does the database do? If you are in a Windows environment and you have a database server somewhere on the network, the following things happen:
   1. SQL*Plus transmits your SQL query over the network to the database server.
   2. SQL*Plus waits for a reply from the database server.
   3. The database server executes the query and transmits the results back to SQL*Plus.
   4. SQL*Plus displays the query results on your computer screen.

Even if you’re not running in a networked Windows environment, the same things happen. The only difference might be that the database server and SQL*Plus are running on the same physical machine. This would be true, for example, if you were running Personal Oracle on a single PC.

PL/SQL is executed in much the same manner. Type a PL/SQL block into SQL*Plus, and it is transmitted to the database server for execution. If there are any SQL statements in the PL/SQL code, they are sent to the server’s SQL engine for execution, and the results are returned back to the PL/SQL program.

/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
, , , , , ,

Steps to Perform India localization in R12

Steps to Perform India localization in R12
The following table lists setup steps required for India localization.
/by
, , ,

Form Personalization – How to Change Field Name

Form Personalization – How to Change Field Name

Form Personalization feature allows us to alter the behavior of Forms-based screens, including changing properties, displaying messages etc.
For a single form-function(a form running in a particular context based on parameters passed to it defined at function level) we can specify one or more Rules. Each Rule consists of an Event, an optional Condition, the Scope for which it applies, and one or more Actions to perform.

Here we will discuss about how can we change the field display name.

Basic Requirement

Our basic requirement is to change name the ‘Latest Start Date‘ field to ‘ABCD‘ in people Screen. Remember this name change should only be applicable for persons who are using ‘UK HRMS Manager’.

Solution ApproachForm Personalization feature is declarative and any personalization to form may interfere with base code of a  form.
before we start personalization please ensure that the following security profiles are properly set   1) FND_HIDE_DIAGNOSTICS (Hide Diagnostics menu entry)
  2) DIAGNOSTICS (Utilities:Diagnostics)

a) Now open the people & Assignment form from the navigator menu. Click on the ‘Latest Start Date field’. Now go to  Help >> Diagnostics >>  Properties >>  Item.
    It will display the ‘Object Properties’ window. Note Down the  Object2 value (HIRE_DATE) which is nothing but the name of the item.

b) Now to personalize the screen, go to  Help >> Diagnostics >> Custom Code >> Personalize
 Set the following values

Condition TabSeq:- Next highest available number.
Description:- Test Personalization
Level:- Function
Trigger Event:- WHEN-NEW-FORM-INSTANCE
Trigger Object**:-
Condition:-
Processing Mode:- Both
Scope:- Site
** Depending on the Trigger Event, this field may be Disabled, or Enabled and Required in which case it will validate against a List of Values. For example, if Trigger Event WHEN-NEW-ITEM-INSTANCE is selected, then we must enter a specific block.field for that trigger to be processed.

Action Tab
Seq:- 10
Type:- Property
Description:-
Language***:- All
Object Type:- Item
Target Object:- PERSON.HIRE_DATE (Search with string that we copied from step a)
Property Name:- PROMPT_TEXT
Value:- ABCD

 ***  Select ‘All’ to have the action processed for any language, or select a specific language.Generally text-related personalizations are applied for a specific
       language.

c) Validate the design and click on Apply Now.

Note:- 1) Since we have selected processing mode as ‘Both’, hence the field name ‘ABCD’ will appear under both the condition ‘New form is open’ and
             ‘Enter-Query Mode’.
                If we select the processing mode as ‘Only in Enter-Query Mode’, then we will see the original name of the field while opening the form.Where as  if we
            query the form it will execute the trigger event and change the name of the field.

      2) Each Rule consists of one or more Scope rows, and one or more Actions. If a Rule has no Scope rows or Action rows, it is not processed. Note that
         upon saving a Rule, if no Scope rows have been entered the form will automatically create a row at the Site level. If any scope matches the current
         runtime context then the Rule will be processed.

/by