Home » SQL & PL/SQL » SQL & PL/SQL » how to delete blob
how to delete blob [message #224794] Thu, 15 March 2007 09:03 Go to next message
miroconnect@yahoo.com
Messages: 202
Registered: April 2006
Senior Member
I have a table with one column as blob .I inserted wrong data into table and now I want to delete that can I do that using sql if so how ?
Re: how to delete blob [message #224953 is a reply to message #224794] Fri, 16 March 2007 02:27 Go to previous messageGo to next message
martijn
Messages: 283
Registered: December 2006
Location: Netherlands
Senior Member
Can't you delete the whole row and re-create it?

Re: how to delete blob [message #224959 is a reply to message #224794] Fri, 16 March 2007 02:45 Go to previous messageGo to next message
dmitry.nikiforov
Messages: 723
Registered: March 2005
Senior Member
Do you mean you want to clean up BLOB ?

SQL> create table t (id number, blb blob);

Table created.

SQL> insert into t values(1,'4343434343');

1 row created.

SQL> commit;

Commit complete.

SQL> declare
  2   blb blob;
  3  begin
  4   select blb into blb from t where id = 1;
  5   dbms_output.put_line(rawtohex(blb));
  6  end;
  7  /
4343434343

PL/SQL procedure successfully completed.

SQL> declare
  2   blb blob;
  3  begin
  4   select blb into blb from t where id = 1 for update;
  5   dbms_lob.trim(blb,0);
  6   commit;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> declare
  2   blb blob;
  3  begin
  4   select blb into blb from t where id = 1;
  5   dbms_output.put_line(rawtohex(blb));
  6  end;
  7  /

PL/SQL procedure successfully completed.


Rgds.
Re: how to delete blob [message #224967 is a reply to message #224959] Fri, 16 March 2007 03:28 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I think what the OP is saying is that they've got a table whose structure is
CREATE TABLE poor_design (col_1 blob);
and now they've put the wrong data in the table they don't know which row to delete.

Quite how they were planning to access the data in the first place is a question that begs an answer.
Re: how to delete blob [message #224970 is a reply to message #224967] Fri, 16 March 2007 03:47 Go to previous messageGo to next message
dmitry.nikiforov
Messages: 723
Registered: March 2005
Senior Member
Seems you are absolutely right.

Maybe INSTR could halp OP (if they know some specific
pattern of wrong data of course)

SQL> create table t (blb blob);

Table created.

SQL> insert into t values('4344454644748');

1 row created.

SQL> insert into t values('545454545462');

1 row created.

SQL> commit;

Commit complete.

SQL> select rowid from t where dbms_lob.instr(blb,'62') > 0;

ROWID
------------------
AAAFtEAAEAAAABnAAB

Rgds.
Re: how to delete blob [message #224979 is a reply to message #224970] Fri, 16 March 2007 05:49 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If they've put binary data (a JPG for example) into the table, then they've got problems.
Re: how to delete blob [message #224982 is a reply to message #224979] Fri, 16 March 2007 06:31 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Then one option could be to write some sort of program/script that extracts/displays the JPEGS and the matching ROWID in some way, look for the right JPEG and delete the row with that ROWID.

But since I can't imagine a usefull way to access those JPEGS they should just drop the table and start over. Wink
Re: how to delete blob [message #224984 is a reply to message #224982] Fri, 16 March 2007 06:36 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
ThomasG wrote on Fri, 16 March 2007 12:31
But since I can't imagine a usefull way to access those JPEGS they should just drop the table and start over. Wink


And this (the total lack of usability) makes me think there are other columns in the table besides the blob.
Previous Topic: return a type record in a function??
Next Topic: A question about determining table size.
Goto Forum:
  


Current Time: Mon Dec 05 15:16:46 CST 2016

Total time taken to generate the page: 0.09007 seconds