, , ,

PL/SQL Block

PL/SQL is a block-structured language, meaning that programs can be divided into logical blocks. Program units can be named or unnamed blocks. Unnamed blocks are known as anonymous blocks. The PL/SQL coding style differs from that of the C, C++, and Java programming languages. For example, curly braces do not delimit blocks in PL/SQL.
A PL/SQL block consists of up to three sections: declarative (optional), executable (required), and exception handling (optional).
Note: In PL/SQL, an error is called an exception.


Executing Statements
DECLARE v_variable VARCHAR2(5);
BEGIN
SELECT column_name INTO v_variable FROM table_name;
EXCEPTION
WHEN exception_name THEN
…
END;

  • Place a semicolon (;) at the end of a SQL statement or PL/SQL control statement.
  • Section keywords DECLARE, BEGIN, and EXCEPTION are not followed by semicolons. END and all other PL/SQL statements require a semicolon to terminate the statement.

Block Types
A PL/SQL program comprises one or more blocks. These blocks can be entirely separate or nested one within another. The basic units (procedures and functions, also known as subprograms, and anonymous blocks) that make up a PL/SQL program are logical blocks, which can contain any number of nested subblocks. Therefore, one block can represent a small part of another block, which in turn can be part of the whole unit of code.

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

Registering Executable, Concurrent Program etc from backend

For Registering the Executable from backend.

PROMPT Creating Concurrent Executable XXM_XYZ_EMPLOYEE ……
PROMPT

BEGIN
FND_PROGRAM.executable(‘XXM_XYZ_EMPLOYEE’ — executable
, ‘ABC’ — application
, ‘ABC_XYZ_EMPLOYEE’ — short_name
, ‘Executable for Migrating Employee’ — description
, ‘PL/SQL Stored Procedure’ — execution_method
, ‘abc_xyz_employee_pkg.create_employee’ — execution_file_name
, ” — subroutine_name
, ” — icon_name
, ‘US’ — language_code
, ”); 

END;
/

For Registering the Concurrent program for the Executable file created.

PROMPT Creating Concurrent Program ABC_XYZ_EMPLOYEE …
PROMPT

BEGIN
FND_PROGRAM.register(‘ABC Data migration program for HR-Employee’ — program
, ‘XXM’ — application
, ‘Y’ — enabled
, ‘ABC_XYZ_EMPLOYEE’ — short_name
, ‘Data Migration Program for Migrating HR-Employee’ — description
, ‘ABC_XYZ_EMPLOYEE’ — executable_short_name
, ‘XYZ’ — executable_application
, ” — execution_options
, ” — priority
, ‘Y’ — save_output
, ‘Y’ — print
, ” — cols
, ” — rows
, ” — style
, ‘N’ — style_required
, ” — printer
, ” — request_type
, ” — request_type_application
, ‘Y’ — use_in_srs
, ‘N’ — allow_disabled_values
, ‘N’ — run_alone
, ‘TEXT’ — output_type
, ‘N’ — enable_trace
, ‘Y’ — restart
, ‘Y’ — nls_compliant
, ” — icon_name
, ‘US’); — language_code
END;
/

For attaching the concurrent program to the request group.

PROMPT Adding Concurrent program to request group ‘XYZ Group’
PROMPT

BEGIN
FND_PROGRAM.add_to_group(‘XYZ_ABC_EMPLOYEE’ — program_short_name
, ‘XYZ’ — application
, ‘XYZ Group’ — Report Group Name
, ‘XYZ’); — Report Group Application

END;
/

Submit the Concurrent Program from Backend.
Submit Program is the Function. where. we need to pass the Application name, Program, stage etc.

function FND_SUBMIT.SUBMIT_PROGRAM
(application IN varchar2,
program IN varchar2,
stage IN varchar2,
argument1,…argument100)
return boolean;

PROMPT Submitting the Concurrent Program from backend.
PROMPT

BEGIN
–fnd_global.apps_initialize( user_id => , resp_id => , resp_appl_id => );

l_success := fnd_submit.set_request_set(‘‘, ‘‘);

IF l_success = TRUE
THEN

l_success := fnd_submit.submit_program(
‘‘ –Application
,’‘ — program
,’‘ –Stage
,’NEW’ — Arument1
,’10’ -Arument2
,’N’
,’N’
,’N’);

— If fail then for the log record.

IF (NOT l_success)
THEN
fnd_file.put_line(
fnd_file.LOG
,’Request submission of stage STAGE FAILED’);
ELSE
fnd_file.put_line(
fnd_file.LOG
,’Request submission of stage STAGE10 SUCCESSFUL’);
END IF;
l_req_id := fnd_submit.submit_set(NULL, FALSE );

IF (l_req_id <= 0) THEN fnd_file.put_line( fnd_file.LOG ,’REQUEST SET SUBMISSION FAILED’); ELSE fnd_file.put_line( fnd_file.LOG ,’REQUEST SET SUBMITTED SUCCESSFULLY: ‘ || l_req_id); END IF; END IF; END;

I hope the above document is helpful to all of you for understanding the backend process and API’s to register the program and submit from the backend.

/by