concurrent executables method codes

,      execution_method_code in fnd_executables

 

               'A', 'Spawned'
             , 'B', 'Request Set Stage Function'
             , 'E', 'Perl Concurrent Program'
             , 'H', 'Host'
             , 'I', 'PL/SQL Stored Procedure'
             , 'J', 'Java Stored Procedure'
             , 'K', 'Java Concurrent Program'
             , 'L', 'SQL*Loader'
             , 'M', 'Multi Language Function'
             , 'P', 'Oracle Reports'
             , 'Q', 'SQL*Plus'
             , 'S', 'Immediate'

Item Sub Inventories Conversion Script

Staging Table Script:

  CREATE TABLE XXXX_INV_ITEM_SUB_INV_STG

   ( "OLD_INV_ORGANIZATION_CODE" VARCHAR2(250 BYTE), 

"INVENTORY_ORGANIZATION_CODE" VARCHAR2(250 BYTE), 

"OLD_SUBINVENTORY" VARCHAR2(250 BYTE), 

"SUBINVENTORY_CODE" VARCHAR2(250 BYTE), 

"OLD_ITEM_CODE" VARCHAR2(250 BYTE), 

"ITEM_CODE" VARCHAR2(250 BYTE), 

"DESCRIPTION" VARCHAR2(250 BYTE), 

"MIN_MAX_PLANNING" VARCHAR2(250 BYTE), 

"MIN_QTY" NUMBER, 

"MAX_QTY" NUMBER, 

"OLD_UNIT_OF_MEASURE" VARCHAR2(250 BYTE), 

"UOM" VARCHAR2(250 BYTE), 

"ORDER_MOD_FIX_LOT_MULTIPLE" VARCHAR2(250 BYTE), 

"SOURCING_TYPE" VARCHAR2(250 BYTE), 

"SOURCING_ORGANIZATION" VARCHAR2(250 BYTE), 

"OLD_SOURCING_SUBINVENTORY" VARCHAR2(250 BYTE), 

"SOURCING_SUBINVENTORY" VARCHAR2(250 BYTE), 

"ERROR_MSG" VARCHAR2(4000 BYTE), 

"STATUS" VARCHAR2(100 BYTE), 

"REFERENCE1" VARCHAR2(150 BYTE), 

"PROGRAM_ID" NUMBER, 

"SOURCE" VARCHAR2(20 BYTE)

   );

Package Specification ;

CREATE OR REPLACE PACKAGE xxxx_item_sub_inv_conv_pkg
AS
   PROCEDURE xxxx_item_sub_inv_conv_proc (
      errbuff     OUT      VARCHAR2,
      retcode     OUT      NUMBER);
END xxxx_item_sub_inv_conv_pkg;
/

Package Body ;

CREATE OR REPLACE PACKAGE BODY APPS.xxxx_item_sub_inv_conv_pkg
AS
   PROCEDURE xxxx_item_sub_inv_conv_proc (
      errbuff     OUT      VARCHAR2,
      retcode     OUT      NUMBER)
   AS
      CURSOR c_sub_inv
      IS
         SELECT old_inv_organization_code,inventory_organization_code,old_subinventory,subinventory_code,
               OLD_ITEM_CODE,ITEM_CODE,DESCRIPTION,MIN_MAX_PLANNING,MIN_QTY,MAX_QTY,OLD_UNIT_OF_MEASURE,
               uom,order_mod_fix_lot_multiple,
               DECODE(sourcing_type,'Subinventory',3,'Supplier',2,'Inventory',1,sourcing_type) sourcing_type,
               SOURCING_ORGANIZATION,OLD_SOURCING_SUBINVENTORY,SOURCING_SUBINVENTORY
               FROM xxxx_inv_item_sub_inv_stg xis
          WHERE status = 'V'
          AND NOT EXISTS (SELECT 1
                         FROM mtl_item_sub_inventories   misi
                              ,mtl_secondary_inventories msi
                              ,mtl_system_items_b        msb
                              ,mtl_parameters            mp
                         WHERE 1=1
                         AND misi.inventory_item_id=msb.inventory_item_id
                         AND misi.organization_id= msb.organization_id
                         AND misi.secondary_inventory=msi.secondary_inventory_name
                         AND misi.organization_id=mp.organization_id
                         AND mp.organization_code=xis.inventory_organization_code
                         AND msb.segment1=xis.item_code
                         AND msi.secondary_inventory_name=xis.subinventory_code);

      ln_org_id        NUMBER;
      ln_item_id       NUMBER;
      ln_source_org    NUMBER;
      LN_CONC_REQ_ID   number;
      LN_PROCESSED     number:= 0;
      ln_not_processed number:= 0;
      lc_error_msg     VARCHAR2 (4000);
      lc_error_flag    VARCHAR2 (1);
      LN_COUNT1        number          := 0;
      lc_ins_err       VARCHAR2(1000);
      ln_inventory_planning_code  NUMBER;
     
   BEGIN
      LN_CONC_REQ_ID := FND_PROFILE.value ('CONC_REQUEST_ID');
      FND_FILE.PUT_LINE(FND_FILE.log,' CONC_REQUEST_ID : '||LN_CONC_REQ_ID);
      FOR a_cur IN c_sub_inv
      LOOP
        LC_ERROR_FLAG:='N';      
         begin
              SELECT organization_id INTO ln_org_id FROM org_organization_definitions
               WHERE organization_code = a_cur.inventory_organization_code;
 
              SELECT inventory_item_id INTO ln_item_id FROM mtl_system_items_b
               WHERE segment1 = a_cur.item_code AND organization_id = ln_org_id;

              begin
                    SELECT organization_id INTO ln_source_org FROM org_organization_definitions
                     where ORGANIZATION_CODE = A_CUR.SOURCING_ORGANIZATION;
              EXCEPTION
                  when NO_DATA_FOUND then
                        ln_source_org:='';
                  WHEN OTHERS THEN
                        fnd_file.put_line (fnd_file.LOG, SQLERRM||' Error occured while fetching source organization.');
              END;

           UPDATE xxxx_inv_item_sub_inv_stg SET program_id = ln_conc_req_id WHERE status = 'V'
               AND old_inv_organization_code = a_cur.old_inv_organization_code
               AND old_subinventory = a_cur.old_subinventory
               and OLD_ITEM_CODE = A_CUR.OLD_ITEM_CODE;
            COMMIT;
         EXCEPTION
            WHEN OTHERS
            then
               lc_error_flag='Y';
               lc_error_msg := SQLERRM;
               xxxx_conv_error_insert_proc ('ITEM SUBINVENTORY','I','PROGRAM_ID',ln_conc_req_id,lc_error_msg,ln_conc_req_id,NULL,NULL,NULL,NULL,lc_ins_err);
               fnd_file.put_line (fnd_file.LOG,'Error in Fetching Program Id');
         END;

         begin
            SELECT DECODE(a_cur.min_qty,NULL,6,2) INTO ln_inventory_planning_code FROM dual;
           
            fnd_file.put_line (fnd_file.LOG,'enters into sub inventory loop with -'
                               || a_cur.inventory_organization_code ||'-'||ln_item_id||'-'||a_cur.max_qty||'-'||a_cur.min_qty||'-'||ln_org_id||'-'||
                             a_cur.subinventory_code||'-'|| ln_source_org||'-'||a_cur.sourcing_subinventory||'-'||a_cur.sourcing_type
                              );

            INSERT INTO mtl_item_sub_inventories
                        (created_by, creation_date, inventory_item_id,
                         inventory_planning_code, last_update_date,
                         last_update_login,
                         last_updated_by, max_minmax_quantity,
                         min_minmax_quantity, organization_id,
                         secondary_inventory, source_organization_id,
                         source_subinventory, source_type
                        )
                 VALUES (fnd_profile.VALUE ('USER_ID'), SYSDATE, ln_item_id,
                         ln_inventory_planning_code,                         --INVENTORY_PLANNING_CODE,
                           SYSDATE,
                         fnd_profile.VALUE ('USER_ID'),
                         fnd_profile.VALUE ('USER_ID'), a_cur.max_qty,
                         a_cur.min_qty, ln_org_id,
                         a_cur.subinventory_code, ln_source_org,
                         a_cur.sourcing_subinventory, a_cur.sourcing_type
                        );

            fnd_file.put_line (fnd_file.LOG,'Inserted into Item Sub Inventories Table');
            commit;
         EXCEPTION
            WHEN OTHERS
            THEN
               lc_error_flag := 'Y';
               LC_ERROR_MSG :=SQLERRM;
               xxxx_conv_error_insert_proc ('ITEM SUBINVENTORY','I','Insertion','Organization_code : '||a_cur.old_inv_organization_code||'Subinventory : '||a_cur.old_subinventory||'Item Code : '||A_CUR.OLD_ITEM_CODE,lc_error_msg,ln_conc_req_id,NULL,NULL,NULL,NULL,lc_ins_err);
               fnd_file.put_line(fnd_file.LOG,'Exception Occured  while inserting into Item Sub Inventories '|| SQLERRM);
         END;

         BEGIN
            IF lc_error_flag = 'Y' THEN
               UPDATE xxxx_inv_item_sub_inv_stg
                  SET status = 'E',error_msg=lc_error_msg
                WHERE old_inv_organization_code =a_cur.old_inv_organization_code
                  AND old_subinventory = a_cur.old_subinventory
                  AND old_item_code = a_cur.old_item_code
                  and PROGRAM_ID = LN_CONC_REQ_ID;
              commit;
               LN_NOT_PROCESSED := LN_NOT_PROCESSED + 1;
               fnd_file.put_line (fnd_file.LOG,'Error Occurred status set to E');
            ELSE
               UPDATE xxxx_inv_item_sub_inv_stg
                  SET status = 'P'
                WHERE old_inv_organization_code =a_cur.old_inv_organization_code
                  AND old_subinventory = a_cur.old_subinventory
                  AND old_item_code = a_cur.old_item_code
                  and PROGRAM_ID = LN_CONC_REQ_ID;
                commit;
                  LN_PROCESSED:=LN_PROCESSED+1;
               fnd_file.put_line (fnd_file.LOG, 'Record is Processed status set to P');
            END IF;
         END;
      END LOOP;
      FND_FILE.PUT_LINE (FND_FILE.log,'Number of Records Processed : '||LN_PROCESSED);
      fnd_file.put_line (fnd_file.LOG,'Number of Records Not Processed : '||ln_not_processed);
   END xxxx_item_sub_inv_conv_proc;
END xxxx_item_sub_inv_conv_pkg;
/

comparision of dates in CO class file

Comparision of Dates in Controller

1)  

    Date StartDate= (Date)CurrentRow.getAttribute("StartDate");

    Date CurrentDate = (Date)am.getOADBTransaction().getCurrentDBDate();

    java.sql.Date sdate = (java.sql.Date) StartDate.dateValue();

    java.sql.Date cdate = (java.sql.Date) CurrentDate.dateValue();

    int days = daysBetween(sdate,cdate );

 

  public int daysBetween(java.sql.Date d1, java.sql.Date d2){

     return (int)( (d2.getTime() - d1.getTime()) / (1000 * 60 * 60 * 24));

         }

2)

OAViewObject oaviewobject1 = (OAViewObject)am.findViewObject("NewEmployeeVO1");

System.out.println("1");
if (oaviewobject1 != null)
{
System.out.println("Inside");
oaviewobject1.reset(); //New line added
oaviewobject1.next(); //new line added
OARow row = (OARow)oaviewobject1.getCurrentRow();


Date sDate =(Date)row.getAttribute("EffectiveStartDate");

Date eDate =(Date)row.getAttribute("EffectiveEndDate");
java.util.Date VChangeDateTime =
new java.util.Date(sDate.timestampValue().getTime());

if (sDate !=null && eDate !=null)
{
long m1 = sDate.timestampValue().getTime() ;
long m2 = eDate.timestampValue().getTime();

long diff = m2 - m1;

int diffDays = Math.round(diff / (24 * 60 * 60 * 1000));

if (diffDays >365)
{
System.out.println("Date is greater than 365");
}
else

{
System.out.println("Date is less than 365");
}

JDR_UTILS packages in oaf

Jdr_utils package is most important APIs commonly used in OA Framework customisation. The most import scripts used are as follows :

·                     JDR_UTILS.LISTDOCUMENTS

·                     JDR_UTILS.PRINTDOCUMENT

·                     JDR_UTILS.DELETEDOCUMENT

                       JDR_UTILS.LISTCUSTOMIZATIONS

. 

execute jdr_utils.listcustomizations('/oracle/apps/irc/candidateSelfService/server/JobResultsSSVO');


execute jdr_utils.printdocument('/oracle/apps/irc/candidateSelfService/server/customizations/site/0/JobResultsSSVO');



execute jdr_utils.deletedocument('/oracle/apps/irc/candidateSelfService/server/customizations/site/0/JobResultsSSVO');

DECLARE 
BEGIN 
    jdr_utils.listdocuments ( '/oracle/apps/per/selfservice.webui' , TRUE ) ;
END ;


DECLARE 
BEGIN
    jdr_utils.printdocument ( ' p_document => /oracle/apps/per/selfservice.webui/ somepagePG ') ;
END ;


DECLARE 
BEGIN 
    jdr_utils.deletedocument ( ' p_document => /oracle/apps/per/selfservice.webui/ somepagePG ') ;
END ;

BOM ( Bill Of Materials) Extraction Script in R12

SELECT msi1.segment1 Parent_item,
  msi1.description Parent_item_description,
  msi1.item_type Parent_item_type,
  msi2.segment1 Child_item,
  msi2.description Child_item_description,
  msi2.item_type Child_item_type,
  --assembly_item_id, bom.organization_id, bic.component_item_id,
  bic.component_quantity,
  bic.effectivity_date,
  bic.implementation_date,
  bic.disable_date,
  ood.organization_code,
  OOD.ORGANIZATION_NAME,
  bom.assembly_type,
  bic.planning_factor,
  bic.quantity_related,
  bic.include_in_cost_rollup,
  Bic.Check_Atp,
  Bic.Component_Yield_Factor
FROM apps.bom_bill_of_materials bom,
  Apps.Bom_Inventory_Components Bic,
  apps.mtl_system_items_b msi1,
  apps.mtl_system_items_b msi2,
  apps.org_organization_definitions ood
WHERE BOM.bill_sequence_id = bIC.bill_sequence_id
      AND msi1.inventory_item_id         = bom.assembly_item_id
      AND msi1.organization_id              = bom.organization_id
      AND msi2.inventory_item_id            = bic.component_item_id
      AND  msi2.organization_id             = bom.organization_id
      AND ood.organization_id            = bom.organization_id
      And Nvl(Bic.Disable_Date, Sysdate + 1) > Sysdate
      and bom.organization_id not in (select organization_id from apps.org_organization_definitions where organization_code not in ('CSM', 'DS3', 'FS1'))
      and UPPER(msi1.item_type) not in ('FG', 'NRE', 'PC')
      AND Upper(Msi2.Item_Type) NOT IN ('RW','FG', 'NRE', 'RETICLE')
      AND Msi1.Inventory_Item_Status_Code='Active' ;
     
     
      

Oracle ASL Extraction Script R12

-- Item ASL

SELECT
  DECODE(NVL(MTL.SEGMENT1,'COMMODITY'),'COMMODITY','COMMODITY','ITEM') TYPE,
  MC.CONCATENATED_SEGMENTS COMMODITY,
  MTL.SEGMENT1 ITEM,
  MTL.DESCRIPTION DESCRIPTION,
  PASL.VENDOR_BUSINESS_TYPE,
  PV.VENDOR_NAME,
  PVS.VENDOR_SITE_CODE,
  OU.NAME OPERATING_UNIT,
  DECODE(PASL.USING_ORGANIZATION_ID, -1, 'Y','N') GLOBAL,
  HR.organization_code owning_org,
  PASL.PRIMARY_VENDOR_ITEM Supplier_Item,
  PASL.COMMENTS,
  PAA.PURCHASING_UNIT_OF_MEASURE,
  PAD.DOCUMENT_TYPE_CODE,
  POH.SEGMENT1 DOCUMENT_NUM,
  --POH.authorization_status,
  POL.LINE_NUM DOCUMENT_LINE_NUM,
  PAA.CONSIGNED_FROM_SUPPLIER_FLAG,
  PAA.CONSUME_ON_AGING_FLAG,
  PAA.AGING_PERIOD
FROM
APPS.PO_APPROVED_SUPPLIER_LIST PASL,
APPS.MTL_SYSTEM_ITEMS_B MTL,
APPS.MTL_CATEGORIES_KFV MC,
APPS.PO_VENDORS PV ,
APPS.PO_VENDOR_SITES_ALL PVS ,
APPS.PO_ASL_STATUSES PAST ,
APPS.PO_ASL_ATTRIBUTES PAA ,
APPS.PO_ASL_DOCUMENTS PAD,
APPS.HR_ORGANIZATION_UNITS OU,
APPS.ORG_ORGANIZATION_DEFINITIONS HR,
APPS.PO_HEADERS_ALL POH,
APPS.PO_LINES_ALL POL
WHERE
 MTL.INVENTORY_ITEM_ID                                       = PASL.ITEM_ID
AND MTL.ORGANIZATION_ID                                      = PASL.OWNING_ORGANIZATION_ID
AND MC.CATEGORY_ID (+)                                       = PASL.CATEGORY_ID
AND HR.OPERATING_UNIT                                        = OU.ORGANIZATION_ID
AND HR.ORGANIZATION_ID                                       = PASL.OWNING_ORGANIZATION_ID
AND PVS.VENDOR_SITE_ID(+)                                    = PASL.VENDOR_SITE_ID                                    
/*AND ((PASL.VENDOR_SITE_ID                                   IS NOT NULL
AND PVS.VENDOR_SITE_CODE                                    IS NOT NULL)
OR (PASL.VENDOR_SITE_ID                                     IS NULL
AND PVS.VENDOR_SITE_CODE                                    IS NULL))*/
AND PV.VENDOR_ID(+)                                          = PASL.VENDOR_ID                                          
AND PASL.USING_ORGANIZATION_ID                               = PAA.USING_ORGANIZATION_ID
--AND NVL(PAA.CONSIGNED_FROM_SUPPLIER_FLAG,'N')                ='N'
--AND PASL.ASL_STATUS_ID=2
AND PASL.ASL_STATUS_ID                                       = PAST.STATUS_ID
AND PASL.ASL_ID                                              = PAA.ASL_ID
AND PASL.USING_ORGANIZATION_ID                               = PAA.USING_ORGANIZATION_ID
--AND NVL(PASL.DISABLE_FLAG,'N')<>'Y'
AND PAD.ASL_ID(+) = PASL.ASL_ID
AND POH.PO_HEADER_ID(+)                                      = PAD.DOCUMENT_HEADER_ID
AND POL.PO_HEADER_ID(+)                                      = PAD.DOCUMENT_HEADER_ID
AND POL.PO_LINE_ID(+)                                        = PAD.DOCUMENT_LINE_ID
AND( EXISTS (SELECT ASL_ID FROM  
            APPS.PO_ASL_DOCUMENTS PAD,          
            APPS.PO_HEADERS_ALL PH
            WHERE PAD.ASL_ID = PASL.ASL_ID
              --AND PAD.OWNING_ORGANIZATION_ID=PASL.OWNING_ORGANIZATION_ID            
             -- AND PH.AUTHORIZATION_STATUS in ('CLOSED','CANCELLED')
              AND PH.PO_HEADER_ID=PAD.DOCUMENT_HEADER_ID            
              ) OR
   (NOT EXISTS(SELECT ASL_ID FROM   APPS.PO_ASL_DOCUMENTS PAD
            WHERE ASL_ID = PASL.ASL_ID          
            )));

/

-- Commodity ASL

SELECT
  DECODE(NVL(NULL,'COMMODITY'),'COMMODITY','COMMODITY','ITEM') TYPE,
  MC.CONCATENATED_SEGMENTS COMMODITY,
  PASL.VENDOR_BUSINESS_TYPE,
  PV.VENDOR_NAME,
  PVS.VENDOR_SITE_CODE,
  OU.NAME OPERATING_UNIT,
  DECODE(PASL.USING_ORGANIZATION_ID, -1, 'Y','N') GLOBAL,
  HR.organization_code owning_org,
  PASL.PRIMARY_VENDOR_ITEM Supplier_Item,
  PASL.COMMENTS,
  PAA.PURCHASING_UNIT_OF_MEASURE,
 PAA.CONSIGNED_FROM_SUPPLIER_FLAG,
  PAA.CONSUME_ON_AGING_FLAG,
  PAA.AGING_PERIOD
FROM
APPS.PO_APPROVED_SUPPLIER_LIST PASL,
APPS.MTL_CATEGORIES_KFV MC,
APPS.PO_VENDORS PV ,
APPS.PO_VENDOR_SITES_ALL PVS ,
APPS.PO_ASL_STATUSES PAST ,
APPS.PO_ASL_ATTRIBUTES PAA ,
APPS.HR_ORGANIZATION_UNITS OU,
APPS.ORG_ORGANIZATION_DEFINITIONS HR
WHERE 1=1
AND MC.CATEGORY_ID (+)                                       = PASL.CATEGORY_ID
AND HR.OPERATING_UNIT                                        = OU.ORGANIZATION_ID
AND HR.ORGANIZATION_ID                                       = PASL.OWNING_ORGANIZATION_ID
AND PVS.VENDOR_SITE_ID(+)                                    = PASL.VENDOR_SITE_ID                                    
AND PV.VENDOR_ID(+)                                          = PASL.VENDOR_ID                                          
AND PASL.USING_ORGANIZATION_ID                               = PAA.USING_ORGANIZATION_ID
AND PASL.ASL_STATUS_ID=2
AND PASL.ASL_STATUS_ID                                       = PAST.STATUS_ID
AND PASL.ASL_ID                                              = PAA.ASL_ID
AND PASL.USING_ORGANIZATION_ID                               = PAA.USING_ORGANIZATION_ID
AND PASL.ITEM_ID is  null

PLSQL Basic Interview questions and answers

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

2)What is the basic structure of PL/SQL ?
A: PL/SQL uses block structure as its basic structure. Anonymous blocks or nested blocks can be used in PL/SQL.
DECLARE
--all the variables u use in ur program should be declared here
---
BEGIN
--application logic goes here
--
EXCEPTION HANDLING
--very imp
END

3)What are the datatypes a available in PL/SQL ?
A: Some scalar data types such as NUMBER, VARCHAR2, DATE, CHAR, LONG, BOOLEAN. Some composite data types such as RECORDS & COLLECTIONS.

4)What are % TYPE and % ROWTYPE ? What are the advantages of using these over datatypes?
A:%ROWTYPE allows you to associate a variable with an entire table row. The
%TYPE associates a variable with a single column type.

5) What is difference between % ROWTYPE and TYPE RECORD ?
A:% 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.

6)What is PL/SQL table ?
A: Objects of type TABLE are called "PL/SQL tables", which are modeled as (but not the same as) database tables, PL/SQL tables use a primary PL/SQL tables can have one column and a primary key.

7)What is a cursor ? Why Cursor is required ?
A: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.
The oracle server uses works areas called private sql area.Here all the DML statement is executed and to processing statement.basically

8)Explain the two type of Cursors ?
A:there are two types of cursor
1.Implicit cursor.
2.Explicit cursor.
Implicit cursor is open for all DML statement.after execute the statement cursor is atomatically closed.
Explicit cursor is created by programmer.
explicit cursor is needed when
query returns more than one rows.
In that case,programmer creates
explicit cursor.open the cursor.
then fetch the value from the active set.
after fetching all the value,
cursor is closed by programmer.

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

10)What are the cursor attributes used in PL/SQL ?
A:%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.

11) How to avoid using cursors? What to use instead of cursor and in what cases to do so?
Ajust use subquery in for clause
ex:
for emprec in (select * from emp)
loop
dbms_output.put_line(emprec.empno);
end loop;
no exit sttement needed..implicit open,fetch,close occurs

12)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;
END LOOP;

13)What will happen after commit statement ?
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.
After commit statement, all changed data will be saved in permenent database.
After commit all the locks on the database tables are leased.

14)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.

What is a database trigger ? Name some usages of database trigger ?
Trigger is a stored plsql program, which is fired automatically when an event occur on the database.

Triggers can be created on
1.DML statements
2.DDL staments
3.Viwes(for views only insted of trigger is valid)
4.Any database event occures like database startup or shutdown.

Advantages of triggers:
1.Data auditing.
2.Enforce complex Business rules.
3.providing security.

How many types of database triggers can be specified on a table ? What are they ?
We can fire 12 type of triggers.
DML: Insert, Update, Delete
Timing: Before, After
Levels: Row, Statment

all combination of these three will be 12 can be fired.Statement Trigger which fires only once regardless of number of rows.
Row Trigger which fires once for each row.

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.
we can use TCL commands in trigger by using autonomous transactions feature of oracle.

What are two virtual tables available during database trigger execution ?
Two tables are: OLD and NEW.
Insert Trigger :
OLD - no value.
NEW - inserted value.

UPDATE TRIGGER -
OLD- old value.
NEW- new updated value.

DELETE TRIGGER -
OLD - old value.
NEW - no value.

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.Trigger will be called... Based on the event what trigger as to do. if trigger is also doing the same update statment then Mutating Table occurs. if trigger is not doing any DML statment nothing happens just Trigger will be called..

Write the order of precedence for validation of a column in a table I. done using Database triggers.ii. done using Integarity Constraints. ?
1. Before Statement Trigger
2. Integrity constraint
3. After statement trigger

What is an Exception ? What are types of Exception ?
Exception is the error handling part of PL/SQL block.
1. Predefined Exceptions: These are raised by oracle itself.
2. Non-predefined Exceptions: Declare the exception in declarative section and allow oracle to raise the error implicitly. This can be done using PRAGMA_EXCEPTION_INIT.
3. User defined exception: We can explictly define the exceptions bases on business rules and raise by using RAISE keyword The types are Predefined and user defined. Some of Predefined exceptions are.
CURSOR_ALREADY_OPEN
DUP_VAL_ON_INDEX
NO_DATA_FOUND
TOO_MANY_ROWS
INVALID_CURSOR
INVALID_NUMBER
LOGON_DENIED
NOT_LOGGED_ON
PROGRAM-ERROR
STORAGE_ERROR
TIMEOUT_ON_RESOURCE
VALUE_ERROR
ZERO_DIVIDE
OTHERS.

What is Pragma EXECPTION_INIT ? Explain the usage ?
The PRAGMA EXECPTION_INIT tells the complier to associate an user defined exception with an oracle error number. To get an error message of a specific oracle error.
x exception;
Pragma exception_int(x,error number)

What are the return values of functions SQLCODE and SQLERRM ?
SQLCODE returns the latest code of the error that has occurred.
SQLERRM returns the relevant error message of the SQLCODE.

Where the Pre_defined_exceptions are stored ?
Standard_package it consist of two sub packages...
for Ora-Errors
1.Oracle package(ORA)
for pls -Errors
2.pl/sql package(PLS)

What is a stored procedure ?
A stored procedure is a named pl/sql block which performs an action.It is stored in the database as a schema object and can be repeatedly executed.It can be invoked, parameterised and nested.

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.
A function can call directly by sql statement like select func_name from dual while procedure cannot.
In case of function, only it takes IN parameters
IN case of procedure
only it take IN,OUT,INOUT parameters..

What are advantages fo Stored Procedures ?
Extensibility,Modularity, Reusability, Maintainability and one time compilation.
Faster in execution: As Procedure is a complied schema object and stored in database, hence it takes less time to execution.

What are the modes of parameters that can be passed to a procedure ?
IN,OUT,IN-OUT parameters.
IN parameter is the default mode which acts as a constant inside calling environment.value passed in this parameter can not be changed.OUT parameter mode is used to pass value from calling environment into main block,here we can change the value.It acts as a variable inside calling environment.
INOUT parameter mode which pass value into calling environment and will get the value back in main block.
IN parameter mode uses call by reference method to pass value from formal parameter to actual parameter.
OUT & INOUT parameter mode uses call by value method to pass values.

What are the  parts of a procedure ?
basically procedure has three
parts
1.variable declaretion(optional)
2.body(mandetory)
3.Exception(optional)

Give the structure of the procedure ?
CREATE [OR REPLACE] PROCEDURE proc_name [Parameter List]
IS
BEGIN
-- statements
[EXCEPTION]
-- Exception handling
END;

Give the structure of the function ?
create or replace function <name>(arg1,arg2,....)
return datatype
as
....
variable declaration
begin
...
program code
...
return <variable>
exception
exception statement
end;

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');

What is Overloading of procedures ?
Same procedure name with different parameters called procedure overloading, parameters may different by their datatypes, sequence and position.
eg1:
get_employee(Emp_id number);
get_employee(Emp_id Varchar(20));
eg2:
get_employee(ID number, name char(20))
get_employee(ID Number, name char(20), dept char(20))
get_employee(name char(20), dept char(20),ID Number)

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.

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.
One more differene is cursor declared in a package specification must have RETURN type

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);
EXEC SQL EXECUTE
b.
BEGIN
PACKAGE NAME.PROCEDURE NAME (parameters)
variable := PACKAGE NAME.FUNCTION NAME (arguments);
END;
END EXEC;
c. EXECUTE PACKAGE NAME.PROCEDURE if the procedures does not have any out/in-out parameters. A function can not be called.

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

What is autonomous Transaction? Where are they used?
Autonomous transaction is the transaction which acts independantly from the calling part and could commit the process done.
example using prgma autonomous incase of mutation problem happens in a trigger.An autonomous transaction is a transaction that is started within the context of another transaction, called the parent transaction, but is independent of it.
An autonomous transaction can be committed or rolled back regardless of the state of the parent transaction.
We can specify a transaction as autonomous using a PRAGMA.
PRAGMA AUTONOMOUS TRANSACTION

What is ref cursor in pl/sql?
Ref Cursors known as Dynamic cursors cane be associated with as many SELECT statements you want at different times.You can associate with different SELECT stataments at dynamic time.
Static cursor (general) is associated with only one SELECT statement.It can't be with many.That too we can't use at dynamic time.

Declare
TYPE empcurtyp IS REF CURSOR RETURN emp%ROWTYPE;
refcur1  empcurtyp;
Begin
Open refcur1 for select * from emp;
Open refcur1 for select * from dept;
End;

What is mutating trigger? How do you resolve it?If a trigger body is trying to update dependent tables, will it get a mutating error?
If a trigger body is trying to update dependent tables the best approach in this case to avoid mutating trigger errors would be to make the transactions independent and not associated with triggers which is technically called as autonomous transactions.

What are the advantages of bulk binding in PL/SQL?
It retreives multiple rows with a single fetch...It increases the performance by reducing no.of context switches

How many number of triggers can be created on a table?
There is no limit on number of triggers on one table.
you can write as many u want for insert,update or delte by diff names.

What is the difference between stand alone procedures and stored procedures?

How to tune package, how can we see the output, which logic taking more time to execute?

Can we use commit or rollback command in the exception part of PL/SQL block?
Yes, we can use the TCL commands(commit/rollback) in the exception block of a stored procedure/function.


Cursor Advantages and Disadvantages?
Cursor advantages:

--> Using cursor to getting multiple values.
--> One cursor have multiple select statements
--> Cursor parameters
       i.e cursor cursor_name (parameter datatype)
            is
            select * from table_name
            where(condition)
-->Where Current of Clause: this is use full when the primary key is not present.

Cursor disadvantages:

-->It is returned by only one row at a time.
--> It gives less performance
-->Each time we fetch a row from the cursor, where as a normal cursor select statement query makes only one trip.        

Is it possible to define a RETURN statement in the exception part of a function?
Explain it with example.?
Yes, you can return from exception block.
example: create or replace function TestReturn return varchar2 is
Result varchar2(1);--size 1
begin
Result := 123;--size 3
return Test;

exception when others then
return sqlerrm;
end TestReturn;
---------------------------
declare
test_ varchar2(100);
BEgin
test_ := TestReturn();
dbms_output.put_line(test_);
end;
.above code returns the exception
ORA-06502: PL/SQL: numeric or value error: character string buffer too small

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

Can we create a table by using a procedure or a function?
We can create a table in procedure by executing a string statement using EXECUTE IMMEDIATE or using DBMS_SQL.EXECUTE.

How to return more than one value from a function?
f you want to return more than 1 value & dont want to use ref curser...try this, 1. Return comma separated varchar & then use dbms_utility.comma_to_table function to parse them.2. Try returning variable of type pl/sql table or varray.I have not tried number 2, but should work.

Can we give COMMIT or ROLLBACK within a trigger?
Although you cant give commit or rollback in trigger directly, still there are ways to give them
You can use the autonomus transactions to do that

What are the types of collection in PL/SQL? What is the advantage of nested tables?
Varrays,nested tables,index by tables are oracle collection.Index by tables also known as associative array similar to hashtables in java. Varrays are like arrays in java static in nature need to define maximum size at time of declaration.Nested tables are like nested class in java holding a table as a column of a table.