Tuesday 27 March 2012

PL/SQL - FAQ -1


1.    What is security attributes?
Security Attributes are used by Oracle self service web Applications to allow rows of data to be visible to specified users responsibilities based on the specific data contained in the row.
2.    What is a Profile Option?
*       Profile options are the set of changeable options that affects how the application looks and behaves.
*       By setting profile options, the applications can be made to react in different ways for different users depending on the specific user attributes.
3.    What are steps involved in developing a flex field?
*       Designing the table structure
*       Creating fields on the form (Visible/Hidden)
*       Calling appropriate routines
*       Registration of the flex field.
*       Definition of the flex field.
<Flex fields / key/ Register>
<Flex fields/Descriptions / Register>
4.    What are an application /Module?
Application is a collection of forms, function and menus
5.    What are Alerts?
*       Alert is a mechanism that checks the database for a specific exception condition.
*       An alert is characterized by the SQL select statements it contains.
*       A SQL select statement fells the application what database exception to identify as well as what output to produce for that exception.
6.    What are composite Data types?
*       Composite Data types are of two types
            1. PL/SQL Records
2. PL/SQL Collections
                     à Index by Table
                     à Nested Table
                     à VARRAY
*       Composite data types are also known as collections.
*       They are RECORD,TABLE,NESTED TABLE and VARRAY
RECOD data type:-
ü  A RECORD is a group of related data items stored as fields each with its own name and data type.
ü  PL/SQL Records are similar to structures in 3GL’s
ü  A RECORD is not the same as Row in a database table
ü  RECORD treats a collection of fields as a logical unit.
ü  These are (RECORD type) convenient for fetching a row of data from a table for processing
ü  RECORDS also can be declared.
Syntax à TYPE type_name is RECORD (filed declaration…);
                      Identifier type_name;
Ex: - TYPE emp_record_type is RECORD
            last_name  varchar2(50),
            job_id varchar2(10),
            salary number*8,2));
            emp_record emp_ record_type;
ü  Fields declared as NOT NULL must be initialized.
INDEX BY Table data types:-
Ø  This data type contains two components.
            1. Primary key of data type BINARY_INTEGER
            2. Column of scalar or record data type.
Ø  Objects of the TABLE type are called INDEX BY Tables
·         They are modeled as (but not the same as) data base tables.
·         INDEX BY Table is a primary key to provide the user with array-like access to rows.
·         INDEX BY table is similar to an ARRAY.
·         It can be increased in size dynamically because they are un constrained.
Ø  There are two steps involved in creating a INDEX BY table.
            1. Declare a TABLE data type.
            2. Declare a variable of that type.
Ø  The size of the INDEX BY Table is un constrained increase dynamically so that INDEX BY Table an increase dynamically, so that INDEX BY Table grows as new rows are added.
Ø  INDEX BY Tables can have one column and a unique identifier to that one column neither of which can be named.
Ø  The column can belong to any scalar or record data type, but the primary key must be ling to type  BINARY_INTEGER
Ø  INDEX BY Tables cannot be initialized at the time of its declaration and also it cannot be populated at the time of declaration.
Ø  An exploit executable statement is required to initialize (populate) the INDEX BY TABLE.

INDEX BY TABLE STRUCTURE
                     Unique identifier                           Column
                    
…….
1
2
……
Gopi
Raj
….
                     BINARY_INTEGER                         SCALOU        
Syntax à        TYPE  ename_table_type      IS TABLE OF
                     Employees.last_name%TYPE
                     INDEX BY BINARY_INTEGER;
-this can be reterened by
  INDEX Bytable_name (primary_key_value);
- The Following methods are used  with INDEX BY Tables.
1.                                       EXISTS
2.                                       OUNT
3.                                       FIRST AND LAST
4.                                       PRIOR
5.                                       NEXT
6.                                       TRIM
7.                                       DELETE
INDEX BY Table of Records:
Ø  At a given point of time. INDEX BY Table can store only the details of any one of the columns of a database table
Ø  To store al the columns retried by a query, INDEX BY Table of Records are used.

Ø  Because only the table definition is needed to hold information about all of the fields of a data base table, the table of records greatly increases the functionality if INDEX BY Table.
Syntax à TYPE dept_table_type IS TABLE OF
                      Departments % ROWTYPE
                      INDEX BY BINARY_INTEGER;
                      Dept_table dept_table_type;
Ø  % ROW TYPE attribute can be used to declare a record that represents a row in a database table.
Ø  The difference between the % ROWTYPE attribute and the composite data type RECORD is that RECORD allows to specify the data types of fields in the record or to declare new fields with new data types.

NESTED TABLES
Ø  Nested Table is an ordered group of items of type TABLE.
Ø  Nested Table contains multiple columns and can be used as variables, parameters, results, attributes and columns.
Ø  They can be thought of as one column data base tables.
Ø  Rows of a nested table are not stored in any particular order.
Ø  The size of a nested table can be increased dynamically i.e. nested tables are unbounded.
Ø  Elements in a table initially have consecutive subscripts, but as elements are deleted, they can have non-consecutive subscripts.
Ø  The range of values for nested table subscripts is 1..2147483647.
Ø  To extend a nested table, the built-in procedure EXTEND must be used.
Ø  To delete elements, the built-in procedure DELETE must be used.
Ø  A UN initialized nested table is automatically null, so the IS NULL comparison operator can be used to ses if nested table is null.
Ø  The operators CAST, THE and MULTISET are used or manipulating nested tables.
1     CREATION OF A NESTED TABLE
Defining an object type.
SQL> Create type ELEMENTS AS OBJECT
                      (ELEM_ID Number (6),
                      PRICE Number (7,2));
                      /
2     Create a table type ELEMENTS_TAB which stores ELEMENTS objects.
            SQL>  Create TYPE ELEMENTS_TAB AS TABLE OF ELEMENTS
            /
3     Create a data base table STORAGE having type ELEMENTS_TAB as one of its columns.
SQL> Create Table STORAGE
                     (Salesman number (4),
                     Elem_id number (6),
                     Ordered Date,
                     Items Elements_Tab)
                     NESTED TABLE ITEMS STORE AS ITEMS_TAB;
VARRAYS:-
Ø  VARRAYS are ordered group of items of type VARRAY.
Ø  VARRAYS can be used to associate a single identifier with an entire collection.
Ø  This allows manipulation of the collection as a whole and easy reference of individual elements.
Ø  The maximum size of VARRAY needs to be specified in its type definition.
Ø  The range of values for the index of a VARRAY is from 1 to the maximum specified in its type definition.
Ø  If no elements are in the (table) ARRAY, then the ARRAY is automatically null.
Ø  The main use of VARRAY is to group small of uniform-sized collection of objects.
Ø  Elements of a VARRAY cannot be accessed individually SQL, although they can be accessed in PL/SQL, OCI, or Pro*C using the array style subscript.
Ø  The type of the element of a VARRAY can be any PL/SQL type except the following.
BOOLEAN, TABLE, VARRAY etc.
Ø  VARRAYS can be used to retrieve an entire collection as a value.
Ø  VARRAY data is stored in-line, in the table space as the other data in its row.
Ø  When a VARRAY is declared, a constructor with the same name as the VARRAY is implicitly defined.
Ø  The constructor creates a VARRAY from the elements passed to it.
Ø  A VARRAY can be assigned to another VARRAY, provided the data types are the exact same type.
Ø  TYPE my_VARRAY1 IS VARRAY (10) OF MY_Type;
Ø  Is NULL comparison operator can be used to see if a VARRAY is null.
Ø  VAARAYS cannot be compared for equality or in equality.

CREATING A VARRAY:-
1. Defining object type ELEMENTS
  SQL> Create TYPE MEDICINES AS OBJECT
            (MED_ID NUMBER (6),
            MED_NAME Varchar2 (14),
            MANF_DATE DATE);
            /
2. Define a VARRAY type MEDICINE_ARR which stores MEDICINES. objects
  SQL> Create TYPE MEDICINE_ARR AS VARRAY (40)
            OF MEDICIES;
            /
3. Creating a relational table MED_STORE which has MEDICINE_ARR as a column type
  SQL> Create table MED_STORE (
            Location varchar2 (15),
            Store_Size number (7),
            Employees number (6),
            Med_Items Medicine_Arr);

DIFFERENCES BETWEEN NESTED TABLES AND VARRAYS
ü  Nested Tables are unbounded, where as Varrays have a maximum size.
ü  Individual elements can be deleted from a nested table, but not from a Varray.
ü  Therefore nested tables can be spares, where as Varrays always are dense.
ü  Varrays are stored by Oracle in-line (in the same table space), where as nested table data is out-of-line in a store table, which is a system generated data base table associated with the nested table.
ü  When stored in the data base, nested tables do not retain their ordering and subscripts, where as Varrays do.
ü  Nested tables support indexes while VARRAYS do not.

7.    Differences between conversions and Interfaces:-
   Conversion                                                  Interface
1. Conversion is one-time process              1. Interface is the post production
   This is performed once before                              process.
   production go on live.(Pre-production process)
2. Data comes in to Oracle Applications        2. Interface is the integration of
    Only (One way process)                                    two systems.
3. Interactive Process                               3. Scheduled and respective process.

8.    Oracle Reports – Trouble Shooting
1.    Concurrent Request Logs:-
ü  The first step of reports debugging should be to examine the log of concurrent request.
2.    Running from the operating system:-
ü  If the problem is not resolved with log, then the report should be run through from the operating system.
ü  Along with the standard report arguments, the report should be run along with the arguments passed by the concurrent manager.
ü  If it is run successfully then the problem is with the environment from with the concurrent manager was started.

3.    Using r25run in place of ar25run:-
ü  For this debugging step, AOL provides a report $FND_TOP/ SRW/FNDNOEXT.rdf (UNIX path name) which has no user exits.
ü  If this step also fails, then the problem could be with Oracle Applications Installation.

4.    Running the Print Environment Variable Values Report:-
ü  The concurrent manager inherits its environment variables from the shell from which it was started and then runs report using this environment.
ü  This environment could be different from that a user sees logging in to the Applications because the concurrent manager may have been started by a different user with different environment settings.
ü  Due to this difference, it is sometimes difficult to determine the cause of error in running reports.
ü  To examine the values of few variables, prints environment variable values, Report to print out the variable as seen by the concurrent manager to see if is correct.
ü  Very common and often problems such as a problem in compilation or the concurrent managers inability to locate a library happen due to incorrect REPORTS 60_PATH.

5.    Emulate Concurrent Manager Environment:-
ü  For UNIX plat forms, to assist in determining where the problem lies, Oracle Applications AOL ships a program called $ FND_TOP / SRW / ar60run.oc.
ü  This program helps to emulate the concurrent manager environment when testing reports from the OS command line.
ü  This program writes all the environment variables and arguments passed to it in a log file ar60run.log. (Located by default in the $FND_TOP / $APPLLOG directory).
ü  Save the ar60run.oc source code to a file named ar60run.oc compile it and rename the executable as ar60run.(new).
ü  Save $FND_TOP / bin / ar60run in to some other file and place the new ar60run into $FND_top / bin.
ü  Compiling and re linking has been incorporated in to fnd.mk which will, by default build an executable $FND_TOP / bin /ar60rund, which can be renamed to ar60run (new).
ü  Submit the report from concurrent manager and look at ar60run.log.
ü  Then run the report from OS with the same arguments as shown by ar60run.log shows using old ar60run.

6.    Bitmapped Reports
ü  Printer drivers should be provided with print style (Landscape) to determine how to print text files.
ü  Bitmapped reports are not text files and these are output as post script files.
ü  The post script file is asset of instructions telling the printer exactly a landscape report; the post script file must be generated as landscape.

9.    Frequently asked questions in Reports
*       Why does my report only fail from the concurrent manager?
This is because the environment from which the concurrent manager launches a report is different from the one when running the report from OS command line.

*       Why does my report show different data?
ü  If the report shows different data when it is run as a stand alone report, sometimes the data in the output may be different for different situations.
ü  This is usually due to different / no profile options or other values being passed to the report by the concurrent manager.
ü  Check the calls to SRWINIT and SRWEXIT, it those are found disabled, they should be re-enables, before the report is run through concurrent manager.

*       Why do I get the error REP_0713 when I run my report?
ü  Oracle Reports uses a text file called uiprint.txt to hold printer names.
ü  If the current printer name is not in this file, then the error REP_0713 error.

*       Why do I get many pages of nonsense when I print my Report?
ü  Post script code should be recognized by the printer driver.
ü  ‘ enscript ’  program cannot be used for printing.

*       What does the ‘SEP-0065 ’; virtual memory system error?
ü  This error could be due to the following reasons.
ü  By default Oracle Reports uses / tmp directory to write temporary files, à which may be getting full.
ü  These files could be directed to another directory using the environment variable TMPDIR.
ü  Are the failing reports using page N or M? This can consume a lot of Oracle Reports virtual memory.
ü  If possible, Reports should be run against a smaller database.

10. FND_PROGRAM Package
FND_PROGRAM.Executable:-
Procedure FND_PROGRAM. Executable IS
  (executable in Varchar2,
  application in varchar2, (full name)
  short_name in varchar2, (executable short name)
  description in varchar2 default NULL,
  execution_method in varchar2,
  execution_file_name in varchar2 default null,
  Subrowline_name in varchar2 default null, (only for spawned immediate)
Icon_name in varchar2 default null,
Language_code in varchar2 default ‘US’,
Execution_file_path in varchar2 default null);
            For Java Concurrent Program.

FND. PROGRAM. REGISTER:-
Procedure FND_PROGRAM.Register IS
(Program in varchar2,
application in varchar2,
enabled in varchar2,
short_name in varchar2,
description in varchar2, default null,
executable_short_name in varchar2,
executable_application in varchar2,
execution_options in varchar2, default null,
priority in number default null,
save_output in varchar2 default ‘Y’,
print in varchar2 dafault ‘Y’,
cols in varchar2 default null,
rows in varchar2, default null,
style in varchar2, default null,
style_required in varchar2, default ‘N’,
printer in varchar2, default null,
Requets_Type in varchar2, default null,
Request_type_Application in varchar2 default null,
Use_in_Srs in varchar2, default ‘N’,
Allow_disabled_valuer in varchar2 default ‘N’,
Run_alone in varchar2 default ‘N’,
Output_type in varchar2 default ‘TEXT’,
Enable_trace in varchar2 default ‘N’,
Restart in varchar2 default ‘Y’,
nls_complaint in varchar2 default ‘Y’,
icon_name in varchar2 default null,
language_code in varchar2, default ‘US’,
mls_function_short_name in varchar2 default null,
mls_function_application in varchar2 default null,
incrementor in varchar2 default null);

11. Property Classes
*       A property class is a named object that contains a list of properties and their settings.
*       Once a property class is created, it can be assigned to any object.
*       An object based on a property class can inherit the settings of any property in that property class.
*       There can be number of properties in a property class, and the properties in a class can apply to different objects.
*       When an object is based on a property class, all the properties which are inherited from the property class can be controlled locally also.
*       Property classes are separate objects and can be copied between modules if required.
*       A property class can be sub classed in only number of modules.

12. Visual Attributes
*       Visual attributes are the font, color and pattern properties that can be set for form and menu modules which are appeared in application’s interface.
Font Properties: font name, font size, font style, font width, font height.
Color and pattern properties:  Foreground color, Back ground color, fill pattern, char mode, logical attribute, White on Black.
*       Every interface object has its visual attribute group property that determines how the objects individual visual attribute settings are derived.
*       The visual property group property can be set to default, NULL, or the name of a named visual attribute defined in the same module.
*       An object’s named visual attribute setting can be changed programmatically to change the font, color and pattern of the object at runtime

No comments:

Post a Comment