CREATE TABLE MTL_CUSTOMER_ITEM_XREFS_V (CUSTOMER_ITEM_ID NUMBER,CONCATENATED_SEGMENTS VARCHAR2(40),INVENTORY_ITEM_ID NUMBER,CUSTOMER_NAME VARCHAR2(360),CUSTOMER_ITEM_NUMBER VARCHAR2(50))

INSERT INTO MTL_CUSTOMER_ITEM_XREFS_V (CUSTOMER_ITEM_ID,CONCATENATED_SEGMENTS,INVENTORY_ITEM_ID,CUSTOMER_NAME,CUSTOMER_ITEM_NUMBER) 
VALUES(96454,'PLS-1356',123897,'Oracle',456123)

INSERT INTO MTL_CUSTOMER_ITEM_XREFS_V (CUSTOMER_ITEM_ID,CONCATENATED_SEGMENTS,INVENTORY_ITEM_ID,CUSTOMER_NAME,CUSTOMER_ITEM_NUMBER) 
VALUES(96454,'TV1415-S4-5',123954,'Oracle',456123)

SELECT * FROM MTL_CUSTOMER_ITEM_XREFS_V

DECLARE 
L_CUST_ITEM_ID  NUMBER; 
L_INV_ITEM_NAME VARCHAR2(50); 
L_INV_ITEM_ID   VARCHAR2(50); 
P_CUST_NAME VARCHAR2(50); 
P_CUST_ITEM_NAME VARCHAR2(50); 
BEGIN 
L_CUST_ITEM_ID  := NULL; 
L_INV_ITEM_NAME := NULL; 
L_INV_ITEM_ID   := NULL; 
P_CUST_NAME     := 'Oracle'; 
P_CUST_ITEM_NAME:= '456123'; 
DBMS_OUTPUT.PUT_LINE('************BEFORE SELECT************'); 
DBMS_OUTPUT.PUT_LINE('L_CUST_ITEM_ID :-'||L_CUST_ITEM_ID||CHR(10)||'L_INV_ITEM_NAME :-'||L_INV_ITEM_NAME||CHR(10)||'L_INV_ITEM_ID :-'||L_INV_ITEM_ID); 
 
    SELECT MCIXRF.CUSTOMER_ITEM_ID, 
           CONCATENATED_SEGMENTS, 
           INVENTORY_ITEM_ID 
      INTO L_CUST_ITEM_ID, L_INV_ITEM_NAME, L_INV_ITEM_ID 
      FROM MTL_CUSTOMER_ITEM_XREFS_V MCIXRF 
     WHERE 1 = 1 
       AND MCIXRF.CUSTOMER_NAME = P_CUST_NAME 
       AND MCIXRF.CUSTOMER_ITEM_NUMBER = P_CUST_ITEM_NAME; 
       DBMS_OUTPUT.PUT_LINE('************AFTER SELECT************'); 
DBMS_OUTPUT.PUT_LINE('L_CUST_ITEM_ID :-'||L_CUST_ITEM_ID||CHR(10)||'L_INV_ITEM_NAME :-'||L_INV_ITEM_NAME||CHR(10)||'L_INV_ITEM_ID :-'||L_INV_ITEM_ID); 
EXCEPTION 
WHEN OTHERS THEN 
DBMS_OUTPUT.PUT_LINE('************EXCEPTION ***********'); 
DBMS_OUTPUT.PUT_LINE('L_CUST_ITEM_ID :-'||L_CUST_ITEM_ID||CHR(10)||'L_INV_ITEM_NAME :-'||L_INV_ITEM_NAME||CHR(10)||'L_INV_ITEM_ID :-'||L_INV_ITEM_ID); 
DBMS_OUTPUT.PUT_LINE('************SQLERRM ***********'); 
DBMS_OUTPUT.PUT_LINE(SQLERRM); 
END;