Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Wednesday, 27 June 2012

Interview Preparation SQL/PLSQL/D2K Part4

How many types of columns are there and what are they?
Formula columns: r doing mathematical calculations and returning one value
Summary Columns: For doing summary calculations such as summations etc.
Place holder Columns: These columns are useful for storing the value in a variable
Can you have more than one layout in report?
It is possible to have more than one layout in a report by using the additional layout option in the layout editor.
Can you run the report with out a parameter form?
Yes it is possible to run the report without parameter form by setting the PARAM value to Null
What is the lock option in reports layout?
By using the lock option we cannot move the fields in the layout editor outside the frame. This is useful for maintaining the fields .
Define Flex & Confine Mode in Reports ?
Confine mode: It prevents operations which would cause a report not to work e.g. moving a field outside its parent repeating frame
Flex mode: Moves the object or its enclosing objects and objects in their push path simultaneously to maintain the same overall relationship in the report. e.g. if you try to move a field outside its repeating frame, the Repeating Frame will grow to accommodate the field and so will any objects around the repeating frame. Only one object can be moved/resized at one time in flex mode - if you try more than one only one whose control point is clicked on will be done, the other objects will be de-selected. Objects can be moved\resized horizontally or vertically; not diagonally.
What is the minimum number of groups required for a matrix report?
The minimum of groups required for a matrix report are 4
What are Report Level Triggers and their Sequence of execution?
Before parameter form,
After parameter form
Before Report,
Between Pages
After Report
What are Lexical & Bind Parameters in Reports?
Lexical Parameters: Lexical references are placeholders for text that you embed in a SELECT statement.  You can use lexical references to replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH.
You cannot make lexical references in a PL/SQL statement.  You can, however, use a bind reference in PL/SQL to set the value of a parameter that is then referenced lexically in SQL.  Look at the example below.
You create a lexical reference by entering an ampersand (&) followed immediately by the column or parameter name.  A default definition is not provided for lexical references. Therefore, you must do the following:
Ø       Before you create your query, define a column or parameter in the data model for each lexical reference in the query.  For columns, you must enter Value if Null, and, for parameters, you must enter Initial Value.  Report Builder uses these values to validate a query with a lexical reference.
Ø       Create your query containing lexical references.
Bind Parameters: Bind references (or bind variables) are used to replace a single value in SQL or PL/SQL, such as a character string, number, or date.  Specifically, bind references may be used to replace expressions in SELECT, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH clauses of queries.  Bind references may not be referenced in FROM clauses or in place of reserved words or clauses.
You create a bind reference by entering a colon (:) followed immediately by the column or parameter name.  If you do not create a column or parameter before making a bind reference to it in a SELECT statement, Report Builder will create a parameter for you by default.
How many minimum groups are required for a Matrix report?
The minimum number of groups in matrix report is 4
Anchoring in Reports
Anchors are used to determine the vertical and horizontal positioning of a child object relative to its parent.  The end of the anchor with a symbol on it is attached to the parent object. Since the size of some layout objects may change when the report runs (and data is actually fetched), you need anchors to define where you want objects to appear relative to one another.  An anchor defines the relative position of an object to the object to which it is anchored.  Positioning is based on the size of the objects after the data has been fetched rather than on their size in the editor.  It should also be noted that the position of the object in the Layout editor effects the final position in the report output.  Any physical offset in the layout is incorporated into the percentage position specified in the Anchor property sheet.
What is Drill down report?
A drill-down report is actually two or more reports working together.  The top-level report launches a secondary report that provides more details about the data in the current record.  To add flexibility in the selection of details, you can create a button in the top-level report that executes PL/SQL to call the detail report and provide conditions for its execution.  The detail report can provide details for a single record, a group of records, or the report as a whole. A report can include buttons with PL/SQL actions attached to them.  For example, you could use the packaged procedure SRW.RUN_REPORT to define an action for a button that calls another report.  This second (or detail) report appears in its own, modal Runtime Previewer.  There is no limit to the number of levels you can drill down in this way.
What is New Feature in form 6i comparably to form 4.5 and 5.0
Forms runtime diagnostics
Enhanced PL/SQL editing capabilities and environment
An LOV wizard
A hierarchical tree control
Support for pluggable Java components
Web Enabled
What purpose ‘Do_Key’ is used?
Executes the key trigger that corresponds to the specified built-in subprogram.  If no such key trigger exists, then the specified subprogram executes.  This behavior is analogous to pressing the corresponding function key. PROCEDURE DO_KEY (built-in_subprogram_name VARCHAR2);
DO_KEY accepts built-in names only, not key names: DO_KEY (ENTER_QUERY).  To accept a specific key name, use the EXECUTE_TRIGGER built-in: EXECUTE_TRIGGER ('KEY_F11').
What are different Types of Menus in form?
Menus are lists of items that end users use to select specific functions or operations.  Each item on a menu represents a command or a submenu.
Form Menus:  A form menu typically includes standard form-level commands for navigation, editing, and database interaction.  Every form runs with one of the following form menus: default, custom, none.
Menu Toolbars: If you create any type of custom form menu module for your form, you also can derive a menu toolbar from that form menu.  A menu toolbar displays selected menu items (from the current form menu) as icons on a horizontal or vertical ribbon.
Popup Menus: In addition to one form menu, each form can include any number of popup menus.  Popup menus are context-sensitive menus attached to individual canvases and items within a form. You can attach a single popup menu to one or more canvases or items. E ach canvas or item can support one and only one popup menu
Tear-off: A tear-off menu is a menu that end users can drag away from the menu bar and reposition elsewhere on the screen.  You can enable tear-off functionality for any menu, provided your window-manager supports this feature.
Debugger menu: The Debugger menu includes three menus: View, Debug, and Navigator.
What is difference in Pre-Query/Pre-Select in Form?
A PRE-QUERY fires just before Forms begins to prepare the SQL statement that will be required to query the appropriate records from the database in response to the query criteria that have been entered into the example record. It is the designer’s last chance to "deposit" query criteria into the items in the block as if they had been entered into the example record by the operator.
PRE-SELECT fires after the PRE-QUERY trigger has fired, and just before the actual, physical construction of the SELECT statement begins. It would typically be used in conjunction with an ON-SELECT trigger in which the
designer would be supplanting the normal construction of the SELECT statement with a user exit of his own to construct and prepare the SELECT statement for execution (perhaps against a foreign data source).
What is Sequence of trigger when Form is opened & closed?
Run Form
1.  Pre-Logon                                        Form
2.  On-Logon                                         Form
3.  Post-Logon                                      Form
4.  Pre-Form                                           Form
5.  When-Create-Record                     Block
6.  Pre-Block                                          Block
7.  Pre-Record                                       Block
8.  Pre-Text-Item                                   Item
9.  When-New-Form-Instance            Form
10. When-New-Block-Instance          Block
11. When-New-Record-Instance       Block
12. When-New-Item-Instance            Item
1.  Post-Text-Item                  Item
2.  Post-Record                      Block
3.  Post-Block                        Block
4.  Post-Form                         Form
5.  On-Rollback                     Form
6.  Pre-Logout                       Form
7.  On-Logout                        Form
8.  Post-Logout                      Form     
What is Sequence of trigger when Navigation from one item to another?
Key-next -1
Post Change –1
When validate -1
Post text -1
Pre – text - 2
When new item instance - 2
Difference between Dbms_Alert & Dbms_Pipe?
The DBMS_ALERT package provides support for the asynchronous notification of database events (alerts). By appropriate use of this package and database triggers, an application can cause itself to be notified whenever values of interest in the database are changed. Alerts are transaction-based. This means that the waiting session does not get alerted until the transaction signaling the alert commits. There can be any number of concurrent signalers of a given alert, and there can be any number of concurrent waiters on a given alert.
The DBMS_PIPE package lets two or more sessions in the same instance communicate. Oracle pipes are similar in concept to the pipes used in UNIX, but Oracle pipes are not implemented using the operating system pipe mechanisms. Information sent through Oracle pipes is buffered in the system global area (SGA). All information in pipes is lost when the instance is shut down. Depending upon your security requirements, you may choose to use either a public or a private pipe.
What is Check option/Force View?
FORCE: creates the view regardless of whether the view's base tables exist or the owner of the schema containing the view has privileges on them. Note that both of these conditions must be true before any SELECT, INSERT, UPDATE, or DELETE statements can be issued against the view.
WITH CHECK OPTION: specifies that inserts and updates performed through the view must result in rows that the view query can select. The CHECK OPTION cannot make this guarantee if there is a subquery in the query of this view or any view on which this view is based.
What are Bind Variables & Host Variables?
Bind variables are used in SQL and PL/SQL statements for holding data or result sets. They are commonly used in SQL statements to optimize statement performance. A statement with a bind variable may be re-executed multiple times without needing to be re-parsed. Their values can be set and referenced in PL/SQL blocks. They can be referenced in SQL statements e.g. SELECT. Except in the VARIABLE and PRINT commands, bind variable references should be prefixed with a colon.
Host variables are the key to communication between your host program and Oracle. Typically, a precompiler program inputs data from a host variable to Oracle, and Oracle outputs data to a host variable in the program. Oracle stores input data in database columns, and stores output data in program host variables.
A host variable can be any arbitrary C expression that resolves to a scalar type. But, a host variable must also be an lvalue. Host arrays of most host variables are also supported.
What is the purpose of Define & Accept Command?
Use the DEFINE command to explicitly create substitution variables. The DEFINE command can also be used to display the value of a known variable. It shows the variable name, value and type. Using DEFINE with no arguments lists all defined substitution variables. Any variable that DEFINE lists is said to be defined. A variable may be redefined by repeating the DEFINE command with a different value. The DEFINE command only ever creates variables with type CHAR.
The ACCEPT command always prompts for a variable's value, creating a new variable or replacing an existing one. ACCEPT has advantages over a double ampersand (&&) variable reference that causes a prompt. ACCEPT allows the prompting text to be customized and allows a default value to be specified. ACCEPT does type and format checking. The ACCEPT command understands numbers, strings and dates. If a FORMAT clause is used, SQL*Plus validates the input against the given format. If the input is not valid, you are re-prompted for a value.
Difference between Post-Query/Pre-Query?

Pre-Query: Fires during Execute Query or Count Query processing, just before Form Builder constructs and issues the SELECT statement to identify rows that match the query criteria.

Use a Pre-Query trigger to modify the example record that determines which rows will be identified by the query.

Post-Query: When a query is open in the block, the Post-Query trigger fires each time Form Builder fetches a record into a block.  The trigger fires once for each record placed on the block's list of records.

Use a Post-Query trigger to perform the following tasks:
Ø       Populate control items or items in other blocks
Ø       Calculate statistics about the records retrieved by a query
Ø       Calculate a running total
When you use a Post-Query trigger to SELECT non-base table values into control items, Form Builder marks each record as CHANGED, and so fires the When-Validate-Item trigger by default. 
You can avoid the execution of the When-Validate-Item trigger by explicitly setting the Status property of each record to QUERY in the Post-Query trigger.  To set record status programmatically, use SET_RECORD_PROPERTY.
What are PL/SQL enhancements in different version of oracle (7.3/8/8i/9i)?
Some new pl/sql enhancements in oracle 8i and 8
New Pragmas-serially reusable, autonomous transactions
New Database Triggers: startup, shutdown, logon, logoff, alter, create, drop
Expanded Oracle Supplied Packages: dbms_profiles,dbms_trace,dbms_debug
Extended Security Methodology
Password expire
Password reuse max
Password grace time
Locking user accounts, and unlocking
Different ways in which block can be built?
Table, Views, Procedure, Ref cursors, Transactional triggers and From clause of a query.
Difference in Parameter Form & Report Parameter Form?
In Report Parameter form all values are lost when report is closed. Also we can’t use Global Variables through Report Parameter Form.
‘POST’ in forms. Different from Commit?
Writes data in the form to the database, but does not perform a database commit.  Form Builder first validates the form.  If there are changes to post to the database, for each block in the form, Form Builder writes deletes, inserts, and updates to the database.
Any data that you post to the database is committed to the database by the next COMMIT_FORM that executes during the current Runform session.  Alternatively, this data can be rolled back by the next CLEAR_FORM
During a default commit operation, Form Builder issues the SQL statements necessary to update, delete, or insert records that have been marked in the form as changed, deleted, or inserted.  Form Builder then issues the COMMIT statement to commit these transactions in the database.
Posting consists of writing updates, deletions, and insertions in the form to the database, but not committing these transactions to the database.  You can explicitly cause Form Builder to post without committing by executing the POST built-in procedure.
When an application executes the POST procedure, Form Builder does all of the default validation and commits processing, but does not issue the COMMIT statement to finalize these transactions.
Since posted transactions have been written to the database, Form Builder does not have to maintain the status of the affected records across called forms.  More importantly, because these transactions have not been committed, they can be rolled back programmatically.  You can take advantage of this functionality to manage transactions across called forms.
What are the Types of Views based on block?
Updateable view, INSTEAD OF trigger view, Non-updateable view
What are the Default Validations done by forms?
Datatype, Range, Lov for validation, Required, Format mask
Differences between Nested Tables and Varrays?
  1. Varrays have a maximum size, but nested tables do not.
  2. Varrays are always dense, but nested tables can be sparse. So, you can delete individual elements from a nested table but not from a varray.
  3. Oracle stores varray data in-line (in the same tablespace). But, Oracle stores nested table data out-of-line in a store table, which is a system-generated database table associated with the nested table.
  4. When stored in the database, varrays retain their ordering and subscripts, but nested tables do not.


Post a Comment