Skip to Main Content
Forums

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

delete blob column

Locke90210Jun 7 2013 — edited Jun 10 2013
Hey all,
I have these tables:
Products 
CREATE TABLE "COMPDB"."PRODUCTS" 
   (	"PRO_ID" NUMBER NOT NULL ENABLE, 
	"PRO_NAME" VARCHAR2(40 BYTE) NOT NULL ENABLE, 
	"COMPETITOR" NUMBER NOT NULL ENABLE, 
	"CATEGORY" NUMBER NOT NULL ENABLE, 
	"INS_USER" VARCHAR2(50 BYTE), 
	"UPD_USER" VARCHAR2(50 BYTE), 
	"INS_DATE" DATE, 
	"UPD_DATE" DATE, 
	"VERSION" NUMBER, 
	"PHOTO" BLOB, 
	"FILENAME" VARCHAR2(255 BYTE), 
	"MIMETYPE" VARCHAR2(255 BYTE), 
	"LAST_UPDATE_DATE" DATE, 
	 CONSTRAINT "PRODUCT_PK" PRIMARY KEY ("PRO_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "COMPDB"  ENABLE, 
	 CONSTRAINT "PRO_CAT_FK" FOREIGN KEY ("CATEGORY")
	  REFERENCES "COMPDB"."CATEGORY" ("CAT_ID") ON DELETE CASCADE ENABLE, 
	 CONSTRAINT "PRO_COMP_FK" FOREIGN KEY ("COMPETITOR")
	  REFERENCES "COMPDB"."COMPETITORS" ("COMP_ID") ON DELETE CASCADE ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "COMPDB" 
 LOB ("PHOTO") STORE AS BASICFILE (
  TABLESPACE "COMPDB" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION 
  NOCACHE LOGGING 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ;
 

  CREATE OR REPLACE TRIGGER "COMPDB"."TRG_PRODUCTS_BRI" BEFORE
  INSERT ON PRODUCTS REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW BEGIN IF :NEW.PRO_ID IS NULL THEN
  SELECT PRODUCTS_SEQ.NEXTVAL INTO :NEW.PRO_ID FROM DUAL;
END IF;
:NEW.INS_USER := NVL(:NEW.INS_USER,Comm_Usermanagement.Get_Username);
:NEW.INS_DATE := sysdate;
:NEW.UPD_USER := NVL(:NEW.UPD_USER,Comm_Usermanagement.Get_Username);
:NEW.UPD_DATE := sysdate;
:NEW.VERSION  := NVL(:NEW.VERSION,0);
END;
/
ALTER TRIGGER "COMPDB"."TRG_PRODUCTS_BRI" ENABLE;
 

 CREATE OR REPLACE TRIGGER "COMPDB"."TRG_PRODUCTS_BRU" BEFORE
 UPDATE ON PRODUCTS REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW BEGIN IF NOT UPDATING ('UPD_USER')
 OR :NEW.UPD_USER IS NULL THEN :NEW.UPD_USER := Comm_Usermanagement.Get_Username;
END IF;
IF NOT UPDATING ('UPD_DATE') OR :NEW.UPD_DATE IS NULL THEN
  :NEW.UPD_DATE                               := sysdate;
END IF;
IF NOT UPDATING ('VERSION') THEN
  :NEW.VERSION := :OLD.VERSION + 1;
END IF;
END;
/
ALTER TRIGGER "COMPDB"."TRG_PRODUCTS_BRU" ENABLE;
Competitors: 
  CREATE TABLE "COMPDB"."COMPETITORS" 
   (	"COMP_ID" NUMBER NOT NULL ENABLE, 
	"COMP_NAME" VARCHAR2(40 BYTE) NOT NULL ENABLE, 
	"INS_USER" VARCHAR2(50 BYTE), 
	"UPD_USER" VARCHAR2(50 BYTE), 
	"INS_DATE" DATE, 
	"UPD_DATE" DATE, 
	"VERSION" NUMBER, 
	 CONSTRAINT "COMPETITOR_PK" PRIMARY KEY ("COMP_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "COMPDB"  ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "COMPDB" ;
 

  CREATE OR REPLACE TRIGGER "COMPDB"."TRG_COMPETITORS_BRI" BEFORE
  INSERT ON COMPETITORS REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW BEGIN IF :NEW.COMP_ID IS NULL THEN
  SELECT COMPETITORS_SEQ.NEXTVAL INTO :NEW.COMP_ID FROM DUAL;
END IF;
:NEW.INS_USER := NVL(:NEW.INS_USER,Comm_Usermanagement.Get_Username);
:NEW.INS_DATE := sysdate;
:NEW.UPD_USER := NVL(:NEW.UPD_USER,Comm_Usermanagement.Get_Username);
:NEW.UPD_DATE := sysdate;
:NEW.VERSION  := NVL(:NEW.VERSION,0);
END;
/
ALTER TRIGGER "COMPDB"."TRG_COMPETITORS_BRI" ENABLE;
 

  CREATE OR REPLACE TRIGGER "COMPDB"."TRG_COMPETITORS_BRU" BEFORE
  UPDATE ON COMPETITORS REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW BEGIN IF NOT UPDATING ('UPD_USER')
  OR :NEW.UPD_USER IS NULL THEN :NEW.UPD_USER := Comm_Usermanagement.Get_Username;
END IF;
IF NOT UPDATING ('UPD_DATE') OR :NEW.UPD_DATE IS NULL THEN
  :NEW.UPD_DATE                               := sysdate;
END IF;
IF NOT UPDATING ('VERSION') THEN
  :NEW.VERSION := :OLD.VERSION + 1;
END IF;
END;
/
ALTER TRIGGER "COMPDB"."TRG_COMPETITORS_BRU" ENABLE;
Category 
 

  CREATE TABLE "COMPDB"."CATEGORY" 
   (	"CAT_ID" NUMBER NOT NULL ENABLE, 
	"CAT_NAME" VARCHAR2(40 BYTE) NOT NULL ENABLE, 
	"INS_USER" VARCHAR2(50 BYTE), 
	"UPD_USER" VARCHAR2(50 BYTE), 
	"INS_DATE" DATE, 
	"UPD_DATE" DATE, 
	"VERSION" NUMBER, 
	 CONSTRAINT "CATEGORY_PK" PRIMARY KEY ("CAT_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "COMPDB"  ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "COMPDB" ;
 

  CREATE OR REPLACE TRIGGER "COMPDB"."TRG_CATEGORY_BRI" BEFORE
  INSERT ON CATEGORY REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW BEGIN IF :NEW.CAT_ID IS NULL THEN
  SELECT CATEGORY_SEQ.NEXTVAL INTO :NEW.CAT_ID FROM DUAL;
END IF;
:NEW.INS_USER := NVL(:NEW.INS_USER,Comm_Usermanagement.Get_Username);
:NEW.INS_DATE := sysdate;
:NEW.UPD_USER := NVL(:NEW.UPD_USER,Comm_Usermanagement.Get_Username);
:NEW.UPD_DATE := sysdate;
:NEW.VERSION  := NVL(:NEW.VERSION,0);
END;
/
ALTER TRIGGER "COMPDB"."TRG_CATEGORY_BRI" ENABLE;
 

  CREATE OR REPLACE TRIGGER "COMPDB"."TRG_CATEGORY_BRU" BEFORE
  UPDATE ON CATEGORY REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW BEGIN IF NOT UPDATING ('UPD_USER')
  OR :NEW.UPD_USER IS NULL THEN :NEW.UPD_USER := Comm_Usermanagement.Get_Username;
END IF;
IF NOT UPDATING ('UPD_DATE') OR :NEW.UPD_DATE IS NULL THEN
  :NEW.UPD_DATE                               := sysdate;
END IF;
IF NOT UPDATING ('VERSION') THEN
  :NEW.VERSION := :OLD.VERSION + 1;
END IF;
END;
/
ALTER TRIGGER "COMPDB"."TRG_CATEGORY_BRU" ENABLE;
Now my problem:
In want to delete the blob column(The Content) in Products and the columns, which belong to this column(Filename,Mimetype,Last_update_date).
EMPTY_BLOB() doesn't work :-/
also set to null.

I can still download the file,though I can see null in this column in sql developer.


Hope someone can help.
This post has been answered by Mike Kutz on Jun 7 2013
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 8 2013
Added on Jun 7 2013
#apex, #apex-discussions
7 comments
3,541 views