请大家帮忙分析此过程

Question

dbms_sql是動態sql，dbms_lob是處理blob的包

Answer 1

执行时总是报错,我也不知应如向解决,请大家再指点一下的.SQL> CREATE OR REPLACE PROCEDURE hd6_update_image(
  2       table_name   IN  VARCHAR2,
  3       rec_no_name   IN  VARCHAR2,
  4       image_name   IN  VARCHAR2,
  5       rec_no    IN  INTEGER,
  6       image_len   IN OUT BINARY_INTEGER,
  7       image    IN  RAW)
  8  IS
  9   lob_loc   BLOB;
 10   sel_stmt  VARCHAR2(500);
 11   sel_cursor_id         NUMBER;
 12   ret   NUMBER;
 13  
 14  BEGIN
 15  
 16   sel_stmt := 'SELECT ' || image_name || ' FROM ' || table_name || ' WHERE ' || rec_no_name || ' = :rno FOR UPDATE';
 17   sel_cursor_id := dbms_sql.open_cursor;
 18   dbms_sql.parse(sel_cursor_id, sel_stmt, dbms_sql.native);
 19   dbms_sql.define_column(sel_cursor_id, 1, lob_loc);
 20   dbms_sql.bind_variable(sel_cursor_id, ':rno', rec_no);
 21   ret := dbms_sql.execute_and_fetch(sel_cursor_id);
 22  
 23         IF ret > 0 THEN
 24  
 25    dbms_sql.column_value(sel_cursor_id, 1, lob_loc);
 26    dbms_lob.open(lob_loc,dbms_lob.lob_readwrite);
 27    dbms_lob.writeappend(lob_loc, image_len, image);
 28    dbms_lob.close(lob_loc);
 29  
 30   END IF;
 31   dbms_sql.close_cursor(sel_cursor_id);
 32   exception when others then
 33    if dbms_sql.is_open(sel_cursor_id) then
 34     dbms_sql.close_cursor(sel_cursor_id);
 35    end if;
 36  END hd6_update_image;
 37  /警告：已创建的过程出现编译错误。SQL> SHOW ERRORS
PROCEDURE HD6_UPDATE_IMAGE出现错误：LINE/COL ERROR                                                                                      
-------- -----------------------------------------------------------------                          
26/3     PL/SQL: Statement ignored                                                                  
26/34    PLS-00302: 必须说明'LOB_READWRITE'组件                                                     
27/3     PL/SQL: Statement ignored                                                                  
27/12    PLS-00302: 必须说明'WRITEAPPEND'组件                                                       
28/3     PL/SQL: Statement ignored                                                                  
28/12    PLS-00302: 必须说明'CLOSE'组件                                                             
SQL> 
SQL> CREATE OR REPLACE PROCEDURE hd6_read_image(
  2       table_name   IN  VARCHAR2,
  3       rec_no_name   IN  VARCHAR2,
  4       image_name   IN  VARCHAR2,
  5       rec_no    IN  INTEGER,
  6       image_len   IN OUT BINARY_INTEGER,
  7       offset    IN   INTEGER,
  8       image    OUT  RAW)
  9  IS
 10   lob_loc   BLOB;
 11   sel_stmt  VARCHAR2(500);
 12   sel_cursor_id NUMBER;
 13   ret    NUMBER;
 14  BEGIN
 15   sel_stmt := 'SELECT ' || image_name || ' FROM ' || table_name || ' WHERE ' || rec_no_name || ' = :rno';
 16   sel_cursor_id := dbms_sql.open_cursor;
 17   IF dbms_sql.is_open(sel_cursor_id) THEN
 18    dbms_sql.parse(sel_cursor_id, sel_stmt, dbms_sql.native);
 19    dbms_sql.define_column(sel_cursor_id, 1, lob_loc);
 20    dbms_sql.bind_variable(sel_cursor_id, ':rno', rec_no);
 21    ret := dbms_sql.execute_and_fetch(sel_cursor_id);
 22    IF ret > 0 THEN
 23     dbms_sql.column_value(sel_cursor_id, 1, lob_loc);
 24     dbms_lob.open(lob_loc, dbms_lob.lob_readonly);
 25     dbms_lob.read(lob_loc, image_len, offset, image);
 26     dbms_lob.close(lob_loc);
 27    END IF;
 28    dbms_sql.close_cursor(sel_cursor_id);
 29   ELSE
 30    image_len := 0;
 31   END IF;
 32   exception when others then
 33    if dbms_sql.is_open(sel_cursor_id) then
 34     dbms_sql.close_cursor(sel_cursor_id);
 35    end if;
 36  END hd6_read_image;
 37  /警告：已创建的过程出现编译错误。SQL> SHOW ERRORS
PROCEDURE HD6_READ_IMAGE出现错误：LINE/COL ERROR                                                                                      
-------- -----------------------------------------------------------------                          
24/4     PL/SQL: Statement ignored                                                                  
24/36    PLS-00302: 必须说明'LOB_READONLY'组件                                                      
26/4     PL/SQL: Statement ignored                                                                  
26/13    PLS-00302: 必须说明'CLOSE'组件                                                             
SQL> 
SQL> spool off