add

About Me

My photo
Oracle Apps - Techno Functional consultant

Friday, June 22

Oracle DBA Important Interview Questions


 
1) Difference between 8 and 8i

1. Transportable Tablespaces
            This feature allows a user to move a subset of an Oracle database into another Oracle database. It is a lot like
            unplugging the subset from the original database and plugging it into another one.

2. Extended Aggregate Operations
            The GROUP BY clause of SQL queries can now specify additional OLAP aggregate operations. These new
            operations are called ROLLUP and CUBE, and are effective for computing subtotals and cross-tabulations across
            multiple dimensions

3. Function-Based Indexes
            Indexes can now be created on functions and expressions that involve one or more columns in the table being
            indexed. A function-based index precomputes the value of the function or expression and stores it in the index.

4. Descending Indexes
            The DESC keyword on the CREATE INDEX statement is no longer ignored. It specifies that the index should be
            created in  descending order.

5. General Enhancements
             Some of the key enhancements include:
           
• LOB column support provides the ability to store large objects, such as text documents and images required
              by  various data cartridges and Web-based applications.
• Secondary index support allows for efficient access using non-primary key columns.
• Index-organized tables (IOTs) can be rebuilt with the new MOVE clause for ALTER TABLE, which also
  supports an ONLINE keyword. The ONLINE keyword may only be used for IOTs and allows DML
              operations on the IOT while the primary key index is being built.
                         CREATE TABLE... AS SELECT enables parallel loading of an index-organized table.

6. Triggers on Nested Table View Columns

7. DBMS_REPAIR Package
            Oracle8i provides enhanced block corruption repair capability through the new DBMS_REPAIR package. It
            provides the DBA with a three-stage approach to addressing corruptions.

8. Redo Log Analysis Using LogMiner
             Log files contain a wealth of useful information about the activities and history of an Oracle database, but until
            Oracle8i  there has been no easy tool that could tap into this information. LogMiner allows online and archived redo
            log files to be  read, analyzed, and interpreted by the user using SQL.

9. You can drop a new column

10. Online Read-Only Tablespaces
            Oracle8i improves the performance of the operation that places a tablespace in read-only mode. A tablespace in
            Oracle8i can be placed in read-only mode when there are no outstanding transactions in that tablespace alone, unlike
            previous versions of  Oracle where the operation completed only when there were no outstanding transactions in the
            entire database.

11. TRIM Function
             This enhancement implements the ANSI standard TRIM function. It combines the functionality of the existing
             LTRIM and RTRIM functions, allowing the user to trim leading or trailing characters, or both, from a character
            string.



12. SQL*Loader Enhancements
             For Oracle8i provides the following the SQL*Loader enhancements:
• SQL*Loader now includes support for the loading of objects, collections, and LOBs.
• There is no longer a 64K physical record size limit.
• A new keyword, FILLER, can be used to specify a filler field: a data file mapped field which corresponds to no database
  column. The filler field is assigned values from the data field to which it is mapped. 

13. Export and Import Utilities
             Many of the changes for the Import and Export utilities in Oracle8i are in support of other features. However, there
            are a few enhancements in these utilities which stand on their own.
• The ability to specify a query for the select statements that export uses to unload the tables.
• The ability to specify multiple dump files for an export command. (This allows users to circumvent the previous 2Gb limit
   for export dump files.)

14. Autonomous PL/SQL Blocks
             Autonomous PL/SQL blocks are PL/SQL blocks that have a transaction scope independent of the transaction scope
            of the calling PL/SQL block. They can perform operations, commit, and rollback independent of the transactions in
            the calling  block, before returning to the calling block. Transactions within an autonomous PL/SQL block are
            referred to as autonomous      transactions.

15 Parameter Passing by Reference
             In Oracle8i, PL/SQL supports three parameter passing modes: IN, IN OUT, and OUT. IN parameters are passed by
            reference; IN OUT parameters support copy-in and copy-out semantics; OUT parameters support copy-out
            semantics. Through a new syntax, using NOCOPY mode, Oracle8i allows all parameters to be passed efficiently by
            reference

16 OEM

17. Execute immediate function to execute DDL in PL/SQL.

18. Java support. web-based.


2)   Difference between 9i and 8i

3)   Difference between tablespace and datafile.

            TableSpace:   A database is divided into one or more logical storage units called tablespaces.
                                    Tablespaces are divided into logical units of storage called segments, which are further divided into
                                    extents.

• The SYSTEM Tablespace
• Using Multiple Tablespaces
• Space Management in Tablespaces
• Online and Offline Tablespaces
• Read-Only Tablespaces
• Temporary Tablespaces
• Transporting Tablespaces between Databases

            Syntax:
                               CREATE TABLESPACE tabspace_5 
                                DATAFILE 'diskb:tabspace_file3.dat' SIZE 500K REUSE
                               AUTOEXTEND ON NEXT 500K MAXSIZE 10M;
                               
                               CREATE TABLESPACE tabspace_3 
                               DATAFILE 'tabspace_file5.dbf' SIZE 2M
                               MINIMUM EXTENT 64K
                               DEFAULT STORAGE (INITIAL 128K NEXT 128K)
                               LOGGING;
 

 

DATAFILE filespec

Specify the datafile or files to make up the tablespace.

 

autoextend_clause

Use the autoextend_clauseto enable or disable the automatic extension of the datafile.
OFF :  Specify OFF to disable autoextend if it is turned on. NEXT and MAXSIZEare set to zero. Values for
            NEXTand MAXSIZE must be respecified in further ALTER TABLESPACEAUTOEXTEND statements.
ON:     Specify ONto enable autoextend.
NEXT integer:            Specify the disk space to allocate to the datafile when more extents are required
maxsize_clause:           The maxsize_clauselets you specify the maximum disk space allowed for allocation to the
                        datafile. 
integer:                       Specify in bytes the maximum disk space allowed for allocation to the tempfile. Use Kor M to
                        specify this space in kilobytes or megabytes
 UNLIMITED:           Specify UNLIMITEDto set no limit on allocating disk space to the datafile.

            MINIMUMEXTENT integer

Specify the minimum size of an extent in the tablespace. This clause lets you control free space fragmentation in the tablespace by ensuring that every used or free extent size in a tablespace is at least as large as, and is a multiple of, integer.
           

            LOGGING| NOLOGGING

Specify the default logging attributes of all tables, indexes, and partitions within the tablespace. LOGGINGis the
default.
The tablespace-level logging attribute can be overridden by logging specifications at the table, index, and partition
levels.
Only the following operations support the NOLOGGINGmode:
·         DML: direct-load INSERT(serial or parallel), Direct Loader (SQL*Loader)
·         DDL: CREATE TABLE... AS SELECT, CREATE INDEX, ALTER INDEX... REBUILD, ALTERINDEX ... REBUILDPARTITION, ALTERINDEX ... SPLITPARTITION, ALTERTABLE ... SPLITPARTITION, and ALTERTABLE ... MOVEPARTITION
In NOLOGGING mode, data is modified with minimal logging (to mark new extents INVALIDand to record
dictionary changes). When applied during media recovery, the extent invalidation records mark a range of blocks as
logically corrupt, because the redo data is not logged. Therefore, if you cannot afford to lose the object, you should
take a backup after the NOLOGGINGoperation.

DEFAULT storage_clause

Specify the default storage parameters for all objects created in the tablespace. For a dictinary-managed temporary
tablespace, Oracle considers only the NEXTparameter of the storage_clause.

 ONLINE | OFFLINE

 ONLINE:       Specify ONLINEto make the tablespace available immediately after creation to users who have been

                        granted  access to the tablespace. This is the default.

OFFLINE:      Specify OFFLINEto make the tablespace unavailable immediately after creation.

                                    The data dictionary view DBA_TABLESPACESindicates whether each tablespace is online or offline.  

 

PERMANENT | TEMPORARY

 

PERMANENT:          Specify PERMANENTif the tablespace will be used to hold permanent objects. This is the default

            TEMPORARY:        Specify TEMPORARYif the tablespace will be used only to hold temporary objects, for example,
                                    segments used by implicit sorts to handle ORDERBY clauses.
            Restriction: If you specify TEMPORARY, you cannot specify EXTENT MANAGEMENTLOCAL. 

 

extent_management_clause

The extent_management_clauselets you specify how the extents of the tablespace will be managed.
  
            Datafiles:         A tablespace in an Oracle database consists of one or more physical datafiles. A datafile can be associated
                                    with only one tablespace and only one database.
                                    The data associated with schema objects in a tablespace is physically stored in one or more of the datafiles
                                    that constitute the tablespace. Note that a schema object does not correspond to a specific datafile; rather, a
                                    datafile is a repository for the data of any schema object within a specific tablespace. Oracle allocates space
                                    for the data associated with a schema object in one or more datafiles of a tablespace. Therefore, a schema
                                    object can span one or more datafiles.


                                              ALTER TABLESPACE users
                                             ADD DATAFILE '/u02/oracle/rbdb1/users03.dbf' SIZE 10M
                                              AUTOEXTEND ON
                                              NEXT 512K
                                             MAXSIZE 250M;
The value of NEXT is the minimum size of the increments added to the file when it extends. The value of MAXSIZE is the maximum size to which the file can automatically extend.
Taking Datafiles Offline in NOARCHIVELOG Mode
The following statement takes the specified datafile offline:
                               ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users03.dbf' OFFLINE DROP;

Renaming Datafiles in a Single Tablespace

                               ALTER TABLESPACE users
                               RENAME DATAFILE '/u02/oracle/rbdb1/user1.dbf',
                                                                              '/u02/oracle/rbdb1/user2.dbf'
                                                               TO        '/u02/oracle/rbdb1/users01.dbf', 
                                                              '/u02/oracle/rbdb1/users02.dbf';


4)      Oracle Architecture.

Background Processes
---------------------
1) Database Writer: The database writer writes modified blocks from the database buffer cache to the datafiles.
    initialization parameter DB_WRITER_PROCESSES specifies the number of DBWn processes.

2) Log Writer :  The log writer writes redo log entries to disk. Redo log entries are generated in the redo log buffer of the
                        system global area(SGA),and LGWR writes the redo log entries sequentially into an online redo log file.

3) Checkpoint: At specific times, all modified database buffers in the system global area are written to the datafiles by DBWn; this event is called a checkpoint.

4) System Monitor (SMON)
The system monitor performs crash recovery when a failed instance starts up
again. In a multiple instance system (one that uses Oracle Parallel Server),
the SMON process of one instance can perform instance recovery for other
instances that have failed. SMON also cleans up temporary segments that are no longer in use and recovers dead transactions skipped during crash and instance recovery because of file-read or offline errors. These transactions are eventually recovered by SMON when the tablespace or file is brought back online. SMON also coalesces free extents within the database's dictionary-managed tablespaces to make free space contiguous and easier to allocate.

5) Process Monitor (PMON)
The process monitor performs process recovery when a user process fails. PMON
is responsible for cleaning up the cache and freeing resources that the process was using. PMON also checks on dispatcher (see below) and server processes and restarts them if they have failed.

6) Archiver (ARCn)
The archiver copies the online redo log files to archival storage when they
are full or a log switch occurs. Although a single ARCn process (ARC0) is
sufficient for most systems, you can specify up to ten ARCn processes by
using the dynamic initialization parameter LOG_ARCHIVE_MAX_PROCESSES. If the
workload becomes too great for the current number of ARCn processes, LGWR
automatically starts another ARCn process up to the maximum of ten processes. ARCn is active only when a database is in ARCHIVELOG mode and automatic
archiving is enabled.

7) Recoverer (RECO)
The recoverer is used to resolve distributed transactions that are pending
due to a network or system failure in a distributed database. At timed intervals, the local RECO attempts to connect to remote databases and automatically complete the commit or rollback of the local portion of any pending distributed transactions.

8) Dispatcher (Dnnn)
Dispatchers are optional background processes, present only when a multi-threaded server configuration is used. At least one dispatcher process is created for every communication protocol in use (D000, . . ., Dnnn). Each dispatcher process is responsible for routing requests from connected user processes to available shared server processes and returning the responses back to the appropriate user processes.

9) Lock (LCK0)
The lock process (LCK0) is used for inter-instance locking in the Oracle
Parallel Server.

10) Job Queue (SNPn)
In a distributed database configuration, up to thirty-six job queue processes
(SNP0, ..., SNP9, SNPA, ..., SNPZ) can automatically refresh table snapshots.
These processes wake up periodically and refresh any snapshots that are
scheduled to be automatically refreshed. If more than one job queue process is used, the processes share the task of refreshing snapshots. These processes also execute job requests created by the DBMS_JOB package and propagate queued messages to queues on other databases.

11) Queue Monitor (QMNn)
The queue monitor(s) are optional background processes that monitor the
message queuesfor Oracle Advanced Queuing (Oracle AQ). You can configure up
to ten queue monitor processes.

 

Memory Structures

      ----------------------------

 

System Global Area

The System Global Area (SGA) is a shared memory region that contains data and control information for one Oracle
instance. An SGA and the Oracle background processes constitute an Oracle instance. Oracle allocates the system global area when an instance starts and deallocates it when the instance shuts down. Each instance has its own system global area.

Program Global Areas (PGA)

A program global area (PGA) is a memory region containing data and control information for a single process
(server or background). Consequently, a PGA is sometimes called a process global area.
  
Q)                What is named and positioned notation in procedures and function

Positional versus Named Notation

When calling a subprogram, you can write the actual parameters using either positional or named notation.
That is, you can indicate the association between an actual and formal parameter by position or name. So,
given the declarations
               DECLARE
                  acct INTEGER;
                  amt  REAL;
                  PROCEDURE credit_acct (acct_no INTEGER, amount REAL) IS ...
you can call the procedure credit_acct in four logically equivalent ways:
               BEGIN
                  credit_acct(acct, amt);                  -- positional notation
                  credit_acct(amount => amt, acct_no => acct);  -- named notation
                  credit_acct(acct_no => acct, amount => amt);  -- named notation
                  credit_acct(acct, amount => amt);             -- mixed notation

 

Using Positional Notation

The first procedure call uses positional notation. The PL/SQL compiler associates the first actual parameter, acct, with the first formal parameter, acct_no. And, the compiler associates the second actual parameter, amt, with the second formal parameter, amount.

Using Named Notation

The second procedure call uses named notation. An arrow (=>) serves as the association operator, which associates the formal
parameter to the left of the arrow with the actual parameter to the right of the arrow. The third procedure call also uses named
notation and shows that you can list the parameter pairs in any order. So, you need not know the order in which the formal
parameters are listed.

Using Mixed Notation

The fourth procedure call shows that you can mix positional and named notation. In this case, the first parameter uses positional
notation, and the second parameter uses named notation. Positional notation must precede named notation. The reverse is not
allowed. For example, the following procedure call is illegal:
               credit_acct(acct_no => acct, amt);  -- illegal


Q)  what does the NOCOPY compiler hint do?

Using the NOCOPY Compiler Hint

Suppose a subprogram declares an INparameter, an OUT parameter, and an IN OUTparameter. When you call
the subprogram, the INparameter is passed by reference. That is, a pointer to the INactual parameter is
passed to the corresponding formal parameter. So, both parameters reference the same memory location,
which holds the value of the actual parameter.
By default, the OUTand IN OUTparameters are passed by value. That is, the value of the INOUT actual
parameter is copied into the corresponding formal parameter. Then, if the subprogram exits normally, the
values assigned to the OUTand IN OUTformal parameters are copied into the corresponding actual
parameters.

When the parameters hold large data structures such as collections, records, and instances of object types,
all this copying slows down execution and uses up memory. To prevent that, you can specify the NOCOPY
hint, which allows the PL/SQL compiler to pass OUTand IN OUTparameters by reference.
In the following example, you ask the compiler to pass IN OUTparameter my_staff by reference instead of
by value:
               DECLARE
                  TYPE Staff IS VARRAY(200) OF Employee;
                  PROCEDURE reorganize (my_staff IN OUT NOCOPY Staff) IS ...
Remember, NOCOPYis a hint, not a directive. So, the compiler might pass my_staffby value despite your
request. Usually, however, NOCOPYsucceeds. So, it can benefit any PL/SQL application that passes around
large data structures

NOCOPY lets you trade well-defined exception semantics for better performance. Its use affects exception
handling in the following ways:
  • Because NOCOPY is a hint, not a directive, the compiler can pass NOCOPY parameters to a subprogram by value or by reference. So, if the subprogram exits with an unhandled exception, you cannot rely on the values of the NOCOPY actual parameters.
  • By default, if a subprogram exits with an unhandled exception, the values assigned to its OUT and IN OUT formal parameters are not copied into the corresponding actual parameters, and changes appear to roll back. However, when you specify NOCOPY, assignments to the formal parameters immediately affect the actual parameters as well. So, if the subprogram exits with an unhandled exception, the (possibly unfinished) changes are not "rolled back."

Restrictions on NOCOPY

In the following cases, the PL/SQL compiler ignores the NOCOPY hint and uses the by-value parameter-
passing method (no error is generated):
  • The actual parameter is an element of an index-by table. This restriction does not apply to entire index-by tables.
  • The actual parameter is constrained (by scale or NOT NULL for example). This restriction does not extend to constrained elements or attributes. Also, it does not apply to size-constrained character strings.
  • The actual and formal parameters are records, one or both records were declared using %ROWTYPE or %TYPE, and constraints on corresponding fields in the records differ.
  • The actual and formal parameters are records, the actual parameter was declared (implicitly) as the index of a cursor FOR loop, and constraints on corresponding fields in the records differ.
  • Passing the actual parameter requires an implicit datatype conversion.
  • The subprogram is involved in an external or remote procedure call.

Q)  Can u write return statement in procedures ? if yes what will happen?

No, we cannot have a return statement in procedure. If given it gives the foll. Info.

PLS-00372 In a procedure, RETURN statement cannot contain an expression
Cause: In a procedure, a RETURN statement contains an expression, which is not allowed. In functions, a RETURN statement must contain an expression because its value is assigned to the function identifier. However, in procedures, a RETURN statement lets you exit before the normal end of the procedure is reached.
Action: Remove the expression from the RETURN statement, or redefine the procedure as a function.

Q)    How can u make a procedure return a value?
Specify OUT parameter.

Procedure Syntax :
               [CREATE [OR REPLACE]]
               PROCEDURE procedure_name[(parameter[, parameter]...)]
                  [AUTHID {DEFINER | CURRENT_USER}] {IS | AS}
                  [PRAGMA AUTONOMOUS_TRANSACTION;]
                  [local declarations]
               BEGIN
                  executable statements
               [EXCEPTION
                  exception handlers]
               END [name];
 
 
where parameter stands for the following syntax:
               parameter_name [IN | OUT [NOCOPY] | IN OUT [NOCOPY]] datatype
                                  [{:= | DEFAULT} expression]




Q) What does the returning clause in  DML operation will do?
      Returning_clause: This clause lets you return values from the deleted rows, thereby eliminating the need to SELECT the rows beforehand. You  can retrieve the column values into variables and/or host variables, or into collections and/or host arrays. However, you cannot use the RETURNINGclause for remote or parallel deletes.

RETURNING INTO ... : Used only for DML statements that have a RETURNINGclause (without a BULK COLLECT

 clause), this clause specifies the bind variables into which column values are returned. For each value returned by the DML

statement, there must be a corresponding, type-compatible variable in the RETURNING INTOclause.


            returning_clause

The returning clause retrieves the rows affected by a DML (INSERT, UPDATE, or DELETE)statement. You can specify
this clause for tables and snapshots, and for views with a single base table.
·         When operating on a single row, a DML statement with a returning_clausecan retrieve column expressions using the affected row, rowid, and REFsto the affected row and store them in host variables or PL/SQL variables.
·         When operating on multiple rows, a DML statement with the returning_clausestores values from expressions, rowids, and REFsinvolving the affected rows in bind arrays.

expr  
Each item in the expr list must be a valid expression syntax. 
INTO 
The INTO clause indicates that the values of the changed rows are to be stored in the variable(s) specified in data_item list. 
data_item 
Each data_item is a host variable or PL/SQL variable that stores the retrieved expr value. 



For each expression in the RETURNINGlist, you must specify a corresponding type-compatible PL/SQL variable or host
variable in the INTOlist.
Restrictions:
·         You cannot use this clause with parallel DML or with remote objects.
·         You cannot retrieve LONGtypes with this clause.
·         You cannot specify this clause for a view on which an INSTEADOF trigger has been defined.

RETURNING Clause Example

The following example returns column sal from the deleted rows and stores the result in bind array :1:
                               DELETE FROM emp
                               WHERE job = 'SALESMAN' AND COMM < 100
                               RETURNING sal INTO :1;

Q) Types of indexes
Indexes are optional structures associated with tables and clusters. You can create indexes on one or more columns of a table
to speed SQL statement execution on that table.

1)       Unique and Nonunique Indexes: Indexes can be unique or nonunique. Unique indexes guarantee
      that no two rows of a table have duplicate values in the columns that define the index.
      Nonunique indexes do not impose this restriction on the column values.
2)       Composite Indexes: A composite index (also called a concatenated index) is an index that you create on multiple columns in a table. Columns in a composite index can appear in any order and need not be adjacent in the table.
3)       Function-Based Indexes: A function-based index precomputes the value of the function or expression and stores it in the index. You can create a function-based index as either a B-tree or a bitmap index. Function-based indexes provide an efficient mechanism for evaluating statements that contain functions in their WHERE clauses. You can create a function-based index to materialize computational-intensive expressions in the index, so that Oracle does not need to compute the value of the expression when it processes SELECT and DELETE statements.
4)       B-tree indexes: The B-tree structure has the following advantages:
·         All leaf blocks of the tree are at the same depth, so retrieval of any record from anywhere in the index takes approximately the same amount of time.
·         B-tree indexes automatically stay balanced.
·         All blocks of the B-tree are three-quarters full on the average.
·         B-trees provide excellent retrieval performance for a wide range of queries, including exact match and range searches.
·         Inserts, updates, and deletes are efficient, maintaining key order for fast retrieval.
·         B-tree performance is good for both small and large tables, and does not degrade as the size of a table grows.
5)       B-tree cluster indexes
6)       Hash cluster indexes
7)       Reverse key indexes: Creating a reverse key index, compared to a standard index, reverses the bytes of each column indexed (except the rowid) while keeping the column order. Such an arrangement can help avoid performance degradation in an Oracle Parallel Server environment where modifications to the index are concentrated on a small set of leaf blocks. By reversing the keys of the index, the insertions become distributed across all leaf keys in the index.
Using the reverse key arrangement eliminates the ability to run an index range scanning query on
the index. Because lexically adjacent keys are not stored next to each other in a reverse-key
index, only fetch-by-key or full-index (table) scans can be performed.
8)       Bitmap indexes: A bitmap for each key value is used instead of a list of rowids.
Bitmap indexing benefits data warehousing applications which have large amounts of data and
ad hoc queries but a low level of concurrent transactions. For such applications, bitmap indexing provides:
·         Reduced response time for large classes of ad hoc queries
·         Asubstantial reduction of space usage compared to other indexing techniques
·         Dramatic performance gains even on very low end hardware
·         Very efficient parallel DML and loads
Bitmap indexes are not suitable for OLTP applications with large numbers of concurrent transactions modifying the data. These indexes are primarily intended for decision support in data warehousing applications where users typically query the data rather than update it.

Indexes and Keys: 

Indexes are structures actually stored in the database, which users create, alter, and drop using SQL statements. You create an
index to provide a fast access path to table data. Keysare strictly a logical concept. Keys correspond to another feature of
Oracle called integrity constraints, which enforce the business rules of a database.

            Indexes and Nulls:

NULL values in indexes are considered to be distinct except when all the non-NULL values in two or more rows of an index
are identical, in which case the rows are considered to be identical. Therefore, UNIQUE indexes prevent rows containing
NULL values from being treated as identical. This does not apply if there are no non-NULL values--in other words, if the
rows are entirely NULL.

Q) What are optimizer hints?
Hints allow you to make decisions usually made by the optimizer. You can use hints to specify the
following:
·         The optimization approach for a SQL statement.
·         The goal of the cost-based optimizer for a SQL statement.
·         The access path for a table accessed by the statement.
·         The join order for a join statement.
·         A join operation in a join statement.
            Hints provide a mechanism to direct the optimizer to choose a certain query execution plan based on the
            following criteria:
·         Join order
·         Join method
·         Access method
·         Parallelization
               
               Hints for Join Operations
1)      USE_NL:  hint causes Oracle to join each specified table to another row source with a nested loops join using the 
                  specified table as the inner table
                               
                               SELECT /*+ ORDERED USE_NL(customers) to get first row faster */
                                              accounts.balance, customers.last_name, customers.first_name
                               FROM accounts, customers
                               WHERE accounts.custno = customers.custno;
 
2)   USE_MERGE: hint causes Oracle to join each specified table with another row source with a sort-merge join.
                               
                               SELECT /*+USE_MERGE(emp dept)*/ * 
                               FROM emp, dept 
        WHERE emp.deptno = dept.deptno;

3)    USE_HASH: hint causes Oracle to join each specified table with another row source with a hash join.

               
                               SELECT /*+use_hash(emp dept)*/ * 
                               FROM emp, dept 
                               WHERE emp.deptno = dept.deptno; 
 

    4)   DRIVING_SITE: hint forces query execution to be done at a different site than that selected by Oracle. This hint can

be used with either rule-based or cost-based optimization.

 
SELECT /*+DRIVING_SITE(dept)*/ * 
FROM emp, dept@rsite 
WHERE emp.deptno = dept.deptno;
 
               5)   LEADING: hint causes Oracle to use the specified table as the first table in the join order. 
Where table is the name or alias of a table to be used as the first table in the join order.
If you specify two or more LEADINGhints on different tables, then all of them are ignored. If you specify
the ORDEREDhint, then it overrides all LEADINGhints.
 

Q) What are diff types of joins?

 

1.      Equijoins: An equijoin is a join with a join condition containing an equality operator. An equijoin combines rows

                           that have equivalent values for the specified columns.

 
                               SELECT ename, job, dept.deptno, dname
                               FROM emp, dept
                               WHERE emp.deptno = dept.deptno;

2.         Self Joins: A self join is a join of a table to itself. This table appears twice in the FROMclause and is followed by

                            table aliases that qualify column names in the join condition.

 
                               SELECT e1.ename||' works for '||e2.ename "Employees and their Managers" 
                               FROM emp e1, emp e2   WHERE e1.mgr = e2.empno; 
 

3.   Outer Joins: An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join

         Condition and those rows from one table for which no rows from the other satisfy the join condition.

 
            Outer join queries are subject to the following rules and restrictions:
·         The (+) operator can appear only in the WHERE clause or, in the context of left-correlation (that is, when specifying the TABLEclause) in the FROM clause, and can be applied only to a column of a table or view.
·         If A and B are joined by multiple join conditions, you must use the (+) operator in all of these conditions. If you do not, Oracle will return only the rows resulting from a simple join, but without a warning or error to advise you that you do not have the results of an outer join.
·         The (+) operator can be applied only to a column, not to an arbitrary expression. However, an arbitrary expression can contain a column marked with the (+) operator.
·         A condition containing the (+) operator cannot be combined with another condition using the ORlogical operator.
·         A condition cannot use the IN comparison operator to compare a column marked with the (+) operator with an expression.
·         A condition cannot compare any column marked with the (+) operator with a subquery.

                               SELECT ename, job, dept.deptno, dname 
                               FROM emp, dept 
                               WHERE emp.deptno (+) = dept.deptno; 

SUBQUERY: A subquery in the FROM clause of a SELECTstatement is also called an inline view.
   A subquery in the WHEREclause of a SELECT statement is also called a nested subquery.

A correlated subquery is evaluated once for each row processed by the parent statement.

               SELECT select_list 
                   FROM table1 t_alias1 
                   WHERE expr operator 
                                (SELECT column_list 
                                  FROM table2 t_alias2 
                                  WHERE t_alias1.column operator t_alias2.column); 
               UPDATE table1 t_alias1 
                   SET column = 
                       (SELECT expr 
                           FROM table2 t_alias2 
                           WHERE t_alias1.column = t_alias2.column); 
               DELETE FROM table1 t_alias1 
                   WHERE column operator 
                       (SELECT expr 
                           FROM table2 t_alias2 
                           WHERE t_alias1.column = t_alias2.column); 

Q) What are the 2 types of views?
A view is a tailored presentation of the data contained in one or more tables or other views. A view takes the output of a
query and treats it as a table. Therefore, a view can be thought of as a stored query or a virtual table.

1) Updatable Join Views

A join view is defined as a view that has more than one table or view in its FROM clause (a join) and that does not use any of
these clauses: DISTINCT, AGGREGATION, GROUP BY, START WITH, CONNECT BY, ROWNUM, and set operations
(UNION ALL, INTERSECT, and so on).
An updatable join view is a join view that involves two or more base tables or views, where UPDATE, INSERT, and
DELETE operations are permitted. The data dictionary views ALL_UPDATABLE_COLUMNS,
DBA_UPDATABLE_COLUMNS, and USER_UPDATABLE_COLUMNS contain information that indicates which of the
view columns are updatable.


2) Object Views:

In the Oracle object-relational database, object viewsallow you to retrieve, update, insert, and delete relational data as if
they were stored as object types. You can also define views that have columns which are object datatypes, such as
objects, REFs, and collections (nested tables and VARRAYs).

3) Inline Views:

An inline view is not a schema object. It is a subquery with an alias (correlation name) that you can use like a view
within a SQL statement.
                               SELECT v.year, s.prod_name, SUM(s.sum_sales) FROM sumtab s, 
                               (SELECT DISTINCT t.month, t.year FROM time t) v 
                               WHERE s.month = v.month 
                               GROUP BY v.year, s.prod_name; 
 

            4) Materialized Views:

Materialized views, also called snapshots, are schema objects that can be used to summarize, precompute, replicate, and
distribute data. They are suitable in various computing environments such as data warehousing, decision support, and
distributed or mobile computing:


Q) Can u create a view without creating the underlying table.
No (Check this).

Q) What is mutating and constraining table error??
 
Mutating Tables : A mutating table is a table that is currently being modified by an UPDATE, DELETE, or INSERT
statement, or it is a table that might need to be updated by the effects of a declarative DELETECASCADE
referential integrity constraint. The restrictions on such a table apply only to the session that issued the statement in progress.
Tables are never considered mutating for statement triggers unless the trigger is fired as the result of a
DELETE CASCADE. Views are not considered mutating in INSTEADOF triggers.
For all row triggers, or for statement triggers that were fired as the result of a DELETECASCADE, there are two important restrictions regarding mutating tables. These restrictions prevent a trigger from seeing an inconsistent set of data.
·         The SQL statements of a trigger cannot read from (query) or modify a mutating table of the triggering statement.

The mutating error  prevents the trigger from reading or modifying the table that the parent statement is modifying. Before Oracle8i, there was a "constraining error" that prevented a row trigger from modifying a table when the parent statement implicitly read that table to enforce a foreign key constraint. As of Oracle8i, there is no constraining error. In addition, checking of the foreign key is deferred until at least the end of the parent statement.

Q) What is a trigger and what are the types of DB Triggers?

Triggers are procedures that are stored in the database and implicitly run, or fired, when something happens.

 

Types of Triggers

A trigger is either a stored PL/SQL block or a PL/SQL, C, or Java procedure associated with a table, view, schema, or the database itself. Oracle automatically executes a trigger when a specified event takes place, which may be in the form of a system event or a DML statement being issued against the table.
Triggers can be:
·         DML triggers on tables.
·         INSTEAD OF triggers on views.

Triggers can be created or fired on any of the following:
·         DML statements (DELETE, INSERT, UPDATE)
·         DDL statements (CREATE, ALTER, DROP)
·         Database operations (SERVERERROR, LOGON, LOGOFF, STARTUP, SHUTDOWN)

Q) What are autonomous transactions?
An autonomous transaction (AT) is an independent transaction started by another transaction, the main transaction (MT). It
lets you suspend the main transaction, do SQL operations, commit, or roll back those operations, then resume the main
transaction. An autonomous transactionexecutes within an autonomous scope. An autonomous scope is a routine you mark
with the pragma (compiler directive) AUTONOMOUS_TRANSACTION.

Q) Advantages of using packages?
Packages encapsulate related procedures, functions, and associated cursors and variables together as a unit in the database.

 

Advantages of Packages

Packages are used to define related procedures, variables, and cursors and are often implemented to provide advantages in the
following areas:
·         Encapsulation of related procedures and variables
·         Declaration of public and private procedures, variables, constants, and cursors
·         Better performance

Q) What is Procedure/Package overloading ?

Procedure Overloading: creating multiple procedures with the same name in the same package, each taking arguments of
                                    different number or  datatype.
Package Overloading:  PL/SQL allows two or more packaged subprograms to have the same name. This option is useful
                                   when you want a subprogram to accept parameters that have different datatypes.


Q) What is forward declaration?

PL/SQL requires that you declare an identifier before using it. Therefore, you must declare a subprogram before calling it.
For example, the following declaration of procedure award_bonusis illegal because award_bonus calls procedure
calc_rating, which is not yet declared when the call is made:
               DECLARE
                  ...
                  PROCEDURE award_bonus IS
                  BEGIN
                     calc_rating(...);  -- undeclared identifier
                     ...
                  END;
                  PROCEDURE calc_rating (...) IS
                  BEGIN 
                     ... 
                  END;
 
In this case, you can solve the problem easily by placing procedure calc_ratingbefore procedure award_bonus. However, the
easy solution does not always work. For example, suppose the procedures are mutually recursive (call each other) or you
want to define them in logical or alphabetical order.
You can solve the problem by using a special subprogram declaration called a forward declaration, which consists of a subprogram spec terminated by a semicolon. In the following example, the forward declaration advises PL/SQL that the body of procedure calc_ratingcan be found later in the block.
               DECLARE
                  PROCEDURE calc_rating ( ... );  -- forward declaration
                  ...
Although the formal parameter list appears in the forward declaration, it must also appear in the subprogram body. You can
place the subprogram body anywhere after the forward declaration, but they must appear in the same program unit.


Q) What are recursive functions/Sub Programs?
A recursive subprogram is one that calls itself. Think of a recursive call as a call to some other subprogram that does the
same task as your subprogram. Each recursive call creates a new instance of any items declared in the subprogram, including
parameters, variables, cursors, and exceptions. Likewise, new instances of SQL statements are created at each level in the
recursive descent.
There must be at least two paths through a recursive subprogram: one that leads to the recursive call and one that does not. At
least one path must lead to a terminating condition. Otherwise, the recursion would (theoretically) go on forever. In practice,
if a recursive subprogram strays into infinite regress, PL/SQL eventually runs out of memory and raises the predefined
exception STORAGE_ERROR.

FUNCTION fac (n POSITIVE) RETURN INTEGER IS  -- returns n!
BEGIN
   IF n = 1 THEN  -- terminating condition
      RETURN 1;
   ELSE
      RETURN n * fac(n - 1);  -- recursive call
   END IF;
END fac;


Q) What are different types of cursors??

Cursor: Oracle uses work areas to execute SQL statements and store processing information
2 Kinds of Cursors are:  implicitand explicit.

Explicit Cursors

The set of rows returned by a query can consist of zero, one, or multiple rows, depending on how many rows meet your search
criteria. When a query returns multiple rows, you can explicitly declare a cursor to process the rows. Moreover, you can declare a
cursor in the declarative part of any PL/SQL block, subprogram, or package.
You use three commands to control a cursor: OPEN, FETCH, and CLOSE. First, you initialize the cursor with the OPEN
statement, which identifies the result set. Then, you can execute FETCH repeatedly until all rows have been retrieved, or you can
use the BULKCOLLECT clause to fetch all rows at once. When the last row has been processed, you release the cursor with the
CLOSE statement. You can process several queries in parallel by declaring and opening multiple cursors
A cursor can take parameters, which can appear in the associated query wherever constants can appear. The formal parameters of
a cursor must be INparameters. Therefore, they cannot return values to actual parameters. Also, you cannot impose the constraint
 NOTNULL on a cursor parameter.






Implicit Cursor
Oracle implicitly opens a cursor to process each SQL statement not associated with an explicit cursor. PL/SQL lets you refer to
the most recent implicit cursor as the SQLcursor, which has four attributes: %FOUND, %ISOPEN, %NOTFOUND, and
%ROWCOUNT. They give you useful information about the execution of data manipulation statements. The SQLcursor has an
additional attribute, %BULK_ROWCOUNT, designed for use with the FORALLstatement.

%BULK_ROWCOUNT

This is a composite attribute designed for use with the FORALLstatement. This attribute has the semantics of an index-by
table. Its ith element stores the number of rows processed by the ith execution of an UPDATEor DELETE statement. If the
ith execution affects no rows, %BULK_ROWCOUNT(i) returns zero.

%FOUND

This attribute yields TRUEif an INSERT, UPDATE, or DELETE statement affected one or more rows or a SELECT INTO
statement returned one or more rows. Otherwise, it yields FALSE.

index

This is an expression that must yield (or convert implicitly to) an integer.

%ISOPEN

This attribute always yields FALSEbecause Oracle closes the SQLcursor automatically after executing its associated SQL
statement.

%NOTFOUND

This attribute is the logical opposite of %FOUND. It yields TRUE if an INSERT, UPDATE, or DELETEstatement affected
no rows, or a SELECTINTO statement returned no rows. Otherwise, it yields FALSE.

%ROWCOUNT

This attribute yields the number of rows affected by an INSERT, UPDATE, or DELETEstatement, or returned by a SELECT
INTO statement.

SQL

This is the name of the Oracle implicit cursor.

You can use cursor attributes in procedural statements but not in SQL statements. Before Oracle opens the SQLcursor
automatically, the implicit cursor attributes yield NULL. The values of cursor attributes always refer to the most recently
executed SQL statement, wherever that statement appears. It might be in a different scope. So, if you want to save an attribute
value for later use, assign it to a Boolean variable immediately.



Q) what are ref cursors or Cursor variable what is their advantages?
Cursor variable points to the current row in the result set of a multi-row query. But, cursors differ from cursor variables the way constants differ from variables. Whereas a cursor is static, a cursor variable is dynamic because it is not tied to a specific query. You can open a cursor variable for any type-compatible query.

Cursor Variables: which hold the memory location (address) of some item instead of the item itself.
      Advantages:
·         Dynamic (U can pass dynamic queries).
·         To pass query result sets between PL/SQL stored subprograms and various clients.
·         you can declare a cursor variable on the client side(if PL/SQL Engine exists), open and fetch from it on the server side, then continue to fetch from it back on the client side.
·         Can reduce network traffic by having a PL/SQL block open (or close) several host cursor variables in a single round trip.

Defining REF CURSOR Types

First, define a REFCURSOR type, then declare cursor variables of that type. Define REFCURSOR types in any PL/SQL block, subprogram, or package using the syntax

TYPE ref_type_name IS REF CURSOR [RETURN return_type];
 
where ref_type_nameis a type specifier used in subsequent declarations of cursor variables and return_typemust represent a record or a row in a database table. In the following example, you specify a return type that represents a row in the database table dept:

 
 
 
 
DECLARE
   TYPE DeptCurTyp IS REF CURSOR RETURN dept%ROWTYPE;
 
REF CURSORtypes can be strong (restrictive) or weak (nonrestrictive). As the next example shows, a strong REFCURSOR type definition specifies a return type, but a weak definition does not:
DECLARE
   TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;  -- strong
   TYPE GenericCurTyp IS REF CURSOR;  -- weak
 
Strong REFCURSOR types are less error prone because the PL/SQL compiler lets you associate a strongly typed cursor variable only with type-compatible queries. However, weak REFCURSOR types are more flexible because the compiler lets you associate a weakly typed cursor variable with any query.


Q) Diff between nested tables, varrays and index by tables?

Nested Table: This can be considered one-column database tables
Within PL/SQL, nested tables are like one-dimensional arrays.
However, nested tables differ from arrays in two important
·         Arrays have a fixed upper bound, but nested tables are unbounded. So, the size of a nested table can increase
dynamically.
·         Arrays must be dense(have consecutive subscripts). So, cannot delete individual elements from an array. Initially, nested tables are dense, but they can be sparse(have nonconsecutive subscripts). So, can delete elements from a nested table using the built-in procedure DELETE. That might leave gaps in the index, but the built-in function NEXTlets you iterate over any series of subscripts.

Nested Tables versus Index-by Tables
·         Nested tables can be stored in a database column (hence the term "nested table") but index-by tables cannot.
·         Nested tables extend the functionality of index-by tables by letting you SELECT, INSERT, UPDATE, and DELETEnested tables stored in the database. For example, the built-in procedure TRIMcannot be applied to index-by tables.
·         An uninitialized nested table is atomically null (that is, the table itself is null, not its elements), but an uninitialized index-by table is merely empty. So, you can apply the ISNULL comparison operator to nested tables but not to index-by tables.
·         PL/SQL supports implicit (automatic) datatype conversion between host arrays and index-by tables (but not nested tables). So, the most efficient way to pass collections to and from the database server is to use anonymous PL/SQL blocks to bulk-bind input and output host arrays to index-by tables.
·         index-by tables are initially sparse. So, they are convenient for storing reference data using a numeric primary key (account numbers or employee numbers for example) as the index.
·         index-by tables can have negative subscripts (nested tables cannot).
·         to extend a nested table, you must use the built-in procedure EXTEND, but to extend an index-by table, you just specify larger subscripts

Varrays versus Nested Tables

·         Varrays have a maximum size, but nested tables do not.
·         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.
·         Oracle stores varray data in-line (in the same table) unless it exceeds 4K, in which case the data is stored out-of-line (but 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.
·         When stored in the database, varrays retain their ordering and subscripts, but nested tables do not.

Varray Syntax
                               CREATE TYPE Project AS OBJECT(  --create object
                                                project_no NUMBER(2), 
                                                title      VARCHAR2(35),
                                                cost       NUMBER(7,2))
                               /
                              CREATE TYPE ProjectList AS VARRAY(50) OF Project  -- define VARRAY 
                               type
                               /
                               CREATE TABLE department (  -- create database table
                                                 dept_id  NUMBER(2),
                                                 name     VARCHAR2(15),
                                                 budget   NUMBER(11,2),
                                                 projects ProjectList)  -- declare varray as column
 
               Nested Table Syntax
               ----------------------------
 
                                CREATE TYPE Course AS OBJECT (
                                                course_no NUMBER(4), 
                                                title     VARCHAR2(35),
                                                credits   NUMBER(1));
 
                               CREATE TYPE CourseList AS TABLE OF Course;
 
                               CREATE TABLE department (
                               name     VARCHAR2(20),
                               director VARCHAR2(20),
                               office   VARCHAR2(20),
                               courses  CourseList) 
                               NESTED TABLE courses STORE AS courses_tab;

Q) What are pseudo columns?
CURRVAL, NEXTVAL, LEVEL, or ROWNUM
Before you can reference CURRVAL in a session, you must use NEXTVAL to generate a number. A reference to NEXTVALstores the current sequence number in CURRVAL. NEXTVAL increments the sequence and returns the next value. To obtain the current or next value in a sequence, you must use dot notation, as follows:
sequence_name.CURRVAL
sequence_name.NEXTVAL
 
After creating a sequence, you can use it to generate unique sequence numbers for transaction processing. However, you can use CURRVALand NEXTVAL only in a SELECTlist, the VALUES clause, and the SETclause. In the following example, you use a sequence to insert the same employee number into two tables:
INSERT INTO emp VALUES (empno_seq.NEXTVAL, my_ename, ...);
INSERT INTO sals VALUES (empno_seq.CURRVAL, my_sal, ...);

 

LEVEL: LEVEL used with the SELECT CONNECTBY statement to organize rows from a database table into a tree

structure. LEVELreturns the level number of a node in a tree structure. The root is level 1, children of the root are
level 2, grandchildren are level 3, and so on. In the START WITHclause, you specify a condition that identifies the
root of the tree. You specify the direction in which the query walks the tree (down from the root or up from the
branches) with the PRIORoperator.

 

ROWID: ROWID returns the rowid (binary address) of a row in a database table. You can use variables of type UROWID

           to store rowids in a readable format. In the following example, you declare a variable named row_idfor that purpose:

                               DECLARE
                                  row_id UROWID;
 
When you select or fetch a physical rowid into a UROWID variable, you can use the function ROWIDTOCHAR, which
converts the binary value to an 18-byte character string. Then, you can compare the UROWIDvariable to the ROWID
pseudocolumn in the WHEREclause of an UPDATE or DELETEstatement to identify the latest row fetched from a cursor.

 

ROWNUM: ROWNUM returns a number indicating the order in which a row was selected from a table. The first row

            selected has a ROWNUMof 1, the second row has a ROWNUMof 2, and so on. If a SELECT statement includes an

            ORDERBY clause, ROWNUMs are assigned to the retrieved rows before the sort is done.

You can use ROWNUMin an UPDATE statement to assign unique values to each row in a table. Also, you can use
ROWNUM in the WHEREclause of a SELECT statement to limit the number of rows retrieved, as follows:
                               DECLARE
                                  CURSOR c1 IS SELECT empno, sal FROM emp
                                     WHERE sal > 2000 AND ROWNUM < 10;  -- returns 10 rows
 
The value of ROWNUMincreases only when a row is retrieved, so the only meaningful uses of ROWNUMin a WHERE
clause are
                               ... WHERE ROWNUM < constant;
                               ... WHERE ROWNUM <= constant;

Q) What are the types of exception and how u generate a user defined exception?

·         Predefined Exceptions
·         User-Defined Exceptions

Predefined Exception:
An internal exception is raised implicitly whenever your PL/SQL program violates an Oracle rule or exceeds a system-
dependent limit. Every Oracle error has a number, but exceptions must be handled by name. So, PL/SQL predefines some
common Oracle errors as exceptions. For example, PL/SQL raises the predefined exception NO_DATA_FOUNDif a
SELECT INTOstatement returns no rows.
To handle other Oracle errors, you can use the OTHERS handler. The functions SQLCODE and SQLERRMare especially
useful in the OTHERShandler because they return the Oracle error code and message text. Alternatively, you can use the
pragma EXCEPTION_INITto associate exception names with Oracle error codes.

            User-Defined Exceptions

PL/SQL lets you define exceptions of your own. Unlike predefined exceptions, user-defined exceptions must be declared and
must be raised explicitly by RAISEstatements.

Declaring Exceptions

Exceptions can be declared only in the declarative part of a PL/SQL block, subprogram, or package. You declare an
exception by introducing its name, followed by the keyword EXCEPTION. In the following example, you declare an
exception named past_due:
 
                               DECLARE
                                  past_due EXCEPTION;

 

Using EXCEPTION_INIT

To handle unnamed internal exceptions, you must use the OTHERS handler or the pragma EXCEPTION_INIT.
A pragma is a compiler directive, which can be thought of as a parenthetical remark to the compiler. Pragmas (also called
               pseudoinstructions) are processed at compile time, not at run time. 
 
            In PL/SQL, the pragma EXCEPTION_INITtells the compiler to associate an exception name with an Oracle error number.
            That allows you to refer to any internal exception by name and to write a specific handler for it.
Code the pragma EXCEPTION_INITin the declarative part of a PL/SQL block, subprogram, or package using the syntax
                               PRAGMA EXCEPTION_INIT(exception_name, Oracle_error_number);
where exception_nameis the name of a previously declared exception. The pragma must appear somewhere after the
exception declaration in the same declarative section, as shown in the following example:
               
               DECLARE
                  deadlock_detected EXCEPTION;
                  PRAGMA EXCEPTION_INIT(deadlock_detected, -60);
               BEGIN
                                  ...
               EXCEPTION
                  WHEN deadlock_detected THEN
                     -- handle the error
               END;

 

 

Using raise_application_error

Package DBMS_STANDARD, which is supplied with Oracle, provides language facilities that help your application interact
with Oracle. For example, the procedure raise_application_errorlets you issue user-defined error messages from stored
subprograms. To call raise_application_error, use the syntax
                               raise_application_error(error_number, message[, {TRUE | FALSE}]);
where error_numberis a negative integer in the range -20000 .. -20999 and messageis a character string up to 2048 bytes
 long. If the optional third parameter is TRUE, the error is placed on the stack of previous errors. If the parameter is FALSE
(the default), the error replaces all previous errors. Package DBMS_STANDARD is an extension of package STANDARD,
so you need not qualify references to its contents.
An application can call raise_application_erroronly from an executing stored subprogram (or method). When called,
raise_application_errorends the subprogram and returns a user-defined error number and message to the application. The
error number and message can be trapped like any Oracle error.
In the following example, you call raise_application_errorif an employee's salary is missing:
               CREATE PROCEDURE raise_salary (emp_id NUMBER, amount NUMBER) AS
                  curr_sal NUMBER;
               BEGIN
                  SELECT sal INTO curr_sal FROM emp WHERE empno = emp_id;
                  IF curr_sal IS NULL THEN
                     /* Issue user-defined error message. */
                                     raise_application_error(-20101, 'Salary is missing');
                  ELSE
                     UPDATE emp SET sal = curr_sal + amount WHERE empno = emp_id;
                  END IF;
               END raise_salary;
 
The calling application gets a PL/SQL exception, which it can process using the error-reporting functions SQLCODEand
SQLERRM in an OTHERShandler. Also, it can use the pragma EXCEPTION_INITto map specific error numbers returned
by raise_application_errorto exceptions of its own, as the following Pro*C example shows:
               EXEC SQL EXECUTE
                  /* Execute embedded PL/SQL block using host 
                     variables my_emp_id and my_amount, which were
                     assigned values in the host environment. */
                  DECLARE
                                     ...
                                     null_salary EXCEPTION;
                     /* Map error number returned by raise_application_error  to user-defined exception. */
                     PRAGMA EXCEPTION_INIT(null_salary, -20101);
                  BEGIN
                     ...
                     raise_salary(:my_emp_id, :my_amount);
                  EXCEPTION
                     WHEN null_salary THEN
                        INSERT INTO emp_audit VALUES (:my_emp_id, ...);
                                     ...
                  END;
               END-EXEC;
 
This technique allows the calling application to handle error conditions in specific exception handlers.

 

Using the RAISE Statement

PL/SQL blocks and subprograms should raise an exception only when an error makes it undesirable or impossible to finish
processing. You can place RAISEstatements for a given exception anywhere within the scope of that exception. In the
following example, you alert your PL/SQL block to a user-defined exception named out_of_stock:
               DECLARE
                  out_of_stock   EXCEPTION;
                  number_on_hand NUMBER(4);
               BEGIN
                                  ...
                  IF number_on_hand < 1 THEN
                     RAISE out_of_stock;
                  END IF;
               EXCEPTION
                  WHEN out_of_stock THEN
                     -- handle the error
               END;
 
Q) Different built in packages
               
DBMS_ALERT: Provides support for the asynchronous notification of database events
DBMS_DDL: Provides access to some SQL DDL statements from stored procedures, and provides special
                        administration operations not available as DDLs.
DBMS_DESCRIBE: Describes the arguments of a stored procedure with full name translation and security checking.
DBMS_OUTPUT:  Accumulates information in a buffer so that it can be retrieved out later.
DBMS_ROWID:           Provides procedures to create ROWIDsand to interpret their contents.
DBMS_SESSION: Provides access to SQL ALTER SESSION statements, and other session information, from stored
                            procedures.
DBMS_SQL:  Lets you use dynamic SQL to access the database
DBMS_UTILITY: Provides various utility routines.
DBMS_TRANSACTION: Provides access to SQL transaction statements from stored procedures and monitors transaction activities. 
UTL_FILE: Enables your PL/SQL programs to read and write operating system (OS) text files and provides a restricted version of standard OS stream file I/O. 

Q) Difference between procedure and function?

Function: Functions are similar to operators in that they manipulate data items and return a result. Functions differ from operators in the format of their arguments.
A function is a subprogram that can take parameters and be invoked. Generally, you use a function to compute a value. A function has two parts: the specification and the body. The specification (spec for short) begins with the keyword FUNCTIONand ends with the RETURN clause, which specifies the datatype of the return value. Parameter declarations are optional. Functions that take no parameters are written without parentheses. The function body begins with the keyword IS(or AS) and ends with the keyword END followed by an optional function name.
The function body has three parts: an optional declarative part, an executable part, and an optional exception-handling part. The declarative part contains declarations of types, cursors, constants, variables, exceptions, and subprograms. These items are local and cease to exist when you exit the function. The executable part contains statements that assign values, control execution, and manipulate Oracle data. The exception-handling part contains handlers that deal with exceptions raised during execution.

Procedure: A procedure is a subprogram that can take parameters and be invoked. Generally, you use a procedure to perform an action. A procedure has two parts: the specification and the body. The specification (spec for short) begins with the keyword PROCEDUREand ends with the procedure name or a parameter list. Parameter declarations are optional. Procedures that take no parameters are written without parentheses. The procedure body begins with the keyword IS(or AS) and ends with the keyword END followed by an optional procedure name.
The procedure body has three parts: an optional declarative part, an executable part, and an optional exception-handling part. The declarative part contains declarations of types, cursors, constants, variables, exceptions, and subprograms. These items are local and cease to exist when you exit the procedure. The executable part contains statements that assign values, control execution, and manipulate Oracle data. The exception-handling part contains handlers that deal with exceptions raised during execution.

Q) What does the following function do? trim, cast, floor ceil, rollup, cube.

TRIM: TRIM enables you to trim leading or trailing characters (or both) from a character string.
               SELECT TRIM (0 FROM 0009872348900) "TRIM Example"  FROM DUAL;
 
               TRIM example
               ------------
                   98723489





FLOOR: FLOOR returns largest integer equal to or less than n.
               SELECT FLOOR(15.7) "Floor" FROM DUAL;
               
                   Floor
               ----------
               15

CEIL: CEIL returns smallest integer greater than or equal to n.
               SELECT CEIL(15.7) "Ceiling" FROM DUAL;
 
               Ceiling
               ---------
                              16

ROLLUP: ROLLUP is an extension to the group_by_clausethat groups the selected rows based on the values of the first n,
                 n-1, n-2, ... 0 expressions for each row, and returns a single row of summary for each group. You can use the
                 ROLLUPoperation to produce subtotal values.
      Example: given three expressions in the ROLLUPclause of the group_by_clause, the operation results in n+1 = 3+1 = 4 groupings.
      Rows based on the values of the first 'n' expressions are called regular rows, and the others are called superaggregate rows
               SELECT DECODE(GROUPING(dname), 1, 'All Departments',  dname) AS dname, 
                                DECODE(GROUPING(job), 1, 'All Jobs', job) AS job, 
                                COUNT(*) "Total Empl", AVG(sal) * 12 "Average Sal"
                FROM emp, dept 
                WHERE dept.deptno = emp.deptno 
                GROUP BY ROLLUP (dname, job); 
 
 
      CUBE: CUBE is an extension to the group_by_clause that groups the selected rows based on the values of all possible
                  combinations of expressions for each row, and returns a single row of summary information for each group. You can
                  use the CUBEoperation to produce cross-tabulation values.
Example: given three expressions in the CUBE clause of the group_by_clause, the operation results in 2n = 23 = 8 groupings.
Rows based on the values of 'n' expressions are called regular rows, and the rest are called superaggregate rows

                               SELECT                DECODE(GROUPING(dname), 1, 'All Departments',  dname) AS dname,
                                               DECODE(GROUPING(job), 1, 'All Jobs', job) AS job,
                                                COUNT(*) "Total Empl", AVG(sal) * 12 "Average Sal"
                               FROM emp, dept 
                               WHERE dept.deptno = emp.deptno
                               GROUP BY CUBE (dname, job);

CAST:  CASTexpression converts one built-in datatype or collection-typed value into another built-in datatype or
            collection-typed value.
                  You can cast an unnamed operand (such as a date or the result set of a subquery) or a named collection (such as a
                  varray or a nested table) into a type-compatible datatype or named collection. The type_namemust be the name of a
                  built-in datatype or collection type and the operandmust be a built-in datatype or must evaluate to a collection
                  value.

Q) What are the different types of check constraints?

CHECK integrity constraint on a column or set of columns requires that a specified condition be true or unknown for every
Row of the table. If a DML statement results in the condition of the CHECK constraint evaluating to false, then the statement
is rolled back.
CHECK constraints enable you to enforce very specific or sophisticated integrity rules by specifying a check condition. The condition of a CHECK constraint has some limitations:
·         It must be a Boolean expression evaluated using the values in the row being inserted or updated, and
    • It cannot contain subqueries, sequences, the SQL functions SYSDATE, UID, USER, or USERENV, or the  pseudocolumns CURRVAL, NEXTVAL, LEVEL, or ROWNUM.
    • Queries to refer to values in other rows.
    • Date constants that are not fully specified.

Q) How do u add a constraint to an existing column
 
               ALTER TABLE dept ADD CONSTRAINT mgr_cons FOREIGN KEY (mgr_ref)REFERENCES emp;

Q) How 2 add a datafile  to an existing tablespace.
               ALTER TABLESPACE accounting NOLOGGING
               ADD DATAFILE 'disk3:pay3.dbf' (Datafile path)
                   SIZE 50K
                   AUTOEXTEND ON
                   NEXT 10K
                   MAXSIZE 100K;

Renamethe datafile using the ALTER TABLESPACEstatement with the RENAME DATAFILEclause:
         ALTER TABLESPACE accounting
         RENAME DATAFILE 'diska:pay1.dbf'
    TO     'diskb:receive1.dbf'


Q) What is PCTUSED & PCTFREE parameters?
     
PCTUSED and PCTFREE are used to
·         Increase the performance of writing and retrieving a data or index segment
·         Decrease the amount of unused space in data blocks
·         Decrease the amount of row chaining between data blocks

Setting PCTFREE, you should understand the nature of the table or index data. Updates can cause rows to grow.
When using NUMBER, VARCHAR2, LONG, or LONG RAW, new values might not be the same size as values they
replace. If there are many updates in which data values get longer, then increase PCTFREE; if updates to rows do not affect
the total row width, then PCTFREE can be low.
PCTFREE also affects the performance of a given user's queries on tables with uncommitted transactions belonging to other users. Assuring read consistency might cause frequent reorganization of data in blocks that have little free space.
A lower PCTFREE:
·         Reserves less room for updates to existing table rows
·         Allows inserts to fill the block more completely
·         Might save space, because the total data for a table or index is stored in fewer blocks (more rows or entries per block)
·         Increases processing costs because blocks frequently need to be reorganized as their free space area becomes filled with new or updated data
·         Potentially increases processing costs and space required if updates to rows or index entries cause rows to grow and span blocks (because UPDATE, DELETE, and SELECTstatements might need to read more blocks for a given row and because chained row pieces contain references to other pieces)
A higher PCTFREE:
·         Reserves more room for future updates to existing table rows
·         Might require more blocks for the same amount of inserted data (inserting fewer rows per block)
·         Lessens processing costs, because blocks infrequently need reorganization of their free space area
·         Might improve update performance, because Oracle must chain row pieces less frequently, if ever

PCTUSED

Once the percentage of free space in a data block reaches PCTFREE, no new rows are inserted in that block until the
percentage of space used falls below PCTUSED. Oracle tries to keep a data block at least PCTUSEDfull. The percent is of
block space available for data after overhead is subtracted from total space.
The default for PCTUSEDis 40 percent; any integer between 0 and 99, inclusive, is acceptable as long as the sum of
PCTUSED and PCTFREEdoes not exceed 100.
A lower PCTUSED:
·         Usually keeps blocks less full than a higher PCTUSED
·         Reduces processing costs incurred during UPDATE and DELETEstatements for moving a block to the free list when the block has fallen below that percentage of usage
·         Increases the unused space in a database
A higher PCTUSED:
·         Usually keeps blocks fuller than a lower PCTUSED
·         Improves space efficiency
·         Increases processing cost during INSERTs and UPDATEs

Choosing Associated PCTUSED and PCTFREE Values

If decided not to use the default values for PCTFREE and PCTUSED, then use the following guidelines.
·         The sum of PCTFREEand PCTUSED must be equal to or less than 100.
·         If the sum is less than 100, then the ideal compromise of space utilization and I/O performance is a sum of PCTFREE and PCTUSEDthat differs from 100 by the percentage of space in the available block that an average row occupies. For example, assume that the data block size is 2048 bytes, minus 100 bytes of overhead, leaving 1948 bytes available for data. If an average row requires 195 bytes, or 10% of 1948, then an appropriate combination of PCTUSED and PCTFREEthat sums to 90% would make the best use of database space.
·         If the sum equals 100, then Oracle attempts to keep no more than PCTFREEfree space, and the processing costs are highest.
·         Fixed block overhead is not included in the computation of PCTUSEDor PCTFREE.
·         The smaller the difference between 100 and the sum of PCTFREEand PCTUSED (as in PCTUSEDof 75, PCTFREE of 20), the more efficient space usage is at some performance cost.

Q) What are co-related queries ?

A correlated subquery is evaluated once for each row processed by the parent statement.

Eg:      The following statement assigns an alias to emp, the table containing the salary information, and then uses the alias
            in a correlated subquery:
                   SELECT deptno, ename, sal 
                   FROM emp x 
                   WHERE sal > (SELECT AVG(sal) 
                                         FROM emp 
                                         WHERE x.deptno = deptno) 
                                         ORDER BY deptno; 
 
For each row of the emptable, the parent query uses the correlated subquery to compute the average salary for members of the same department. The correlated subquery performs the following steps for each row of the emptable:
1.      The deptnoof the row is determined.
2.      The deptnois then used to evaluate the parent query.
3.      If that row's salary is greater than the average salary for that row's department, then the row is returned.
The subquery is evaluated once for each row of the emptable.


Q) What is clusters? Their usage ?

Cluster:     A clusterprovides an optional method of storing table data. A cluster is made up of a group of tables that share the
                  same data blocks, which are grouped together because they share common columns and are often used together. For
                  example, the EMP and DEPT table share the DEPTNO column. When you cluster the EMP and DEPT tables,
                  Oracle physically stores all rows for each department from both the EMP and DEPT tables in the same data blocks.
                  You should not use clusters for tables that are frequently accessed individually.

Because clusters store related rows of different tables together in the same data blocks, properly used clusters offer two primary
benefits:
·         Disk I/O is reduced and access time improves for joins of clustered tables.
·         The cluster keyis the column, or group of columns, that the clustered tables have in common. You specify the columns of the cluster key when creating the cluster. You subsequently specify the same columns when creating every table added to the cluster. Each cluster key value is stored only once each in the cluster and the cluster index, no matter how many rows of different tables contain the value.
Therefore, less storage might be required to store related table and index data in a cluster than is necessary in non-clustered table
format. For example, notice how each cluster key (each DEPTNO) is stored just once for many rows that contain the same value
in both the EMP and DEPT tables.


Q) Difference between delete and truncate statement?
Truncate :            TRUNCATEstatement removes all rows from a table or cluster and reset the STORAGEparameters to the values
 when the table or cluster was created.
Restrictions:
·         You cannot individually truncate a table that is part of a cluster. You must either truncate the cluster, delete all rows from the table, or drop and re-create the table.
·         You cannot truncate the parent table of an enabled referential integrity constraint. You must disable the constraint before truncating the table. (An exception is that you may truncate the table if the integrity constraint is self-referential.)
·         You cannot truncate a table if any domain indexes defined on any of its columns are marked LOADINGor FAILED.

* You cannot roll back a TRUNCATEstatement

               eg., TRUNCATE TABLE emp; 
 
 
 
Delete: The DELETE statement removes entire rows of data from a specified table or view.
The DELETEANY TABLEsystem privilege also allows you to delete rows from any table or table partition, or any view's
base table.


Q) Query to write top 3 salaried employee?
  SELECT EMPNO,SAL,DEPTNO
  FROM EMP E
 WHERE 3 > (SELECT COUNT(*)
FROM EMP B
WHERE B.SAL > E.SAL)
  ORDER BY E.SAL DESC

Q) Query to remove duplicate records in a table?
                        delete from emp a
where rowid not in (Select min(rowid) from emp group by empno)
     

Q) what is DB link? Syntax?
A database link is a schema object that causes Oracle to connect to a remote database to access databases other than your local database
                               
               1)            CREATE DATABASE LINK SUDHIR
                              CONNECT TO CURRENT_USER
                              USING 'RAO';
               2)            CREATE DATABASE LINK SUDHIR 
                              CONNECT TO scott IDENTIFIED BY tiger 
                              USING 'rao';
                             
   CREATE SHARED PUBLIC DATABASE LINK SUDHIR
                               CONNECT TO scott IDENTIFIED BY tiger 
       AUTHENTICATED BY RANGUSS IDENTIFIED BY SUDHIR
       USING 'rao'; 


Q) What are synonyms ? their uses?
      A synonym is an alias for a table, view, snapshot, sequence, procedure, function, or package.
                               CREATE PUBLIC SYNONYM SUDHIR_EMP FOR RIJESH.Emp_tab;
                               CREATE SYNONYM market FOR scott.market_research;

            USE : Synonyms provide both data independence and location transparency. Synonyms permit applications to function
                        without modification regardless of which user owns the table or view and regardless of which database holds the
                        table or view.


Q) What does savepoint do?
SAVEPOINT names and marks the current point in the processing of a transaction. Used with the ROLLBACKTO statement, savepoints let you undo parts of a transaction instead of the whole transaction. In the example below, you mark a savepoint before doing an insert. If the INSERTstatement tries to store a duplicate value in the empnocolumn, the predefined exception DUP_VAL_ON_INDEXis raised. In that case, you roll back to the savepoint, undoing just the insert.
DECLARE
   emp_id  emp.empno%TYPE;
BEGIN
   UPDATE emp SET ... WHERE empno = emp_id;
   DELETE FROM emp WHERE ...
   ...
   SAVEPOINT do_insert;
   INSERT INTO emp VALUES (emp_id, ...);
EXCEPTION
   WHEN DUP_VAL_ON_INDEX THEN
      ROLLBACK TO do_insert;
END;
 
When you roll back to a savepoint, any savepoints marked after that savepoint are erased. However, the savepoint to which you roll back is not erased
If you mark a savepoint within a recursive subprogram, new instances of the SAVEPOINTstatement are executed at each level in the recursive descent. However, you can only roll back to the most recently marked savepoint.
Savepoint names are undeclared identifiers and can be reused within a transaction. This moves the savepoint from its old position to the current point in the transaction. Thus, a rollback to the savepoint affects only the current part of your transaction. An example follows:
BEGIN
   SAVEPOINT my_point;
   UPDATE emp SET ... WHERE empno = emp_id;
   ...
   SAVEPOINT my_point;  -- move my_point to current point
   INSERT INTO emp VALUES (emp_id, ...);
EXCEPTION
   WHEN OTHERS THEN
      ROLLBACK TO my_point;
END;
 
The number of active savepoints per session is unlimited. An active savepoint is one marked since the last commit or rollback.


Q) What is  partioning?? what is its use?

Partitioning addresses the key problem of supporting very large tables and indexes by allowing you to decompose them into smaller and more manageable pieces called partitions. Once partitions are defined, SQL statements can access and manipulate the partitions rather than entire tables or indexes. Partitions are especially useful in data warehouse applications, which commonly store and analyze large amounts of historical data.

Partitioning Methods

Two primary methods of partitioning are available:
1) range partitioning: which partitions the data in a table or index according to a range of values,
2) hash partitioning: which partitions the data according to a hash function.
3) composite partitioning:  partitions the data by range and further subdivides the data into subpartitions using a hash
                                          function.

CREATE TABLE sales ( acct_no         NUMBER(5), 
                     acct_name       CHAR(30), 
                     amount_of_sale  NUMBER(6), 
                     week_no         INTEGER ) 
       PARTITION BY RANGE ( week_no ) ... 
           (PARTITION sales1 VALUES LESS THAN ( 4 ) TABLESPACE ts0, 
            PARTITION sales2 VALUES LESS THAN ( 8 ) TABLESPACE ts1, 
            ... 
            PARTITION sales13 VALUES LESS THAN ( 52 ) TABLESPACE ts12 );

                  To select the records from a single partition then the syntax is:
                  SELECT * FROM SALES PARTITION (SALES1)
Advantages of Partitioning

1)   Very Large Databases (VLDBs)

A very large database (VLDB) contains hundreds of gigabytes or even a few terabytes of data. Partitioning provides
support for VLDBs that contain mostly structured data, rather than unstructured data. These VLDBs typically owe their
size to the presence of a few very large data objects (tables and indexes) rather than to the presence of a very large
number of data objects.
There are two major categories of VLDBs:
·         On-Line Transaction Processing (OLTP) databases are designed for large numbers of concurrent transactions, where each transaction is a relatively simple operation processing a small amount of data.
·         Decision Support Systems (DSS) are designed for very complex queries that need to access and process large amounts of data.

2)   Reducing Downtime for Scheduled Maintenance

Partitions enable data management operations like data loads, index creation, and data purges at the partition level, rather
than on the entire table, resulting in significantly reduced times for these operations.
Partitioning can significantly reduce the impact of scheduled downtime for maintenance operations:
·         By introducing partition maintenance operations that operate on an individual partition rather than on an entire table or index
·         By providing partition independence so that maintenance operations can be performed concurrently on different partitions

3)   Reducing Downtime Due to Data Failures

Some maintenance operations are unplanned events, required to recover from hardware or software failures that cause
data loss or corruption. Recovery from hardware failures and many system software failures is accomplished by running
the RECOVER statement on a database, tablespace, or datafile. Any tables or indexes that have records in a tablespace or
datafile being recovered remain unavailable during recovery. Increased availability is particularly important for mission-critical OLTP databases.
Because partitions are independent of each other, the unavailability of a piece or a subset of pieces does not affect access
to the rest of the data. Storing partitions in separate tablespaces provides the following benefits:
·         Downtime due to execution of the RECOVER statement is reduced because the unit of recovery (a tablespace) is smaller.
·         Disk resources needed for recovery of an offline tablespace (deferred rollback segments) are reduced because the unit of recovery is smaller.
·         The amount of unavailable data is reduced, because only the partitions stored in the recovered tablespace have to be taken offline. User applications and maintenance operations can still access the other partitions. This is another example of partition independence.

4)      DSS Performance

DSS queries on very large tables present special performance problems. A query that requires a table scan can take a
long time, because it must inspect every row in the table. There is no way to identify and skip subsets of irrelevant rows.

5)    I/O Performance

Partitioning can control how data is spread across physical devices. To balance I/O use, you can specify where to store
the partitions of a table or index. With this level of location control, you can accommodate the special needs of
applications that require fast response time by reducing disk contention and using faster devices. On the other hand, data
that is accessed infrequently, such as old historical data, can be moved to slow disks or stored in subsystems that support
a storage hierarchy.

6)    Disk Striping: Performance versus Availability

Disk striping and partitioning are both tools that can improve performance through the reduction of contention for disk
arms. Which tool to use, or in which proportions to use them together, is an important issue to consider when physically
designing databases. These issues should be considered not only with respect to performance, but also with respect to
availability and partition independence.

7)    Partition Transparency

The vast majority of application programs requirepartition transparency. That is, the programs should be insensitive to
whether the data they access is partitioned and how it is partitioned. A few application programs, however, can take
advantage of partitions by explicitly requesting access to an individual partition, rather than the entire table. For example,
a user might want to break a long batch job on a very large table into a sequence of short nightly batch jobs on individual
 partitions.

Q) Can u display the output of a table twice?
      Yes. Using UNION ALL.

Q) Can u write commit in a DB trigger?
      No. U cannot write any DDL statements in DB trigger.

Q) How do u set a specific rollback segment for a particular transaction?
A transaction can be explicitly assigned to a specific rollback segment using the SET TRANSACTION statement with the
USE ROLLBACK SEGMENT clause. Transactions are explicitly assigned to rollback segments for the following reasons:
·         The anticipated amount of rollback information generated by a transaction can fit in the current extents of the assigned rollback segment.
·         Additional extents do not have to be dynamically allocated (and subsequently truncated) for rollback segments, which reduces overall system performance.
To assign a transaction to a rollback segment explicitly, the rollback segment must be online for the current instance, and the
SET TRANSACTION USE ROLLBACK SEGMENT statement must be the first statement of the transaction. If a specified
rollback segment is not online or a SET TRANSACTION USE ROLLBACK SEGMENT clause is not the first statement in a
transaction, an error is returned.
For example, if you are about to begin a transaction that contains a significant amount of work (more than most transactions),
you can assign the transaction to a large rollback segment, as follows:
               
                               SET TRANSACTION USE ROLLBACK SEGMENT large_rs1;

After the transaction is committed, Oracle will automatically assign the next transaction to any available rollback segment
 unless the new transaction is explicitly assigned to a specific rollback segment by the user.


Q) What are distributed databases?

A distributed database system allows applications to access data from local and remote databases. In a homogenous
distributed system, each database is an Oracle database. In a heterogeneous distributed system, at least one of the databases is
a non-Oracle database. Distributed database uses a client-server architecture to process information requests

 

Homogenous Distributed Database Systems

A homogenous distributed database system is a network of two or more Oracle databases that reside on one or more
machines. An application can simultaneously access or modify the data in several databases in a single distributed
environment.

Heterogeneous Distributed Database Systems

In a heterogeneous distributed database system, at least one of the databases is a non-Oracle system. To the application, the
heterogeneous distributed database system appears as a single, local, Oracle database; the local Oracle server hides the
distribution and heterogeneity of the data.

Client-Server Database Architecture

A database server is the Oracle software managing a database, and a client is an application that requests information from a
server. Each computer in a network is a node that can host one or more databases. Each node in a distributed database system
can act as a client, a server, or both, depending on the situation.

Q) By_Value/By Reference Method

by-value method :       the value of an actual parameter is passed to the subprogram.
by-reference method:              only a pointer to the value is passed, in which case the actual and formal parameters reference the
                                    same item.


Q) What is PL/SQL wrapper?
You can deliver your stored procedures in object code format using the PL/SQL Wrapper. Wrapping your PL/SQL code
hides your application internals. To run the PL/SQL Wrapper, enter the WRAP statement at your system prompt using the
 following syntax:
                         wrap INAME=input_file [ONAME=ouput_file]





Q) What is an anonymous block?
An anonymous block is a PL/SQL program unit that has no name and it does not require the explicit presence of the BEGINand END keywords to enclose the executable statements. An anonymous block consists of an optional declarativepart, an executable part, and one or more optional exception handlers.
The declarative part declares PL/SQL variables, exceptions, and cursors. The executable part contains PL/SQL code and SQL statements, and can contain nested blocks. Exception handlers contain code that is called when the exception is raised, either as a predefined PL/SQL exception (such as NO_DATA_FOUNDor ZERO_DIVIDE) or as an exception that you define.
The following short example of a PL/SQL anonymous block prints the names of all employees in department 20 in the Emp_tabtable, using the DBMS_OUTPUTpackage:
 
   DECLARE
   Emp_name    VARCHAR2(10);
   Cursor      c1 IS SELECT Ename FROM Emp_tab
                  WHERE Deptno = 20;
BEGIN
   OPEN c1;
   LOOP
      FETCH c1 INTO Emp_name;
      EXIT WHEN c1%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(Emp_name);
   END LOOP;
END;


What is Oracle's Parallel Server option?
Can u have package specification w/o pakage body? when do u require this?
Steps involved in execution of an sql statement.


SQL Loader
----------

Can u give a filtering condition in sql loader control file?
A field condition is a statement about a field in a logical record that evaluates as true or false. It is used in the NULLIF and
DEFAULTIF clauses, as well as in the WHEN clause.
A field condition is similar to the condition in the CONTINUEIF clause, with two important differences. First, positions in
the field condition refer to the logical record, not to the physical record. Second, you may specify either a position in the
logical record or the name of a column that is being loaded.

            Comparing Fields to Literals

When a data field is compared to a literal string that is shorter than the data field, the string is padded. Character
strings are padded with blanks, for example:
                               NULLIF (1:4)=" " 
This example compares the data in position 1:4 with 4 blanks. If position 1:4 contains 4 blanks, then the clause
evaluates as true. Hexadecimal strings are padded with hexadecimal zeros. The clause
                               NULLIF (1:4)=X'FF' 
compares position 1:4 to hexadecimal 'FF000000'.

The BLANKS keyword makes it possible to determine easily if a field of unknown length is blank.
For example, use the following clause to load a blank field as null:
       full_fieldname ... NULLIF column_name=BLANKS 
The BLANKS keyword only recognizes blanks, not tabs. It can be used in place of a literal string in any field comparison. The condition is TRUE whenever the column is entirely blank.
The BLANKS keyword also works for fixed-length fields. Using it is the same as specifying an appropriately sized literal string of blanks. For example, the following specifications are equivalent:
fixed_field CHAR(2) NULLIF fixed_field=BLANKS 
fixed_field CHAR(2) NULLIF fixed_field="  " 
 
Note:         There can be more than one blank in a multibyte character set. It is a good idea to use the BLANKS keyword with
                  these character sets instead of specifying a string of blank characters.

Q) Can u insert data into 2 tables using the control file?
 
  -- Loads EMP records from first 23 characters
   -- Creates and loads PROJ records for each PROJNO listed
   -- for each employee
 
   LOAD DATA
   INFILE 'ulcase5.dat'
   BADFILE 'ulcase5.bad'
   DISCARDFILE 'ulcase5.dsc'
1)   REPLACE
2)    INTO TABLE emp
      (empno   POSITION(1:4)     INTEGER EXTERNAL,
      ename    POSITION(6:15)    CHAR,
                     deptno   POSITION(17:18)   CHAR,
      mgr      POSITION(20:23)   INTEGER EXTERNAL)
2)   INTO TABLE proj
   -- PROJ has two columns, both not null: EMPNO and PROJNO
3)   WHEN projno != '   '
                                  (empno   POSITION(1:4)     INTEGER EXTERNAL,
3)   projno   POSITION(25:27)   INTEGER EXTERNAL)   -- 1st proj
2)   INTO TABLE proj
4)   WHEN projno != '   '
                   (empno   POSITION(1:4)     INTEGER EXTERNAL,
4)   projno   POSITION(29:31    INTEGER EXTERNAL)   -- 2nd proj
 
2)   INTO TABLE proj
5)   WHEN projno != '   '
      (empno   POSITION(1:4)    INTEGER EXTERNAL,
5)  projno  POSITION(33:35)  INTEGER EXTERNAL)   -- 3rd proj 
 
Notes:
1.      REPLACE specifies that if there is data in the tables to be loaded (EMP and PROJ), SQL*loader should delete the data before loading new rows.
2.      Multiple INTO clauses load two tables, EMP and PROJ. The same set of records is processed three times, using different combinations of columns each time to load table PROJ.
3.      WHEN loads only rows with nonblank project numbers. When PROJNO is defined as columns 25...27, rows are inserted into PROJ only if there is a value in those columns.
4.      When PROJNO is defined as columns 29...31, rows are inserted into PROJ only if there is a value in those columns.
5.      When PROJNO is defined as columns 33...35, rows are inserted into PROJ only if there is a value in those columns.

Q) How do u specify the date format in the control file?
The data field contains character data that should be converted to an Oracle date using the specified date mask. The syntax is:
For example:
LOAD DATA 
INTO TABLE DATES (COL_A POSITION (1:15) DATE "DD-Mon-YYYY") 
BEGINDATA 
1-Jan-1991 
1-Apr-1991 28-Feb-1991 
Attention: Whitespace is ignored and dates are parsed from left to right unless delimiters are present.

Q) What are the diff modes in which data can be loaded into a table?

Loading Data into Nonempty Tables

If the tables you are loading into already contain data, you have three options:
  • APPEND
  • REPLACE
  • TRUNCATE
Caution: When the REPLACE or TRUNCATE keyword is specified, the entire table is replaced, not just individual rows. After the rows are successfully deleted, a commit is issued. You cannot recover the data that was in the table before the load, unless it was saved with Export or a comparable utility.

APPEND

If data already exists in the table, SQL*Loader appends the new rows to it. If data does not already exist, the new rows are
simply loaded.

REPLACE

With REPLACE, all rows in the table are deleted and the new data is loaded.

TRUNCATE

Using this method, SQL*Loader uses the SQL TRUNCATE statement to achieve the best possible performance. For the
TRUNCATE statement to operate, the table's referential integrity constraints must first be disabled. If they have not been
disabled, SQL*Loader returns an error.
Once the integrity constraints have been disabled, DELETE CASCADE is no longer defined for the table. If the DELETE
CASCADE functionality is needed, then the contents of the table must be manually deleted before the load begins.

Q) how do u assign null values through the control file?

Setting a Column to Null or Zero

If you want all inserted values for a given column to be null, omit the column's specifications entirely. To set a column's
values conditionallyto null based on a test of some condition in the logical record, use the NULLIF clause. To set a numeric column to zero instead of NULL, use the DEFAULTIF clause

               COLUMN1  POSITION(11:17)  CHAR  NULLIF  (COLUMN1 = "unknown") 

DEFAULTIF Clause

Using DEFAULTIF on numeric data sets the column to zero when the specified field condition is true.
Using DEFAULTIF on character (CHAR or DATE) data sets the column to null
            DEFAULTIF  field_condition
 
A column may have both a NULLIF clause and a DEFAULTIF clause, although this often would be redundant.
Note: The same effects can be achieved with the SQL string and with SQL DECODE expressions.

 

NULLIF Clause

Use the NULLIF clause after the datatype and optional delimiter specification, followed by a condition. The condition has the
same format as that specified for a WHEN clause. The column's value is set to null if the condition is true. Otherwise, the
value remains unchanged.
                               NULLIF field_condition 
The NULLIF clause may refer to the column that contains it, as in the following example:
                               COLUMN1  POSITION(11:17)  CHAR  NULLIF  (COLUMN1 = "unknown") 
 
This specification may be useful if you want certain data values to be replaced by nulls. The value for a column is first
determined from the datafile. It is then set to null just before the insert takes place.
 Note:The same effect can be achieved with the SQL string and the NVL function. 

 

Null Columns at the End of a Record

When the control file specifies more fields for a record than are present in the record, SQL*Loader must determine whether
the remaining (specified) columns should be considered null or whether an error should be generated. The TRAILING
NULLCOLS clause

INTO TABLE dept 
    TRAILING NULLCOLS 
( deptno CHAR TERMINATED BY " ", 
  dname  CHAR TERMINATED BY WHITESPACE, 
  loc    CHAR TERMINATED BY WHITESPACE 
) 

what is a parameter file and what are its uses?
what are the different parameters in sql loader?


Forms
-----

Q) Difference between Forms6i and previous versions?
1. LOV Wizard
      2. Web-based. Forms can be run on the web.
      3. Auto-indenting of code.
      4. You can create a block based on a stored procedure or object
      5. Syntax palette:- Helps in reducing coding time by automatically provided the  built in func and proc and also basic syntax
                                in a pl/sql block. (Somewhat same as the auto-complete feature of VB)
      6. You can perform a global search through all the opened forms.
      7. Seperate PL/SQL engine stored in the forms server.
8. Property for different items has the option of choosing the colors  from color palette instead of color codes(earlier
    version).
      9. Color syntax for oracl reserve words, comments.

Q) Diff between open call and new form. when to use which one?
Call form: Runs an indicated form while keeping the parent form active.  Form Builder runs the called form with the same
                      Runform preferences as the parent form.  When the called form is exited Form Builder processing resumes in the
                      calling form at the point from which you initiated the call to CALL_FORM.
                 
CALL_FORM(theformname, hide, no_replace, no_query_only,  pl_id);
New Form: Exits the current form and enters the indicated form.  The calling form is terminated as the parent form.  If the
                  calling form had been called by a higher form, Form Builder keeps the higher call active and treats it as a call to
                  the new form.  Form Builder releases memory (such as database cursors) that the terminated form was using.
      Form Builder runs the new form with the same Runform options as the parent form.  If the parent form was a
      called form, Form Builder runs the new form with the same options as the parent form.
                             
NEW_FORM(formname);

Open Form: Opens the indicated form.  Use OPEN_FORM to create multiple-form applications, that is, applications that
                    open more than one form at the same time.
                  OPEN_FORM (form_name, activate_mode, session_mode, data_mode, paramlist_id);

Q) What are different triggers (form level) that fire when u run a form and the sequence in which they fire?

1)      Pre-Form
2)      When-new-form-instance
3)      Post-Form

Q) describe the different master-detail property and triggers ?
      1) on-clear_Details (form)
      2) on-populate_Details (block)
      3) on-check_Delete-master (block)

Q ) Can u set property classes at runtime?
                  Yes. By set_item_property for item.

Q) Can u set visual attributes at runtime?
                  Yes. By set_item_property

Q) Can u change the label of an alert button at runtime?
                  Yes. SET_ALERT_PROPERTY(title)

Q) Can u change the record group for an LOV at runtime?
                  Yes. By Populate_ group_with_query


Q) What are the different objects on which a block can be based?
                  Table,  Procedure, Transactional Triggers and Ref Cursors

Q) what does the following built in do?

SYNCHRONIZE:           Synchronizes the terminal screen with the internal state of the form.  That is, SYNCHRONIZE updates the
                              screen display to reflect the information that Form Builder has in its internal representation of the screen.

NAME_IN:                      The NAME_IN function returns the contents of an indicated variable or item.  Use the NAME_IN function
                              to get the value of an item without referring to the item directly.  Note: The NAME_IN function cannot
                              return the contents of  a global or local variable.
                              The following statements are equivalent:
IF :emp.ename = 'smith' -- direct reference
IF NAME_IN('emp.ename') = 'smith' -- indirect reference

The return value is always a character string.  To use NAME_IN for a DATE or NUMBER item, convert
the string to the desired data type with the appropriate conversion function:
date_var := TO_DATE(Name_In('order.date_item'));
num_var := TO_NUMBER(Name_In('order.number_item'));

COPY:                 The COPY procedure assigns an indicated value to an indicated variable or item.  Unlike standard PL/SQL
assignment, however, using the COPY procedure allows you to indirectly reference the item whose value is
being set:
:emp.ename := 'smith'; -- direct reference
Copy('smith','emp.ename');     -- indirect reference
COPY can be used with the NAME_IN function to assign a value to an item whose name is stored in a
reference variable or item:
/* put value 'smith' in item whose name is stored in ref_item */
            Copy('smith',Name_In('control.ref_item'));

  Referencing items indirectly allows you to write more generic, reusable code.  By using variables in place of actual item
  names, you can write a subprogram that can operate on any item whose name has been assigned to the indicated variable.
 Also, using indirect reference is mandatory when you refer to the value of a form bind variable (item, parameter, global
 variable) in PL/SQL that you write in a library or a menu module.  Because libraries, menus, and forms are separate
 application modules, you cannot refer directly to the value of a form item in a menu-item command or library procedure. 

ID_NULL:                       Returns a BOOLEAN value that indicates whether the object ID is available.
Use ID_NULL when you want to check for the existence of an object created dynamically at runtime.  For
example, if a specific record group already exists, you will receive an error message if you try to create that
record group.  To perform this check, follow this general process:

1.   Use the appropriate FIND_ built-in to obtain the object ID.
2.   Use ID_NULL to check whether an object with that ID already exists.
3.   If the object does not exist, proceed to create it.
If you are going to test for an object’s existence at various times (that is, more than once during a run), then
you need to reissue the appropriate FIND_ every time -- once preceding each use of ID_NULL.  

LOGON:              Performs the default Form Builder logon processing with an indicated username and password.  Call this
                              procedure from an On-Logon trigger when you want to augment default logon processing.

                              LOGON (username , password ,  logon_screen_on_error);


Q) What are the different system variables ? name a few
      BLOCK_STATUS,  CURRENT_BLOCK, CURRENT_DATETIME, CURRENT_FORM, CURRENT_ITEM CURSOR_ITEM, CURSOR_RECORD, CURSOR_VALUE, EVENT_WINDOW, FORM_STATUS, LAST_FORM, LAST_QUERY, LAST_RECORD, MASTER_BLOCK, MESSAGE_LEVEL, MODE, RECORD_STATUS, SUPPRESS_WORKING.




Q) What are object library, PL/SQL library ?
The Object Library provides an easy method of reusing objects and enforcing standards across the entire development
organization. We can use the Object Library to:
1. Create, store, maintain, and distribute standard and reusable objects. 
2. Rapidly create applications by dragging and dropping predefined objects to your form.
There are several advantages to using object libraries to develop applications:
1. Object libraries are automatically re-opened when you startup Form Builder, making your reusable objects
    immediately accessible.
2. You can associate multiple object libraries with an application.  For example, you can create an object library
     specfically for corporate standards, and you can create an object library to satisfy project-specific requirements.
3. Object libraries feature SmartClasses-- objects that you define as being the standard.  You use SmartClasses to
    convert objects to standard objects.


Q) What are property classes? can u attach a trigger to it?
A property class is a named object that contains a list of properties and their settings. Once you create a property class
you can base other objects on it.  An object based on a property class can inherit the setting of any property in the class
that makes sense for that object.
       Yes. We can attach a trigger to it.

suppose u have a trigger on a property class and also on the item on
...which it is defined, which one will fire first?

Q) For a block how many times will post-query fire?
Perform an action after fetching a record, such as looking up values in other tables based on a value in the current record.  Fires once for each record fetched into the block.

Q) What are timers? syntax for creating timers?

Timer: A timer is an "internal time clock" that you programmatically create to perform an action each time the timer expires.
DECLARE
   the_timer  CHAR := GET_APPLICATION_PROPERTY(TIMER_NAME);
BEGIN
   SET_TIMER(the_timer, 60000, REPEAT);
END;

Q) Suppose u have 3-4 timers in a form how do u determine will one expired first?
When working with multiple timers, remember that the When-Timer-Expired is a form-level trigger.  It fires any time a timer
expires.  If your application contains several timers, your When-Timer-Expired trigger should contain code that will handle
the different timers accordingly.
Note:  To retrieve the timer name of the most recently executed timer, initiate a call to GET_APPLICATION_PROPERTY
            from within a When-Timer-Expired trigger.  Otherwise, the results of the built-in are undefined.

DECLARE
     expired_timer  CHAR(20);
BEGIN
     expired_timer:=GET_APPLICATION_PROPERTY(TIMER_NAME);
     IF expired_timer='T1'
        THEN /* handle timer T1 */;
     ELSIF expired_timer='T2'
        THEN /* handle timer T2 */;
     ELSE /* handle all other timers */;
     END IF;
END;


Q) What are restricted and unrestricted built ins?
      Restricted : Any built-in subprogram that initiates navigation is restricted.  This includes subprograms that move the input
                       focus from one item to another, and those that involve database transactions.  Restricted built-ins are not allowed
                       in triggers that fire in response to navigation.
                              Eg., GO-ITEM, NEXT_SET
UnRestricted: Form Builder provides built-in subprograms that you can call from triggers and user-named subprograms that
                     you write yourself. Built-ins provide programmatic control over standard application functions, including
                     navigation, interface control, and transaction processing.


Q) Can u write go_item in post text item?
      No.

Q) PL/SQL Wrapper ?

Use this standalone utility to deliver PL/SQL applications without exposing your source code.  The Wrapper converts
PL/SQL source code into portable object code.
Advantages of the PL/SQL Wrapper include:
1.   platform independence
2.   dynamic loading
3.   dynamic binding
4.   dependency checking
5.   no affect on importing or exporting

Q) Diff between post text and key next and when validate item trigger
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. The Post-Text-Item trigger does not fire when the input focus is
in a text item and the operator uses the mouse to click on a button, check box, list item, or radio group item
that has the Mouse Navigate property Off.  When Mouse Navigate is Off for these items, clicking them
with the mouse is a non-navigational event, and the input focus remains in the current item (in this
example, a text item).
            key next          :          

Q) Can u disable the required property for an item at runtime using some  form level property ?
Yes, using Defer Required Enforcement to YES (Default is no)

Q) Can u create record groups at runtime?
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.

DECLARE
   group_id                   RecordGroup;
   query_ok                  NUMBER;
BEGIN
/* create the group prod_group and assign its id to the  variable group_id  */
   group_id := Create_Group_From_Query ('prod_group',  'SELECT product.id, product.name,
                                       inventory.warehouse_id, inventory.amount_in_stock
                                       FROM product, inventory    WHERE product.id = warehouse.product_id');
/* now execute the new group's query, using the variable  group_id to identify the group */
   query_ok := Populate_Group(group_id);
 /* if the query failed, abort this trigger by raising a  predefined exception */
   IF query_ok <> 0 THEN
     RAISE Form_Trigger_Failure;
   END IF;
END;



Q) how do u come out of enter query mode?
Exit_Form;

Q) what does last_recod do?
Navigates to the last record in the block's list of records.  If a query is open in the block, Form Builder fetches the remaining
selected records into the block's list of records, and closes the query.


when does on-insert trigger fire?

Q) what is the  text_io package?
The Text_IO Package contains constructs that provide ways to write and read information to and from files.  There are
several procedures and functions available in Text_IO, falling into the following categories:

file operations:            The FILE_TYPE record, the FOPEN and IS_OPEN functions, and the FCLOSE procedure enable you to
                        define FILE_TYPE variables, open files, check for open files, and close open files, respectively.
output (write) operations:       The PUT, PUTF, PUT_LINE, and NEW_LINE procedures enable you to write information to an
                                    open file or output it to the Interpreter.
input (read) operations:           The GET_LINE procedure enables you to read a line from an open file.


Q) What are Diff types of canvases?
Content, Stacked, Horizontal and Vertical Toolbar, Tab Canvas.


Q) What does forms_ddl do?
Issues dynamic SQL statements at runtime, including server-side PL/SQL and DDL.
Note:  All DDL operations issue an implicit COMMIT and will end the current transaction without allowing Form Builder
            to process any pending changes.

BEGIN
 Forms_DDL('create table temp(n NUMBER)');
  IF NOT Form_Success THEN
    Message ('Table Creation Failed');
  ELSE
    Message ('Table Created');
  END IF;
END;  


Q) What is the key-others trigger used for?
A Key-Others trigger fires when an operator presses the associated key. It is associated with all keys that can have key
triggers associated with them but are not currently defined by function key triggers (at any level).
A Key-Others trigger overrides the default behavior of a Runform function key (unless one of the restrictions apply).  When this occurs, however, Form Builder still displays the function key's default entry in the Keys screen.


Q) How do u call a report from a form? what are the diff parameters?

      Invokes one of the supported Oracle tools products and specifies the name of the module or module to be run.  If the called
      product is unavailable at the time of the call, Form Builder returns a message to the end user.
If you create a parameter list and then reference it in the call to RUN_PRODUCT, the form can pass text and data parameters
to the called product that represent values for command line parameters, bind or lexical references, and named queries. Parameters of type DATA_PARAMETER are pointers to record groups in Form Builder. You can pass DATA_PARAMETERs to Report Builder and Graphics Builder, but not to Form Builder.





PROCEDURE Run_Emp_Report IS
  pl_id ParamList;
BEGIN
  /* Check to see if the 'tmpdata' parameter list exists.  */
                    pl_id := Get_Parameter_List('tmpdata');
  /* If it does, then delete it before we create it again in case it contains parameters that are not useful for our
      purposes here.   */
                    IF NOT Id_Null(pl_id) THEN
                              Destroy_Parameter_List( pl_id );
                   END IF;
 


/* Create the 'tmpdata' parameter list afresh.  */
                           pl_id := Create_Parameter_List('tmpdata');
  /* Add a data parameter to this parameter list that will  establish the relationship between the named query
       'EMP_QUERY' in the report, and the record group named  'EMP_RECS' in the form.  */
                         Add_Parameter(pl_id,'EMP_QUERY',DATA_PARAMETER,'EMP_RECS');
  /* Pass a Parameter into PARAMFORM so that a parameter dialog will not appear for the parameters being
      passing in. */
                                             Add_Parameter(pl_id, 'PARAMFORM', TEXT_PARAMETER, 'NO');
                        /*  Run the report synchronously, passing the parameter list */
                                   Run_Product(REPORTS, 'empreport', SYNCHRONOUS, RUNTIME, FILESYSTEM, pl_id, NULL);
        END;

SYNCHRONOUS:   Specifies that control returns to Form Builder only after the called product has been exited.  The
                                    end user cannot work in the form while the called product is running.
ASYNCHRONOUS:            Specifies that control returns to the calling application immediately, even if the called application
                                    has not completed its display.


Q) What does the message_level system variable do?

SYSTEM.MESSAGE_LEVEL stores one of the following message severity levels: 0, 5, 10, 15, 20, or 25. The default value
is 0. SYSTEM.MESSAGE_LEVEL can be set to either a character string or a number. The values assigned can be any value
between 0 and 25, but values lower than 0 or higher than 25 will generate an error. During a Runform session, Form Builder suppresses all messages with a severity level that is the same or lower (less severe) than the indicated severity level.
Assign a value to the SYSTEM.MESSAGE_LEVEL system variable with standard PL/SQL syntax:
:System.Message_Level := value;


Q) What are the 4 system variables whose values can be set ?
1.  SYSTEM.DATE_THRESHOLD
SYSTEM.DATE_THRESHOLD represents the database date requery threshold.  This variable works in conjunction
with the three system variables $$DBDATE$$, $$DBDATETIME$$, and $$DBTIME$$, and controls how often
Form Builder synchronizes the database date with the RDBMS. 
2.  SYSTEM.EFFECTIVE_DATE
SYSTEM.EFFECTIVE_DATE represents the effective database date.  The variable value must always be in the
following format:
                        DD-MON-YYYY HH24:MI:SS
3.  SYSTEM.MESSAGE_LEVEL
SYSTEM.MESSAGE_LEVEL stores one of the following message severity levels: 0, 5, 10, 15, 20, or 25. The
default value is 0. SYSTEM.MESSAGE_LEVEL can be set to either a character string or a number. The values
assigned can be any value between 0 and 25, but values lower than 0 or higher than 25 will generate an error. During
 a Runform session, Form Builder suppresses all messages with a severity level that is the same or lower (less
severe) than the indicated severity level.
Assign a value to the SYSTEM.MESSAGE_LEVEL system variable with standard PL/SQL syntax:
:System.Message_Level := value;


      
      4.   SYSTEM.SUPPRESS_WORKING:
SYSTEM.SUPPRESS_WORKING suppresses the "Working..." message in Runform, in order to prevent the screen
update usually caused by the display of the "Working..." message. The value of the variable is one of the following
two CHAR values:
TRUE  Prevents Form Builder from issuing the  "Working..." message.
FALSE            Allows Form Builder to continue to issue the "Working..." message.


Q) how do u call a user-defined trigger in forms?
A user-named trigger is one that has a unique, user-supplied name.  Because its name does not correspond to any Form
Builder event, a user-named trigger can only be executed by calling it from within a built-in trigger, menu item command, or
user-named subprogram.  To call a user-named trigger, use the EXECUTE_TRIGGER built-in procedure.  This procedure
takes a parameter that names the trigger to be fired:
Execute_Trigger('my_user_named_trigger');
User-named triggers are required only in special situations.  For most applications, writing a user-named subprogram and
then calling that from a trigger or menu item command is preferred.


Q) How do u interact with operating system application?
      Host: Executes an indicated operating system command.

Parameters
system_command_ string:      Specifies the system command you want to pass to your particular
                                                operating system.
screen_action:                         Specifies one of the following constants:

no parameter  Specifies that Form Builder will:
1. Clear the screen
                                                                  2. Prompt the operator to return from the command
NO_PROMPT  Specifies that Form Builder will:

                                                                  1. Clear the screen (does not prompt the operator to return from the command)
NO_SCREEN  Specifies that Form Builder will:
1. Not clear the screen
2. Not prompt the operator to return from the system command
(The HOST command should not send output to the screen when using the NO_SCREEN parameter.)


Q) What does NULL statement do?
The NULL statement explicitly specifies inaction; it does nothing other than pass control to the next statement. In a construct
allowing alternative actions, the NULL statement serves as a placeholder. Syntax
null_statement ::=
NULL;
Comments:      The NULL statement improves readability by making the meaning and action of conditional statements
                        clear. It tells readers that the associated alternative has not been overlooked, but that indeed no action is
                        necessary. Each clause in an IF statement must contain at least one executable statement. The NULL
                        statement meets this requirement. So, you can use the NULL statement in clauses that correspond to
                        circumstances in which no action is taken.
Do not confuse the NULL statement with the Boolean non-value NULL; they are unrelated.


Q) Diff between post and commit?
POST:             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.






COMMIT:     Commit processing is the way Form Builder attempts to make the data in the database identical to the data
                        in the form. Form Builder's normal cycle of operation is:
1. Read records from the database.
2. Allow the end user to make tentative insertions, updates, and deletions. The tentative changes appear
    only in the form. The database remains unchanged.
3. Post changes to the database. Form Builder does all of its remaining processing and sends the data to the
    database. After posting the data, Form Builder can only roll back the changes (via the [Clear Form]
    function key or CLEAR_FORM built-in) or commit them.
4. Form Builder commits the posted changes. They become permanent changes to the database.

Q) Suppose u have a key next item at item ,block and form level which one will fire first
Item Level, Block Level and Form Level

Q) What are the properties in form that can be set for better performance

1. Query Array Size property
             Specifies the maximum number of records that Form Builder should fetch from the database at one time.
 A size of 1 provides the fastest perceived response time, because Form Builder fetches and displays only 1 record at
 a time.  By contrast, a size of 10 fetches up to 10 records before displaying any of them, however, the larger size
reduces overall processing time by making fewer calls to the database for records.
2.  Number of Records Buffered property
            Specifies the minimum number of records buffered in memory during a query in the block.
Form Builder buffers any additional records beyond the maximum to a temporary file on disk.
1. Improve processing speed by increasing the number of records buffered.
2. Save memory by decreasing the number of records buffered.  This can, however, result in slower disk I/O.
3. If you anticipate that the block may contain a large number of records either as the result of a query or of heavy
    data entry, consider raising the Number of Records Buffered property to increase performance.
4. Consider lowering the Number of Records Buffered property if you anticipate retrieving large items, such as
    image items, because of the amount of memory each item buffered may require.
            3.  Update Changed Columns Only property
                        When queried records have been marked as updates, specifies that only columns whose values were actually
                        changed should be included in the SQL UPDATE statement that is sent to the database during a COMMIT.  By
                        default, Update Changed Columns Only is set to No, and all columns are included in the UPDATE statement.
1. If the DML Array Size property is set to a value greater than 1, this Update Changed Columns Only property will
    be ignored at runtime.  That is, a DML Array Size greater than 1 causes all columns to be updated – even if
    Update Changed Columns Only was set to Yes.
2. When Update Changed Columns Only is No, Form Builder can reuse the same SQL statement for multiple
    updates, without having to reparse each time. Setting Update Changed Columns Only to Yes can degrade
    performance because the UPDATE statement must be reparsed each time. In general, you should only set Update
    Changed Columns Only to Yes when you know that operators will seldom update column values that will take a
    long time to transfer over the network, such as LONGs.
3. Set Update Changed Columns Only to Yes in the following circumstances:
            1. To save on network traffic, if you know an operator will primarily update only one or two columns.
2. To avoid re-sending large items that are not updated, such as images or LONGs.
3. To fire database triggers on changed columns only.  For example, if you implement a security scheme
    with a database trigger that fires when a column has been updated and writes the userid of the person
     performing the update to a table.

Q) What are the diff values for block status, record status?
      SYSTEM.BLOCK_STATUS
 Represents the status of a Data block where the cursor is located, or the current data  block during trigger processing.  The    
 value can be one of three character strings:
CHANGED    Indicates that the block contains at least one Changed record. 
NEW   Indicates that the block contains only New records.
QUERY          Indicates that the block contains only Valid records that have been retrieved from the database.

Each time this value is referenced, it must be constructed by Form Builder.  If a block contains a large number of records, using SYSTEM.BLOCK_STATUS could adversely affect performance.



      SYSTEM.RECORD_STATUS
Represents the status of the record where the cursor is located.  The value can be one of  four character strings:
CHANGED    Indicates that a queried record's validation status is Changed.
INSERT          Indicates that the record's validation status is Changed and that the record does not exist in the
                        database.
NEW               Indicates that the record's validation status is New.
QUERY                      Indicates that the record's validation status is Valid and that it was retrieved from the database.     
            Both SYSTEM.RECORD_STATUS and the GET_RECORD_PROPERTY built-in return the status of a record in a given
            block, and in most cases, they return the same status.  However, there are specific cases in which the results may differ.
SYSTEM.RECORD_STATUS can in certain cases return a value of NULL, because SYSTEM.RECORD_STATUS is
undefined when there is no current record in the system.  For example, in a When-Clear-Block trigger, Form Builder is at the
block level in its processing sequence, so there is no current record to report on, and the value of SYSTEM.RECORD_STATUS is NULL.
GET_RECORD_PROPERTY, on the other hand, always has a value of NEW, CHANGED, QUERY, or INSERT, because it
returns the status of a specific record without regard to the processing sequence or whether the record is the current record.


Q) Diff between pre-query and post-query?
The Pre-Query and Post-Query triggers allow control over query processing.  They can be defined at the form or block level. 
Most often, attach them to specific blocks to control the query functionality of those blocks.

The Pre-Query trigger fires just before Form Builder issues the SELECT statement to the database, after the operator has
defined the example record by entering query criteria in Enter Query mode. Inside a Pre-Query trigger, the example record defined by the query criteria is the current record.  This means that trigger code can read and set the values of items in the example record using standard :block_name.item_name syntax.
A Pre-Query trigger can be used to disallow query conditions that might be invalid.  When a form is in Enter Query mode,
normal validation is suspended and no validation triggers fire as they do in Normal mode.  The Pre-Query trigger thus allows
you to verify that any values entered by the operator are valid query conditions.
When invalid query conditions have been entered, you can abort the query by raising the FORM_TRIGGER_FAILURE
built-in exception in the Pre-Query trigger.
You can also call SET_BLOCK_PROPERTY to modify the block's WHERE and ORDER BY clauses from within the Pre-
Query trigger, to further restrict or order the records the query will retrieve.

The Post-Query trigger fires after the query has executed, when Form Builder is fetching records into the form.  Post-Query fires once for each record retrieved into the form, which allows you to read and set the values of items in a fetched record before the operator sees them displayed in the block.
A Post-Query trigger is also useful for populating control items whose values are derived from a table other than the block's
base table.


how do u get the total no of records that will be fetched based on a query condition?


Q) how u run a form in debug mode?
      There is an option in the form builder to run the form in debug mode.













Reports
-------

Q) Types of reports?
      1. Tabular
      2. Group Left
      3. Group Above
      4. Form-Like
      5. Mailing Label
      6. Form Letter
      7. Matrix
      8. Matrix with group

Q) How u call a report from a report?

Q) How 2 perform DML using reports
Use DML or DDL in your PL/SQL, you should use the SRW.DO_SQL  packaged function.  Note that
SRW.DO_SQL should only be used for DML and DDL; you should not use it to fetch data. 
     
      SRW.DO-SQL: This procedure executes the specified SQL statement from within Report Builder.  The SQL statement can be DDL (statements that define data), or DML (statements that manipulate data).  DML statements are usually faster when they are in PL/SQL, instead of in SRW.DO_SQL.
Since you cannot perform DDL statements in PL/SQL, the SRW.DO_SQL packaged procedure is especially useful for
performing them within Report Builder, instead of via a user exit.
Syntax
SRW.DO_SQL (sql_statement CHAR);
            E.g.     FUNCTION CREATETAB RETURN BOOLEAN IS
BEGIN
SRW.DO_SQL('CREATE TABLE CHECK (EMPNO NUMBER NOT NULL
            PRIMARY KEY, SAL NUMBER (10,2)) PCTFREE 5  PCTUSED 75');
RETURN(TRUE);
EXCEPTION
WHEN SRW.DO_SQL_FAILURE THEN
 SRW.MESSAGE(100, 'ERROR WHILE CREATING CHECK TABLE.');
 SRW.MESSAGE(50, 'REPORT WAS STOPPED BEFORE THE RUNTIME
                                                                                                                                     PARAMETER FORM.');
 RAISE SRW.PROGRAM_ABORT;
END;

Q) What are the various report triggers and what is execution sequence?
       Before Parameter Form, After Parameter Form, Before Report, Between Pages, After Report

Q) How many group are there in a mtarix report ? Explain the answer?
A matrix (crosstab) report contains one row of labels, one column of labels, and information in a grid format that is related to
the row and column labels.  A distinguishing feature of matrix reports is that the number of columns is not known until the
data is fetched from the database. 
To create a matrix report, you need at least four groups: one group must be a cross-product group, two of the groups must be
within the cross-product group to furnish the "labels," and at least one group must provide the information to fill the cells.  The groups can belong to a single query or to multiple queries. 

Q) What does the no of repeating frames in a report indicate?
The Maximum Records Per Page property is the maximum number of instances of the repeating frame that will be formatted
on a logical page.  Suppose that you have a repeating frame with many instances.  To improve the appearance of your report,
you prefer to have at most three instances of the repeating frame on a given logical page.  To ensure that you never have more
than three instances per logical page, you set Maximum Records Per Page to 3.
Values:  a whole number from 1 through 32K, means that number of instances is the maximum that can be formatted on a
            logical page.
blank   Means that as many instances of the repeating frame as possible can be formatted on a logical page.

Q) Which property automatically takes care of the width and height of text items?
      Vertical Elasticity & Horizontal Elasticity

Q) What are place holder ,formula columns and summary columns ?
1. Placeholder:           A placeholder is a column for which you set the datatype and value in PL/SQL that you define.  You can
                        set the value of a placeholder column in the following places:
1. The Before Report Trigger, if the placeholder is a report-level column
2. A report-level formula column, if the placeholder is a report-level column
3. A formula in the placeholder's group or a group below it (the value is set once for each record of the
    group)
      2. Formula Column: A formula column performs a user-defined computation on another column(s) data.
                              Formulas are PL/SQL functions that populate formula or placeholder columns.  You can access the
                              PL/SQL for formulas from the Object Navigator, the PL/SQL Editor, or the Property Palette (i.e., the
                              PL/SQL Formula property).
A column of datatype Number can only have a formula that returns a value of datatype NUMBER.  A
column of Datatype Date can only have a formula that returns a value of datatype DATE.  A column of
 Datatype Character can only have a formula that returns a value of datatype CHARACTER, VARCHAR,
or VARCHAR2.
            3.  Summary Column: A summary column performs a computation on another column's data.  Using the Report Wizard or
Data Wizard, you can create the following summaries:  sum, average, count, minimum, maximum, % total. 
You can also create a summary column manually in the Data Model view, and use the Property Palette to
create the following additional summaries:  first, last, standard deviation, variance.

Q) How u display message using reports?
SRW.MESSAGE: This procedure displays a message with the message number and text that you specify.  The message is displayed in the format below.  After the message is raised and you accept it, the report execution will continue.
MSG-msg_number:  msg_text.
Syntax
SRW.MESSAGE (msg_number NUMBER, msg_text CHAR);

Q) What does a data link do ?
Data links relate the results of multiple queries.  A data link (or parent-child relationship) causes the child query to be
executed once for each instance of its parent group.  When you create a data link in the Data Model view of your report,
Report Builder constructs a clause (as specified in the link's Property Palette) that will be added to the child query's SELECT
statement at runtime.  You can view the SELECT statements for the individual parent and child queries in the Builder, but can not view the SELECT statement that includes the clause created by the data link you define.
Oracle Reports does not support data links between queries that contain column objects.  If you attempt to create such a link,
a message dialog box displays, which enables you to choose whether to tell Reports to create a group-to-group query instead
(using the parent groups), or to cancel the operation.  If you want to create a link between any type of column and a column
object, you can manually type the SQL statement using the appropriate column alias(es).


Q) Will the between pages trigger fire when u move from 1 page to the previous page?
The Between Pages trigger fires before each page of the report is formatted, except the very first page.  This trigger can be
used for customized page formatting.  In the Runtime Previewer or Live Previewer, this trigger only fires the first time that
you go to a page.  If you subsequently return to the page, the trigger does not fire again.
Displays an error message when you try to go to the page for which the trigger returned FALSE.  The pages subsequent to the page that returned FALSE are not formatted.  If the trigger returns FALSE on the last page, nothing happens because the report is done formatting.  The Between Pages trigger does not fire before the first page.  If the trigger returns FALSE on the first page, the first page is displayed, but, if you try to go to the second page, an error message is displayed.


Q) What should be done to suppress the parameter form screen?
Action Trigger can be written to suppress. In tools(toolbar) preferences we can check the run time parameter field.

No comments: