Case Sensitive Passwords in Oracle Database 11g Release 1

Case sensetive login password

 ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;

Yasser

Lear HTML

 https://www.websiteplanet.com/blog/html-guide-beginners/

Oracle Select all column and one in the begining

Oracle Select all column and one in the begining

example

 select i.BONUS_ITEM_FLAG,i.* 

from SAL_INVOICES_ITEMS i ;

My youtube links

 https://www.youtube.com/channel/UCDlpipY3srYehE9EcqoN_kQ/

Oracle Forms add_list_element

ADD_LIST_ELEMENT built-in

Description

Adds a single element to a list item.

Syntax

PROCEDURE ADD_LIST_ELEMENT (list_name VARCHAR2, list_index, NUMBER list_label VARCHAR2, list_value NUMBER);

PROCEDURE ADD_LIST_ELEMENT (list_id ITEM,

list_index VARCHAR2, list_label VARCHAR2, list_value NUMBER);

Built-in Type  unrestricted procedure

Enter Query Mode  yes

Parameters

list_id   Specifies the unique ID that Form Builder assigns when it creates the list

item.  Use the FIND_ITEM built-in to return the ID to an appropriately

typed variable.  The data type of the ID is ITEM.

list_name         The name you gave to the list item when you created it.  The data type of

                           the name is VARCHAR2.

list_index      (counter)        Specifies the list index value.  The list index is 1 based.

list_label       Specifies the VARCHAR2 string that you want displayed as the label of the

                       list element.

list_value       The actual list element value you intend to add to the list item.

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

ADD_LIST_ELEMENT restrictions

For a base table list with the List Style property set to Poplist or T-list, Form Builder does not allow you to add another values element when the block contains queried or changed records. Doing so causes an error. This situation can occur if you have previously used DELETE_LIST_ELEMENT or CLEAR_LIST to remove the other values element that was specified at design time by the Mapping of Other Values list item property setting.

Note: The block status is QUERY when a block contains queried records. The block status is CHANGED when a block contains records that have been either inserted or updated.

ADD_LIST_ELEMENT Example:

------

PROCEDURE YLISTE IS

cursor c is 

select CODES_ID,       

USER_CODE ||' - '||

DECODE(GET_USER_LANG(:GLOBAL.LANGUAGE_ID), 'P', PRIMARY_NAME, SECONDARY_NAME) ITEM_NAME

 from codes where code_types_id = 583007;

cnt number := 1;

it_id ITEM;

begin

it_id := Find_Item('SAL_INVOICES.DRIVER_ID');

clear_list(it_id);

for i in c loop

add_list_element(it_id,cnt,i.ITEM_NAME,i.CODES_ID);

cnt := cnt + 1;

end loop;

end; 

-----------

Yasser

Oracle With Clause Syntax and Examples :

Oracle With Clause Syntax and Examples :

In this section i would like to explain the syntax as well as examples of With clause in oracle.Before checking the syntax and examples of With clause in oracle let us first check some important bullet points of With Clause :

With Clause in Oracle  is released in Oracle 9i release 2 to improve the performance of complex sql queries.

The clause works like a global temporary tables of oracle which is used to improve the query speed of complex sql queries.

This technique is also called as sub-query factoring as it is used to De-factor the subqueries.

With clause in oracle is not supported by all oracle versions ,the oracle version 9i and beyond versions.

When sub-query needs to be executed multiple times at that time With clause is used.

The name which is assigned to the sub-query is treated as though it was an inline view or table.

The With Clause is useful in Recursive queries as well.

WITH 

  items_costs AS (

    SELECT items_id, SUM(UNIT_COST) items_total

    FROM   STOCK_IN_DOCUMENTS_ITEMS e, STOCK_IN_DOCUMENTS d

    WHERE  e.document_id = d.document_id

         and e.STORES_ID  = d.STORES_ID

    GROUP BY items_id),

  avg_cost AS (

    SELECT SUM(items_total)/COUNT(*) avg

    FROM   items_costs)

SELECT *

FROM   items_costs

WHERE  items_total > (SELECT avg FROM avg_cost)

ORDER BY items_id;

Oracle script for learning schema scott, table emp, dept and salgrade

 EMP, DEPT and SALGRADE Tables Data

--->> EMP Table

CREATE TABLE EMP

       (EMPNO NUMBER(4) NOT NULL,

        ENAME VARCHAR2(10),

        JOB VARCHAR2(9),

        MGR NUMBER(4),

        HIREDATE DATE,

        SAL NUMBER(7, 2),

        COMM NUMBER(7, 2),

        DEPTNO NUMBER(2));

INSERT INTO EMP VALUES (7369, 'SMITH',  'CLERK',     7902, TO_DATE('17-DEC-1980', 'DD-MON-YYYY'),  800, NULL, 20);

INSERT INTO EMP VALUES (7499, 'ALLEN',  'SALESMAN',  7698, TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600,  300, 30);

INSERT INTO EMP VALUES (7521, 'WARD',   'SALESMAN',  7698, TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250,  500, 30);

INSERT INTO EMP VALUES (7566, 'JONES',  'MANAGER',   7839, TO_DATE('2-APR-1981', 'DD-MON-YYYY'),  2975, NULL, 20);

INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN',  7698, TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);

INSERT INTO EMP VALUES (7698, 'BLAKE',  'MANAGER',   7839, TO_DATE('1-MAY-1981', 'DD-MON-YYYY'),  2850, NULL, 30);

INSERT INTO EMP VALUES (7782, 'CLARK',  'MANAGER',   7839, TO_DATE('9-JUN-1981', 'DD-MON-YYYY'),  2450, NULL, 10);

INSERT INTO EMP VALUES (7788, 'SCOTT',  'ANALYST',   7566, TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);

INSERT INTO EMP VALUES (7839, 'KING',   'PRESIDENT', NULL, TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);

INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN',  7698, TO_DATE('8-SEP-1981', 'DD-MON-YYYY'),  1500, NULL, 30);

INSERT INTO EMP VALUES (7876, 'ADAMS',  'CLERK',     7788, TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);

INSERT INTO EMP VALUES (7900, 'JAMES',  'CLERK',     7698, TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),   950, NULL, 30);

INSERT INTO EMP VALUES (7902, 'FORD',   'ANALYST',   7566, TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),  3000, NULL, 20);

INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK',     7782, TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

--->> DEPT Table

CREATE TABLE DEPT (DEPTNO NUMBER(2),DNAME VARCHAR2(14),LOC VARCHAR2(13) );

INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');

INSERT INTO DEPT VALUES (20, 'RESEARCH',   'DALLAS');

INSERT INTO DEPT VALUES (30, 'SALES',      'CHICAGO');

INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');

--->> SALGRADE Table

CREATE TABLE SALGRADE (GRADE NUMBER,LOSAL NUMBER,HISAL NUMBER);

INSERT INTO SALGRADE VALUES (1,  700, 1200);

INSERT INTO SALGRADE VALUES (2, 1201, 1400);

INSERT INTO SALGRADE VALUES (3, 1401, 2000);

INSERT INTO SALGRADE VALUES (4, 2001, 3000);

INSERT INTO SALGRADE VALUES (5, 3001, 9999);

COMMIT;

Oracle Difference between Rank and Dense_Rank

 To show the difference practically please

create a new table named dense_rank_demo for demonstration:

CREATE TABLE dense_rank_demo (

    col VARCHAR2(10) NOT NULL

);

Next, insert some values into the dense_rank_demo table:

INSERT ALL 

    INTO dense_rank_demo(col) VALUES('A')

    INTO dense_rank_demo(col) VALUES('A')

    INTO dense_rank_demo(col) VALUES('B')

    INTO dense_rank_demo(col) VALUES('C')

    INTO dense_rank_demo(col) VALUES('C')

    INTO dense_rank_demo(col) VALUES('C')

    INTO dense_rank_demo(col) VALUES('D')

SELECT 1 FROM dual; 

SELECT

 col, RANK () OVER (  ORDER BY col ) My_rank,

 DENSE_RANK () OVER (  ORDER BY col ) My_rank

FROM dense_rank_demo

SQL> /

COL          MY_RANK MY_DENSE_RANK

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

A                  1             1

A                  1             1

B                  3             2

C                  4             3

C                  4             3

C                  4             3

D                  7             4

7 rows selected.

COL          MY_RANK   MY_DENSE_RANK

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

A                  1                     1

A                  1                     1

B                  3                     2

C                  4                     3

C                  4                     3

C                  4                     3

D                  7                     4

7 rows selected.

I think you can see the difference

Oracle DENSE_RANK() function

Oracle DENSE_RANK() function

The DENSE_RANK() is an analytic function that calculates the rank of a row in an ordered set of rows. The returned rank is an integer starting from 1.

The following shows the syntax of DENSE_RANK():

DENSE_RANK( ) OVER([ query_partition_clause ] order_by_clause)

In this syntax, the order_by_clause is required because the DENSE_RANK() function is ordered sensitive. The following is the syntax of the order by clause:

ORDER BY expression1 [,expression2,...] [ASC | DESC ] [NULLS FIRST | LAST]

If you omit the query_partition_by clause, the function will treat the whole result set as a single partition. Otherwise, the partition by clause will divide the result set into partitions to which the function applies.

PARTITION BY expression1 [,expression2, ...]

Note that the partition by clause must appear before the order by clause.

You will find the DENSE_RANK() function very useful for top-N and bottom-N queries.

Oracle DENSE_RANK() function examples

Let’s take a simple example to understand the DENSE_RANK() function:

Oracle DENSE_RANK() function illustration

First, create a new table named dense_rank_demo for demonstration:

CREATE TABLE dense_rank_demo (

    col VARCHAR2(10) NOT NULL

);

Next, insert some values into the dense_rank_demo table:

INSERT ALL 

    INTO dense_rank_demo(col) VALUES('A')

    INTO dense_rank_demo(col) VALUES('A')

    INTO dense_rank_demo(col) VALUES('B')

    INTO dense_rank_demo(col) VALUES('C')

    INTO dense_rank_demo(col) VALUES('C')

    INTO dense_rank_demo(col) VALUES('C')

    INTO dense_rank_demo(col) VALUES('D')

SELECT 1 FROM dual; 

Then, query data from the dense_rank_demo table:

SELECT col FROM dense_rank_demo;

After that, use the DENSE_RANK() function to calculate a rank for each row:

SELECT

col,

DENSE_RANK () OVER ( 

ORDER BY col ) 

col

FROM

dense_rank_demo;

The following picture shows the output:

Oracle DENSE_RANK illustration

As clearly shown in the output:

Rows with the same values such as first and second receive the same rank values.

Rank values are consecutive even in the event of ties.

Oracle DENSE_RANK() function examples

We’ll use the products table from the sample database to demonstrate the DENSE_RANK() function:

products table

The following example uses the DENSE_RANK() function to calculate rank values with the list price as a rank criterion for each product:

SELECT 

    user_code, 

    ITEM_PRICE, 

    RANK() OVER(ORDER BY ITEM_PRICE) 

FROM 

    ITEMS;

Here is the partial output:

Oracle DENSE_RANK function example

To get the top-10 cheapest product, you use a common table expression that wraps the above query and selects only 10 products with the lowest prices as follows:

WITH cte_products AS(  

SELECT 

    product_name, 

    list_price, 

    RANK() OVER(

    ORDER BY list_price

    ) my_rank

FROM 

    products

)

SELECT * FROM cte_products

WHERE my_rank <= 10;

Oracle dept and emp table script

create table dept(

deptno number(2,0), dname varchar2(14), loc varchar2(13), constraint pk_dept primary key (deptno) )

/

create table emp( empno number(4,0), ename varchar2(10), job varchar2(9), mgr number(4,0), hiredate date, sal number(7,2), comm number(7,2), deptno number(2,0), constraint pk_emp primary key (empno), constraint fk_deptno foreign key (deptno) references dept (deptno) )

/

insert into DEPT (DEPTNO, DNAME, LOC)
values(10, 'ACCOUNTING', 'NEW YORK')

1 row(s) inserted.

insert into dept  
values(20, 'RESEARCH', 'DALLAS')

1 row(s) inserted.

insert into dept  

values(30, 'SALES', 'CHICAGO')

1 row(s) inserted.

insert into dept  

values(40, 'OPERATIONS', 'BOSTON')

1 row(s) inserted.

Insert EMP row, using TO_DATE function to cast string literal into an oracle DATE format.

insert into emp  

values(  

 7839, 'KING', 'PRESIDENT', null,  

 to_date('17-11-1981','dd-mm-yyyy'),  

 5000, null, 10  

)

/

1 row(s) inserted.

insert into emp  

values(  

 7698, 'BLAKE', 'MANAGER', 7839,  

 to_date('1-5-1981','dd-mm-yyyy'),  

 2850, null, 30  

)

/

1 row(s) inserted.

insert into emp  

values(  

 7782, 'CLARK', 'MANAGER', 7839,  

 to_date('9-6-1981','dd-mm-yyyy'),  

 2450, null, 10  

)

/

1 row(s) inserted.

insert into emp  

values(  

 7566, 'JONES', 'MANAGER', 7839,  

 to_date('2-4-1981','dd-mm-yyyy'),  

 2975, null, 20  

)

/

1 row(s) inserted.

insert into emp  

values(  

 7788, 'SCOTT', 'ANALYST', 7566,  

 to_date('13-JUL-87','dd-mm-rr') - 85,  

 3000, null, 20  

)

/

1 row(s) inserted.

insert into emp  

values(  

 7902, 'FORD', 'ANALYST', 7566,  

 to_date('3-12-1981','dd-mm-yyyy'),  

 3000, null, 20  

)

/

1 row(s) inserted.

insert into emp  

values(  

 7369, 'SMITH', 'CLERK', 7902,  

 to_date('17-12-1980','dd-mm-yyyy'),  

 800, null, 20  

);

1 row(s) inserted.

insert into emp  

values(  

 7499, 'ALLEN', 'SALESMAN', 7698,  

 to_date('20-2-1981','dd-mm-yyyy'),  

 1600, 300, 30  

);

1 row(s) inserted.

insert into emp  

values(  

 7521, 'WARD', 'SALESMAN', 7698,  

 to_date('22-2-1981','dd-mm-yyyy'),  

 1250, 500, 30  

)

1 row(s) inserted.

insert into emp  

values(  

 7654, 'MARTIN', 'SALESMAN', 7698,  

 to_date('28-9-1981','dd-mm-yyyy'),  

 1250, 1400, 30  

);

1 row(s) inserted.

insert into emp  

values(  

 7844, 'TURNER', 'SALESMAN', 7698,  

 to_date('8-9-1981','dd-mm-yyyy'),  

 1500, 0, 30  

)

1 row(s) inserted.

insert into emp  

values(  

 7876, 'ADAMS', 'CLERK', 7788,  

 to_date('13-JUL-87', 'dd-mm-rr') - 51,  

 1100, null, 20  

)

1 row(s) inserted.

insert into emp  

values(  

 7900, 'JAMES', 'CLERK', 7698,  

 to_date('3-12-1981','dd-mm-yyyy'),  

 950, null, 30  

);

1 row(s) inserted.

insert into emp  

values(  

 7934, 'MILLER', 'CLERK', 7782,  

 to_date('23-1-1982','dd-mm-yyyy'),  

 1300, null, 10  

);

1 row(s) inserted.

Simple natural join between DEPT and EMP tables based on the primary key of the DEPT table DEPTNO, and the DEPTNO foreign key in the EMP table.

select ename, dname, job, empno, hiredate, loc  

from emp, dept  

where emp.deptno = dept.deptno  

order by ename

 

ENAME DNAME JOB EMPNO HIREDATE LOC

ADAMS RESEARCH CLERK 7876 23-MAY-87 DALLAS

ALLEN SALES SALESMAN 7499 20-FEB-81 CHICAGO

BLAKE SALES MANAGER 7698 01-MAY-81 CHICAGO

CLARK ACCOUNTING MANAGER 7782 09-JUN-81 NEW YORK

FORD RESEARCH ANALYST 7902 03-DEC-81 DALLAS

JAMES SALES CLERK 7900 03-DEC-81 CHICAGO

JONES RESEARCH MANAGER 7566 02-APR-81 DALLAS

KING ACCOUNTING PRESIDENT 7839 17-NOV-81 NEW YORK

MARTIN SALES SALESMAN 7654 28-SEP-81 CHICAGO

MILLER ACCOUNTING CLERK 7934 23-JAN-82 NEW YORK

SCOTT RESEARCH ANALYST 7788 19-APR-87 DALLAS

SMITH RESEARCH CLERK 7369 17-DEC-80 DALLAS

TURNER SALES SALESMAN 7844 08-SEP-81 CHICAGO

WARD SALES SALESMAN 7521 22-FEB-81 CHICAGO

14 rows selected.

What is Oracle autonomous transaction?

 Oracle autonomous transaction

An autonomous transaction is an independent transaction that is initiated by another transaction (the parent transaction). An autonomous transaction can modify data and commit or rollback independent of the state of the parent transaction.

PLSQL

Difference b/w procedure and function?  A procedure may return (one or more values using OUT & INOUT Parameters) or may not return a value. But a function has to return a single value and has the return clause in its definition. Function can be called in select statements but procedure can only be called in a pl/sql block.  Procedure's parameters can have IN or OUT or INOUT parameters. But function's parameters can only have IN parameters.

Difference b/w ROWID and ROWNUM? ROWID : It gives the hexadecimal string representing the address of a row.It gives the location in database where row is physically stored. ROWNUM: It gives a sequence number in which rows are retrieved from the database.

Give some examples of pseudo columns? NEXTVAL, CURRVAL, LEVEL, SYSDATE

Difference b/w implicit cursor and explicit cursor? Implicit cursors are automatically created by oracle for all its DML stmts. Examples of implicit cursors: SQL%FOUND, SQL%NOTFOUND, SQL%ROWCOUNT, SQL%ISOPEN; Explicit cursors are created by the users for multi row select stmts.

How to create a table in a procedure or function? See the below piece of code:  Since create stmt can be used only at the sql prompt, we have used dynamic sql to create a table.            

DECLARE

                    L_STMT VARCHAR2(100);

BEGIN

                    DBMS_OUTPUT.PUT_LINE('STARTING ');

                    L_STMT := 'create table dummy1 (X VARCHAR2(10) , Y NUMBER)';

                    EXECUTE IMMEDIATE L_STMT;

                    DBMS_OUTPUT.PUT_LINE('end ');

END;

The above piece of code can be written In procedure and function DDL's can be used in function provided that function should be invoked in Begin-End block not from Select statement.

Explain the usage of WHERE CURRENT OF clause in cursors ? Look at the following pl/sql code:

DECLARE

                   CURSOR wip_cur IS

                   SELECT acct_no, enter_date

                   FROM wip

WHERE enter_date < SYSDATE -7

FOR UPDATE;

BEGIN

FOR wip_rec IN wip_cur

LOOP

                   INSERT INTO acct_log (acct_no, order_date)

                   VALUES (wip_rec.acct_no, wip_rec.enter_date);

                   DELETE FROM wip

WHERE CURRENT OF wip_cur;

          END LOOP;

END;

"WHERE CURRENT OF" has to be used in concurrence with "FOR UPDATE"  in the cursor select stmt.

"WHERE CURRENT OF" used in delete or update stmts means, delete/update the current record specified by the cursor.

By using WHERE CURRENT OF, you do not have to repeat the WHERE clause in the SELECT statement.

What is the purpose of FORUPDATE? Selecting in FOR UPDATE mode locks the result set of rows in update mode, which means that row cannot be updated or deleted until a commit or rollback is issued which will release the row(s). If you plan on updating or deleting records that have been referenced by a Select For Update statement, you can use the Where Current Of statement.                                                                                                

What is RAISE_APPLICATION_ERROR? The RAISE_APPLICATION_ERROR is a procedure defined by Oracle that allows the developer to raise an exception and associate an error number and message with the procedure other than just Oracle errors. Raising an Application Error With raise_application_error

DECLARE

   num_tables NUMBER;

BEGIN

   SELECT COUNT(*) INTO num_tables FROM USER_TABLES;

   IF num_tables < 1000 THEN

      /* Issue your own error code (ORA-20101) with your own error message. 

         Note that you do not need to qualify raise_application_error with 

         DBMS_STANDARD */

      raise_application_error(-20101, 'Expecting at least 1000 tables');

   ELSE

      NULL; -- Do the rest of the processing (for the non-error case).

   END IF;

END;

/

The procedure RAISE_APPLICATION_ERROR lets you issue user-defined ORA- error messages from stored subprograms. That way, you can report errors to your application and

avoid returning unhandled exceptions.

What is mutating error? Mutating error occurs in the following scenario:

WHEN WE ARE UPDATING A TABLE (TRIGGER WRITTEN ON A TABLE FOR UPDATE) AND AT THE SAME TIME TRYING TO RETRIEVE DATA FROM THAT TABLE. IT WILL RESULT INTO MUTATING TABLE AND IT WILL RESULT INTO MUTATING ERROR. 

Can we have commit/rollback in DB triggers? Having Commit / Rollback inside a trigger defeats the standard of whole transaction's commit / rollback all together. Once trigger execution is complete then only a transaction can be said as complete and then only commit should take place. If we still want to carry out some action which should be initiated from trigger but should be committed irrespective of trigger completion / failure we can have AUTONOMUS TRANSACTION. Inside Autonomous transaction block we can have Commit and it will act as actual commit.                                                                                                 

Can we make the trigger an autonomous transaction? This makes all the difference because within the autonomous transaction (the trigger), Oracle will view the triggering table as it was before any changes occurred—that is to say that any changes are uncommitted and the autonomous transaction doesn’t see them. So the potential confusion Oracle normally experiences in a mutating table conflict doesn’t exist.

What is autonomous transaction? Autonomous transaction means a transaction that is embedded in some other transaction, but functions independently.

What is a REF Cursor? The REF CURSOR is a data type in the Oracle PL/SQL language. It represents a cursor or a result set in Oracle Database.

What is the difference between ref cursors and normal pl/sql cursors?

   Declare

          type rc is ref cursor;

          cursor c is

          select * from dual;

          l_cursor rc;

       begin

          if ( to_char(sysdate,'dd') = 30 ) then

                   open l_cursor

                             for select * from emp;

          elsif ( to_char(sysdate,'dd') = 29 ) then

                   open l_cursor

                             for select * from dept;

          else

                   open l_cursor

                             for select * from dual;

          end if;

          open c;

      end;                                                                                                                    

Given that block of code you see perhaps the most "salient" difference, no matter how many times you run that block The cursor C will always be select * from dual.  The ref cursor can be anything.

Is Truncate a DDL or DML statement? And why? Truncate is a DDL statement. Check the LAST_DDL_TIME on USER_OBJECTS after truncating your table. TRUNCATE will automatically commit, and it's not rollback able. This changes the storage definition of the object. That's why it is a DDL.

What are the actions you have to perform when you drop a package? If you rename a package, the other packages that use it will have to be MODIFIED. A simple compilation of the new renamed package won't do. If you have toad, go to the "used by" tab that will show you the packages that call the package being renamed.

What is cascading triggers? When a trigger fires, a SQL statement within its trigger action potentially can fire other triggers, resulting in cascading triggers.

What are materialized views? A materialized view is a database object that stores the results of a query (possibly from a remote database). Materialized views are sometimes referred to as snapshots.

Example

If the materialized view will access remote database objects, we need to start by creating a database link to the remote DB:

CREATE DATABASE LINK remotedb

CONNECT TO scott IDENTIFIED BY tiger

USING 'orcl';

Now we can create the materialized view to pull in data (in this example, across the database link):

CREATE MATERIALIZED VIEW items_summary_mv

 ON PREBUILT TABLE

 REFRESH FORCE  AS

 SELECT  a.PRD_ID, a.SITE_ID, a.TYPE_CODE, a.CATEG_ID,

        sum(a.GMS)       GMS,

        sum(a.NET_REV)   NET_REV,

        sum(a.BOLD_FEE)  BOLD_FEE,

        sum(a.BIN_PRICE) BIN_PRICE,

        sum(a.GLRY_FEE)  GLRY_FEE,

        sum(a.QTY_SOLD)  QTY_SOLD,

        count(a.ITEM_ID) UNITS

FROM  items@remotedb a

GROUP BY  a.PRD_ID, a.SITE_ID, a.TYPE_CODE, a.CATEG_ID;

Materialized view logs:

Materialized view logs are used to track changes (insert, update and delete) to a table. Remote materialized views can use the log to speed-up data replication by only transferring changed records.

Example:

CREATE MATERIALIZED VIEW LOG ON items;

Commonly occurring Errors in Reports?

Some of the errors are defined below

1. There Exists uncompiled unit: When the report is not compiled before loading in the Oracle Applications.

2. Report File not found: When the rdf is not uploaded in proper directory

3. Width or margin is zero: When the repeating frame is not within proper frames

4. Not in proper group: When the repeating frame is not referred to proper group

What is the difference between Compile and Incremental Compile in oracle reports?

In Full compile all the PL/SQL within the reports are compiled but in incremental compile only the changed PL/SQL units are compiled.

When compiling the report for the first time, we should do the full compilation and not the Incremental compile.

How to compile Procedures and Packages?

ALTER <proc/package> <name>COMPILE;

Oracle Reports Interview Questions

1. How many different layouts are available in Reports? 

Ans: There are eight different layout formats:

1. Tabular

2. Form Like

3. Form Letter

4. Mailing Label

5. Group Left

6. Group Above

7. Matrix

8. Matrix with group

2. How many different triggers are available in Report?

Ans: There are five types of triggers in report 6i

1) Before report trigger

2) After report trigger

3) Before Parameter trigger

4) After parameter trigger

5) Between pages trigger

  

3.What is the Firing sequence of report trigger?

Ans: The reports triggers are fired in the following sequence.

*Before Parameter Form

* After Parameter Form

* Before Report

* Between Pages

* After Report

4. What is the difference between After Parameter Trigger and Before Report Trigger? 

Ans:

After parameter Trigger: It will fire after the parameter form is displayed.here we can do validation on parameter values.

Before Report Trigger: It will fire before the report is executed and after the query is parsed and date is fetched. 

5. What is the Format Trigger?

Ans: Format Trigger is a PL/SQL function. This trigger is going to fire before an object is printed in report output. it return boolean-true then go to print -false then don't print.

6. What happens when Flex mode is on ? 

When flex mode is on, reports automatically resizes the parent when the child is resized.

7. What happens when confine mode on ? 

When confine mode is on, the object cannot be moved outside its parent in the layout.  

8. What is a lexical parameter?

Lexical Parameter is used to replace the where, order by conditions at run time.

9. What are bind variables? 

Bind variables are used in oracle reports  for replacing the single parameter in the select statement.

10. What is the minimum number of groups required for a matrix report? 

The minimum of groups required for a matrix report are 4

11. What is the use of an Anchor in Reports? 

Anchor is used to make fixed distance between two objects in Reports Layout.

12. What is the difference between Frame and Repeating Frame? 

Frames are used to surround other objects and protect them from being overwritten or pushed by other objects. For example a frame might be used to surround all objects owned by a group to surround column headings or to surround summaries. 

When you default the layout for a report Report Builder creates frames around report objects as needed; you can also create a frame manually in the Layout Model view.

Repeating frames surround all of the fields that are created for a group’s columns. The repeating frame prints (is fired) once for each record of the group. 

When you default the layout for a report Report Builder creates repeating frames around fields as needed; you can also create a repeating frame manually in the Layout Model view.

13. What are different types of column in reports? 

There are three types of columns in Oracle report: 

1) Placeholder Column: Placeholder column is used to store a value for a variable.

2) Formula Column:  Used For doing mathematical calculations and returning one value

3) Summary Column: The summary columns perform aggregate functions such as SUM, COUNT, MAX, MIN, AVG, and the like.

14. Can u have more than one layout in report? 

It is possible to have more than one layout in a report by using the additional layout option in the layout editor.

===================

====================

1. What are Placeholder Columns ?

A placeholder is a "dummy" column for which you can conditionally set the datatype and value via PL/SQL or a user exit. Placeholder columns are useful when you want to selectively populate a column with a value (e.g., each time the nth record is fetched, or each time a record is fetched containing a specific value, etc.).

2. What are the various Module Types in Reports ?

A You can build three types of modules with Oracle Reports:

* external queries, which are ANSI-standard SQL SELECT statements that can be referenced by modules

* external PL/SQL libraries, which are collections of PL/SQL source code that can be referenced by modules

* reports, which are collections of report-level objects and references to external queries and PL/SQL libraries (optional) that can be referenced by modules

3. What are Physical and Logical pages in Reports ?

A report page can have any length and any width. Because printer pages may be smaller or larger than your report's "page," the concept of physical and logical pages is used.

Physical Page : A physical page (or panel) is the size of a page that will be output by your printer.

Logical Page : A logical page is the size of one page of your actual report; one logical page may be made up of multiple physical pages. The Previewer displays the logical pages of your report output, one at a time.

4. What are the various page layout sections in Oracle Reports ?

A report has three sections : 

•the report header pages, 

•report body/margin pages, 

•and report trailer pages.

5. What are various types of parameters ?

There are two types of parameters:

• default (system parameters)

• user-created (bind and lexical parameters)

6. How do you reference parameters and columns in reports ?

There are two ways to reference a parameter in Oracle reports:

* As bind references

* As lexical references

7. What are Bind Referencing and Lexical Referencing ?

Bind Referencing : Bind references are used to replace a single value in SQL or PL/SQL, such as a character string, number, or date. Use bind reference when you want the parameter to substitute only one value at runtime. Specifically, bind references may be used to replace expressions in SELECT, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH clauses of queries.

Lexical Referencing : Lexical references are placeholders for text that you embed in a SELECT statement. Use Lexical reference when you want the parameter to substitute multiple values at runtime. You can use lexical references to replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH.

8. What are the Types of Matrix report ?

 1. Single query 

 2. Multi query

 3. Nested Query 

 4. Matrix Break

9. What are widow lines ?

Widow lines are the minimum number of lines of the boilerplate text or field that should appear on the logical page where the text starts to print. If the number of lines specified for this property cannot fit on the logical page, then all lines of the boilerplate are moved to the next page.

10. What are widow records ?

Widow records are the minimum number of instances (records) that should appear on the logical page where the repeating frame starts to print. If the number of instances specified for this property cannot fit on the logical page where the repeating frame is initially triggered to print, then the repeating frame will start formatting on the next page.

11. What is 'page protect' property for objects ?

Page protect property for an object indicates whether to try to keep the entire object and its contents on the same logical page. Checking Page Protect means that if the contents of the object cannot fit on the current logical page, the object and all of its contents will be moved to the next logical page.

12. What is the 'Print Condition Type' property ?

'Print Condition Type' property specifies the frequency with which you want the object to appear in the report. The Print Condition Type options indicate the logical page(s) on which the object should be triggered to print with regard to the Print Condition Object.

13. What is the 'Print Condition Object' property ?

'Print Condition Object' property specifies the object on which to base the Print Condition Type of the current object. For example, if you specify a Print Condition Type of All and a Print Condition Object of Anchoring Object, the current object will be triggered to print on every logical page on which its anchoring object (parent object) appears.

14. What are the various values of the 'Print Condition Object' property in Reports ?

The various values are :

•Anchoring Object : Anchoring Object is the parent object to which the current object is implicitly or explicitly anchored.

•Enclosing Object : Enclosing Object is the object that encloses the current object.

15. What is the horizontal of vertical sizing property of objects ?

Horizontal of vertical sizing property specifies how the horizontal or vertical size of the object may change at runtime to accommodate the objects or data within it.

=============

1.What are the various types of reports ?

There are 8 Types of reports in oracle report.

1.Tabular

2. Form Like

3. Form Letter

4. Mailing Label

5. Group Left

6. Group Above

7. Matrix

8. Matrix with group

2. What are Anchors in Oracle Reports?

An anchor defines the relative position of an object to the object to which it is anchored. Anchors are used to determine the vertical and horizontal positioning of a child object relative to its parent. Since the size of some layout objects may change when the report runs (and data is actually fetched), you need anchors to define where you want objects to appear relative to one another.

3. What are the various types of anchors in Reports ?

A There are two types of anchors in Oracle Reports:

* implicit (anchors that Oracle Reports creates when a report is run)

* explicit (anchors you create)

4. What is an Implicit Anchor?

Implicit Anchors : At runtime, Oracle Reports generates an implicit anchor for each layout object that does not already have an explicit anchor. It determines for each layout object which objects, if any, can overwrite it, then creates an anchor from the layout object to the closest object that can overwrite it. This prevents the object from being overwritten. The implicit anchor functionality saves you from having to define the positioning of each object. Implicit anchors are not visible in the Layout editor. However, you can specify that the Object Navigator display anchoring information using the Object Navigator Options dialog.

5. What is an Explicit Anchor?

Explicit Anchors : Create an anchor in the Layout editor by clicking on the Anchor tool, dragging from one edge of the child to the one of the parent's edges, then specifying the anchor's properties in its property sheet. Any anchor you create for an object will override its implicit anchoring. Explicit anchors are always visible in the Layout editor unless you specify otherwise via the Layout Options dialog

6. What are the various report triggers ? What is their order of firing ?

A There are eight report triggers. Of these there are five global triggers called the Report Triggers. They are fired in the following order :

* Before Parameter Form

* After Parameter Form

* Before Report

* Between Pages

* After Report

7. Apart from the global report triggers what are the other triggers used in Oracle Reports?

Apart from the Five Global Report Triggers, there are three other types of triggers :

* Validation Triggers

* Format Triggers

* Action Triggers

8. What is Before Form Trigger? What is the importance of Before Form Trigger?

Before Form : It Fires before the Runtime Parameter Form is displayed. Using this trigger, you can access and change the values of parameters, PL/SQL global variables, and report-level columns. (Note : If the Runtime Parameter Form is suppressed, this trigger still fires. Consequently, you can use this trigger for validation of command line parameters).

9. What is After Form Trigger? What is the importance of After Form Trigger? 

After Form : It Fires after the Runtime Parameter Form is displayed. Using this trigger, you can access parameters and check their values. This trigger can also be used to change parameter values or, if an error occurs, return to the Runtime Parameter Form. Columns from the data model are not accessible from this trigger. (Note : If the Runtime Parameter Form is suppressed, the After Form trigger still fires. Consequently, you can use this trigger for validation of command line parameters or other data).

10. What is Before Report Trigger? What is the importance of Before Report Trigger? 

Before Report : Fires before the report is executed but after queries are parsed and data is fetched.

11. What is Between Pages Trigger? What is the importance of Between Pages Trigger?

Between Pages : Fires before each page of the report is formatted, except the very first page. This trigger can be used for customized page formatting. (Note : In the 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.)

12. What is After Report Trigger? What is the importance of After Report Trigger?

After Report : Fires after you exit the Previewer, or after report output is sent to a specified destination, such as a file, a printer, or an Oracle*Mail userid. This trigger can be used to clean up any initial processing that was done, such as deleting tables. Note, however, that this trigger always fires, whether or not your report completed successfully.

13. What is Validation Trigger? What is the importance of Validation  Trigger?

Validation Triggers : Validation Triggers are PL/SQL functions that are executed when parameter values are specified on the command line and when you accept the Runtime Parameter Form. (Notice that this means each Validation Trigger may fire twice when you execute the report). Validation Triggers are also used to validate the Initial Value of the parameter in the Parameter property sheet.

14. What is Format Trigger? What is the importance of Format Trigger?

Format Triggers : Format Triggers are PL/SQL functions executed before the object is formatted. The trigger can be used to dynamically change the formatting attributes of the object.

15. What is Action Trigger? What is the importance of Action Trigger?

Action Triggers : Action Triggers are PL/SQL procedures executed when a button is selected in the Previewer. The trigger can be used to dynamically call another report (drill down) or execute any other PL/SQL.

=========

1.What are the various values of the horizontal of vertical sizing property ?

The various values are :

Contract : Contract means the vertical size of the object decreases, if the formatted objects or data within it are short enough, but it cannot increase to a height greater than that shown in the editor. Note : Truncation of data may occur. (You can think of this option as meaning "only contract, do not expand.")

Expand : Expand means the vertical size of the object increases, if the formatted objects or data within it are tall enough, but it cannot decrease to a height less than that shown in the editor. (You can think of this option as meaning "only expand, do not contract.")

Fixed : Fixed means the height of the object is the same on each logical page, regardless of the size of the objects or data within it. Note : Truncation of data may occur. The height of the object is defined to be its height in the editor.

Variable : Variable means the object may expand or contract vertically to accommodate the objects or data within it (with no extra space), which means the height shown in the editor has no effect on the object's height at runtime.

2. How do you display a message in reports ?

Using SRW.Message

3. What are the various values of 'Print Panel Order' property of report ?

The various values are :

•Across/Down : Across/Down means the physical pages of the report body will print left-to-right then top-to-bottom.

•Down/Across : Down/Across means the physical pages of the report body will print top-to-bottom and then left-to-right.

4. What is the 'Print Direction' Property of Repeating frames ?

'Print Direction' Property specifies the direction in which successive instances of the repeating frame appear.

5. What are the various values of the 'Print Direction' Property of Repeating frames ?

Following are the various values :

•Across : Across means that each instance of the repeating frame subsequent to the first instance is printed to the right of the previous instance across the logical page.

•Across/Down : Across/Down means that each instance of the repeating frame subsequent to the first instance is printed to the right of the previous instance until an entire instance cannot fit between the previous instance and the right margin of the logical page. At that time, Oracle Reports prints the instance below the left-most instance on the logical page, provided there is enough vertical space left on the logical page for the instance to print completely.

•Down : Down means that each instance of the repeating frame subsequent to the first instance is printed below the previous instance down the logical page.

•Down/Across : Down/Across means that each instance of the repeating frame subsequent to the first instance is printed below the previous instance until an entire instance cannot fit inside the bottom margin of the logical page. At that time, Oracle Reports prints the instance to the right of the topmost instance on the logical page, provided there is enough horizontal space left on the logical page for the instance to print completely.

6. What is the 'Keep with Anchoring Object' object property ?

'Keep with Anchoring Object' object property indicates whether to keep an object and the object to which it is anchored on the same logical page. Checking Keep with Anchoring Object means that if the object, its anchoring object, or both cannot fit on the logical page, they will be moved to the next logical page.

7. What is 'Page Break Before' object property ?

'Page Break Before' object property indicates that you want the object to be formatted on the page after the page on which it is initially triggered to print. Note that this does not necessarily mean that all the objects below the object with Page Break Before will move to the next page.

8. What is 'Page Break After' object property ?

'Page Break After' object property indicates that you want all children of the object to be moved to the next page. In other words, any object that is a child object of an anchor (implicit or explicit) to this object will be treated as if it has Page Break Before set. Note that this does not necessarily mean that all the objects below the object with Page Break After will move to the next page.

9. What is the 'Break Order' property of columns ?

The 'Break Order' property is the order in which to display the column's values. This property applies only to columns that identify distinct values of user-created groups (i.e., break groups). The order of column values in a default group is determined by the ORDER BY clause of the query. For column values in user-created groups, however, you must use Break Order to specify how to order the break column's values.

10. What are the various types of links ?

The Data Link tool draws a link between a parent group and a child query. Creating a link is a drag and drop operation. Clicking and dragging from one column to another creates a link between those two columns (column to column link). Clicking and dragging from one query to another creates all possible links between columns selected by the queries based on database constraints (query to query link). Clicking and dragging between two groups creates a group-to-group link (i.e., a link with no columns).

11. Name some of the procedures in the SRW package ?

* SRW.Message, SRW.User_Exit, SRW.Do_Sql, SRW.Run_Report

12. What are the various report layout regions ?

 There are three report regions in the Layout editor :

• header

• body/margin

• trailer

13. In Brief Describe the various report layout regions?

Header : The report header pages appear once at the beginning of each report on a set of separate pages. They can contain text, graphics, data, and computations.

Body/Margin : The body/margin pages appear between the header and trailer pages, and are the bulk of the report. Each physical page in this section consists of a body and a margin. The body contains the majority of the report's text, graphics, data, and computations.

A top and bottom margin appear on each page, until all data within the body has been formatted. A margin may include text, graphics, page numbers, page totals, and grand totals. The default margin size is one half inch each for the top and bottom margins and zero for the left and right margins.

Trailer : The report trailer pages appear once at the end of each report on a set of separate pages. They can contain text, graphics, data, and computations.

14. What is more efficient : Maximum rows or Group Filter ?

Maximum Rows in the Query property sheet restricts the number of records fetched by the query. A group filter determines which records to include and which records to exclude. Since Maximum Rows actually restricts the amount of data retrieved, it is faster than a group filter in most cases. If you are using a Filter of Last or Conditional, Oracle Reports must retrieve all of the records in the group before applying the filter criteria. As a result, Maximum Rows or a Filter of First is faster.

===============

1. What is SRW Package? 

The Report builder Built in package know as SRW Package (Sql Report Writer) This package extends reports, Control report execution, output message at runtime, Initialize layout fields, Perform DDL statements used to create or Drop temporary table, Call User Exit, to format width of the columns, to page break the column, to set the colors Ex: SRW.DO_SQL, It’s like DDL command, we can create table, views, etc.

2. What is SRW package and some procedures in SRW?

It is the standard reports package and it has many procedures like USER_EXITS, DO_SQL, RUN_REPORT, MESSAGE,TRACE, BREAK, SET_ATTR.

3. Where in reports do you set the context information (like org_id)?

SRW.INIT

4. What is User Parameters?

Parameter, which is created by user. For to restrict values with where clause in select statement. We can use Lovs in  user parameter with static and Dynamic Select Statement.

5. What is System Parameters?

These are built-in parameters provided by Oracle corporation.

BACKGROUND: Is whether the report should run in the foreground or the background.

COPIES Is the number of report copies that should be made when the report is printed.

CURRENCY Is the symbol for the currency indicator (e.g., " $?).

DECIMAL Is the symbol for the decimal indicator (e.g., ".").

DESFORMAT Is the definition of the output device's format (e.g., landscape mode for a printer). This parameter is used when running a report in a character-mode environment, and when sending a bitmap report to a file (e.g. to create PDF or HTML output).

DESNAME Is the name of the output device (e.g., the file name, printer's name, mail userid).

DESTYPE Is the type of device to which to send the report output (screen, file, mail, printer, or

Screen using PostScript format).

MODE Is whether the report should run in character mode or bitmap.

ORIENTATION Is the print direction for the report (landscape, portrait, default).

PRINTJOB Is whether the Print Job dialog box should appear before the report is run.

THOUSANDS Is the symbol for the thousand's indicator (e.g., ",").

6. How can you print barcodes in oracle reports?

By installing the Barcode Font and using the Chart field in the Layout.

7. What are Format triggers?

Format triggers enable you to modify the display of objects dynamically at run time or to suppress display altogether for Headings, repeating frames, field and boilerplate object. Example:

i) To format the max (Sal) for particular department.

ii) To format the Sal column with a Dollar ($) prefix.

iii) To format Date formats….etc

8. What is Data Model?

Data Model is logical group of the Report Objects through query and Data model tools. Once query is compiled report automatically generates group. The queries build the groups and then Groups are used to populate the report. The only function of queries in report is to create the groups. The Report Editor's Data Model view enables you to define and modify the data model objects for a report. 

9. How do you call a concurrent program or another report from a report?

We can use FND_SUBMIT.REQUEST() to call a concurrent program from a report. Use SRW.RUN_REPORT() to run a report directly without registering it as a concurrent program.

10. What is Layout model?

Layout Model is to physically arrange Data model group objects on the Report. The Report Editor's Layout Model view enables you to define and modify the layout model objects for a report. In this view, objects and their property settings are represented symbolically to highlight their types and relationships.

11. What is Live Previewer? 

The Live Previewer is a work area in which you can preview your report and manipulate the actual or live data at the same time. In the Live Previewer you can customize reports interactively, meaning that you can see the results immediately as you make each change.

To activate buttons in the Live Previewer, you must display the report output in the Runtime Previewer. In order to edit your report, such as changing column size, move columns, align columns insert page numbers, edit text, change colors, change fonts set format masks, insert field the Live Previewer must be in Flex Mode.

12. What is Parameter Form 

The Parameter Form view is the work area in which you define the format of the report's Runtime Parameter Form. To do this, you define and modify parameter form objects (fields and boilerplate).When you run a report, Report Builder uses the Parameter Form view as a template for the Runtime Parameter Form. Fields and boilerplate appear in the Runtime Parameter Form exactly as they appear in the Parameter Form view. If you do not define a Runtime Parameter Form in the Parameter Form view, Report Builder displays a default Parameter Form for you at runtime.

13. What are user exits in reports and name a few?

User exits provided a way to pass control from Reports Builder to a program you have written, which performs some function, and then returns control to Reports Builder. Ex: SRW.DO_SQL, SRW.USER_EXIT.

14. What is Group?

Groups are created to organize the columns in your report. When you create a query, Report Builder automatically creates a group that contains the columns selected by the query. You create additional groups to produce break levels in the report, either manually or by using the Report Wizard to create a group above or group left report.

15. What is Template? 

Templates define common characteristics and objects that you want to apply to multiple reports. For example, you can define a template that includes the company logo and sets fonts and colors for selected areas of a report. 

Creation of Template: In Report editor , open a existing Template or Create a new Template and save it concerned directory. Then Edit CAGPREFS.ORA File , and Specify which type of Template are u going to develop.

Ex. Tabular, form, matrix Then give your developed template *.tdf file name.

Develop Report with Newly developed Template.

16. How can you display one record per page?

Set Repeating Frame Properties : Maximum records per page=1 And it will override group filter property.

18. What are the Non_query fields?

Following are the non_query fields: 

Aggregated Information, 

Calculated information, 

A string Function

19. How Can I highlight and change all the format masks and print conditions of a bunch of fields all at once?

If you want to highlight a bunch of objects then right click and select "properties", Oracle gives you a stacked set of the individual properties forms for each of the selected objects. While this may be useful for some objects, it requires changing values individually for each object. However, you can select the group of fields and then select "Common properties" from the "Tools" menu which will allow you to set the format mask , print conditions etc. for the whole set of objects at once.

===============

1.What is the difference between Conditional Formatting and format trigger?

Both provide the similar functionality. They are used to format the output based on particular conditions. Format triggers provide a wide variety of options when compared to conditional formatting(GUI). In format Triggers we have the option to write PL/SQL code where as conditional formatting is GUI based which provide limited options.

2.What is Flex mode and Confine mode?

Confine mode

On: child objects cannot be moved outside their enclosing parent objects.

Off: child objects can be moved outside their enclosing parent objects.

Flex mode:

On: parent borders "stretch" when child objects are moved against them. 

4. What is Page Break? 

To limit the records per page.

5. What is Page Protector property in oracle reports ?

The Page Protect property indicates whether to try to keep the entire object and its contents on the same logical page. Setting Page Protect to Yes means that if the contents of the object cannot fit on the current logical page, the object and all of its contents will be moved to the next logical page. Ex: if you set yes, the object information prints another page.

6.How do you mail the output of a report?

You can use following methogs to mail the output of a report. 

1. Use UTL_SMTP (refer to Scripts tab for more details) 

2. Use MAILX called in a shell script registered as a concurrent program with parameters File name and path.

7. What is Print Direction? 

The Print Direction property is the direction in which successive instances of the repeating frame appear.

8. What is Vertical and Horizental Elasticity

The Horizontal Elasticity property is determins how the horizontal size of the object will change at runtime to accommodate the objects or data within it:

9. What is Formula Column?

A formula column performs a user-defined computation on another column(s) data, including placeholder columns.

10. How do you display only one record on each page in a report?

Give Page Break in the Format trigger of the repeating frame.

11. What is Summary columns? 

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.

12. What is Boilerplate? 

Boilerplate is any text or graphics that appear in a report every time it is run. Report Builder will create one boilerplate object for each label selected in the Report Wizard (it is named B_

Column name). Also, one boilerplate object is sometimes created for each report summary. A boilerplate object is owned by the object surrounding it, unless otherwise noted.

13. What is Data Link

When we join multiple queries in a report the join condition is stored in the data link section

Data links relates 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.

14. What is Break Column? 

We can break a column through data model , it is Displayed once for a group

15. How can you grey out/ highlight /hide some records based on conditions in a report?

You can use Conditional formatting to achieve it.

16. How do u call Report from form? 

Use RUN_PRODUCT and RUN_REPORT_OBJECT to call a report from Oracle Forms.

17. What is Report Bursting?The capability of producing multiple copies of a given report or portion of it in different output formats is referred to as report bursting. Reports bursting offers you to deliver a single report to multiple destinations simultaneously. It offers you to create multiple reports out of one single report model. For example, you can create just one employee report for all your departments and send an email with a PDF-attachment containing the report to each manager. Each report would contain only the relevant department information for that particular manager. Using the reports bursting functionality will reduce the overhead since you will only have a single data fetch and report format.

18. What is Additional Layout?

Additional layout is created for two different formats using same query and groups without modifying default layout created by report wizard., we can use both layouts according to user requirement.

19. How do you write the report output to Excel file or text file?

You can use the following methods to write the output of oracle reports to Excel or text file. 

1.Use TEXT_IO package 

2.Use SPOOL in After Report trigger