Execution Environment: SQL, SQL*Plus, iSQL*Plus]Access Privileges: Requires SYSDBA or select on DBA_OBJECTS and DBA_SOURCEUsage: Save as a script and run it.Instructions:
提供例子:create or replace function verify_source(source_owner in varchar2, source_name in varchar2, source_type in varchar2) return varchar2 as code_source clob; md5hash varchar2(32); cursor source_cursor is select text from dba_source where owner = upper(source_owner) and name = upper(source_name) and type = upper(source_type) order by line; begin code_source := ''; for source_record in source_cursor loop code_source := code_source||source_record.text; end loop; md5hash := rawtohex(dbms_crypto.hash(typ => dbms_crypto.HASH_MD5, src => code_source)); return md5hash; end; / show err-- drop table source_hashes; create table source_hashes(owner varchar2(30), name varchar2(30), type varchar2(12), hash varchar(32), calculation_date date not null); create unique index iph on source_hashes(owner,name,type);-- create a test procedure create or replace procedure scott.test_procedure as begin null; end; /select verify_source('SCOTT','TEST_PROCEDURE','PROCEDURE') hash from dual;-- baseline, for testing, store all hashes for type PROCEDURE from owner SCOTTinsert into source_hashes select owner, object_name , object_type, verify_source(owner, object_name, object_type), sysdate from dba_objects where object_type = 'PROCEDURE' and owner = 'SCOTT';-- alter the test_procedure create or replace procedure scott.test_procedure as a varchar2(20); begin null; end; / -- check for changed pl/sql code : select owner, name, type, calculation_date from source_hashes where type = 'PROCEDURE' and verify_source(owner, name, type) <> hash;
Solution To implement the solution, please execute the following steps: Dropping the repository and recreated it resolved the issue.1) On the system where the database resides, open a new command line session window.2) Change to the %ORACLE_HOME%\bin directory.cd <%ORACLE_HOME%>\bin3) You must set the ORACLE_SID environment variable in the command line session:<%ORACLE_HOME%>\bin> set ORACLE_SID=<SID_NAME> 4) To drop, then recreate the configuration files and repository execute the following command:<%ORACLE_HOME%>\bin> emca -config dbcontrol db -repos recreate
ORA-06550: 第 3 行, 第 3 列:
PLS-00201: 必须声明标识符 'DBMS_CRYPTO'
SQL> declare
2 l_algrithm PLS_INTEGER
3 :=dbms_crypto.encrypt_aes128
4 +dbms_crypto.chain_cbc
5 +dbms_crypto.pad_pkcs5;
6 l_data VARCHAR2(4):='DATA';
7 l_key VARCHAR2(16):='theensryptionkey';
8 l_iv VARCHAR2(16):='0123456789012345';
9 BEGIN
10 DBMS_OUTPUT.put_line
11 ('Encrypted DATA:'
12 ||UTL_RAW.cast_to_varchar2
13 (dbms_crypto.encrypt
14 (UTL_RAW.cast_to_raw(l_data),
15 l_algrithm,
16 UTL_RAW.cast_to_raw(l_key),
17 UTL_RAW.cast_to_raw(l_iv)
18 )));
19 END;
20 /
declare
*
第 1 行出现错误:
ORA-04067: 未执行, package body "SYSTEM.DBMS_CRYPTO" 不存在
ORA-06508: PL/SQL: 无法找到正在调用 : "SYSTEM.DBMS_CRYPTO" 的程序单元
ORA-06512: 在 line 10
Requires SYSDBA or select on DBA_OBJECTS and DBA_SOURCEUsage:
Save as a script and run it.Instructions:
verify_source(source_owner in varchar2,
source_name in varchar2,
source_type in varchar2) return varchar2 as
code_source clob;
md5hash varchar2(32);
cursor source_cursor is select text from dba_source
where owner = upper(source_owner) and name = upper(source_name) and
type = upper(source_type) order by line;
begin
code_source := '';
for source_record in source_cursor loop
code_source := code_source||source_record.text;
end loop;
md5hash := rawtohex(dbms_crypto.hash(typ => dbms_crypto.HASH_MD5,
src => code_source));
return md5hash;
end;
/
show err-- drop table source_hashes;
create table source_hashes(owner varchar2(30),
name varchar2(30),
type varchar2(12),
hash varchar(32),
calculation_date date not null);
create unique index iph on source_hashes(owner,name,type);-- create a test procedure
create or replace procedure scott.test_procedure as
begin
null;
end;
/select verify_source('SCOTT','TEST_PROCEDURE','PROCEDURE') hash from dual;-- baseline, for testing, store all hashes for type PROCEDURE from owner SCOTTinsert into source_hashes
select owner, object_name , object_type,
verify_source(owner, object_name, object_type), sysdate
from dba_objects where object_type = 'PROCEDURE'
and owner = 'SCOTT';-- alter the test_procedure
create or replace procedure scott.test_procedure as
a varchar2(20);
begin
null;
end;
/ -- check for changed pl/sql code :
select owner, name, type, calculation_date from
source_hashes where
type = 'PROCEDURE' and
verify_source(owner, name, type) <> hash;
To implement the solution, please execute the following steps:
Dropping the repository and recreated it resolved the issue.1) On the system where the database resides, open a new command line session window.2) Change to the %ORACLE_HOME%\bin directory.cd <%ORACLE_HOME%>\bin3) You must set the ORACLE_SID environment variable in the command line session:<%ORACLE_HOME%>\bin> set ORACLE_SID=<SID_NAME>
4) To drop, then recreate the configuration files and repository execute the following command:<%ORACLE_HOME%>\bin> emca -config dbcontrol db -repos recreate