Oracle  Placement Paper   Technical - Other   -25 Nov 2008

Oracle  Placement Paper   Technical - Other   -25 Nov 2008

  • Posted by  FreshersWorld 
    7 Jan, 2012


    1. What is the difference between restricted and unrestricted package procedure ?
    Restricted package procedure that affects the basic functions of SQL * Forms. It cannot used in all triggers except key triggers. Unrestricted package procedure that does not interfere with the basic functions of SQL * Forms it can be used in any triggers

    2. A query fetched 10 records How many times does a PRE-QUERY Trigger and POST-QUERY Trigger will get executed ?
    PRE-QUERY fires once.
    POST-QUERY fires 10 times.

    3. Give the sequence in which triggers fired during insert operations, when the following 3 triggers are defined at the same block level ?

    4. State the order in which these triggers are executed ?

    5. What the PAUSE package procedure does ?
    Pause suspends processing until the operator presses a function key

    6. What do you mean by a page ?
    Pages are collection of display information, such as constant text and graphics

    7. What are the type of User Exits ?
    ORACLE Precompliers user exits
    OCI (ORACLE Call Interface)
    Non-ORACEL user exits.
    Page :

    8. What is the difference between an ON-VALIDATE-FIELD trigger and a trigger ?
    On-validate-field trigger fires, when the field Validation status New or changed. Post-field-trigger whenever the control leaving form the field, it will fire.

    9. Can we use a restricted package procedure in ON-VALIDATE-FIELD Trigger ?

    10. Is a Key startup trigger fires as result of a operator pressing a key explicitly ?

    11. Can we use GO-BLOCK package in a pre-field trigger ?

    12. Can we create two blocks with the same name in form 3.0 ?

    13. What is Post-Block is a. ???
    a. Navigational Trigger.
    b. Key trigger
    c. Transaction Trigger.

    14. What does an on-clear-block Trigger fire?
    It fires just before SQL * forms the current block.

    15. Name the two files that are created when you generate the form give the filex extension ?
    INP (Source File)
    FRM (Executable File)

    16. What package procedure used for invoke sql *plus from sql *forms ?
    Host (E.g. Host (sqlplus))

    17. What is the significance of PAGE 0 in forms 3.0 ?
    Hide the fields for internal calculation.

    18. What are the different types of key triggers ?
    Function Key

    19. What is the difference between a Function Key Trigger and Key Function Trigger ?
    Function key triggers are associated with individual SQL*FORMS function keys You can attach Key function triggers to 10 keys or key sequences that normally do not perform any SQL * FORMS operations. These keys referred as key F0 through key F9.

    20. Committed block sometimes refer to a BASE TABLE ?

    21. Error_Code is a package proecdure ?
    a. True b. false

    22. When is cost based optimization triggered? (for DBA
    It's important to have statistics on all tables for the CBO (Cost Based Optimizer) to work correctly. If one table involved in a statement does not have statistics, Oracle has to revert to rule-based optimization for that statement. So you really want for all tables to have statistics right away; it won't help much to just have the larger tables analyzed.
    Generally, the CBO can change the execution plan when you:
    1. Change statistics of objects by doing an ANALYZE;
    2. Change some initialization parameters (for example: hash_join_enabled, sort_area_size, db_file_multiblock_read_count).

    23. How can one optimize %XYZ% queries? (for DBA
    It is possible to improve %XYZ% queries by forcing the optimizer to scan all the entries from the index instead of the table. This can be done by specifying hints. If the index is physically smaller than the table (which is usually the case) it will take less time to scan the entire index than to scan the entire table.

    23. What Enter package procedure does ?
    Enter Validate-data in the current validation unit.

    24. Where can one find I/O statistics per table? (for DBA
    The UTLESTAT report shows I/O per tablespace but one cannot see what tables in the tablespace has the most I/O. The $ORACLE_HOME/rdbms/admin/catio.sql script creates a sample_io procedure and table to gather the required information. After executing the procedure, one can do a simple SELECT * FROM io_per_object; to extract the required information. For more details, look at the header comments in the $ORACLE_HOME/rdbms/admin/catio.sql script.

    25. My query was fine last week and now it is slow. Why? (for DBA
    The likely cause of this is because the execution plan has changed. Generate a current explain plan of the offending query and compare it to a previous one that was taken when the query was performing well. Usually the previous plan is not available.
    Some factors that can cause a plan to change are:
    . Which tables are currently analyzed? Were they previously analyzed? (ie. Was the query using RBO and now CBO?)
    . Has OPTIMIZER_MODE been changed in INIT.ORA?
    . Has the DEGREE of parallelism been defined/changed on any table?
    . Have the tables been re-analyzed? Were the tables analyzed using estimate or compute? If estimate, what percentage was used?
    . Have the statistics changed?
    . Has the INIT.ORA parameter DB_FILE_MULTIBLOCK_READ_COUNT been changed?
    . Has the INIT.ORA parameter SORT_AREA_SIZE been changed?
    . Have any other INIT.ORA parameters been changed?
    . What do you think the plan should be? Run the query with hints to see if this produces the required performance.

    26. Why is Oracle not using the damn index? (for DBA
    This problem normally only arises when the query plan is being generated by the Cost Based Optimizer. The usual cause is because the CBO calculates that executing a Full Table Scan would be faster than accessing the table via the index.
    Fundamental things that can be checked are:
    . USER_TAB_COLUMNS.NUM_DISTINCT - This column defines the number of distinct values the column holds.
    . USER_TABLES.NUM_ROWS - If NUM_DISTINCT = NUM_ROWS then using an index would be preferable to doing a FULL TABLE SCAN. As the NUM_DISTINCT decreases, the cost of using an index increase thereby is making the index less desirable.
    . USER_INDEXES.CLUSTERING_FACTOR - This defines how ordered the rows are in the index. If CLUSTERING_FACTOR approaches the number of blocks in the table, the rows are ordered. If it approaches the number of rows in the table, the rows are randomly ordered. In such a case, it is unlikely that index entries in the same leaf block will point to rows in the same data blocks.
    . Decrease the INIT.ORA parameter DB_FILE_MULTIBLOCK_READ_COUNT - A higher value will make the cost of a FULL TABLE SCAN cheaper.
    . Remember that you MUST supply the leading column of an index, for the index to be used (unless you use a FAST FULL SCAN or SKIP SCANNING).
    . There are many other factors that affect the cost, but sometimes the above can help to show why an index is not being used by the CBO. If from checking the above you still feel that the query should be using an index, try specifying an index hint. Obtain an explain plan of the query either using TKPROF with TIMED_STATISTICS, so that one can see the CPU utilization, or with AUTOTRACE to see the statistics. Compare this to the explain plan when not using an index.

    27. When should one rebuild an index? (for DBA
    You can run the 'ANALYZE INDEX VALIDATE STRUCTURE' command on the affected indexes - each invocation of this command creates a single row in the INDEX_STATS view. This row is overwritten by the next ANALYZE INDEX command, so copy the contents of the view into a local table after each ANALYZE. The 'badness' of the index can then be judged by the ratio of 'DEL_LF_ROWS' to 'LF_ROWS'.

    28. What are the unrestricted procedures used to change the popup screen position during run time ?
    Resize -View

    29. What is an Alert ?
    An alert is window that appears in the middle of the screen overlaying a portion of the current display.

    30. Deleting a page removes information about all the fields in that page ? a. True. b. False?
    a. True

    31. Two popup pages can appear on the screen at a time ?Two popup pages can appear on the screen at a time ? a. True. b. False?
    a. True

    32. Classify the restricted and unrestricted procedure from the following: ...
    Classify the restricted and unrestricted procedure from the following.
    a. Call
    b. User-Exit
    c. Call-Query
    d. Up
    e. Execute-Query
    f. Message
    g. Exit-From
    h. Post
    i. Break?

    a. Call - unrestricted
    b. User Exit - Unrestricted
    c. Call_query - Unrestricted
    d. Up - Restricted
    e. Execute Query - Restricted
    f. Message - Restricted
    g. Exit_form - Restricted
    h. Post - Restricted
    i. Break - Unrestricted.

    33. What is an User Exits ?
    A user exit is a subroutine which are written in programming languages using pro*C pro *Cobol , etc., that link into the SQL * forms executable

    34. What is a Trigger ?
    A piece of logic that is executed at or triggered by a SQL *forms event.

    35. What is a Package Procedure ?
    A Package procedure is built in PL/SQL procedure.

    36. What is the maximum size of a form ?
    255 character width and 255 characters Length.

    37. What is the difference between system.current_field and system.cursor_field ?
    1. System.current_field gives name of the field.
    2. System.cursor_field gives name of the field with block name.

    38. List the system variables related in Block and Field?
    1. System.block_status
    2. System.current_block
    3. System.current_field
    4. System.current_value
    5. System.cursor_block
    6. System.cursor_field
    7. System.field_status.

    39. What are the different types of Package Procedure ?
    1. Restricted package procedure.
    2. Unrestricted package procedure.

    40. What are the types of TRIGGERS ?
    1. Navigational Triggers.
    2. Transaction Triggers.

    41. Identify package function from the following ?
    Identify package function from the following ? 1. Error-Code
    2. Break
    3. Call
    4. Error-text
    5. Form-failure
    6. Form-fatal
    7. Execute-query
    8. Anchor View
    9. Message_code?

    1. Error_Code
    2. Error_Text
    3. Form_Failure
    4. Form_Fatal
    5. Message_Code

    42. Can you attach an lov to a field at run-time? if yes, give the build-in name.?
    Yes. Set_item_proprety

    43. Is it possible to attach same library to more than one form?

    44. Can you attach an lov to a field at design time?

    45. List the windows event triggers available in Forms 4.0?

    46. What are the triggers associated with the image item?
    When-Image-activated(Fires when the operator double clicks on an image Items)
    When-image-pressed(fires when the operator selects or deselects the image item)

    47. What is a visual attribute?
    Visual Attributes are the font, color and pattern characteristics of objects that operators see and intract with in our application.

    48. How many maximum number of radio buttons can you assign to a radio group?
    Unlimited no of radio buttons can be assigned to a radio group

    49. How do you pass the parameters from one form to another form?
    To pass one or more parameters to a called form, the calling form must perform the following steps in a trigger or user named routine execute the create_parameter_list built-in function to programmatically. Create a parameter list to execute the add parameter built-in procedure to add one or more parameters list. Execute the call_form, New_form or run_product built_in procedure and include the name or id of the parameter list to be passed to the called form.

    50. What is a Layout Editor?
    The Layout Editor is a graphical design facility for creating and arranging items and boilerplate text and graphics objects in your application's interface.

    51. List the Types of Items?
    Text item.
    Chart item.
    Check box.
    Display item.
    Image item.
    List item.
    Radio Group.
    User Area item.

    52. List system variables available in forms 4.0, and not available in forms 3.0?
    System Date_threshold

    53. What are the display styles of an alert?
    Stop, Caution, note

    54. What built-in is used for showing the alert during run-time?

    55. What built-in is used for changing the properties of the window dynamically?

    56. What are the different types of windows?
    Root window, secondary window.

    57. What is a predefined exception available in forms 4.0?
    Raise form_trigger_failure

    58. What is a radio Group?
    Radio groups display a fixed no of options that are mutually Exclusive. User can select one out of n number of options.

    59. What are the different type of a record group?
    Query record group
    Static record group
    Non query record group

    60. What are the menu items that oracle forms 4.0 supports?
    Plain, Check,Radio, Separator, Magic

    61. Give the equivalent term in forms 4.0 for the following. Page, Page 0?
    Page - Canvas-View
    Page 0 - Canvas-view null.

    62. What triggers are associated with the radio group?
    Only when-radio-changed trigger associated with radio group
    Visual Attributes

    63. What are the triggers associated with a check box?
    Only When-checkbox-activated Trigger associated with a Check box.

    64.Can you attach an alert to a field?

    65. Can a root window be made modal?

    66. What is a list item?
    It is a list of text elements.

    67. List some built-in routines used to manipulate images in image_item?

    68. Can you change the alert messages at run-time?
    If yes, give the name of the built-in to change the alert messages at run-time. Yes. Set_alert_property.

    69. What is the built-in used to get and set lov properties during run-time?
    Record Group

    70. What is the built-in routine used to count the no of rows in a group?
    Get_group _row_count
    System Variables

    71. Give the Types of modules in a form?

    72. Write the Abbreviation for the following File Extension 1. FMB 2. MMB 3. PLL?
    FMB ----- Form Module Binary.
    MMB ----- Menu Module Binary.
    PLL ------ PL/SQL Library Module Binary

    73. List the built-in routine for controlling window during run-time?

    74. List the built-in routine for controlling window during run-time?

    75. What is the built-in function used for finding the alert?

    76. List the editors availables in forms 4.0?
    Default editor
    User_defined editors
    system editors.

    77. What buil-in routines are used to display editor dynamicaly?
    Edit_text item

    78. What is an Lov?
    A list of values is a single or multi column selection list displayed in a pop-up window

    79. What is a record Group?
    A record group is an internal oracle forms data structure that has a similar column/row frame work to a database table

    80. Give built-in routine related to a record groups?
    Create_group (Function)

    81. List the built-in routines for the controlling canvas views during run-time?

    82. System.effective_date system variable is read only True/False?

    83. What are the built_in used to trapping errors in forms 4?
    Error_type return character
    Error_code return number
    Error_text return char
    Dbms_error_code return no.
    Dbms_error_text return char

    84. What are the design facilities available in forms 4.0?
    Default Block facility.
    Layout Editor.
    Menu Editor.
    Object Lists.
    Property Sheets.
    PL/SQL Editor.
    Tables Columns Browser.
    Built-ins Browser.

    85. What is the most important module in Oracle Financials? (for DBA
    The General Ledger (GL) module is the basis for all other Oracle Financial modules. All other modules provide information to it. If you implement Oracle Financials, you should switch your current GL system first.GL is relatively easy to implement. You should go live with it first to give your implementation team a chance to be familiar with Oracle Financials.

    86. What are the types of canvas-views?
    Content View, Stacked View.

    87. What is the MultiOrg and what is it used for? (for DBA
    MultiOrg or Multiple Organizations Architecture allows multiple operating units and their relationships to be defined within a single installation of Oracle Applications. This keeps each operating unit's transaction data separate and secure.
    Use the following query to determine if MuliOrg is intalled:
    select multi_org_flag from fnd_product_groups;

    88. What is the difference between Fields and FlexFields? (for DBA
    A field is a position on a form that one uses to enter, view, update, or delete information. A field prompt describes each field by telling what kind of information appears in the field, or alternatively, what kind of information should be entered in the field.
    A flexfield is an Oracle Applications field made up of segments. Each segment has an assigned name and a set of valid values. Oracle Applications uses flexfields to capture information about your organization. There are two types of flexfields: key flexfields and descriptive flexfields.

    89. Explain types of Block in forms4.0?
    Base table Blocks.
    Control Blocks.
    1. A base table block is one that is associated with a specific database table or view.
    2. A control block is a block that is not associated with a database table. ITEMS

    90. What is an Alert?
    An alert is a modal window that displays a message notifies the operator of some application condition

    91. What are the built-in routines is available in forms 4.0 to create and manipulate a parameter list?

    92 .What is a record Group?
    A record group is an internal oracle forms data structure that has a similar column/row frame work to a database table

    93 What is a Navigable item?
    A navigable item is one that operators can navigate to with the keyboard during default navigation, or that Oracle forms can navigate to by executing a navigational built-in procedure.

    94. What is a library in Forms 4.0?
    A library is a collection of Pl/SQL program units, including user named procedures, functions & packages

    95. How image_items can be populate to field in forms 4.0?
    A fetch from a long raw database column PL/Sql assignment to executing the read_image_file built_in procedure to get an image from the file system.

    96. What is the content view and stacked view?
    A content view is the "Base" view that occupies the entire content pane of the window in which it is displayed. A stacked view differs from a content canvas view in that it is not the base view for the window to which it is assigned

    97. What is a Check Box?
    A Check Box is a two state control that indicates whether a certain condition or value is on or off, true or false. The display state of a check box is always either "checked" or "unchecked".

    98. What is a canvas-view?
    A canvas-view is the background object on which you layout the interface items (text-items, check boxes, radio groups, and so on.) and boilerplate objects that operators see and interact with as they run your form. At run-time, operators can see only those items that have been assigned to a specific canvas. Each canvas, in term, must be displayed in a specific window.

    99. Explain the following file extension related to library?
    The library pll files is a portable design file comparable to an fmb form file
    The library lib file is a plat form specific, generated library file comparable to a fmx form file
    The pld file is Txt format file and can be used for source controlling your library files Parameter

    100. Explain the usage of WHERE CURRENT OF clause in cursors ?
    WHERE CURRENT OF clause in an UPDATE,DELETE statement refers to the latest row fetched from a cursor. Database Triggers

    101. Name the tables where characteristics of Package, procedure and functions are stored ?
    User_objects, User_Source and User_error.

    102. Explain the two type of Cursors ?
    There are two types of cursors, Implicit Cursor and Explicit Cursor. PL/SQL uses Implicit Cursors for queries. User defined cursors are called Explicit Cursors. They can be declared and used.

    103. What are two parts of package ?
    The two parts of package are PACKAGE SPECIFICATION & PACKAGE BODY. Package Specification contains declarations that are global to the packages and local to the schema. Package Body contains actual procedures and local declaration of the procedures and cursor declarations

    104. What are two virtual tables available during database trigger execution ?
    The table columns are referred as OLD.column_name and NEW.column_name.
    For triggers related to INSERT only NEW.column_name values only available.
    For triggers related to UPDATE only OLD.column_name NEW.column_name values only available.
    For triggers related to DELETE only OLD.column_name values only available.

    105. What is Fine Grained Auditing? (for DBA
    Fine Grained Auditing (DBMS_FGA) allows auditing records to be generated when certain rows are selected from a table. A list of defined policies can be obtained from DBA_AUDIT_POLICIES. Audit records are stored in DBA_FGA_AUDIT_TRAIL. Look at this example:
    o Add policy on table with autiting condition...
    execute dbms_fga.add_policy('HR', 'EMP', 'policy1', 'deptno > 10');
    o Must ANALYZE, this feature works with CBO (Cost Based Optimizer)
    analyze table EMP compute statistics;
    select * from EMP where c1 = 11; -- Will trigger auditing
    select * from EMP where c1 = 09; -- No auditing
    o Now we can see the statments that triggered the auditing condition...
    select sqltext from sys.fga_log$;
    delete from sys.fga_log$;

    106. What is a package ? What are the advantages of packages ? What is Pragma EXECPTION_INIT ? Explain the usage ?
    The PRAGMA EXECPTION_INIT tells the complier to associate an exception with an oracle error. To get an error message of a specific oracle error. e.g. PRAGMA EXCEPTION_INIT (exception name, oracle error number)

    107. What is Fine Grained Access Control? (for DBA
    See question "What is a Virtual Private Database".

    108. What is a Virtual Private Database? (for DBA
    Oracle 8i introduced the notion of a Virtual Private Database (VPD). A VPD offers Fine-Grained Access Control (FGAC) for secure separation of data. This ensures that users only have access to data that pertains to them. Using this option, one could even store multiple companies' data within the same schema, without them knowing about it. VPD configuration is done via the DBMS_RLS (Row Level Security) package. Select from SYS.V$VPD_POLICY to see existing VPD configuration

    109. What is Raise_application_error ?
    Raise_application_error is a procedure of package DBMS_STANDARD which allows to issue an user_defined error messages from stored sub-program or database trigger.

    110. What is Oracle Label Security? (for DBA
    Oracle Label Security (formerly called Trusted Oracle MLS RDBMS) uses the VPD (Virtual Private Database) feature of Oracle8i to implement row level security. Access to rows are restricted according to a user's security sensitivity tag or label. Oracle Label Security is configured, controlled and managed from the Policy Manager, an Enterprise Manager-based GUI utility.

    111. Give the structure of the procedure ?
    PROCEDURE name (parameter list.....)
    local variable declarations
    Executable statements.
    exception handlers

    112. What is OEM (Oracle Enterprise Manager)? (for DBA
    OEM is a set of systems management tools provided by Oracle Corporation for managing the Oracle environment. It provides tools to monitor the Oracle environment and automate tasks (both one-time and repetitive in nature) to take database administration a step closer to "Lights Out" management.

    113.What is PL/SQL ?
    PL/SQL is a procedural language that has both interactive SQL and procedural programming language constructs such as iteration, conditional branching.

    114. What are the components of OEM? (for DBA
    Oracle Enterprise Manager (OEM) has the following components:
    . Management Server (OMS): Middle tier server that handles communication with the intelligent agents. The OEM Console connects to the management server to monitor and configure the Oracle enterprise.
    . Console: This is a graphical interface from where one can schedule jobs, events, and monitor the database. The console can be opened from a Windows workstation, Unix XTerm (oemapp command) or Web browser session (oem_webstage).
    . Intelligent Agent (OIA): The OIA runs on the target database and takes care of the execution of jobs and events scheduled through the Console.

    115. What happens if a procedure that updates a column of table X is called in a database trigger of the same table ?
    Mutation of table occurs.

    116. Is it possible to use Transaction control Statements such a ROLLBACK or COMMIT in Database Trigger ? Why ?
    It is not possible. As triggers are defined for each table, if you use COMMIT of ROLLBACK in a trigger, it affects logical transaction processing.

    117. How many types of database triggers can be specified on a table ? What are they ?
    Insert Update Delete
    Before Row o.k. o.k. o.k.
    After Row o.k. o.k. o.k.
    Before Statement o.k. o.k. o.k.
    After Statement o.k. o.k. o.k.
    If FOR EACH ROW clause is specified, then the trigger for each Row affected by the statement.
    If WHEN clause is specified, the trigger fires according to the returned Boolean value.

    118. What are the modes of parameters that can be passed to a procedure ?
    IN,OUT,IN-OUT parameters.

    119. Where the Pre_defined_exceptions are stored ?
    In the standard package.
    Procedures, Functions & Packages ;

    120. Write the order of precedence for validation of a column in a table ?
    Write the order of precedence for validation of a column in a table ?
    I. done using Database triggers.
    ii. done using Integarity Constraints.?

    121. Give the structure of the function ?
    FUNCTION name (argument list .....) Return datatype is
    local variable declarations
    executable statements
    execution handlers

    122. Explain how procedures and functions are called in a PL/SQL block ?
    Function is called as part of an expression.
    sal := calculate_sal ('a822');
    procedure is called as a PL/SQL statement
    calculate_bonus ('A822');

    123. What are advantages fo Stored Procedures?
    Extensibility, Modularity, Reusability, Maintainability and one time compilation.

    124. What is an Exception ? What are types of Exception ?
    Exception is the error handling part of PL/SQL block. The types are Predefined and user defined. Some of Predefined exceptions are.

    125. What are the PL/SQL Statements used in cursor processing ?
    DECLARE CURSOR cursor name, OPEN cursor name, FETCH cursor name INTO or Record types, CLOSE cursor name.

    126. What are the components of a PL/SQL block ?
    A set of related declarations and procedural statements is called block.

    127. What is a database trigger ? Name some usages of database trigger ?
    Database trigger is stored PL/SQL program unit associated with a specific database table. Usages are Audit data modifications, Log events transparently, Enforce complex business rules Derive column values automatically, Implement complex security authorizations. Maintain replicate tables.

    128. What is a cursor ? Why Cursor is required ?
    Cursor is a named private SQL area from where information can be accessed. Cursors are required to process rows individually for queries returning multiple rows

    129. What is a cursor for loop ?
    Cursor for loop implicitly declares %ROWTYPE as loop index,opens a cursor, fetches rows of values from active set into fields in the record and closes when all the records have been processed.
    eg. FOR emp_rec IN C1 LOOP
    salary_total := salary_total +emp_rec sal;

    130. What will happen after commit statement ?
    Cursor C1 is
    Select empno,
    ename from emp;
    open C1; loop
    Fetch C1 into
    Exit When
    C1 %notfound;-----
    end loop;
    The cursor having query as SELECT .... FOR UPDATE gets closed after COMMIT/ROLLBACK.
    The cursor having query as SELECT.... does not get closed even after COMMIT/ROLLBACK.

    131. How packaged procedures and functions are called from the following?
    a. Stored procedure or anonymous block
    b. an application program such a PRC *C, PRO* COBOL
    c. SQL *PLUS??
    a. PACKAGE NAME.PROCEDURE NAME (parameters);
    variable := PACKAGE NAME.FUNCTION NAME (arguments);
    variable := PACKAGE NAME.FUNCTION NAME (arguments);
    c. EXECUTE PACKAGE NAME. PROCEDURE if the procedures does not have any out/in-out parameters. A function can not be called.

    132. What is a stored procedure ?
    A stored procedure is a sequence of statements that perform specific function.

    133. What are the components of a PL/SQL block ?
    A set of related declarations and procedural statements is called block.

    134. What is difference between a PROCEDURE & FUNCTION ?
    A FUNCTION is always returns a value using the return statement.
    A PROCEDURE may return one or more values through parameters or may not return at all.

    135. What is difference between a Cursor declared in a procedure and Cursor declared in a package specification ?
    A cursor declared in a package specification is global and can be accessed by other procedures or procedures in a package.
    A cursor declared in a procedure is local to the procedure that can not be accessed by other procedures.

    136. What are the cursor attributes used in PL/SQL ?
    %ISOPEN - to check whether cursor is open or not
    % ROWCOUNT - number of rows fetched/updated/deleted.
    % FOUND - to check whether cursor has fetched any row. True if rows are fetched.
    % NOT FOUND - to check whether cursor has fetched any row. True if no rows are featched.
    These attributes are proceeded with SQL for Implicit Cursors and with Cursor name for Explicit Cursors.

    137. What are % TYPE and % ROWTYPE ? What are the advantages of using these over data types?
    % TYPE provides the data type of a variable or a database column to that variable.
    % ROWTYPE provides the record type that represents a entire row of a table or view or columns selected in the cursor.
    The advantages are :
    I. Need not know about variable's data type
    ii. If the database definition of a column in a table changes, the data type of a variable changes accordingly.

    138. What is difference between % ROWTYPE and TYPE RECORD ?
    % ROWTYPE is to be used whenever query returns a entire row of a table or view.
    TYPE rec RECORD is to be used whenever query returns columns of different table or views and variables.
    E.g. TYPE r_emp is RECORD (eno emp.empno% type,ename emp ename %type );
    e_rec emp% ROWTYPE
    cursor c1 is select empno,deptno from emp;
    e_rec c1 %ROWTYPE.

    139. What are the different types of PL/SQL program units that can be defined and stored in ORACLE database ?
    Procedures and Functions,Packages and Database Triggers.

    140. What are the advantages of having a Package ?
    Increased functionality (for example,global package variables can be declared and used by any proecdure in the package) and performance (for example all objects of the package are parsed compiled, and loaded into memory once)

    141. What are the uses of Database Trigger ?
    Database triggers can be used to automatic data generation, audit data modifications, enforce complex Integrity constraints, and customize complex security authorizations.

    142. What is a Procedure ?
    A Procedure consist of a set of SQL and PL/SQL statements that are grouped together as a unit to solve a specific problem or perform a set of related tasks.

    143. What is a Package ?
    A Package is a collection of related procedures, functions, variables and other package constructs together as a unit in the database.

    144. What is difference between Procedures and Functions ?
    A Function returns a value to the caller where as a Procedure does not.

    145. What is Database Trigger ?
    A Database Trigger is procedure (set of SQL and PL/SQL statements) that is automatically executed as a result of an insert in,update to, or delete from a table.

    146. Can the default values be assigned to actual parameters?

    147. Can a primary key contain more than one columns?

    148. What is an UTL_FILE.What are different procedures and functions associated with it?
    UTL_FILE is a package that adds the ability to read and write to operating system files. Procedures associated with it are FCLOSE, FCLOSE_ALL and 5 procedures to output data to a file PUT, PUT_LINE, NEW_LINE, PUTF, FFLUSH.PUT, FFLUSH.PUT_LINE,FFLUSH.NEW_LINE. Functions associated with it are FOPEN, ISOPEN.

    149. What are ORACLE PRECOMPILERS?
    Using ORACLE PRECOMPILERS, SQL statements and PL/SQL blocks can be contained inside 3GL programs written in C,C++,COBOL,PASCAL, FORTRAN,PL/1 AND ADA. The Precompilers are known as Pro*C,Pro*Cobol,... This form of PL/SQL is known as embedded pl/sql,the language in which pl/sql is embedded is known as the host language. The prcompiler translates the embedded SQL and pl/sql ststements into calls to the precompiler runtime library.The output must be compiled and linked with this library to creater an executable.

    150. Differentiate between TRUNCATE and DELETE?
    TRUNCATE deletes much faster than DELETE
    It is a DDL statement
    It is a DML statement
    It is a one way trip,cannot ROLLBACK
    One can Rollback
    Doesn't have selective features (where clause)
    Doesn't fire database triggers
    It requires disabling of referential constraints.

    151. What is difference between a formal and an actual parameter?
    The variables declared in the procedure and which are passed, as arguments are called actual, the parameters in the procedure declaration. Actual parameters contain the values that are passed to a procedure and receive results. Formal parameters are the placeholders for the values of actual parameters

    152. What should be the return type for a cursor variable.Can we use a scalar data type as return type?
    The return type for a cursor must be a record type.It can be declared explicitly as a user-defined or %ROWTYPE can be used. eg TYPE t_studentsref IS REF CURSOR RETURN students%ROWTYPE

    153. What are different Oracle database objects?

    154. 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 '-')

    155. Display the number value in Words?
    SQL> select sal, (to_char(to_date(sal,'j'), 'jsp'))
    from emp;
    the output like,
    --------- ----------------------------------------
    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.

    156. 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.

    157. 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

    158. What a SELECT FOR UPDATE cursor represent.?
    SELECT......FROM......FOR......UPDATE[OF column-reference][NOWAIT]
    The processing done in a fetch loop modifies the rows that have been retrieved by the cursor. A convenient way of modifying the rows is done by a method with two parts: the FOR UPDATE clause in the cursor declaration, WHERE CURRENT OF CLAUSE in an UPDATE or declaration statement.

    159. What are various privileges that a user can grant to another user?

    160. 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);

    161. minvalue.sql Select the Nth lowest value from a table?
    select level, min('col_name') from my_table where level = '&n' connect by prior ('col_name') < 'col_name')
    group by level;
    Given a table called emp with the following columns:
    -- id number
    -- name varchar2(20)
    -- sal number
    -- For the second lowest salary:
    -- select level, min(sal) from emp
    -- where level=2
    -- connect by prior sal < sal
    -- group by level

    162. 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.

    163. Difference between an implicit & an explicit cursor.?
    only one row. However,queries that return more than one row you must declare an explicit cursor or use a cursor FOR loop. Explicit cursor is a cursor in which the cursor name is explicitly assigned to a SELECT statement via the CURSOR...IS statement. An implicit cursor is used for all SQL statements Declare, Open, Fetch, Close. An explicit cursors are used to process multirow SELECT statements An implicit cursor is used to process INSERT, UPDATE, DELETE and single row SELECT. .INTO statements.

    164. What is a OUTER JOIN?
    Outer Join--Its a join condition used where you can query all the rows of one of the tables in the join condition even though they don?t satisfy the join condition.

    165. What is a cursor?
    Oracle uses work area to execute SQL statements and store processing information PL/SQL construct called a cursor lets you name a work area and access its stored information A cursor is a mechanism used to fetch more than one row in a Pl/SQl block.

    166. What is the purpose of a cluster?
    Oracle does not allow a user to specifically locate tables, since that is a part of the function of the RDBMS. However, for the purpose of increasing performance, oracle allows a developer to create a CLUSTER. A CLUSTER provides a means for storing data from different tables together for faster retrieval than if the table placement were left to the RDBMS

    167. What is OCI. What are its uses?
    Oracle Call Interface is a method of accesing database from a 3GL program. Uses--No precompiler is required,PL/SQL blocks are executed like other DML statements.
    The OCI library provides
    --functions to parse SQL statemets
    --bind input variables
    --bind output variables
    --execute statements
    --fetch the results

    168. How you open and close a cursor variable. Why it is required?
    OPEN cursor variable FOR SELECT...Statement
    CLOSE cursor variable In order to associate a cursor variable with a particular SELECT statement OPEN syntax is used. In order to free the resources used for the query CLOSE statement is used.

    169. Display Odd/ Even number of records?
    Odd number of records:
    select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);
    Even number of records:
    select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp)

    170. What are various constraints used in SQL?

    171. Can cursor variables be stored in PL/SQL tables.If yes how. If not why?
    No, a cursor variable points a row which cannot be stored in a two-dimensional PL/SQL table.

    172. Difference between NO DATA FOUND and %NOTFOUND?
    NO DATA FOUND is an exception raised only for the SELECT....INTO statements when the where clause of the querydoes not match any rows. When the where clause of the explicit cursor does not match any rows the %NOTFOUND attribute is set to TRUE instead.

    173. Can you use a commit statement within a database trigger?

    174. What WHERE CURRENT OF clause does in a cursor?
    SELECT num_credits INTO v_numcredits FROM classes
    WHERE dept=123 and course=101;
    UPDATE students
    FHKO;;;;;;;;;SET current_credits=current_credits+v_numcredits

2009-2016 All rights reserved.