oracle interviews21

Question: How will you migrate Oracle General Ledger Currencies and Sets of Books Definitions fromone environment to another without reKeying? Will you use FNDLOAD?
Answer: FNDLOAD can not be used in the scenario. You can use migrator available in "Oracle iSetup" Responsibility

Question: This is a very tough one, almost impossible to answer, but yet I will ask. Which Form in Oracle Applications has most number of Form Functions?
Answer: "Run Reports". And why not, the Form Function for this screen has a parameter to which we pass name of the "Request Group", hence securing the list of Concurrent Programs that are visible in "Run Request" Form. Just so that you know, there are over 600 form functions for "Run Reports"

Question: Which responsibility do you need to extract Self Service Personalizations?
Answer:Functional Administrator

Question: Can you list any one single limitation of Forms Personalization feature that was delivered with 11.5.10
Answer:You can not implement interactive messages, i.e. a message will give multiple options for Response. The best you can get from Forms Personalization to do is popup up Message with OK option.

Question: You have just created two concurrent programs namely "XX PO Prog1" & "XX PO Prog2". Now you wish to create a menu for Concurrent Request submission such that only these two Concurrent Programs are visible from that Run Request menu. Please explain the steps to implement this?
Answer:
a) Define a request group, lets say with name "XX_PO_PROGS"
b) Add these two concurrent programs to the request group "XX_PO_PROGS"
c) Define a new Form Function that is attached to Form "Run Reports"
d) In the parameter field of Form Function screen, enter
REQUEST_GROUP_CODE="XX_PO_PROGS" REQUEST_GROUP_APPL_SHORT_NAME="XXPO" TITLE="XXPO:XX_PO_PROGS"
e) Attach this form function to the desired menu.


Question: Does Oracle 10g support rule based optimization?
Answer: The official stance is that RBO is no longer supported by 10g.


Question: Does oracle support partitioning of tables in Oracle Apps?
Answer: Yes, Oracle does support partitioning of tables in Oracle Applications. There are several implementations that partition on GL_BALANCES. However your client must buy licenses to if they desire to partition tables. To avoid the cost of licensing you may suggest the clients may decide to permanently close their older GL Periods, such that historical records can be archived.
Note: Before running the archival process the second time, you must clear down the archive table GL_ARCHIVE_BALANCES (don’t forget to export archive data to a tape).


Question: What will be your partitioning strategy on GL_BALANCES? Your views please?
Answer: This really depends upon how many periods are regularly reported upon, how many periods are left open etc. You can then decide to partition on period_name, or period ranges, or on the status of the GL Period.


Question: Does Oracle support running of gather stats on SYS schema in Oracle Apps?
Answer: If your Oracle Applications instance is on 10g, then you can decide to run stats for SYS schema. This can be done by exec dbms_stats.gather_schema_stats('SYS');
Alternately using command dbms_stats.gather_schema_stats('SYS',cascade=>TRUE,degree=>20);
I will prefer the former with default values.
If you wish to delete the stats for SYS use exec dbms_stats.delete_schema_stats('SYS');
You can schedule a dbms_job for running stats for SYS schema.


Question: Can you use concurrent program "Gather Schema Statistics" to gather stats on sys schema in oracle apps?
Answer: No, "Gather Schema Statistics" has no parameters for SYS schema. Please use dbms_job.


Question: Which table is used to provide drill down from Oracle GL into sub-ledger?
Answer: GL_IMPORT_REFERENCES

Question: What is the significance of profile option “Node Trust Level” in Oracle Apps.
Answer: If this profile option is set to a value of external against a server, then it signifies that the specific mid-tier is External i.e. it will be exposed to the www. In other words this server is not within the firewall of your client. The idea behind this profile option is to flag such middle-tier so that special restrictions can be applied against its security, which means a very restricted set of responsibilities will be available from such Middle-Tier.


Question: What is the significance of profile option “Responsibility Trust Level”.
Answer: In order to make a responsibility accessible from an external web tier, you must set profile option “Responsibility Trust Level” at responsibility level to “External”. Only those responsibilities that have this profile option against them will be accessible from External Middle tiers.


Question: What else can you suggest to restrict the access to screens from external web tiers?
Answer: You may use URL filtering within Apache.


Question: What is the role of Document Manager in Oracle Purchasing?
Answer: POXCON is an immediate concurrent program. It receives pipe signal from the application when a request is made for approval/reservations/receipts.


Question: How to debug a document manager in Oracle Apps?
Answer: Document manger runs within the concurrent manager in Oracle Applications. When an application uses a Document Manager, it sends a pipe signal which is picked up by the document manager.
There are two mechanisms by which to trace the document manager
1. Set the debugging on by using profile option
STEP 1. Set profile option "Concurrent:Debug Flags" to TCTM1
This profile should only generate debugs when set at Site level(I think, as I have only tried site), because Document Manager runs in a different session.
STEP 2. Bounce the Document Managers
STEP 3. Retry the Workflow to generate debugs.
STEP 4. Reset profile option "Concurrent:Debug Flags" to blank
STEP 5. have a look at debug information in table fnd_concurrent_debug_info

2. Enable tracing for the document managers
This can be done by setting profile option “Initialization SQL Statement – Custom” against your username before reproducing the issue. The value of this profile will be set so as to enable trace using event 10046, level 12.


Question: You have written a Java Concurrent Program in Oracle Apps. You want to modify the CLASSPATH such that new class CLASSPATH is effective just for this program.
Answer: In the options field of the concurrent program you can enter something similar to below.
-cp :/home/xxvisiondev/XXDEVDB/comn/java/appsborg.zip:/home/xxvisiondev/XXDEVDB/comn/java


Question: How will you open a bc4j package in jdeveloper?
Answer: Oracle ships a file named server.xml with each bc4j package. You will need to ftp that file alongside other bc4j objects(VO’s, EO’s, AM, Classes etc).
Opening the server.xml will load the complete package starting from AM(application module). This is a mandatory step when building Extensions to framework.


Question: In OA Framework Self-Service screen, you wish to disable a tab. How will you do it?
Answer: Generally speaking, the tabs on a OA Framework page are nothing but the SubMenus. By entering menu exclusion against the responsibility, you can remove the tab from self service page.

Question: In self service, you wish to change the background color and the foreground text of the OA Framework screens to meet your corporate standards. How will you do it?
Answer: You will need to do the below steps
a….Go to Mid Tier, and open $OA_HTML/cabo/styles/custom.xss
b…Enter below text( change colours as needed)


c… cd $OA_HTML/cabo/styles/cache
d…Take a backup of all the css files.
e…Delete all the files of following pattern oracle-desktop*.css
The idea here is to delete the cache. Next time when you logon to Oracle Apps Self Service, the Framework will rebuild the css file if found missing for your browser.


Question: Can you extend and substitue a root AM ( Application Module) in OA Framework using JDeveloper.
Answer: You can extend the AM in jDeveloper, but it doesn’t work( at least it didn’t work in 11.5.9). I am hopeful that Oracle will deliver a solution to this in the future.

Question: In a workflow notification, you have a free text response field where the user enters the Vendor Number for the new vendor. You want to validate the value entered in the notification response field upon the submission of a response. How will you do it?
Answer: You will need to attach a post notification function to the Workflow Notification.
The PL/SQL code will look similar to below:-
The below code will display an error in the notification when user attempts to create a Duplicate Vendor Number.
PROCEDURE validate_response_from_notif
(
itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,RESULT IN OUT VARCHAR2
) IS
l_nid NUMBER;
l_activity_result_code VARCHAR2(200);
v_newly_entered_vendor_num VARCHAR2(50);
CURSOR c_get_response_for_new_vendor IS
SELECT wl.lookup_code
FROM wf_notification_attributes wna
,wf_notifications wn
,wf_message_attributes_vl wma
,wf_lookups wl
WHERE wna.notification_id = l_nid
AND wna.notification_id = wn.notification_id
AND wn.message_name = wma.message_name
AND wn.message_type = wma.message_type
AND wna.NAME = wma.NAME
AND wma.SUBTYPE = 'RESPOND'
AND wma.format = wl.lookup_type
AND wna.text_value = wl.lookup_code
AND wma.TYPE = 'LOOKUP'
AND decode(wma.NAME, 'RESULT', 'RESULT', 'NORESULT') = 'RESULT';
BEGIN
IF (funcmode IN ('RESPOND'))
THEN
l_nid := wf_engine.context_nid;
OPEN c_get_response_for_new_vendor;
FETCH c_get_response_for_new_vendor
INTO l_activity_result_code;
CLOSE c_get_response_for_new_vendor;
v_newly_entered_vendor_num := wf_notification.getattrtext(l_nid,'NEWLY_ENTERED_VENDOR_NUM_4_PO');
IF l_activity_result_code = 'NEW_VENDOR'
AND does_vendor_exist(p_vendor => v_newly_entered_vendor_num)
THEN
RESULT := 'ERROR: VendorNumber you entered already exists';
RETURN;
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
RESULT := SQLERRM;
END validate_response_from_notif;


Question: How to make concurrent program end with warning?
Answer: If the concurrent program is of type PL/SQL, you can assign a value of 1 to the “retcode” OUT Parameter.
For a Java Concurrent program, use the code similar to below
ReqCompletion lRC;
//get handle on request completion object for reporting status
lRC = pCpContext.getReqCompletion();
lRC.setCompletion(ReqCompletion.WARNING, "WARNING");


Question: How do you link a Host type concurrent program to Concurrent Manager?
Answer: Assuming your executable script is LOADPO.prog, then use the commands below
cd $XXPO_TOP/bin
ln -s $FND_TOP/bin/fndcpesr $XXPO_TOP/bin/LOADPO


Question: How do you know if a specific Oracle patch has been applied in apps to your environment.
Answer: Use table ad_bugs, in which column bug_number is the patch number.
SELECT bug_number
,to_char(creation_date, 'DD-MON-YYYY HH24:MI:SS') dated
FROM apps.ad_bugs
WHERE bug_number = TRIM('&bug_number') ;


Question: How do you send a particular Oracle Apps Workflow Activity/Function within a workflow process into background mode.
Answer: If cost of the workflow activity is greater than 50, then the workflow activity will be processed in background mode only, and it won’t be processed in online mode.

Question: What are the various ways to kick-off a workflow
Answer: You can eiter use wf_engine.start_process or you can attach a runnable process such ghat it subscribes to a workflow event.

Question: When starting (kicking off) an oracle workflow process, how do you ensure that it happens in a background mode?
--a)if initiating the process using start_process, do the below
wf_engine.threshold := -1;
wf_engine.createprocess(l_itemtype
,l_itemkey
,'');
wf_engine.startprocess(l_itemtype, l_itemkey)
--B) When initiating the workflow process through an event subscription, set the Execution Condition Phase to be equal to or above 100 for it to be executed by background process.


Question: On 10g, how will you use awr?
Answer: By running below scripts. These are both the same scripts, but with differing parameters.
$ORACLE_HOME/rdbms/admin/awrrpt.sql
$ORACLE_HOME/rdbms/admin/awrrpti.sql

Question : How will you configure Apache to run in Debug mode, specifically usefull when debugging iProcurement ( prior to 11.5.10).
Answer: After 11.5.10, FND Logging can be used for debugging Oracle iProcurement.
Prior to 11.5.10
----STEPS IN A NUTSHELL-----
cd $ORACLE_HOME/../iAS/Apache
vi $ORACLE_HOME/../iAS/Apache/Jserv/etc/ssp_init.txt
DebugOutput=/home/<>/ora9/iAS/Apache/Apache/logs/debug.log
DebugLevel=5
DebugSwitch=ON

vi $ORACLE_HOME/../iAS/Apache/Jserv/etc/jserv.conf
ApJServLogLevel debug

vi $ORACLE_HOME/../iAS/Apache/Jserv/etc/jserv.properties
log=true


Question: How will you add a new column to a List Of Values ( LOV ) in Oracle Applications Framework? Can this be done without customization?
Answer: Yes, this can be done without customization, i.e. by using OA Framework Extension coupled with Personalization. Implement the following Steps :-
a) Extend the VO ( View Object ), to implement the new SQL required to support the LOV.
b) Substitute the base VO, by using jpximport [ similar to as explained in Link ]
c) Personalize the LOV Region, by clicking on Add New Item. While adding the new Item, you will cross reference the newly added column to VO.


Question: Can you do fnd_request.submit_request from SQL Plus in Oracle?
Answer: You will need to initialize the global variables first using fnd_global.initialize
DECLARE
v_session_id INTEGER := userenv('sessionid') ;
BEGIN
fnd_global.initialize
(
SESSION_ID => v_session_id
,USER_ID =>
,RESP_ID =>
,RESP_APPL_ID =>
,SECURITY_GROUP_ID => 0
,SITE_ID => NULL
,LOGIN_ID => 3115003--Any number here
,CONC_LOGIN_ID => NULL
,PROG_APPL_ID => NULL
,CONC_PROGRAM_ID => NULL
,CONC_REQUEST_ID => NULL
,CONC_PRIORITY_REQUEST => NULL
) ;
commit ;
END ;
/
Optionally you may use fnd_global.apps_initialize, which internally calls fnd_global.initialize
fnd_global.apps_initialize(user_id => :user_id,
resp_id => :resp_id,
resp_appl_id => :resp_appl_id,
security_group_id => :security_group_id,
server_id => :server_id);
By doing the above, your global variables upon which Concurrent Managers depend upon will be populated. This will be equivalent to logging into Oracle Apps and submitting the concurrent request from a responsibility.

Question: You are told that the certain steps in the Oracle Apps Form/Screen are running slow, and you are asked to tune it. How do you go about it.
Answer: First thing to do is to enable trace. Preferably, enable the trace with Bind Variables. This can be done by selecting menu Help/Diagnostics/Trace/”Trace With Binds and Wait”
Internally Oracle Forms issues a statement similar to below:-
alter session set events='10046 trace name context forever, level 12' ;
Enable Trace with Bind Variables in Apps
Enable Trace with Bind Variables in Apps

This will enable the trace with Bind Variable values being shown in the trace file.
The screen in Oracle Apps will also provide the name of the trace file which is located in directly identified by
select value from v$parameter where name like '%us%r%dump%'
Doing a tkprof with explain plan option, reviewing plans and stats in trace file can help identify the slow performing SQL.




Question: What is the difference between running Gather Stats and “Program – Optimizer[RGOPTM]” in Oracle General Ledger?
Answer: “Gather Stats” will simply gather the stats against existing tables, indexes etc. However Gather Stats does not create any new indexes. But “Program – Optimizer[RGOPTM]” can create indexes on GL_CODE_COMBINATIONS, provided accounting segment has the indexed flag enabled,


Question: You have written a piece of code in POR_CUSTOM_PKG for Oracle iProcurement, but its not taking any effect? What may be the reason?
Answer: Depending upon which procedure in POR_CUSTOM_PKG has been programmed, one or more of the below profile options must be set to Yes
POR: Enable Req Header Customization
POR: Enable Requisition Line Customization
POR: Enable Req Distribution Customization


Question: What is the key benefit of punching out to suppliers catalogs rather than loading their catalogs locally in Oracle iProcurement?
Answer: Punchout has several advantages like, Catalogs don’t need to be loaded locally saves space on your system. You can get up-to-date list of catalogs by punching out and also you get the benefit of up-to-date pricing information on vendor items.


Question: Does oracle have a test environment on exchange?
Answer: http://testexchange.oracle.com


Question: Does Oracle Grants use its own schema or does it uses Oracle Project Accounting schema?
Answer: Although Oracle Grants has its own schema i.e. GMS, it reuses many of the tables with in Oracle Projects Schema like PA_PROJECTS_ALL, PA_EXPENDITURE_ITEMS_ALL, PA_EXPENDITURE_TYPES etc.


Question: How to make an Oracle Report Type concurrent program produce an excel friendly output?
Answer: Comma can be concatenated between the column values, however a better option is to create tab delimited file, as it takes care of commas within the string.
For this, use SQL similar to below in the report
select 'a' || chr(9) || 'b' from dual;


Question: What are the settings needed for printing bitmap reports?
Answer: Get your DBA to configure two files i.e. uiprint.txt & default.ppd
For details, refer to Metalink Note 189708.1


Question: For a PL/SQL based concurrent program do you have to issue a commit at the end?
Answer: The concurrent program runs within its own new session. In APPS, the default database setting enforces a commit at the end of each session. Hence no explicit COMMIT is required.


Question: What is the best way to add debugging to the code in apps?
Answer: Use fnd_log.string , i.e. FND Logging. Behind the scenes Oracles FND Logging uses autonomous transaction to insert records in a table named fnd_log_messages.
For example
DECLARE
BEGIN
fnd_log.STRING(log_level => fnd_log.level_statement
,module => 'xxxx ' || 'pkg/procedurename '
,message => 'your debug message here');
END ;
Three profile options effecting FND Logging are
FND: Debug Log Mode
FND: Debug Log Enabled
FND: Debug Log Module


Question: If you wish to trigger of an update or insert in bespoke table or take some action in response to a TCA record being created or modified, how would you do it? Will you write a database triggers on TCA Tables?
Answer: There are various pre-defined Events that are invoked from the Oracle TCA API’s.
TCA was Oracle’s first initiative towards a fully API based approach, which means the screen and the processes all use the same set of APIs for doing same task.
In order to take an action when these events occur, you can subscribe a custom PL/SQL procedure or a Custom Workflow to these events. Some of the important TCA events are listed below:-
oracle.apps.ar.hz.ContactPoint.update
oracle.apps.ar.hz.CustAccount.create
oracle.apps.ar.hz.CustAccount.update
oracle.apps.ar.hz.CustAcctSite.create
oracle.apps.ar.hz.CustAcctSite.update
oracle.apps.ar.hz.CustAcctSiteUse.create
oracle.apps.ar.hz.CustAcctSiteUse.update
oracle.apps.ar.hz.Location.create
oracle.apps.ar.hz.Location.update
oracle.apps.ar.hz.Organization.create
oracle.apps.ar.hz.Organization.update
oracle.apps.ar.hz.PartySite.create
oracle.apps.ar.hz.PartySite.update
oracle.apps.ar.hz.PartySiteUse.create
oracle.apps.ar.hz.PartySiteUse.update
oracle.apps.ar.hz.Person.create
oracle.apps.ar.hz.Person.update


Question: In Oracle OA Framework, is the MDS page/document definition stored in database or in the file system?
Answer: The MDS document details are loaded into database, in the following sets of tables.
JDR_ATTRIBUTES
JDR_ATTRIBUTES_TRANS
JDR_COMPONENTS
JDR_PATHS
The Document is loaded via XMLImporter, as detailed in XMLImporter Article


Question: In a Oracle Report data group, you have a “data link” between two queries. How do you ensure that the data link is made Outer Joined?
Answer: The data link is an Outer Join by default.


Question: How does substitution work in OA Framework?
What are the benefits of using Substitution in OA Framework?
Answer: Based on the user that has logged into OA Framework, MDS defines the context of the logged in user. Based upon this logged in context, all applicable personalization are applied by MDS. Given that substitutions are loaded as site level personalizations, MDS applies the substituted BC4J objects along with the personalizations. The above listed steps occur as soon as Root Application module has been loaded.
The benefit of using Substitution is to extend the OA Framework without customization of the underlying code. This is of great help during Upgrades. Entity Objects and Validation Objects can be substituted. I think Root AM’s can’t be substituted given that substitution kicks off after Root AM gets loaded.

Question: In OA Framework, once your application has been extended by substitutions, is it possible to revert back to remove those substitutions?
Answer: yes, by setting profile option “Disable Self-Service Personal%” to Yes, keeping in mind that all your personalizations will get disabled by this profile option. This profile is also very useful when debugging your OA Framework based application in the event of some error. By disabling the personalization via profile, you can isolate the error, i.e. is being caused by your extension/substitution code or by Oracle’s standard functionality.

Question: How can you import invoices into Oracle Receivables?
Answer: You can either use AutoInvoice by populating tables RA_INTERFACE_LINES_ALL, RA_INTERFACE_DISTRIBUTIONS_ALL & RA_INTERFACE_SALESCREDITS_ALL.
Alternately you may decide to use API ar_invoice_api_pub.create_single_invoice for Receivables Invoice Import.

Question: How do you setup a context sensitive flexfield
Answer: Note: I will publish a white paper to sho step by step approach.
But for the purpose of your interview, a brief explanation is…a)Create a reference field, b) Use that reference field in “Context Field” section of DFF Segment screen c) For each possible value of the context field, you will need to create one record in section “Context Field Value” ( beneath the global data elements).

Question: Does Oracle iProcurement use same tables as Oracle Purchasing?
Answer: Yes, iProcurement uses the same set of requisition tables as are used by Core Purchasing.

Question: What is the name of the schema for tables in tca
Answer: AR (at least till 11.5.10, not sure about 11.5.10).

Question: Are suppliers a part of TCA?
Answer: Unfortunately not yet. However, Release 12 will be merging Suppliers into TCA.

Question: What is the link between order management and purchasing
Answer: Internal Requisitions get translated into Internal Sales Orders.

Question: How would you know if the purchase order XML has been transmitted to vendor, looking at the tables.
Answer: The XML delivery status can be found from a table named ecx_oxta_logmsg. Use the query below
SELECT edoc.document_number
,decode(eol.result_code, 1000, 'Success', 'Failure') AS status
,eol.result_text
FROM ecx_oxta_logmsg eol
,ecx_doclogs edoc
,ecx_outbound_logs eog
WHERE edoc.msgid = eol.sender_message_id
AND eog.out_msgid = edoc.msgid
ORDER BY edoc.document_number

Question: You have done forms personalization, now how will you move it from one environment to another?
Answer: Use FNDLOAD. For examples visit FNDLOAD Article


Question: What are the key benefits of forms personalization over custom.pll?
Answer:
-->Multiple users can develop forms personalization at any given point in time.
-->It is fairly easy to enable and disable forms personalizations.
-->A programmer is not required to do simple things such as hide/disable fields or buttons.
-->Provides more visibility on customizations to the screen.

Question: Tell me some limitations of forms personalization when compared to CUSTOM.pll?
Answer:
-->Can't create record group queries, hence can’t implement LOV Query changes.
-->Can't make things interactive, i.e. can’t have a message box that gives multiple choices for example Proceed or Stop etc.


Question: Give me one example where apps uses partitioning?
Answer: WF_LOCAL_ROLES


Question: Give me one example of securing attributes in iProcurement.
Answer: You can define Realm to bundle suppliers into a Category. Such realm can then be assigned to the User using Define User Screen. Security Attribute ICX_POR_REALM_ID can be used. By doing so, the user will only be made visible those Punchout suppliers that belong to the realm against their securing attributes.


Question: Can you send blob attachments via workflow notifications?
Answer: Yes, you can send BLOB Attachments.

How to interview an Oracle Developer

It looks like the job market for Oracle is opening up. I'm seeing many of my friends find new jobs and I'm doing a lot of interviewing at work. I hope this is a trend that continues. In this entry, I share my method for interviewing Oracle resources and provide some sample interview questions and answers for developers.

Interviewing anyone can be difficult. Interviewing technical resources is very difficult. To me, probably the hardest thing is pinpointing exactly what you want this person to do. What, exactly, will this person be doing in their day-to-day job? It's easy to say having a good job description makes it easier but in many cases, employers have a generic template when looking for someone.

First off, is there a difference between a developer and a programmer? I think there is. A programmer is a coder. I don't mean that as a bad thing. Every project needs coders. A developer should be part analyst and part coder. A developer should be able to handle requirements gathering through implementation. Every project should have at least one good developer. In your interview, you should distinguish between the two.

If you're looking for an Oracle resource, there is a huge possible range of knowledge. Does a developer code for the front end? If so, which tools? Forms, Reports, HTML DB? I hope that you aren't looking for Java because wouldn't that make them a java programmer and not an Oracle developer?

Before you start to interview people, make sure you really know what you're looking for. It's not fair to the candidate to say you're looking for forms experience and then spend all of your time on advanced back end programming. And don't ask a backend coder the fine details of forms.

When I interview someone, I look for more than yes/no answers to my questions. In an interview, I expect a candidate to communicate with me. I'm looking for a comfort level. If the person says they don't know a particular topic, that's acceptable. If they fumble and make something up, that's a problem to me. I don't look for textbook definitions. I want to know they understand what I'm asking and what they're answering. I also don't believe in tricky interviews. What's the point?

Regardless of the exact position, any Oracle resource, including a DBA, should know some basic things about SQL and PL/SQL. Some sample questions I ask are:

For Basic SQL:



* How do you convert a date to a string? To_char. A bonus would be that they always include a format mask.


* What is an aggregate function? I'm looking for "grouping", sums or counts, etc.


* What is an interval? Specifies a period of time.


* What is a nested subquery? A subquery in a where clause.


* What is the dual table? A single row table provided by oracle for selecting values and expressions.




For Basic PL/SQL:



* Describe the block structure of PLSQL. Declaration, Begin, exception, end.


* What is an anonymous block? Unnamed PL/SQL block.


* What is a PL/SQL collection? PL/SQL Table, Varray, PL/SQL Array, etc.


* What is the difference between an explicit cursor and a select into. You might get something about performance but that's a myth. An explicit cursor is just more typing. A cursor for loop would be used to return more than a single row.


* Why would you choose to use a package versus straight procedures and functions? I look for maintenance, grouping logical functionality, dependency management, etc. I want to believe that they believe using packages is a "good thing".




These are pretty basic questions. If I don't get a warm fuzzy from these, and they are 100% answerable by anyone with some real experience, then the person goes no further.

So, where do you go after the basics? That really depends on what you're looking for. If you are hiring a Java coder to work with your Oracle group or you're looking for a DBA, you might end the coding part here. You would expect a DBA to know more but I would move on to administrative questions. You might also stop here if you're looking for a junior developer to train.

If you're looking for a senior PL/SQL coder type, you will want to go deeper. You need to remember to ask specific questions about a person's background and forms developers will have different experience than a back-end developer. But either should have a good grasp of advanced topics.

The hard part is that there are so many advanced topics; it's hard to know what to ask. You need to tailor it for your environment. If you use a lot of AQ, ask AQ questions. If you're very OO, ask OO questions.

Here are some more advanced, but still generic questions:

For Advanced SQL:



* What is the difference between an aggregate and an analytic function? I'm looking for them knowing that a sum aggregate (or any other aggregate function) will return one row for a group and a sum analytic will return one result for each row in the group. If they mention the "Window", they get a bonus point. ;-)


* How do you create a hierarchical query? Connect by.


* How would you generate XML from a query? The answer here is "A lot of different ways". They should know that there are SQL functions: XMLELEMENT, XMLFOREST, etc and PL/SQL functions: DBMS_XMLGEN, DBMS_XMLQUERY, etc.


* What do you need before implementing a member function? You need to create a type.


* How do you tune a query? I'm looking for a discussion of autotrace and/or explain plan. Ask them what they're looking for in a plan. This should not be a single sentence. Look for a comfort level.




For Somewhat Advanced PL/SQL:



* What is the default value of a boolean? NULL. This is somewhat tricky but apparently there are languages that default boolean to false. A PL/SQL developer needs to know all variables default to NULL.


* Why is using implicit conversions a poor programming practice? For dates, you must ASSUME that the default date format will always be the same (and it won't be). In some cases, implicit conversion is slower. I want to feel like they don't believe writing to_char or to_number is more work than it's worth. BTW, this also applies to SQL.


* How can you tell if an UPDATE updated no rows? SQL%NOTFOUND.


* How can you tell if a SELECT returned no rows. NO_DATA_FOUND exception.


* How do you run Native Dynamic SQL? Execute immediate.


* What is an autonomous transaction? Identified by pragma autonomous. A child transaction separate from the parent that MUST be committed or rolled back.




Those are some items that should also give you a warm fuzzy. If a person makes it to here, you can ask the questions specific to your organization, i.e. the AQ, LOB, Forms, HTML DB, etc.

At this point I usually ask the candidate to explain specific statements on the resume. If they say they tuned queries or improved performance, I say how? What did you do? What tools did you use?

That's my interviewing method. I hope that helps you get the best people for your organization.

Lewis

oracle interview

How do you call other Oracle Products from Oracle Forms?
Answer
# 1

Run_product is a built-in, Used to invoke one of the
supported oracle tools products and specifies the name of
the document or module to be run. If the called product is
unavailable at the time of the call, Oracle Forms returns a
message to the opertor

Oracle DBA interview questions

# vidya Says:
July 14th, 2004 at 5:59 pm

the answer about difference b/w truncate and delete can have more points. eg.
truncate does not generate undo, unlike delete operation.
delete triggers are not fired for truncate.
truncate releases used space and has implicit commit(ddl opern.)
# Ade Says:
August 2nd, 2005 at 11:02 am

I saw these questions on a web site. Can I have answers to them?

1. How many memory layers are in the shared pool?

2. How do you find out from the RMAN catalog if a particular archive log has been backed-up?

3. How can you tell how much space is left on a given file system and how much space each of the file system’s subdirectories take-up?

4. Define the SGA and:
• How you would configure SGA for a mid-sized OLTP environment?
• What is involved in tuning the SGA?

5. What is the cache hit ratio, what impact does it have on performance of an Oracle database and what is involved in tuning it?

6. Other than making use of the statspack utility, what would you check when you are monitoring or running a health check on an Oracle 8i or 9i database?

7. How do you tell what your machine name is and what is its IP address?

8. How would you go about verifying the network name that the local_listener is currently using?

9. You have 4 instances running on the same UNIX box. How can you determine which shared memory and semaphores are associated with which instance?

10. What view(s) do you use to associate a user’s SQLPLUS session with his o/s process?

11. What is the recommended interval at which to run statspack snapshots, and why?

12. What spfile/init.ora file parameter exists to force the CBO to make the execution path of a given statement use an index, even if the index scan may appear to be calculated as more costly?

13. Assuming today is Monday, how would you use the DBMS_JOB package to schedule the execution of a given procedure owned by SCOTT to start Wednesday at 9AM and to run subsequently every other day at
2AM.

14. How would you edit your CRONTAB to schedule the running of /test/test.sh to run every other day at 2PM?

15. What do the 9i dbms_standard.sql_txt() and
dbms_standard.sql_text() procedures do?

16. In which dictionary table or view would you look to determine at which time a snapshot or MVIEW last successfully refreshed?

17. How would you best determine why your MVIEW couldn’t FAST REFRESH?

18. How does propagation differ between Advanced Replication and Snapshot Replication (read-only)?

19. Which dictionary view(s) would you first look at to
understand or get a high-level idea of a given Advanced Replication environment?

20. How would you begin to troubleshoot an ORA-3113 error?

21. Which dictionary tables and/or views would you look at to diagnose a locking issue?

22. An automatic job running via DBMS_JOB has failed. Knowing only that “it’s failed”, how do you approach troubleshooting this issue?

23. How would you extract DDL of a table without using a GUI tool?

24. You’re getting high “busy buffer waits” - how can you find what’s causing it?

25. What query tells you how much space a tablespace named “test” is taking up, and how much space is remaining?

26. Database is hung. Old and new user connections alike hang on impact. What do you do? Your SYS SQLPLUS session IS able to connect.

27. Database crashes. Corruption is found scattered among the file system neither of your doing nor of Oracle’s. What database recovery options are available? Database is in archive log mode.

28. Illustrate how to determine the amount of physical CPUs a Unix Box possesses (LINUX and/or Solaris).

29. How do you increase the OS limitation for open files (LINUX and/or Solaris)?

30. Provide an example of a shell script which logs into SQLPLUS as SYS, determines the current date, changes the date format to include minutes & seconds, issues a drop table command, displays the date again, and finally exits.

31. Explain how you would restore a database using RMAN to Point in Time?

32. How does Oracle guarantee data integrity of data changes?

33. Which environment variables are absolutely critical in order to run the OUI?

34. What SQL query from v$session can you run to show how many sessions are logged in as a particular user account?

35. Why does Oracle not permit the use of PCTUSED with indexes?

36. What would you use to improve performance on an insert statement that places millions of rows into that table?

37. What would you do with an “in-doubt” distributed transaction?

38. What are the commands you’d issue to show the explain plan for “select * from dual”?

39. In what script is “snap$” created? In what script is
the “scott/tiger” schema created?

40. If you’re unsure in which script a sys or system-owned object is created, but you know it’s in a script from a specific directory, what UNIX command from that directory structure can you run to find your answer?

41. How would you configure your networking files to connect to a database by the name of DSS which resides in domain icallinc.com?

42. You create a private database link and upon
connection, fails with: ORA-2085: connects to . What is the problem? How would you go about resolving this error?

43. I have my backup RMAN script called “backup_rman.sh”. I am on the target database. My catalog username/password is rman/rman. My catalog db is called rman. How would you run this shell script from the O/S such that it would run as a background process?

44. Explain the concept of the DUAL table.

45. What are the ways tablespaces can be managed and how do they differ?

46. From the database level, how can you tell under which time zone a database is operating?

47. What’s the benefit of “dbms_stats” over “analyze”?

48. Typically, where is the conventional directory structure chosen for Oracle binaries to reside?

49. You have found corruption in a tablespace that contains static tables that are part of a database that is in NOARCHIVE log mode. How would you restore the tablespace without losing new data in the other tablespaces?

50. How do you recover a datafile that has not been physically been backed up since its creation and has been deleted. Provide syntax example.

oracle questions

Oracle Interview Questions & Answers »

| 0 Comments |

What command would you use to create a backup control file?
Alter database backup control file to trace.
Give the stages of instance startup to a usable state where normal users may access it.
STARTUP NOMOUNT - Instance startup
STARTUP MOUNT - The database is mounted
STARTUP OPEN - The database is opened
What column differentiates the V$ views to the [...]

oracle interview questions 1

What is a CO-RELATED SUBQUERY

A CO-RELATED SUBQUERY is one that has a correlation name as table or view designator in the FROM clause of the outer query and the same correlation name as a qualifier of a search condition in the WHERE clause of the subquery.
2. eg
3. SELECT field1 from table1 X
4. WHERE field2>(select avg(field2) from table1 Y
5. where
field1=X.field1);
(The subquery in a correlated subquery is revaluated for every row of the table or view named in the outer query.)

What are various joins used while writing SUBQUERIES

Self join-Its a join foreign key of a table references the same table.
Outer Join–Its a join condition used where One can query all the rows of one of the tables in the join condition even though they don’t satisfy the join condition.
Equi-join–Its a join condition that retrieves rows from one or more tables in which one or more columns in one table are equal to one or more columns in the second table.

What are various constraints used in SQL

NULL
NOT NULL
CHECK
DEFAULT

What are different Oracle database objects

TABLES
VIEWS
INDEXES
SYNONYMS
SEQUENCES
TABLESPACES etc

What is difference between Rename and Alias

Rename is a permanent name given to a table or column whereas Alias is a temporary name given to a table or column which do not exist once the SQL statement is executed.

What is a view

A view is stored procedure based on one or more tables, its a virtual table.

What are various privileges that a user can grant to another user

SELECT
CONNECT
RESOURCE

What is difference between UNIQUE and PRIMARY KEY constraints

A table can have only one PRIMARY KEY whereas there can be any number of UNIQUE keys. The columns that compose PK are automatically define NOT NULL, whereas a column that compose a UNIQUE is not automatically defined to be mandatory must also specify the column is NOT NULL.

Can a primary key contain more than one columns

Yes

How you will avoid duplicating records in a query

By using DISTINCT

What is difference between SQL and SQL*PLUS

SQL*PLUS is a command line tool where as SQL and PL/SQL language interface and reporting tool. Its a command line tool that allows user to type SQL commands to be executed directly against an Oracle database. SQL is a language used to query the relational database(DML,DCL,DDL). SQL*PLUS commands are used to format query result, Set options, Edit SQL commands and PL/SQL.

Which datatype is used for storing graphics and images

LONG RAW data type is used for storing BLOB’s (binary large objects).

How will you delete duplicating rows from a base table

DELETE FROM table_name A WHERE rowid>(SELECT min(rowid) from table_name B where B.table_no=A.table_no);
CREATE TABLE new_table AS SELECT DISTINCT * FROM old_table;
DROP old_table RENAME new_table TO old_table DELETE FROM table_name A WHERE rowid NOT IN (SELECT MAX(ROWID) FROM table_name GROUP BY column_name)

What is difference between SUBSTR and INSTR

SUBSTR returns a specified portion of a string eg SUBSTR(’BCDEF’,4) output BCDE INSTR provides character position in which a pattern is found in a string. eg INSTR(’ABC-DC-F’,'-’,2) output 7 (2nd occurence of ‘-’ )

ooracle see

* Oracle Corporation - company overview
* Oracle Product Set
* Oracle Services

Managing an Oracle Site

Information for managers and team leaders on how to successfully manage an Oracle site:

* Oracle licensing
* Soft Issues - Managing Oracle Staff
* Roles and Responsibilities
* Securing the Environment
* Engaging Oracle Support
* Database Production Acceptance
* Tender questions for Application Vendors

Career Management

Information for Oracle professionals on how to successfully manage their careers.

* Getting a job
* Interview Questions
* Oracle Training
* Oracle Certification Program
* List of well known Oracle Personalities

Technical articles, tips and tricks

Technical product information:
Database

* SQL, PL/SQL and SQL*Plus
* Database versions: 9i, 10g, 11g
* Compatibility matrices and differences between editions
* Database Concepts and Architecture
* Database Administration
* Backup and Recovery
* Performance Tuning
* Replication
* Data Guard
* Install Guides

Development

* Oracle Developer Suite, including Forms, Reports, Discoverer, etc.
* Precompilers like Pro*C and Pro*Cobol
* Java, including SQLJ, JDBC, SQLJ, etc.
* PHP, Perl, Python, Tcl, Ruby on Rails, etc.
* Sample Code

Fusion Middleware (Application Server)

* Oracle Application Server
* Fusion Middleware
* Forms Server, Reports Server
* Enterprise Content Management, TopLink, Secure Enterprise Search