??????????? ??? ?????? ? LOB-?? ? EPC

1
??????????? ??? ?????? ? LOB-?? ? EPC
?????? ?????? ????????? ???????
????? 2007, ???????
2
????? ? LOB ?

• ?????????? ?? Large Object - ??? ?????,
  ???????????? ?? ?????????? ?? ?????????????
  ?????? ? ?????? ?? 4GB
• ???? LOB instance ?? ?????? ?? locator ? value -
  locator-? ? ???????? ??? ??????????? ?????,
  ?????? ?? ????????? value-??
• ?????? ??????? ?? ?????????? ?? value-?? LOB-????
  ?? ????? ?? Persistent ? Temporary

3
?????? LOB ? Oracle

• BLOB - Binary Large Object, ?? ?????????? ??
  ?????? ??????? ??????, ???? ???????????, ?????
  ??? ????? ??????????
• CLOB - Character Large Object, ?? ?????????? ??
  ????? ???? ???????? ??????????, ????????
  NLS_CHARACTERSET ( database character set )
• NCLOB - National Character Set Large Object, ??
  ?????????? ?? ????? ???? ???????? ??????????
  ??????????? NLS_NCHAR_CHARACTERSET ( National
  Character Set )
• BFILE - ????????? ???????? ?????? ?? ???????
  ????? ?????? ?????. O? BFILE ???? ???? ?? ?? ????

4
????? ? Oracle EPC ?

• ????? ?? ?? ????????? ??????? ?? ?????? ??
• Oracle RDBMS ?????????? ( .lib, .so, .dll )

Oracle RDBMS
C interface
C procedures or functions
- SQL - PL/SQL - Java
Java procedures or functions
Java interface
5
????? ?? ???? ? BLOB
Oracle RDBMS
file system
file_to_get.bin
'ABC'
gtfile_to_get.binlt
external procedure call
buffer
OCI libraries
begin some_blob to_blob( '616263' )
c_get_file_to_blob( fn, some_blob ) ...
c_get_file( ... ) ...
6
?????? ?? ?????????? ?? LOB ???? EPC

• ? ??????? c_get_file(), c_put_file() ? Linux
  ?????????? lob_epc.so
• Oracle ?????????? lob_epc_lib
• BLOB PL/SQL ??????? c_get_file_to_blob() ?
  c_put_file_from_blob()
• CLOB PL/SQL ??????? c_get_file_to_clob() ?
  c_put_file_from_clob()
• ???? ?? PL/SQL ?????? ?? ????? ?? ???? ? ????? ?
  ???? ????, ???? BLOB
• ???? ?? PL/SQL ?????? ?? ????? ?? ???? ? ????? ?
  ???? ????, ???? CLOB
• makefile

7
PL/SQL ?????????? lob_epc_lib

• create or replace
• library lob_epc_lib as
• 'ORACLE_HOME/lib/lob_epc.so'
• /

8
????? ????? c_get_file_to_?lob()

• ?????? ?????? ??????????
• pin_path - ????? ?? ?????, ????? ?? ?? ???????
• pio_?lob - ????????????? LOB, ?????? ?? ?? ?????
  ???????????? ?? ?????
• errMsg - ????? ????? ?? ??????
• errCode - ????? ??? ?? ??????
• ??? ???????? ?? ????? ? ???????,
  c_get_file_to_?lob() ????? 0

9
PL/SQL c_get_file_to_?lob()

• create or replace function c_get_file_to_blob(
• pin_path in varchar2,
• pio_blob in out nocopy blob
• errMsg in out varchar2,
• errCode in out binary_integer
• ) return binary_integer as
• language C
• library LOB_EPC_LIB
• name "c_get_file"
• parameters(
• context,
• pin_path string,
• pio_blob ociloblocator,
• pio_blob indicator by reference,
• errMsg string,
• errCode int,
• return int
• )
• /

10
???????????? ?? PL/SQL ? C ???????????
PL/SQL PARAMETERS C
c_get_file_to_blob( parameters( int c_get_file(
context, OCIExtProcContext ctx,
pin_path in varchar2, pin_path string, char pin_path,
pio_blob in out nocopy blob, pio_blob ociloblocator, pio_blob indicator by reference, OCILobLocator pio_blob, short pio_blob_ind,
errMsg in out varchar2, errMsg string, char errMsg,
errCode in out binary_integer errCode int, int errCode
) return binary_integer return int )
11
? ??????? - c_get_file()

• int c_get_file(
• OCIExtProcContext ctx,
• char pin_path,
• OCILobLocator pio_blob,
• short pio_blob_ind,
• char errMsg,
• int errCode
• )
• if( ctx 0
• pio_blob 0 pio_blob 0
  pio_blob_ind 0
• errMsg 0 errCode 0 )
• LogError( "null input pointer", "c_get_file()
  " )
• return( -1 )
• ...

12

• ...
• // get the OCI environment
• status OCIExtProcGetEnv(
• 
  (OCIExtProcContext ) ctx,
• (OCIEnv
  ) envhp,
• 
  (OCISvcCtx ) svchp,
• (OCIError
  ) errhp
• )
• if( status ! OCI_SUCCESS )
• errCode status
• strcpy( errMsg, "Failed to get
  the OCI connections
• environment" )
• LogError( errMsg, "c_get_file() "
  )
• return( -2 )
• ...

13

• ...
• // check whether pio_blob is initialized
• if( pio_blob_ind OCI_IND_NULL )
• errCode -3
• strcpy( errMsg, "The input blob
  is NULL" )
• LogError( errMsg, "c_get_file() "
  )
• return( errCode )
• // check whether the lob is initialized
• status OCILobLocatorIsInit (
• envhp, //
  OCIEnv envhp,
• errhp, //
  OCIError errhp,
• pio_blob, //
  CONST OCILobLocator locp,
• boolval //
  boolean is_initialized
• )

14

• ...
• if( status ! OCI_SUCCESS )
• strcpy( log_msg, "Failed to check
  whether the lob is
• initialized" )
• return( -1 )
• if( boolval FALSE )
• return( -2 ) // lob is not
  initialized
• // get the lob data size
• status OCILobGetLength (
• svchp,
  // OCISvcCtx svchp,
• errhp,
  // OCIError errhp,
• pio_blob,
  // OCILobLocator locp,
• size
  // ub4 lenp
• )
• if( status ! OCI_SUCCESS )

15

• ...
• // truncate the lob
• status OCILobTrim (
• svchp,
  // OCISvcCtx svchp,
• errhp,
  // OCIError errhp,
• pio_blob,
  // OCILobLocator locp,
• 0
  // ub4 newlen
• )
• if( status ! OCI_SUCCESS )
• strcpy( log_msg, "Failed
  to truncate the lob" )
• return( -3 )
• ...
• // open the file
• ...
• // read a part of the file and fulfill the buffer
• // define LOB_BUFF_SIZE ( (ub4)(
  110241024 ) ) // 1 MBytes
• // char lob_buffLOB_BUFF_SIZE

16

• ...
• // write the buffer data into the LOB
• wrote_num to_write_num
• status OCILobWrite (
• svchp, // OCISvcCtx
  svchp,
• errhp, // OCIError errhp,
• pio_blob, // OCILobLocator
  locp,
• wrote_num, // ub4 amtp,
• write_from, // ub4 offset,
• lob_buff, // dvoid bufp,
• to_write_num, // ub4 buflen,
• OCI_ONE_PIECE, // ub1 piece,
• NULL, // dvoid ctxp,
• NULL, //
  OCICallbackLobWrite (cbfp)
• 0, // ub2 csid, The
  character set ID of the data in the buffer
• // 0 -gt client's
  NLS_LANG or NLS_CHAR value
• SQLCS_IMPLICIT // ub1 csfrm, The
  character set ID of the destination LOB
• // SQLCS_IMPLICIT
  -gt database character set

17
??? ?? ?????? character set ID?

• ub2 char_set_id
• char_set_id OCINlsCharSetNameToId (
• envhp, // dvoid
  hndl,
• "WE8ISO8859P1" // CONST
  oratext name
• )
• ...

18

• ...
• if( status ! OCI_SUCCESS )
• sprintf( log_msg, "Failed to
  write u bytes in the lob",
• to_write_num )
• return( -2 )
• if( wrote_num ! to_write_num )
• sprintf( log_msg, "Wrote only u
  from all u bytes in the
• lob", wrote_num, to_write_num )
• return( -3 )
• ...
• // close the file
• ...
• return( 0 )

19
????? ????? c_put_file_from_?lob()

• ?????? ?????? ??????????
• pin_path - ????? ?? ?????, ?????? ?? ?? ??????
  ???????????? ?? ?LOB-?
• pio_?lob - ????????????? ?LOB, ?? ?????? ?? ??
  ????? ???????????? ?? ?????
• errMsg - ????? ????? ?? ??????
• errCode - ????? ??? ?? ??????
• ??? ?????? ?? ????? ? ???????, c_put_file_from_?lo
  b() ????? 0

20
PL/SQL c_put_file_to_?lob()

• create or replace function c_put_file_to_blob(
• pin_path in varchar2,
• pio_blob in out nocopy blob
• errMsg in out varchar2,
• errCode in out binary_integer
• ) return binary_integer as
• is language C
• library LOB_EPC_LIB
• name "c_put_file"
• parameters(
• context,
• pin_path string,
• pio_blob ociloblocator,
• pio_blob indicator by reference,
• errMsg string,
• errCode int,
• return int
• )
• /

21
? ??????? - c_put_file()

• int c_put_file(
• OCIExtProcContext ctx,
• char pin_path,
• OCILobLocator pio_blob,
• short pio_blob_ind,
• char errMsg,
• int errCode
• )
• if( ctx 0
• pio_blob 0 pio_blob 0
  pio_blob_ind 0
• errMsg 0 errCode 0 )
• LogError( "null input pointer", "c_put_file()
  " )
• return( -1 )
• ...
• // get the OCI environment
• ...
• // check whether pio_blob is initialized and
  get its size

22

• ...
• // open the file for write
• ...
• // read path of the blob data and put it into
  buffer
• // define LOB_BUFF_SIZE ( (ub4)(
  110241024 ) ) // 1 MBytes
• // char lob_buffLOB_BUFF_SIZE
• read_num to_read_num
• status OCILobRead (
• svchp, // OCISvcCtx
  svchp,
• errhp, // OCIError errhp,
• pio_blob, // OCILobLocator
  locp,
• read_num, // ub4 amtp,
• read_from, // ub4 offset,
• lob_buff, // dvoid bufp,
• to_read_num, // ub4 bufl,
• NULL, // dvoid ctxp,
• NULL, //
  OCICallbackLobRead (cbfp)
• 0, // ub2 csid, The
  character set ID of the data in the buffer

23

• ...
• if( status ! OCI_SUCCESS status
  OCI_NEED_DATA )
• sprintf( log_msg, "Unable to get the
  lob data" )
• return( -3 )
• ...
• // write the buffer data into the opened file
• ...
• // close the file
• ...
• return( 0 )

24
???? ?? ????????? BLOB ???????

• set serveroutput on
• set verify off
• declare
• pin_path varchar2( 1000 )
• pio_blob blob
• errmsg varchar2( 1000 ) lpad( '',
  1000, ' ' )
• errcode binary_integer
• v_return binary_integer
• begin
• dbms_output.enable
• pin_path '1'
• pio_blob to_blob( 'abcdef' )
• errmsg ''
• errcode 5
• dbms_output.put_line( 'PL/SQL blob test
  will read a blob from file '
• pin_path '...'
  )
• dbms_output.put_line( 'PL/SQL blob test
  c_get_file_to_blob() ...' )
• v_return c_get_file_to_blob( pin_path,
  pio_blob, errmsg, errcode )

25

• ??????????? ?? ?????????? ????? ...
• dbms_output.put_line( 'PL/SQL blob test
  c_get_file_to_blob() returns ' v_return )
• dbms_output.put_line( 'PL/SQL blob test got '
  dbms_lob.getlength( pio_blob )
• ' bytes into blob' )
• dbms_output.put_line( 'PL/SQL blob test blob
  content in hexadecimal numbers' )
• dbms_output.put_line( ' 0..15 ' rawtohex(
  dbms_lob.substr( pio_blob, 16, 1)) )
• dbms_output.put_line( '16..31 ' rawtohex(
  dbms_lob.substr( pio_blob, 16, 17)) )
• dbms_output.put_line( '32..47 ' rawtohex(
  dbms_lob.substr( pio_blob, 16, 33)) )
• pin_path '2'
• errmsg ''
• errcode 5
• dbms_output.put_line( 'PL/SQL blob test will
  write the blob to file '
• 
  pin_path '...' )
• dbms_output.put_line( 'PL/SQL blob test
  c_put_file_from_blob() ...' )
• v_return c_put_file_from_blob( pin_path,
  pio_blob, errmsg, errcode )
• dbms_output.put_line( 'PL/SQL blob test
  c_put_file_from_blob() returns '

26
????????? ?? ???????

• oracle_at_didel4 lob_epc ls -l
• total 68
• -rw-r--r-- 1 oracle dba 2851 Apr 18 2108
  file_get.c
• -rw-r--r-- 1 oracle dba 1075 Apr 18 2108
  file_get.h
• -rw-r--r-- 1 oracle dba 1754 Apr 18 2119
  file_gp_blob_test.sql
• -rw-r--r-- 1 oracle dba 1723 Apr 18 2124
  file_gp_clob_test.sql
• -rw-r--r-- 1 oracle dba 3073 Apr 18 2108
  file_put.c
• -rw-r--r-- 1 oracle dba 1076 Apr 18 2108
  file_put.h
• -rw-r--r-- 1 oracle dba 2568 Apr 18 2108
  lob_epc.sql
• -rw-r--r-- 1 oracle dba 860 Apr 18 2108
  logging.c
• -rw-r--r-- 1 oracle dba 323 Apr 18 2108
  logging.h
• -rw-r--r-- 1 oracle dba 1250 Apr 18 2133
  makefile
• -rw-r--r-- 1 oracle dba 3636 Apr 18 2108
  oci_lobs.c
• -rw-r--r-- 1 oracle dba 763 Apr 18 2108
  oci_lobs.h
• -rw-r--r-- 1 oracle dba 3843 Apr 18 2108
  oci_lobs_fixed_iso8859p1.c
• oracle_at_didel4 lob_epc

27
makefile

• OS_SOURCESoci_lobs.c file_get.c file_put.c
  logging.c
• LOB_EPC_SOORACLE_HOME/lib/lob_epc.so
• LOB_EPC_LOG/tmp/lob_epc.log
• all so func blob clob
• so (LOB_EPC_SO) (SO_SOURCES)
• gcc -shared -o lob_epc.so -I
  ORACLE_HOME/rdbms/public (SO_SOURCES)
• cp lob_epc.so (LOB_EPC_SO)
• chmod 0755 (LOB_EPC_SO)
• ls -l (LOB_EPC_SO)
• func lob_epc.sql (LOB_EPC_SO)
• sqlplus -S di1/x _at_lob_epc.sql
• ?????????? ?? ?????????? ????? ...

28

• ??????????? ?? ?????????? ????? ...
• blob file_gp_blob_test.sql (LOB_EPC_SO)
• rm -rf /tmp/file_in.bin /tmp/file_out.bin
  (LOB_EPC_LOG)
• cp (LOB_EPC_SO) /tmp/file_in.bin
• ls -l /tmp/file_in.bin
• od -t x1 /tmp/file_in.bin head -3
• sqlplus -S di1/x _at_file_gp_blob_test.sql
  /tmp/file_in.bin \
• 
  /tmp/file_out.bin
• diff /tmp/file_in.bin /tmp/file_out.bin
• cat (LOB_EPC_LOG)
• clob file_gp_clob_test.sql (LOB_EPC_SO)
• rm -rf /tmp/file_in.txt /tmp/file_out.txt
  (LOB_EPC_LOG)
• cp makefile /tmp/file_in.txt
• ls -l /tmp/file_in.txt
• od -c /tmp/file_in.txt head -3
• sqlplus -S di1/x _at_file_gp_clob_test.sql
  /tmp/file_in.txt \
• 
  /tmp/file_out.txt

29
make so
make so
oracle_at_didcos42 epc_demo
gcc -shared -o lob_epc.so -I /oracle/10.2/rdbms/pu
blic oci_lobs.c file_get.c file_put.c
logging.c cp lob_epc.so /oracle/10.2/lib/lob_epc.s
o chmod 0755 /oracle/10.2/lib/lob_epc.so ls -l
/oracle/10.2/lib/lob_epc.so -rwxr-xr-x 1 oracle
dba 12365 Apr 19 1027 /oracle/10.2/lib/lob_epc.so
oracle_at_didcos42 epc_demo
30
make func
make func
oracle_at_didcos42 epc_demo
sqlplus -S di1/x _at_lob_epc.sql Library
created. create function c_get_file_to_blob
... Function created. create function
c_get_file_to_clob ... Function created. create
function c_put_file_from_blob ... Function
created. create function c_put_file_from_clob
... Function created.
oracle_at_didcos42 epc_demo
31
make blob
make blob
oracle_at_didcos42 epc_demo
rm -rf /tmp/file_in.bin /tmp/file_out.bin
/tmp/lob_epc.log cp /oracle/10.2/lib/lob_epc.so
/tmp/file_in.bin ls -l /tmp/file_in.bin -rwxr-xr-x
1 oracle dba 12365 Apr 19 1035 /tmp/file_in.bin
od -t x1 /tmp/file_in.bin head -3 0000000 7f 45
4c 46 01 01 01 00 00 00 00 00 00 00 00 00 0000020
03 00 03 00 01 00 00 00 30 0e 00 00 34 00 00
00 0000040 3c 23 00 00 00 00 00 00 34 00 20 00 04
00 28 00
sqlplus -S di1/x _at_file_gp_blob_test.sql
/tmp/file_in.bin /tmp/file_out.bin PL/SQL blob
test will read a blob from file
/tmp/file_in.bin... PL/SQL blob test
c_get_file_to_blob() ... PL/SQL blob test
c_get_file_to_blob() returns 0 PL/SQL blob test
got 12365 bytes into blob PL/SQL blob test blob
content in hexadecimal numbers 0..15
7F454C46010101000000000000000000 16..31
0300030001000000300E000034000000 32..47
3C230000000000003400200004002800 ?????????? ??
?????????? ????? ...
32
??????????? ?? ?????????? ????? ... PL/SQL blob
test will write the blob to file
/tmp/file_out.bin... PL/SQL blob test
c_put_file_from_blob() ... PL/SQL blob test
c_put_file_from_blob() returns 0 PL/SQL
procedure successfully completed. No errors.
diff /tmp/file_in.bin /tmp/file_out.bin
cat /tmp/lob_epc.log Thu Apr 19 103511 2007
info c_get_file() will get /tmp/file_in.bin Thu
Apr 19 103511 2007 info write_lob()
successfully wrote 12365 bytes to pos 1 of the
lob Thu Apr 19 103511 2007 info
c_get_file() successfully got file
/tmp/file_in.bin with size 12365 and putted into
lob Thu Apr 19 103511 2007 info
c_put_file() will create file /tmp/file_out.bin
Thu Apr 19 103511 2007 info c_put_file() the
input lob is valid and its size is 12365 Thu
Apr 19 103511 2007 info read_lob()
successfully read 12365 bytes from pos 1 of the
lob Thu Apr 19 103511 2007 info
c_put_file() created file /tmp/file_out.bin 12365
oracle_at_didcos42 epc_demo
33
make clob
make clob
oracle_at_didcos42 epc_demo
rm -rf /tmp/file_in.txt /tmp/file_out.txt
/tmp/lob_epc.log cp makefile /tmp/file_in.txt ls
-l /tmp/file_in.txt -rw-r--r-- 1 oracle dba 1241
Apr 19 1501 /tmp/file_in.txt
od -c /tmp/file_in.txt head -3 0000000 O S
_ S O U R C E S o c i
_ l 0000020 o b s . c f i l
e _ g e t . c 0000040 f i
l e _ p u t . c l o g g
sqlplus -S di1/x _at_file_gp_clob_test.sql
/tmp/file_in.txt /tmp/file_out.txt PL/SQL clob
test will read a clob from file
/tmp/file_in.txt... PL/SQL clob test
c_get_file_to_clob() ... PL/SQL clob test
c_get_file_to_clob() returns 0 PL/SQL clob test
got 1241 characters into clob PL/SQL clob test
clob content 0..15 SO_SOURCESoci_l 16..31
obs.c file_get.c 32..47 file_put.c
logg ?????????? ?? ?????????? ????? ...
34
??????????? ?? ?????????? ????? ... PL/SQL clob
test will write the clob to file
/tmp/file_out.txt... PL/SQL clob test
c_put_file_from_clob() ... PL/SQL blob test
c_put_file_from_clob() returns 0 PL/SQL
procedure successfully completed. No errors.
diff /tmp/file_in.txt /tmp/file_out.txt
cat /tmp/lob_epc.log Thu Apr 19 150426 2007
info c_get_file() will get /tmp/file_in.txt Thu
Apr 19 150426 2007 info write_lob()
successfully wrote 1241 bytes to pos 1 of the
lob Thu Apr 19 150426 2007 info
c_get_file() successfully got file
/tmp/file_in.txt with size 1241 and putted into
lob Thu Apr 19 150426 2007 info
c_put_file() will create file /tmp/file_out.txt
Thu Apr 19 150426 2007 info c_put_file() the
input lob is valid and its size is 1241 Thu Apr
19 150426 2007 info read_lob() successfully
read 1241 bytes from pos 1 of the lob Thu Apr
19 150426 2007 info c_put_file() created
file /tmp/file_out.txt 1241
oracle_at_didcos42 epc_demo
35
??????? ? ???????? ? !
36
????????? ?? ?????? ????????!