CREATE TABLE "ORDER_LINE" 
(   
"ORDER_ID" NUMBER, 

"PRODUCT_ID" NUMBER, 

"QUANTITY" NUMBER, 

"PRICE" NUMBER, 

"PROD_NAME" VARCHAR2(50 BYTE), 

"PROD_ALIAS" VARCHAR2(10 BYTE), 

 CONSTRAINT "OL_PK" PRIMARY KEY ("ORDER_ID", "PRODUCT_ID")

 CONSTRAINT "PROD_ORDER_FK" FOREIGN KEY ("PRODUCT_ID")
 REFERENCES "PRODUCT" ("PROD_CODE") 
)

CREATE TABLE "PRODUCT" 
   (	"PROD_CODE" NUMBER(5,0) NOT NULL, 
	"PROD_NAME" VARCHAR2(50 BYTE) NOT NULL ENABLE, 
	"PROD_DESC" VARCHAR2(50 BYTE), 
	"DEP_ID" NUMBER(4,0), 
	"CAT_ID" NUMBER(3,0), 
	"PROD_COST" NUMBER(5,0) NOT NULL ENABLE, 
	"PROD_ALIAS" VARCHAR2(26 BYTE) NOT NULL ENABLE, 
	 CONSTRAINT "PROD_PK" PRIMARY KEY ("PROD_CODE")
  
	 CONSTRAINT "CAT_FK" FOREIGN KEY ("CAT_ID")
	  REFERENCES "CATEGORY" ("CAT_ID") ENABLE, 
	 CONSTRAINT "DEP_FK" FOREIGN KEY ("DEP_ID")
	  REFERENCES "DEPARTMENT" ("DEP_ID") ENABLE
   )  

INSERT
  INTO ORDER_LINE
  SELECT  ORDER_ID,
          NVL(
              PROD_ID,
              (
               SELECT  PROD_CODE
                 FROM  PRODUCT P
                 WHERE P.PROD_NAME = E.PROD_NAME
                    OR P.PROD_ALIAS = E.PROD_ALIAS
              )
             ) PRODUCT_ID,
          QUANTITY,
          PRICE,
          NVL(
              PROD_NAME,
              (
               SELECT  PROD_NAME
                 FROM  PRODUCT P
                 WHERE P.PROD_ALIAS = E.PROD_ALIAS
              )
             ) PROD_NAME,
          NVL(
              PROD_ALIAS,
              (
               SELECT  PROD_ALIAS
                 FROM  PRODUCT P
                 WHERE P.PROD_NAME = E.PROD_NAME
              )
             ) PROD_ALIAS
    FROM EXTERNAL_TABLE E
/