PLS-00920 parameter plsql_native_library_dir is not set問題解決

Oracle 10.2.0.1 上在建立儲存過程，報如下錯誤：

PLS-00920: parameter plsql_native_library_dir is not set

對於這個問題，可以透過如下兩種方法解決：
1、禁用plsql的native模式
2、使用native模式，設定正確的plsql_native_library_dir引數

下面演示解決過程：

[@more@]

1、禁用plsql native模式

1.1、錯誤提示：

sys@CNHTM> create or replace function scott.p_test(userName in varchar2) return boolean is 2 Result boolean; 3 begin 4 Result:=upper(userName) not in ('SYS','SYSTEM'); 5 return(Result); 6 end p_test; 7 / Warning: Function created with compilation errors. sys@CNHTM> show error Errors for FUNCTION SCOTT.P_TEST: LINE/COL ERROR -------- ----------------------------------------------------------------- 4/11 PLS-00920: parameter plsql_native_library_dir is not set

1.2、檢查引數

sys@CNHTM> show parameter plsql NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ plsql_ccflags string plsql_code_type string NATIVE plsql_compiler_flags string NATIVE, NON_DEBUG plsql_debug boolean FALSE plsql_native_library_dir string plsql_native_library_subdir_count integer 0 plsql_optimize_level integer 2 plsql_v2_compatibility boolean FALSE plsql_warnings string DISABLE:ALL

1.3、修改plsql_code_type和plsql_compiler_flags引數，禁用native模式

sys@CNHTM> alter system set plsql_code_type=INTERPRETED scope=both; System altered. sys@CNHTM> alter system set plsql_compiler_flags=INTERPRETED,NON_DEBUG scope=both; System altered.

1.4、重新編譯失效的儲存過程

sys@CNHTM> alter function scott.p_test compile; Function altered.

2、使用native模式，設定正確的plsql_native_library_dir引數

如果要編譯的儲存過程必須使用native模式，可以透過這個方式修改，使用native模式的好處見
PL/SQL User's Guide and Reference（B14261-01）第7章

2.1、錯誤提示：

sys@CNHTM> create or replace function scott.p_test(userName in varchar2) return boolean is 2 Result boolean; 3 begin 4 Result:=upper(userName) not in ('SYS','SYSTEM'); 5 return(Result); 6 end p_test; 7 / Warning: Function created with compilation errors. sys@CNHTM> show error Errors for FUNCTION SCOTT.P_TEST: LINE/COL ERROR -------- ----------------------------------------------------------------- 4/11 PLS-00920: parameter plsql_native_library_dir is not set

2.2、檢查引數

sys@CNHTM> show parameter plsql NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ plsql_ccflags string plsql_code_type string NATIVE plsql_compiler_flags string NATIVE, NON_DEBUG plsql_debug boolean FALSE plsql_native_library_dir string plsql_native_library_subdir_count integer 0 plsql_optimize_level integer 2 plsql_v2_compatibility boolean FALSE plsql_warnings string DISABLE:ALL

2.3、修改plsql_native_library_dir和plsql_native_library_subdir_count引數

sys@CNHTM> alter system set plsql_native_library_dir='/oracle/app/10.1/plsql/nativelib' scope=both; System altered. sys@CNHTM> alter system set plsql_native_library_subdir_count=5 scope=both; System altered.

plsql_native_library_dir引數的值為$ORACLE_HOME/plsql/nativelib
如果$ORACLE_HOME/plsql中沒有nativelib目錄，需要手工建立

plsql_native_library_subdir_count引數應為非零的值
並保證$ORACLE_HOME/plsql/nativelib目錄中有d0、d1...等目錄，如果沒有，手工建立，
目錄的個數與plsql_native_library_subdir_count相等
如plsql_native_library_subdir_count=5，則在$ORACLE_HOME/plsql/nativelib目錄下應該有d0、d1、d2、d3、d4五個目錄

2.4、重新編譯失效的儲存過程

sys@CNHTM> alter function scott.p_test compile; Function altered.

--end--