declare
begin
  if :P6_PRODUCT_ID is not null then
    for c1 in (select nvl(dbms_lob.getlength(product_image),0) l
               from demo_product_info
               where product_id = :P6_PRODUCT_ID)
    loop
      if c1.l > 0 then
        return true;
      end if;
    end loop;
  end if;
  return false;
end;

CREATE TABLE  "PHOTO" 
   (    "ID" NUMBER NOT NULL ENABLE, 
    "NEDVIJ_ID" NUMBER, 
    "PHOTO" BLOB, 
    "MIMETYPE" VARCHAR2(255), 
    "FILENAME" VARCHAR2(400), 
    "IMAGE_LAST_UPDATE" DATE, 
     CONSTRAINT "PHOTO_PK" PRIMARY KEY ("ID") ENABLE
   ) ;ALTER TABLE  "PHOTO" ADD CONSTRAINT "PHOTO_CON" FOREIGN KEY ("NEDVIJ_ID")
      REFERENCES  "NEDVIZHIMOST" ("ID") ON DELETE CASCADE ENABLE;

CREATE TABLE  "NEDVIZHIMOST" 
   (    "ID" NUMBER NOT NULL ENABLE, 
    "TIP_NEDV_ID" NUMBER, 
    "REGION_ID" NUMBER, 
    "TIP_SDELKI_ID" NUMBER, 
    "KONTR_ID" NUMBER, 
    "RIELT_ID" NUMBER, 
    "DATE_PRED" DATE, 
    "AKTUALNO" VARCHAR2(10), 
    "COMMISSION" VARCHAR2(10), 
    "ADRESS" VARCHAR2(100), 
    "PRICE" VARCHAR2(10), 
     CONSTRAINT "NEDVIZHIMOST_PK" PRIMARY KEY ("ID") ENABLE
   ) ;ALTER TABLE  "NEDVIZHIMOST" ADD CONSTRAINT "NEDVIZHIMOST_CON" FOREIGN KEY ("TIP_NEDV_ID")
      REFERENCES  "TIP_NEDVIJ" ("ID") ON DELETE CASCADE ENABLE;ALTER TABLE  "NEDVIZHIMOST" ADD CONSTRAINT "NEDVIZHIMOST_CON2" FOREIGN KEY ("REGION_ID")
      REFERENCES  "REGION" ("ID") ENABLE;ALTER TABLE  "NEDVIZHIMOST" ADD CONSTRAINT "NEDVIZHIMOST_CON3" FOREIGN KEY ("TIP_SDELKI_ID")
      REFERENCES  "TIP_SDELKI" ("ID") ENABLE;ALTER TABLE  "NEDVIZHIMOST" ADD CONSTRAINT "NEDVIZHIMOST_CON4" FOREIGN KEY ("KONTR_ID")
      REFERENCES  "СONTRACTOR" ("ID") ENABLE;ALTER TABLE  "NEDVIZHIMOST" ADD CONSTRAINT "NEDVIZHIMOST_CON5" FOREIGN KEY ("RIELT_ID")
      REFERENCES  "REALTOR" ("ID") ENABLE;