File: readlob.sql

package info (click to toggle)
geany 2.1-1
  • links: PTS, VCS
  • area: main
  • in suites: forky, sid
  • size: 39,300 kB
  • sloc: ansic: 168,600; cpp: 77,562; sh: 5,344; makefile: 1,694; cs: 1,233; javascript: 1,024; python: 580; f90: 537; vhdl: 504; sql: 503; lisp: 436; fortran: 389; php: 278; ada: 201; ruby: 163; java: 131; asm: 131; perl: 119; cobol: 88; tcl: 77; erlang: 73; xml: 66; ml: 27; sed: 16; pascal: 15; haskell: 6
file content (39 lines) | stat: -rw-r--r-- 1,220 bytes parent folder | download | duplicates (17) 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
 
rem -----------------------------------------------------------------------
rem URL:        http://www.orafaq.com/scripts/plsql/readlob.txt
rem Filename:   readlob.sql
rem Purpose:    Fetch LOB column values piece-wise from PL/SQL
rem Date:       12-Jun-2000
rem Author:     Frank Naude (frank@ibi.co.za)
rem -----------------------------------------------------------------------

set serveroutput on

DROP TABLE lob_table;                  -- Create table to hols LOBs
CREATE TABLE lob_table  (
        id      INTEGER,
        b_lob   BLOB,
        c_lob   CLOB,
        b_file  BFILE );

INSERT INTO lob_table                  -- Create sample record
	VALUES (1, EMPTY_BLOB(), 'abcde', NULL);

DECLARE
   clob_locator CLOB;
   charbuf      VARCHAR2(20);
   read_offset  INTEGER;
   read_amount  INTEGER;
BEGIN
   -- First we need to get the lob locator
   SELECT c_lob INTO clob_locator FROM lob_table WHERE id = 1;

   DBMS_OUTPUT.PUT_LINE('CLOB Size: ' ||
                       DBMS_LOB.GETLENGTH(clob_locator));

   -- Read LOB field contents
   read_offset := 1;
   read_amount := 20;
   dbms_lob.read(clob_locator, read_amount, read_offset, charbuf);
   dbms_output.put_line('CLOB Value: ' || charbuf);
END;
/