OraFAQ Forum: SQL & PL/SQL » oracle sql UPDATE CLOB

Home » SQL & PL/SQL » SQL & PL/SQL » oracle sql UPDATE CLOB

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

oracle sql UPDATE CLOB [message #180913]

Wed, 05 July 2006 15:59

retep1025
Messages: 2
Registered: July 2006
Location: New York

Junior Member

how do I do SQL that update a clob field using a file. something like this format:

update table SET clob_field = '/filename' Where PK=PK

[Updated on: Wed, 05 July 2006 16:00]

Report message to a moderator

Re: oracle sql UPDATE CLOB [message #180925 is a reply to message #180913]

Wed, 05 July 2006 17:57

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

scott@ORA92> CREATE TABLE your_table
  2    (pk	   NUMBER,
  3  	clob_field CLOB)
  4  /

Table created.

scott@ORA92> INSERT INTO your_table VALUES (1, EMPTY_CLOB())
  2  /

1 row created.

scott@ORA92> COMMIT
  2  /

Commit complete.

scott@ORA92> CREATE OR REPLACE DIRECTORY my_dir2 AS 'D:\oracle2'
  2  /

Directory created.

scott@ORA92> HOST ECHO Hello World > D:\oracle2\testing.txt

scott@ORA92> DECLARE
  2    v_clob  CLOB;
  3    v_bfile BFILE := BFILENAME ('MY_DIR2', 'testing.txt');
  4  BEGIN
  5    SELECT clob_field
  6    INTO   v_clob
  7    FROM   your_table
  8    WHERE  pk = 1
  9    FOR UPDATE;
 10    DBMS_LOB.FILEOPEN (v_bfile);
 11    DBMS_LOB.LOADFROMFILE (v_clob, v_bfile, DBMS_LOB.GETLENGTH (v_bfile));
 12    DBMS_LOB.FILECLOSE (v_bfile);
 13  END;
 14  /

PL/SQL procedure successfully completed.

scott@ORA92> SELECT * FROM your_table
  2  /

        PK CLOB_FIELD
---------- --------------------------------------------------------------------------------
         1 Hello World 


scott@ORA92>

Report message to a moderator

Re: oracle sql UPDATE CLOB [message #180928 is a reply to message #180913]

Wed, 05 July 2006 18:38

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

You could also create your own generic procedure, as demonstrated below, that would allow you to update any clob column of any table, from any file in any directory, based on a numeric primary key, assuming that all necessary permissions and directories exist.

scott@ORA92> CREATE TABLE your_table
  2    (pk	   NUMBER,
  3  	clob_field CLOB)
  4  /

Table created.

scott@ORA92> INSERT INTO your_table VALUES (1, EMPTY_CLOB())
  2  /

1 row created.

scott@ORA92> COMMIT
  2  /

Commit complete.

scott@ORA92> CREATE OR REPLACE DIRECTORY my_dir2 AS 'D:\oracle2'
  2  /

Directory created.

scott@ORA92> HOST ECHO Hello World > D:\oracle2\testing.txt

scott@ORA92> CREATE OR REPLACE PROCEDURE update_clob_from_file
  2    (p_tab  IN VARCHAR2,
  3  	p_col  IN VARCHAR2,
  4  	p_pk   IN VARCHAR2,
  5  	p_dir  IN VARCHAR2,
  6  	p_file IN VARCHAR2)
  7  AS
  8    v_clob	  CLOB;
  9    v_bfile	  BFILE := BFILENAME (UPPER (p_dir), p_file);
 10  BEGIN
 11    EXECUTE IMMEDIATE
 12  	    'SELECT ' || p_col
 13    || '  FROM  ' || p_tab
 14    || '  WHERE  pk = :b_pk FOR UPDATE'
 15    INTO   v_clob
 16    USING p_pk;
 17    DBMS_LOB.FILEOPEN (v_bfile);
 18    DBMS_LOB.LOADFROMFILE (v_clob, v_bfile, DBMS_LOB.GETLENGTH (v_bfile));
 19    DBMS_LOB.FILECLOSE (v_bfile);
 20  END update_clob_from_file;
 21  /

Procedure created.

scott@ORA92> SHOW ERRORS
No errors.
scott@ORA92> EXECUTE update_clob_from_file ('your_table', 'clob_field', 1, 'my_dir2', 'testing.txt')

PL/SQL procedure successfully completed.

scott@ORA92> SELECT * FROM your_table
  2  /

        PK CLOB_FIELD
---------- --------------------------------------------------------------------------------
         1 Hello World 


scott@ORA92>

Report message to a moderator

Re: oracle sql UPDATE CLOB [message #183390 is a reply to message #180925]

Thu, 20 July 2006 13:05

retep1025
Messages: 2
Registered: July 2006
Location: New York

Junior Member

Barbara Boehmer. I get a error message: identifier is too long

DECLARE
vclob CLOB;
v_bfile BFILE := BFILENAME ('/home/phuang/cvs/Ocean/auction/dbscripts/emails/', 'auctioncanceledsellers.html');
BEGIN
SELECT mtp_body
INTO vclob
FROM oc_mail_templates
WHERE mtp_id = 39
FOR UPDATE;
DBMS_LOB.FILEOPEN (v_bfile);
DBMS_LOB.LOADFROMFILE (vclob, v_bfile, DBMS_LOB.GETLENGTH (v_bfile));
DBMS_LOB.FILECLOSE (v_bfile);
END;
Error report:
ORA-00972: identifier is too long
ORA-06512: at "SYS.DBMS_LOB", line 523
ORA-06512: at line 10

Report message to a moderator

Re: oracle sql UPDATE CLOB [message #183671 is a reply to message #183390]

Fri, 21 July 2006 19:35

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

You need to create a directory object, as I demonstrated previously, rather than using the path as a parameter for bfilename.

CREATE DIRECTORY your_dir AS '/home/phuang/cvs/Ocean/auction/dbscripts/emails/'
/
DECLARE
  vclob CLOB;
  v_bfile BFILE := BFILENAME ('YOUR_DIR', 'auctioncanceledsellers.html');
BEGIN
  SELECT mtp_body INTO vclob
  FROM oc_mail_templates
  WHERE mtp_id = 39
  FOR UPDATE;
  DBMS_LOB.FILEOPEN (v_bfile);
  DBMS_LOB.LOADFROMFILE (vclob, v_bfile, DBMS_LOB.GETLENGTH (v_bfile));
  DBMS_LOB.FILECLOSE (v_bfile);
END;
/

Report message to a moderator

Previous Topic:	Question about Oracle 8i dump file?
Next Topic:	how convert row in column

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Aug 27 22:17:56 CDT 2025