Wednesday 31 July 2013

Pipelined Table Functions in PLSQL?

Pipelining negates the need to build huge collections by piping rows out of the function as they are created, saving memory and allowing subsequent processing to start before all the rows are generated.
Pipelined table functions include the PIPELINED clause and use the PIPE ROW call to push rows out of the function as soon as they are created, rather than building up a table collection. Notice the empty RETURN call, since there is no collection to return from the function.

-- Build a pipelined table function.
CREATE OR REPLACE FUNCTION get_tab_ptf (p_rows IN NUMBER) RETURN t_tf_tab PIPELINED AS
BEGIN
  FOR i IN 1 .. p_rows LOOP
    PIPE ROW(t_tf_row(i, 'Description for ' || i));  
  END LOOP;

  RETURN;
END;
/

-- Test it.
SELECT *
FROM   TABLE(get_tab_ptf(10))
ORDER BY id DESC;

        ID DESCRIPTION
---------- --------------------------------------------------
        10 Description for 10
         9 Description for 9
         8 Description for 8
         7 Description for 7
         6 Description for 6
         5 Description for 5
         4 Description for 4
         3 Description for 3
         2 Description for 2
         1 Description for 1

10 rows selected.

SQL>

Once you start working with large warehousing ETL operations the performance improvements can be massive, allowing data loads from external tables via table functions directly into the warehouse tables, rather than loading via a staging area.

Table Functions in PLSQL?

Table functions are used to return PL/SQL collections that mimic tables. They can be queried like a regular table by using the TABLE function in the FROM clause. Regular table functions require collections to be fully populated before they are returned. Since collections are held in memory, this can be a problem as large collections can waste a lot of memory and take a long time to return the first row. These potential bottlenecks make regular table functions unsuitable for large Extraction Transformation Load (ETL) operations. Regular table functions require named row and table types to be created as database objects.
-- Create the types to support the table function.
DROP TYPE t_tf_tab;
DROP TYPE t_tf_row;

CREATE TYPE t_tf_row AS OBJECT (
  id           NUMBER,
  description  VARCHAR2(50)
);
/

CREATE TYPE t_tf_tab IS TABLE OF t_tf_row;
/

-- Build the table function itself.
CREATE OR REPLACE FUNCTION get_tab_tf (p_rows IN NUMBER) RETURN t_tf_tab AS
  l_tab  t_tf_tab := t_tf_tab();
BEGIN
  FOR i IN 1 .. p_rows LOOP
    l_tab.extend;
    l_tab(l_tab.last) := t_tf_row(i, 'Description for ' || i);
  END LOOP;

  RETURN l_tab;
END;
/

-- Test it.
SELECT *
FROM   TABLE(get_tab_tf(10))
ORDER BY id DESC;

        ID DESCRIPTION
---------- --------------------------------------------------
        10 Description for 10
         9 Description for 9
         8 Description for 8
         7 Description for 7
         6 Description for 6
         5 Description for 5
         4 Description for 4
         3 Description for 3
         2 Description for 2
         1 Description for 1

10 rows selected.

SQL>

Notice the above output is in reverse order because the query includes a descending order by clause.

Monday 29 July 2013

What are the types of triggers and how the sequence of firing in text item

Triggers can be classified as Key Triggers, Mouse Triggers, Navigational Triggers, event triggers.
Key Triggers: Key Triggers are fired as a result of Key action. e.g. Key-next-field, Key-up, Key-Down
Mouse Triggers: Mouse Triggers are fired as a result of the mouse navigation. e.g. When-mouse-button-pressed, when-mouse-doubleclicked, etc
Navigational Triggers: These Triggers are fired as a result of Navigation. e.g.  Post-Text-item, Pre-text-item.
Event triggers: when–new-form-instance and when-new-block-instance.

We cannot call restricted procedures like go_to (‘my_block.first_item’) in the Navigational triggers but can use them in the Key-next-item.

The Difference between Key-next and Post-Text is an very important question. The key-next is fired as a result of the key action while the post text is fired as a result of the mouse movement. Key next will not fire unless there is a key event.

The sequence of firing in a text item is as follows:
    a) pre-text
    b) when_new_item
    c) key-next
    d) when_validate
    e) post_text

What is SECURE property?

Hides characters that the operator types into the text item. This setting is typically used for password protection.

What is the Difference between a post query and a pre query?

A post query will fire for every row that is fetched but the pre query will fire only once.

Deffered property and Automatic Query?

If Deffered property is set to ‘NO’, associated detail records are retrieved immediately after master record retrieval.
If Deffered property is set to ‘YES’ & Automatic query property is set to ‘YES’, associated detail records are retrieved when the form cursor is position on detail block.
If Deffered property is set to ‘YES’ & Automatic query property is set to ‘NO’, associated detail records will not be retrieved. An explicit execute_query must be executed for corresponding details records.

By default Deffered property is set to ‘NO’ & Automatic query property is set to ‘YES’.

What are the Various Block Coordination Properties

The various Block Coordination Properties are
A.    Immediate: Default Setting. The Detail records are shown when the Master Record is shown.

B.    Deffered with Auto Query: Oracle Forms defer fetching the detail records until the operator navigates to the detail block.

C.    Deffered with No Auto Query: The operator must navigate to the detail block and explicitly execute a query

Dynamic LOV

SET_LOV_PROPERTY
            (lov_name  VARCHAR2,
            property  NUMBER,       (record group ,  auto refresh, LOV size, Position, Title)
            value     NUMBER);        (T or F, Record group name, x, y)

RECORD GROUPS

There are three types of record groups:

A.    query record groups
B.    static record groups
C.    non-query record groups.

Query Record Group:
A record group is an internal Form Builder data structure that has a column/row framework similar to a database table.  However, unlike database tables, record groups are separate objects that belong to the form module in which they are defined. A record group can have an unlimited number of columns of type CHAR, LONG, NUMBER, or DATE provided that the total number of columns does not exceed 64K.  Record group column names cannot exceed 30 characters. Programmatically, record groups can be used whenever the functionality offered by a two-dimensional array of multiple data types is desirable.

A record group built from a query can store records from database tables much like a database view, with the added advantage that the record group is local to Form Builder, rather than existing in the database.  Also, the SELECT statement used to create and populate a query record group can be constructed dynamically at runtime.

A query record group is a record group that has an associated SELECT statement. The columns in a query record group derive their default names, data types, and lengths from the database columns referenced in the SELECT statement.  The records in a query record group are the rows retrieved by the query associated with that record group.

Non-query Record Group:
 A non-query record group is a group that does not have an associated query, but whose structure and values can be modified programmatically at runtime.

Static Record Group:                         
A static record group is not associated with a query; rather, you define its structure and row values at design time, and they remain fixed at runtime.

At design time, you can create query record groups and static record groups. 
At runtime, you can programmatically create query record groups and non-query record groups.

When you create a record group, you do not specify its type explicitly; rather, the type is implied by when you create the record group (At design time or programmatically at runtime) and by how you define the group.

A)  CREATE_GROUP_FROM_QUERY:
Creates a record group with the given name. The record group has columns representing each column you include in the select list of the query. Add rows to the record group with the POPULATE_GROUP built-in.

FUNCTION CREATE_GROUP_FROM_QUERY
  (recordgroup_name  VARCHAR2,
   query             VARCHAR2); 

FUNCTION CREATE_GROUP_FROM_QUERY
  (recordgroup_name  VARCHAR2,
   query             VARCHAR2,
   scope             NUMBER,
   array_fetch_size  NUMBER);


B)  CREATE_GROUP
Restrictions on Record Groups The type of record group (static, query, or non-query) and when the group was created (at design time or programmatically at runtime) determine which built-in subprograms are valid for a particular group.

For example, you cannot use the ADD_GROUP_COLUMN function to add a column to a record group that was created at design time.  Similarly, you cannot use the ADD_GROUP_ROW procedure to add a row to a static record group.  The following sections describe the types of record groups for which each built-in is valid.

Can we bypass the Oracle login screen?

The first thing that the user sees when using run form is the Oracle logon prompt asking them for their username, password, and database to connect to. You can bypass this screen or customize it by displaying your own logon screen. Eg:
            ON-LOGIN

            declare
                uname varchar2(10);
                pass  varchar2(10);
            begin
                uname := 'username';
                pass  :='password';
                logon(uname, pass||'@connect_database');
            end;

How to use Dynamic SQL in Form?

The reason why you would want do to do this is because, like in database procedures, you can't use DDL such as DROP TABLE 'table_name' directly in PL/SQL with Forms. The way to do it is with the buit-in EXEC_SQL package. The same thing can also be accomplished with the FORMS_DDL built-in but that only applies to the current database connection, with the EXEC_SQL package it’s possible to have database connections to multiple databases and also execute non-oracle database procedures.

Difference between Key-next and Post-Text

Key-Next-Item: The key-next is fired as a result of the key action. Key next will not fire unless there is a key event.
Post-Text–Item: Fires during the Leave the Item process for a text item.  Specifically, this trigger fires when the input focus moves from a text item to any other item.

AR Lookup Navigation

Navigation path:
 "Setup - System - Quickcodes - Receivables".

Difference between NO DATA FOUND and %NOTFOUND?

NO DATA FOUND is an exception raised only for the SELECT....INTO statements when the where clause of the query does not match any rows. When the where clause of the explicit cursor does not match any rows the %NOTFOUND attribute is set to TRUE instead.
If the where clause of an update or delete statement does not match any rows, SQL%NOTFOUND is set to TRUE, rather than raising NO_DATA_FOUND. Because of this, all of the fetch loops shown so for use %NOTFOUND or %FOUND to determine the exits condition for the loop, rather than NO_DATA_FOUND EXCEPTION.

How many Integrity Rules are there and what are they?

There are Three Integrity Rules. They are as follows:
a) Entity Integrity Rule: The Entity Integrity Rule enforces that the Primary key cannot be Null
b) Foreign Key Integrity Rule: The FKIR denotes that the relationship between the foreign key and the primary key has to be enforced. When there is data in Child Tables the Master tables cannot be deleted.
c) Business Integrity Rules: The Third Integrity rule is about the complex business processes which cannot be implemented by the above 2 rules.

Exceptions (User-Defined/Oracle Defined)?

Oracle includes about 20 predefined exceptions (errors) - we can allow Oracle to raise these implicitly. For errors that don't fall into the predefined categories - declare in advance and allow oracle to raise an exception. For problems that are not recognized as an error by Oracle - but still cause some difficulty within your application - declare a User Defined Error and raise it explicitly
Trap non-predefined errors by declaring them. You can also associate the error no. with a name so that you can write a specific handler. This is done with the PRAGMA EXCEPION_INIT pragma. PRAGMA (pseudoinstructions) indicates that an item is a 'compiler directive' running this has no immediate effect but causes all subsequent references to the exception name to be interpreted as the associated Oracle Error. When an exception occurs you can identify the
associated error code/message with two supplied functions SQLCODE and SQLERRM.
Trapping user-defined exceptions

DECLARE the exception
RAISE the exception
Handle the raised exception

Propagation of Exception handling in sub blocks
If a sub block does not have a handler for a particular error it will propagate to the
enclosing block - where it can be caught by more general exception handlers.

RAISE_APPLICATION_ERROR (error_no, message [, {TRUE|FALSE}]);

This procedure allows user defined error messages from stored sub programs - call only from stored sub prog.
Error_no = a user defined no (between -20000 and -20999)

TRUE = stack errors
FALSE = keep just last

This can either be used in the executable section of code or the exception section

How many types of Exceptions?

There are 2 types of exceptions. They are
a) System Exceptions
E.g. When no_data_found, When too_many_rows
b) User Defined Exceptions
E.g. My_exception exception
When My_exception then

What is Pragma EXECPTION_INIT? Explain the usage?

Pragma exception_init Allow you to handle the Oracle predefined message by your own message. Means you can instruct compiler to associate the specific message to oracle predefined message at compile time. This way you improve the Readability of your program, and handle it according to your own way.

It should be declare at the DECLARE section.

declare
salary number;
FOUND_NOTHING exception;
Pragma exception_init(FOUND_NOTHING ,100);
begin
select sal in to salary from emp where ename ='ANURAG';
dbms_output.put_line(salary);
exception
WHEN FOUND_NOTHING THEN
dbms_output.put_line(SQLERRM);
end;

Can we truncate some of the rows from the table instead of truncating the full table?

You can truncate few rows from a table if the table is partitioned. You can truncate a single partition and keep remaining.

CREATE TABLE parttab (
state VARCHAR2(2)
sales NUMBER(10 2))
PARTITION BY LIST (state) (
PARTITION northwest VALUES ('OR' 'WA')
TABLESPACE uwdata
PARTITION southwest VALUES ('AZ' 'CA')
TABLESPACE uwdata);

INSERT INTO parttab VALUES ('OR' 100000);
INSERT INTO parttab VALUES ('WA' 200000);
INSERT INTO parttab VALUES ('AZ' 300000);
INSERT INTO parttab VALUES ('CA' 400000);
COMMIT;

SELECT * FROM parttab;

ALTER TABLE parttab
TRUNCATE PARTITION southwest;

SELECT * FROM parttab;

What are pseudo columns?

A pseudocolumn behaves like a table column, but is not actually stored in the table. You can select from pseudocolumns, but you cannot insert, update, or delete their values.

CURRVAL: returns the current value of a sequence.

NEXTVAL: increments the sequence and returns the next value.

You must qualify CURRVAL and NEXTVAL with the name of the sequence:
sequence.CURRVAL
sequence.NEXTVAL

LEVEL: For each row returned by a hierarchical query, the LEVEL pseudocolumn returns 1 for a root node, 2 for a child of a root, and soon. A root node is the highest node within an inverted tree. A child node is any non–root node. A parent node is any node that has children. A leaf node is any node without children.

ROWID: For each row in the database, the ROWID pseudo column returns a row’s address. ROWID values contain information necessary to locate a row:
·         which data block in the data file
·         which row in the data block (first row is 0)
·         which data file (first file is 1)

ROWNUM: For each row returned by a query, the ROWNUM pseudo column returns a number indicating the order in which Oracle7 selects the row from a table or set of joined rows. The first row selected has a
ROWNUM of 1, the second has 2, and so on.

You can use ROWNUM to limit the number of rows returned by a query, as in this example:
SELECT *
FROM emp
WHERE ROWNUM < 10

SYSDATE: Return the system date.