Oracle Reports (Report 6i) – Technical – Interview – Question Answers


Oracle Reports (Report 6i) – Technical – Interview – Question Answers

Question: How many types of columns are there and what are they

Formula columns: For doing mathematical calculations and returning one value
Summary Columns: For doing summary calculations such as summations etc.
Place holder Columns: These columns are useful for storing the value in a variable

Question: Can u have more than one layout in report

Answer: It is possible to have more than one layout in a report by using the additional layout option in the layout editor.
Answer: Yes it is possible to run the report without parameter form by setting the PARAM value to Null

Question:  What is the lock option in reports layout

Answer:  By using the lock option we cannot move the fields in the layout editor outside the frame. This is useful for maintaining the fields.
Question: What is Flex

Answer: Flex is the property of moving the related fields together by setting the flex property on

Question: What is the minimum number of groups required for a matrix report

Question: How many different triggers are available in Report?

Answer: 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

Question: What is the Firing sequence of report trigger?

Answer: First the before parameter trigger will raise, after firing this trigger parameter form will displayed, after passing parameter after parameter trigger will fire query will parsed & then before report trigger will fired then if there are number of pages in your report then the between pages trigger will fired but it will fire between first & second & so on pages but it will not fired in reverse condition the after report trigger will fire after closing the runtime parameter form is closed.

Question: What is bind variables?

Answer: Bind variables are used in report 6i for replacing the single parameter in the select statement

Question: What is lexical parameter?

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

Question: What are different types of column in reports?

Answer: There are three types of columns in the report 6i these are:

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

2) Formula Column

3) Summary Column

Answer: The minimum of groups required for a matrix report are 4

Question: Can u run the report with out a parameter form

Oracle Application – Top useful SQL Queries


Friends, here some of quite useful regular sql queries in oracle applications.

Query 1: Select responsibility name along with application name

SELECT application_short_name ,

frt.responsibility_id, frt.responsibility_name

FROM apps.fnd_responsibility_tl frt , fnd_application fa

WHERE fa.application_id = frt.application_id;
 

Query 2: Get Menu name for Responsibility ID , You can find out responsibility_id from Query 1

SELECT DISTINCT a.responsibility_name, c.user_menu_name

FROM apps.fnd_responsibility_tl a,

apps.fnd_responsibility b,

apps.fnd_menus_tl c,

apps.fnd_menus d,

apps.fnd_application_tl e,

apps.fnd_application f

WHERE a.responsibility_id(+) = b.responsibility_id

AND a.responsibility_id = &resp_id

AND b.menu_id = c.menu_id

AND b.menu_id = d.menu_id

AND e.application_id = f.application_id

AND f.application_id = b.application_id

AND a.LANGUAGE = 'US';
 

Query 3: Get User name and related assigned responsibilities

SELECT distinct u.user_id, u.user_name user_name,

r.responsibility_name responsiblity,

a.application_name application

FROM fnd_user u,

fnd_user_resp_groups g,

fnd_application_tl a,

fnd_responsibility_tl r

WHERE g.user_id(+) = u.user_id

AND g.responsibility_application_id = a.application_id

AND a.application_id = r.application_id

AND g.responsibility_id = r.responsibility_id

order by 1;

Query 4: Get Request Group associate with Responsibility Name

SELECT responsibility_name responsibility, request_group_name,

frg.description

FROM fnd_request_groups frg, fnd_responsibility_vl frv

WHERE frv.request_group_id = frg.request_group_id

ORDER BY responsibility_name

Query 5: Gets Form personalization listing

Personalization is feature available in 11.5.10.X. For More detail on form Personalization Use Following Tables (Rule_id) is reference key for these tables

applsys.fnd_form_custom_actions, applsys.fnd_form_custom_scopes

SELECT ffft.user_function_name “User Form Name”, ffcr.SEQUENCE,

ffcr.description, ffcr.rule_type, ffcr.enabled, ffcr.trigger_event,

ffcr.trigger_object, ffcr.condition, ffcr.fire_in_enter_query

FROM fnd_form_custom_rules ffcr, fnd_form_functions_vl ffft

WHERE ffcr.ID = ffft.function_id

ORDER BY 1;

Query 6: Query to view the patch level status of all modules

SELECT a.application_name,

DECODE (b.status, ‘I’, ‘Installed’, ‘S’, ‘Shared’, ‘N/A’) status,

patch_level

FROM apps.fnd_application_vl a, apps.fnd_product_installations b

WHERE a.application_id = b.application_id;

Query 7: SQL to view all request who have attached to a responsibility

SELECT responsibility_name , frg.request_group_name,

fcpv.user_concurrent_program_name, fcpv.description

FROM fnd_request_groups frg,

fnd_request_group_units frgu,

fnd_concurrent_programs_vl fcpv,

fnd_responsibility_vl frv

WHERE frgu.request_unit_type = ‘P’

AND frgu.request_group_id = frg.request_group_id

AND frgu.request_unit_id = fcpv.concurrent_program_id

AND frv.request_group_id = frg.request_group_id

ORDER BY responsibility_name;

Query 8: SQL to view all requests who have attached to a responsibility

SELECT responsibility_name , frg.request_group_name,

fcpv.user_concurrent_program_name, fcpv.description

FROM fnd_request_groups frg,

fnd_request_group_units frgu,

fnd_concurrent_programs_vl fcpv,

fnd_responsibility_vl frv

WHERE frgu.request_unit_type = ‘P’

AND frgu.request_group_id = frg.request_group_id

AND frgu.request_unit_id = fcpv.concurrent_program_id

AND frv.request_group_id = frg.request_group_id

ORDER BY responsibility_name;

Query 9: SQL to view all types of request Application wise

SELECT fa.application_short_name,

fcpv.user_concurrent_program_name,

description,

DECODE (fcpv.execution_method_code,

‘B’, ‘Request Set Stage Function’,

‘Q’, ‘SQL*Plus’,

‘H’, ‘Host’,

‘L’, ‘SQL*Loader’,

‘A’, ‘Spawned’,

‘I’, ‘PL/SQL Stored Procedure’,

‘P’, ‘Oracle Reports’,

‘S’, ‘Immediate’,

fcpv.execution_method_code

) exe_method,

output_file_type, program_type, printer_name,

minimum_width,

minimum_length, concurrent_program_name,

concurrent_program_id

FROM fnd_concurrent_programs_vl fcpv, fnd_application fa

WHERE fcpv.application_id = fa.application_id

ORDER BY description

Query 10: SQL to view concurrent request processing time, quite useful

SELECT f.request_id , pt.user_concurrent_program_name user_concurrent_program_name

, f.actual_start_date actual_start_date

, f.actual_completion_date actual_completion_date,

floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)

|| ‘ HOURS ‘ ||

floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) –

floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)

|| ‘ MINUTES ‘ ||

round((((f.actual_completion_date-f.actual_start_date)*24*60*60) –

floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600 –

(floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) –

floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)*60) ))

|| ‘ SECS ‘ time_difference

,

DECODE(p.concurrent_program_name,’ALECDC’,p.concurrent_program_name||'[‘||f.description||’]’,p.concurrent_program_name) concurrent_program_name

, decode(f.phase_code,’R’,’Running’,’C’,’Complete’,f.phase_code) Phase

, f.status_code

FROM apps.fnd_concurrent_programs p

, apps.fnd_concurrent_programs_tl pt

, apps.fnd_concurrent_requests f

WHERE f.concurrent_program_id = p.concurrent_program_id

and f.program_application_id = p.application_id

and f.concurrent_program_id = pt.concurrent_program_id

and f.program_application_id = pt.application_id

AND pt.language = USERENV(‘Lang’)

and f.actual_start_date is not null

ORDER by f.actual_completion_date-f.actual_start_date desc;

Oracle SQL / PLSQL Developer – Technical Question Answers – Part 5


 

 

Question: What are the Pct Free and Pct Used


Answer:  Pct Free is used to denote the percentage of the free space that is to be left when creating a table. Similarly Pct Used is used to denote the percentage of the used space that is to be used when creating a table eg.:: Pctfree 20, Pctused 40 
 
 

 

 
 
 
 

 

Question: What is Row Chaining

Answer: The data of a row in a table may not be able to fit the same data block.Data for row is stored in a chain of data blocks. 
 
 

 

 
 
 
 

 

Question: What is a 2 Phase Commit
Answer: Two Phase commit is used in distributed data base systems. This is useful to maintain the integrity of the database so that all the users see the same values. It contains DML statements or Remote Procedural calls that reference a remote object. There are basically 2 phases in a 2 phase commit.
a) Prepare Phase :: Global coordinator asks participants to prepare
b) Commit  Phase ::

Commit all participants to coordinator to Prepared, Read only or abort Reply 
 
 

 

Question: What is the difference between  deleting and truncating of tables
 
 
 

 

Answer:  Deleting   a table will not remove the rows from the table but entry is there in  the database dictionary and it can be retrieved But truncating a table deletes it completely and it cannot be retrieved. 
 
 

 

 
 
 
 

 

Question: What are mutating tables

Answer: When a table is in state of transition it is said to be mutating eg :: If a row has been deleted then the table is said to be mutating and no operations can be done on the table except select. 
 
 

 

 
 
 
 

 

Question:  What are Codd Rules
Answer:  Codd Rules describe the ideal nature of a RDBMS. No RDBMS satisfies all the 12 codd rules and Oracle Satisfies 11 of the 12 rules and is the only Rdbms to satisfy the maximum number of rules.

Question: What is the Difference between a post query and a pre query

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

 

 
 
 
 

 

Question:  Deleting the Duplicate rows in the table

Answer: We can delete the duplicate rows in the table by using the Rowid 
 
 

 

 
 
 
 

 

Question: Can U disable database trigger? How?
Answer: Yes. With respect to table
     ALTER TABLE TABLE
     [   DISABLE all_trigger ]


Question: What is pseudo columns ? Name them?
Answer:  A pseudocolumn behaves like a table column, but is not actually stored in the table.  You can select from pseudocolumns, but you cannot insert, update, or delete their values. 

   * CURRVAL
    * NEXTVAL
    * LEVEL
    * ROWID
    * ROWNUM 
 
Question:  How many columns can table have?

 

 

 

 
 

 

 
 

 

 
 
 

 

Answer: The number of columns  in a table can range from 1 to 254. 
 
 

 

 
 
 
Question:  What is clustered index?

 

Answer:  In an indexed cluster, rows are stored together based on their cluster key values. Can not apply for HASH. 
 
 

 

 
 
 
Question:  What are attributes of cursor?

 

Answer:    %FOUND, %NOTFOUND, %ISOPEN, %ROWCOUNT 
 
 

 

 
 
 
Question: Can you use select in FROM clause of SQL select?
Answer:  Yes.

 

How to insert & character or special character into Database using SQL*Plus.


Solution 1:

If you are not using substitution variables (&1 &2 &3 etc.) you can do a “SET DEFINE OFF” or “SET SCAN OFF” to turn off the definition of the ampersand as a substitution variable.

 Example:
SQL> SET DEFINE OFF
SQL> INSERT INTO <table_name> VALUES (‘AT&T’);
1 row created

Solution 2:
If you are using substitution variables, set the substitution character to one which will not be encountered when inserting data.
Example:
SQL> SET DEFINE %
SQL> INSERT INTO <temp_table> VALUES (‘AT&T’)
/
1 row created.

Solution 3:
If you precede the special character with a backslash and the ESCAPE setting in SQL*Plus is set to ON, then SQL*Plus will understand the special character following the escape symbol is to be treated as a regular character.
Example:
SQL>set escape on
SQL>show escape
escape “\” (hex 5c)
SQL> INSERT INTO temp_table VALUES (‘select * from emp where ename = \&
1′);
1 row created.

Oracle Database related Questions


Here there are very basic still sometime become critical to know concept if you working in Oracle Database related activities. 

Q: What is an Oracle instance?Every running Oracle database is associated with an Oracle instance. When adatabase is started on a database server (regardless of the type of computer),Oracle allocates a memory area called the System Global Area (SGA) and starts one or more Oracle processes. This combination of the SGA and the Oracle processes is called an Oracle instance. The memory and processes of an instance manage the associated database’s data efficiently and serve the one or multiple users of the database.
The Instance and the Database

 
After starting an instance, Oracle associates the instance with the specified database. This is called mounting the database. The database is then ready to be opened, which makes it accessible to authorized users. Multiple instances can execute concurrently on the same computer, each accessing its own physical database. In clustered and massively parallel systems (MPP),the Oracle Parallel Server allows multiple instances to mount a single database. Only the database administrator can start up an instance and open the database.If a database is open, the database administrator can shut down the database so that it is closed. When a database is closed, users cannot access the information that it contains. Security for database startup and shutdown is controlled via connections to Oracle with administrator privileges. Normal users do not have control over the current status of an Oracle database.

 

 

 

Q: What is a view?

A view is a tailored presentation of the data contained in one or more tables(or other views). Unlike a table, a view is not allocated any storage space, nor does a view actually contain data; rather, a view is defined by a query that extracts or derives data from the tables the view references. These tables are called base tables. Views present a different representation of the data that resides within thebase tables. Views are very powerful because they allow you to tailor the presentation of data to different types of users. Views are often used to:

• provide an additional level of table security by restricting access to a predetermined set of rows and/or columns of a table

• hide data complexity

• simplify commands for the user

• present the data in a different perspective from that of the base table

• isolate applications from changes in definitions of base tables

• express a query that cannot be expressed without using a view

Q: What is referential integrity?
 

 

Rules governing the relationships between primary keys and foreign keys of tables within a relational database that determine data consistency. Referential integrity requires that the value of every foreign key in every table be matched by the value of a primary key in another table.

Q: What is a cursor?A cursor is a private sql work area used to perform manipulations on data using pl\sql, mainly used for multiple row manipulations and locking columns. Data which is populated into the cursor is known as active dataset.

 

Cursors are of two types 1.implicit   2.explicit

Implicit———attributes or properties for implicit cursor

1.sql%is open:attribute returns a boolean value stating wether the cursor is open or closed.

2.sql % found: returns boolean value stating whether the record is found in the cursor.

3.sql%notfound : returns a boolean value stating whether the record is not found in the cursor

4.sql %rowcount :returns a pneumeric value stating no.of rows executed in the cursor.

Explicit cursors—retrives multiple rows, users can perform locks on th data in the cursor attributes-

1.% is open

2.% found

3.% not found

4.% rowcount

WE CAN WRITE TWO CURSORS IN ONE PROGRAM

WE CAN WRITE A CURSOR SPECIFYING PARAMETERS

CURSOR WITH UPDATE CLAUSE IS USED TO PERFORM LOCKS ON DATA.

Q: Why Use Sql* Loader in Oracle Database? 
The Sql Loader utility loads data into an existing ORACLE table from an external files.

 

 

Oracle Technical Interview Questions and Answers : SQL based questions


 

Oracle Technical Interview Questions and Answers : SQL based questions

 

1. To see current user name

Sql> show user;

2. Change SQL prompt name

SQL> set sqlprompt “Start > “

Start >

 

3. Switch to DOS prompt

SQL> host

 

4. How do I eliminate the duplicate rows ?

SQL> delete from table_name where rowid not in (select max(rowid) from table group by

duplicate_values_field_name);

or

SQL> delete duplicate_values_field_name dv from table_name ta where rowid <(select min(rowid) from

table_name tb where ta.dv=tb.dv);

Example.

Table Emp

Empno Ename

101 Scott

102 Jiyo

103 Millor

104 Jiyo

105 Smith

delete ename from emp a where rowid < ( select min(rowid) from emp b where a.ename = b.ename);

The output like,

Empno Ename

101 Scott

102 Millor

103 Jiyo

104 Smith

 

5. How do I display row number with records?

To achive this use rownum pseudocolumn with query, like SQL> SQL> select rownum, ename from emp;

Output:

1 Scott

2 Millor

3 Jiyo

4 Smith

 

6. Display the records between two range

select rownum, empno, ename from emp where rowid in

(select rowid from emp where rownum <=&upto

minus

select rowid from emp where rownum<&Start);

Enter value for upto: 10

Enter value for Start: 7

ROWNUM EMPNO ENAME

——— ——— ———-

1 7782 CLARK

2 7788 SCOTT

3 7839 KING

4 7844 TURNER

 

7. I know the nvl function only allows the same data type(ie. number or char or date Nvl(comm, 0)), if commission is null then the text “Not Applicable” want to display, instead of blank space. How do I write the query?

SQL> select nvl(to_char(comm.),’NA’) from emp;

Output :

NVL(TO_CHAR(COMM),’NA’)

———————–

NA

300

500

NA

1400

NA

NA

 

8. Oracle cursor : Implicit & Explicit cursors

Oracle uses work areas called private SQL areas to create SQL statements. PL/SQL construct to identify each and every work are used, is called as Cursor. For SQL queries returning a single row, PL/SQL declares all implicit cursors. For queries that returning more than one row, the cursor needs to be explicitly declared.

 

9. Explicit Cursor attributes

There are four cursor attributes used in Oracle cursor_name%Found, cursor_name%NOTFOUND, cursor_name%ROWCOUNT, cursor_name%ISOPEN

 

10. Implicit Cursor attributes

Same as explicit cursor but prefixed by the word SQL

SQL%Found, SQL%NOTFOUND, SQL%ROWCOUNT, SQL%ISOPEN

Tips : 1. Here SQL%ISOPEN is false, because oracle automatically closed the implicit cursor after

executing SQL statements.

: 2. All are Boolean attributes.

 

11. Find out nth highest salary from emp table

SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B

WHERE a.sal<=b.sal);

Enter value for n: 2

SAL

———

3700

 

12. To view installed Oracle version information

SQL> select banner from v$version;

 

13. Display the number value in Words

SQL> select sal, (to_char(to_date(sal,’j’), ‘jsp’))

from emp;

the output like,

SAL (TO_CHAR(TO_DATE(SAL,’J’),’JSP’))

——— —————————————————–

800 eight hundred

1600 one thousand six hundred

1250 one thousand two hundred fifty

If you want to add some text like,

Rs. Three Thousand only.

SQL> select sal “Salary “,

(‘ Rs. ‘|| (to_char(to_date(sal,’j’), ‘Jsp’))|| ‘ only.’))

“Sal in Words” from emp

/

Salary Sal in Words

——- ——————————————————

800 Rs. Eight Hundred only.

1600 Rs. One Thousand Six Hundred only.

1250 Rs. One Thousand Two Hundred Fifty only.

 

14. Display Odd/ Even number of recordsOdd number of records:

 

select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);

1

3

5

Even number of records:

select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp)

2

4

6

 

15. Which date function returns number value?

months_between

 

16. Any three PL/SQL Exceptions?Too_many_rows, No_Data_Found, Value_Error, Zero_Error, Others

 

 

17. What are PL/SQL Cursor Exceptions?Cursor_Already_Open, Invalid_Cursor

 

 

18. Other way to replace query result null value with a text

SQL> Set NULL ‘N/A’

to reset SQL> Set NULL ‘’

 

19. What are the more common pseudo-columns?

SYSDATE, USER , UID, CURVAL, NEXTVAL, ROWID, ROWNUM

 

20. What is the output of SIGN function?1 for positive value,

 

0 for Zero,

-1 for Negative value.

 

21. What is the maximum number of triggers, can apply to a single table?

12 triggers.

 

Thanks – Shivmohan Purohit

Discoverer SQL: Long Running Discoverer Report


Discoverer SQL: Long Running Discoverer Report

Here is a small SQL quite useful to work with discoverer to identify long running reports and start looking into the reason / troubleshooting.

SELECT q.qs_doc_name Worksheet,

 q.qs_doc_details Sheet ,

 q.qs_num_rows Lines,

 QS_ACT_CPU_TIME CPU_Time,

 q.qs_act_elap_time Seconds,

 FLOOR(q.qs_act_elap_time/60) || ‘.’ || MOD(FLOOR(q.qs_act_elap_time), 60) minutes, 

 q.qs_created_date Executed,

 q.QS_DOC_OWNER Owner,

 u.user_name APPS_User,

CASE

WHEN q.qs_state = 0 THEN ‘ERROR :CANCELLED’

WHEN q.qs_state = 1 THEN ‘RUNNING’

WHEN q.qs_state = 2 THEN ‘COMPLETED’

END “CURRENT STATUS”

  FROM discovr_apps.eul5_qpp_stats q,

 fnd_user u

 WHERE TRUNC(qs_created_date) >= TRUNC(SYSDATE)-1

AND TO_NUMBER(SUBSTR(q.qs_created_by,2,8)) = u.user_id

–AND u.user_name LIKE ‘D%’

–AND q.qs_state =1

  –AND FLOOR(q.qs_act_elap_time/60) >= 30 

  ORDER BY q.qs_act_elap_time DESC;

Thanks – Shivmohan Purohit