tnsnames;ora:

 

DAYO =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.1.182)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = DAYO.localhost)

    )

  )

 

 

CLOBS

 

INSERT INTO clobs VALUES (1, 'interactive', 'a very short clob');

UPDATE clobs SET c = c || ', but not quite so short now';

SELECT * FROM clobs;

UPDATE clobs SET c = UPPER(c);

SELECT * FROM clobs;

 

 

To get a CLOB from a file... 

 

BFILE

 

 insert into bfiles values (1, 'declaration.txt', bfilename('MYLOBS','declaration.txt'));

 

(directory is case-sensitive!)

 

now what can you do with it?

 

select id, source, dbms_lob.fileexists(f) from bfiles;

select id, source, dbms_lob.getlength(f) from bfiles;

 

What else?

 

DECLARE

  amt NUMBER := 100;

  bf BFILE;

BEGIN

  bf := BFILENAME('MYLOBS','declaration.txt');

  dbms_lob.open(bf, DBMS_LOB.LOB_READONLY);

  dbms_output.put_line(to_char(dbms_lob.substr(bf, 100, 1)));

  dbms_lob.close(bf);

END;

/

 

Hmm, that comes out all hexidecimally.

 

 

fails ('invalid LOB locator specified'):

DECLARE

  bf BFILE;

  c CLOB;

BEGIN

  bf := BFILENAME('MYLOBS','declaration.txt');

  dbms_lob.open(bf, DBMS_LOB.LOB_READONLY);

  dbms_lob.loadfromfile(c, bf, DBMS_LOB.LOBMAXSIZE);

  dbms_lob.close(bf);

  dbms_output.put_line(substr(c, 1, 100));

END;

/

 

fails:

DECLARE

  r RAW;

  amt NUMBER := 100;

  bf BFILE;

BEGIN

  bf := BFILENAME('MYLOBS','declaration.txt');

  dbms_lob.open(bf, DBMS_LOB.LOB_READONLY);

  dbms_lob.read(bf, amt, 1, r);

  dbms_lob.close(bf);

  dbms_output.put_line(r);

END;

/

 

Pull into CLOB

 

CREATE OR REPLACE PROCEDURE insertclob(id IN INTEGER, dirname IN VARCHAR2, filename IN VARCHAR2)

AS

  bf BFILE;

  c  CLOB;

BEGIN

  INSERT INTO clobs

  VALUES (id, filename, EMPTY_CLOB());

  SELECT c

  INTO   c

  FROM   clobs

  WHERE  id = insertclob.id

  FOR UPDATE;

 

  bf := BFILENAME(dirname, filename);

 

  DBMS_LOB.OPEN(bf, DBMS_LOB.LOB_READONLY);

  DBMS_LOB.LOADFROMFILE(c, bf, dbms_lob.lobmaxsize);

  DBMS_LOB.CLOSE(bf);

END;

/

 

EXEC insertclob(2, 'MYLOBS', 'constitution.txt');

 

So, to upload your own story to the table:

 Create your own business wiki!HelpPublic, not yet premium Privacy Policy RSS