Oracle wrap 测试的一些细节问题
今天在做 wrap 的测试实验的时候,出现一个很奇怪的现象,就是加密不成功。
具体表现为:1.加密后的文件大小为0kb。
2.加密后的文件仍然可视。
具体测试步骤如下:
D:\Just4work\someSQLs>wrap iname=test_oracle_warp.sql PL/SQL Wrapper: Release 11.2.0.1.0- Production on 星期五 2月 21 15:26:40 2014 Copyright (c) 1993, 2009, Oracle. All rights reserved. Processing test_oracle_warp.sql to test_oracle_warp.plb D:\Just4work\someSQLs>dir /s test_oracle* 驱动器 D 中的卷是 work 卷的序列号是 F4A9-7648 D:\Just4work\someSQLs 的目录 2014/02/21 15:26 0 test_oracle_warp.plb 2014/02/21 15:23 3,582 test_oracle_warp.sql 2 个文件 3,582 字节 所列文件总数: 2 个文件 3,582 字节 0 个目录 171,316,117,504 可用字节
在另一台装有完整数据库的机器上测试:
oracle@zen-VirtualBox:~$ ls -l | grep test -rw-r----- 1 oracle oinstall 20987904 Feb 21 12:01 tab16ktest.dbf -rw-r--r-- 1 oracle oinstall 1789 Feb 21 15:06 test_oracle_wrap2.plb -rw-r--r-- 1 oracle oinstall 23316 Feb 21 15:03 test_oracle_wrap2.sql -rw-r--r-- 1 oracle oinstall 3492 Feb 21 16:03 test_oracle_wrap3.sql -rw-r--r-- 1 oracle oinstall 552 Feb 21 15:00 test_oracle_wrap.plb -rw-r--r-- 1 oracle oinstall 692 Feb 21 14:59 test_oracle_wrap.sql oracle@zen-VirtualBox:~$ wrap iname=test_oracle_wrap3.sql PL/SQL Wrapper: Release 11.2.0.1.0- 64bit Production on Fri Feb 21 16:04:30 2014 Copyright (c) 1993, 2009, Oracle. All rights reserved. Processing test_oracle_wrap3.sql to test_oracle_wrap3.plb oracle@zen-VirtualBox:~$ ls -l | grep test -rw-r----- 1 oracle oinstall 20987904 Feb 21 12:01 tab16ktest.dbf -rw-r--r-- 1 oracle oinstall 1789 Feb 21 15:06 test_oracle_wrap2.plb -rw-r--r-- 1 oracle oinstall 23316 Feb 21 15:03 test_oracle_wrap2.sql -rw-r--r-- 1 oracle oinstall 1106 Feb 21 16:04 test_oracle_wrap3.plb -rw-r--r-- 1 oracle oinstall 3492 Feb 21 16:03 test_oracle_wrap3.sql -rw-r--r-- 1 oracle oinstall 552 Feb 21 15:00 test_oracle_wrap.plb -rw-r--r-- 1 oracle oinstall 692 Feb 21 14:59 test_oracle_wrap.sql oracle@zen-VirtualBox:~$ cat test_oracle_wrap3.plb CREATE OR REPLACE PROCEDURE zx_test_satisfy_analyse wrapped a000000 354 abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd 7 8d4 3b0 UsqjIEQoNf3Spb35eItCneQalf4wgztc10rrfC+LbrB1BRsJw3mqYURRAFP0BQ3fLdYAPrrR PBxvvb1SKRxU2x2mBt39ytx6iNUc1t7x6FJ4ogwCjSwFT8ETJPYbscWLoWutzzAgICDfxJ/H EPjJTpL/h52YPxLEvgjPKKk7VmXH3k6JodXTL9MrWcYfWdAVYlY7iwxr8l85r8KFi3EHjdg1 QN84hfZQ90yTp8FxGyuFAvysKsbmDE1AxaUvePZqDbnd7aZGa4KHghEZIyvwLtq9td4vjaY7 Gl3ezWCijsfAaXvmcnKkmnGvnn5Cb82geJ/d2ZncUsb7bYZilav/9p9k9QL6NtMcCkb4V01C YuLHHBUDL0CoWeYEB3yZOKusEmYWJvyQTQlJFAK9KQeeIXKNYAz91fPH9gSeA1S6w4UlCb6d Az5GGwOoU2DYVMXT8debif2jwO3iWDauFZcHbLYl0Y1m7qJ1APzYFmpHFq2KaKQ9WaRQU15w Jb4SM7/s3rDdVhZEdK5CXzKHYThIAoZ81S3FFJJqDitc8DNojlgPF6A7/44+p71jwSHnRe86 0H6MQHlPrAvXe2IpfdoUe0nw0HBWXUEDnusZs/8MuI7BTdDH9XbBU9DHz3bBGtDHwHbBkyzB MVJu6yL/KQtXUlCqnOwLKI1yn5faDXI0pzwCK4hx0acp1qOnuJFmRNjLHImjxY9IOzvlYjFf lN9+ILXQZnGchDsYdosevHvCYrEtl2U3zDE+M6TII5f6KaFd9e3NGXPBT52qz+210s2xnLs2 EuTFi63TmOp70TWoviCpvMKghAohkAPOZcXq+nqCp5LJh4+phxOe+nNqSrUI725nWpYubp+a 4YN4r6RI1XTF7UoLi+lFssDWilXvRsmTAsPh94DDpfyXZni0kW6hp/HD0l0o0BWImw== / oracle@zen-VirtualBox:~$
只是装了11g的客户端的机器上测试不成功,生成的文件只有0字节;安装整个数据库的机器上测试成功,没找到确切的原因。
继续测试:
修改文件的开头
oracle@zen-VirtualBox:~$ cat test_oracle_wrap3.sql > test_oracle_wrap4.sql oracle@zen-VirtualBox:~$ vim test_oracle_wrap4.sql oracle@zen-VirtualBox:~$ cat test_oracle_wrap4.sql EATE OR REPLACE PROCEDURE zx_test_satisfy_analyse ( av_task_plan_id IN VARCHAR2, an_region_id IN NUMBER, av_stat_date IN VARCHAR2, av_append_info IN VARCHAR2, av_return OUT VARCHAR2, av_syserr OUT VARCHAR2 ) IS /* *author: zen *created date :20130812 */ v_date VARCHAR2(8); v_channel NUMBER; -- 1 10000,2 114,3 wap net,6 实体渠道,8 装移 BEGIN v_date := av_stat_date; v_channel := av_append_info; --即时测评满意度分析:总体满意度分析 DELETE FROM report_service_satisfy_total t WHERE t.acct_day = v_date AND decode(t.channel_type, 9, 1, 4, 3, 7, 6, t.channel_type) = v_channel; COMMIT; av_return := '0'; av_syserr := '执行成功'; EXCEPTION WHEN OTHERS THEN ROLLBACK; dbms_output.put_line(av_task_plan_id || ' , ' || an_region_id || ' , ' || av_append_info); av_return := SQLCODE; av_syserr := SQLERRM; RETURN; END zx_test_satisfy_analyse; / oracle@zen-VirtualBox:~$ wrap iname=test_oracle_wrap4.sql PL/SQL Wrapper: Release 11.2.0.1.0- 64bit Production on Fri Feb 21 16:09:53 2014 Copyright (c) 1993, 2009, Oracle. All rights reserved. Processing test_oracle_wrap4.sql to test_oracle_wrap4.plb oracle@zen-VirtualBox:~$ ls -l | grep test_oracle_wrap -rw-r--r-- 1 oracle oinstall 1789 Feb 21 15:06 test_oracle_wrap2.plb -rw-r--r-- 1 oracle oinstall 23316 Feb 21 15:03 test_oracle_wrap2.sql -rw-r--r-- 1 oracle oinstall 1106 Feb 21 16:04 test_oracle_wrap3.plb -rw-r--r-- 1 oracle oinstall 3492 Feb 21 16:03 test_oracle_wrap3.sql -rw-r--r-- 1 oracle oinstall 2727 Feb 21 16:09 test_oracle_wrap4.plb -rw-r--r-- 1 oracle oinstall 3490 Feb 21 16:08 test_oracle_wrap4.sql -rw-r--r-- 1 oracle oinstall 552 Feb 21 15:00 test_oracle_wrap.plb -rw-r--r-- 1 oracle oinstall 692 Feb 21 14:59 test_oracle_wrap.sql oracle@zen-VirtualBox:~$ cat test_oracle_wrap4.plb EATE OR REPLACE PROCEDURE zx_test_satisfy_analyse ( av_task_plan_id IN VARCHAR2, an_region_id IN NUMBER, av_stat_date IN VARCHAR2, av_append_info IN VARCHAR2, av_return OUT VARCHAR2, av_syserr OUT VARCHAR2 ) IS /* *author: zen *created date :20130812 */ v_date VARCHAR2(8); v_channel NUMBER; -- 1 10000,2 114,3 wap net,6 实体渠道,8 装移 BEGIN v_date := av_stat_date; v_channel := av_append_info; WHERE t.acct_day = v_date AND decode(t.channel_type, 9, 1, 4, 3, 7, 6, t.channel_type) = v_channel; av_return := '0'; av_syserr := '执行成功'; EXCEPTION WHEN OTHERS THEN ROLLBACK; dbms_output.put_line(av_task_plan_id || ' , ' || an_region_id || ' , ' || av_append_info); av_return := SQLCODE; av_syserr := SQLERRM; RETURN; END zx_test_satisfy_analyse; / oracle@zen-VirtualBox:~$ cat test_oracle_wrap5.sql DELETE FROM report_service_satisfy_total t WHERE t.acct_day = v_date AND decode(t.channel_type, 9, 1, 4, 3, 7, 6, t.channel_type) = v_channel; COMMIT; oracle@zen-VirtualBox:~$ wrap iname=test_oracle_wrap5.sql PL/SQL Wrapper: Release 11.2.0.1.0- 64bit Production on Fri Feb 21 16:24:24 2014 Copyright (c) 1993, 2009, Oracle. All rights reserved. Processing test_oracle_wrap5.sql to test_oracle_wrap5.plb oracle@zen-VirtualBox:~$ ls -l | grep test_oracle_wrap -rw-r--r-- 1 oracle oinstall 1789 Feb 21 15:06 test_oracle_wrap2.plb -rw-r--r-- 1 oracle oinstall 23316 Feb 21 15:03 test_oracle_wrap2.sql -rw-r--r-- 1 oracle oinstall 1106 Feb 21 16:04 test_oracle_wrap3.plb -rw-r--r-- 1 oracle oinstall 3492 Feb 21 16:03 test_oracle_wrap3.sql -rw-r--r-- 1 oracle oinstall 2727 Feb 21 16:09 test_oracle_wrap4.plb -rw-r--r-- 1 oracle oinstall 3490 Feb 21 16:08 test_oracle_wrap4.sql -rw-r--r-- 1 oracle oinstall 163 Feb 21 16:24 test_oracle_wrap5.plb -rw-r--r-- 1 oracle oinstall 167 Feb 21 16:23 test_oracle_wrap5.sql -rw-r--r-- 1 oracle oinstall 552 Feb 21 15:00 test_oracle_wrap.plb -rw-r--r-- 1 oracle oinstall 692 Feb 21 14:59 test_oracle_wrap.sql oracle@zen-VirtualBox:~$ cat test_oracle_wrap5.plb DELETE FROM report_service_satisfy_total t WHERE t.acct_day = v_date AND decode(t.channel_type, 9, 1, 4, 3, 7, 6, t.channel_type) = v_channel; COMMIT; oracle@zen-VirtualBox:~$ cat test_oracle_wrap6.sql DELETE FROM report_service_satisfy_total t WHERE t.acct_day = v_date AND decode(t.channel_type, 9, 1, 4, 3, 7, 6, t.channel_type) = v_channel; COMMIT; CREATE OR REPLACE PROCEDURE zx_test_satisfy_analyse ( av_task_plan_id IN VARCHAR2, an_region_id IN NUMBER, av_stat_date IN VARCHAR2, av_append_info IN VARCHAR2, av_return OUT VARCHAR2, av_syserr OUT VARCHAR2 ) IS /* *author: zen *created date :20130812 */ v_date VARCHAR2(8); v_channel NUMBER; -- 1 10000,2 114,3 wap net,6 实体渠道,8 装移 BEGIN v_date := av_stat_date; v_channel := av_append_info; --即时测评满意度分析:总体满意度分析 DELETE FROM report_service_satisfy_total t WHERE t.acct_day = v_date AND decode(t.channel_type, 9, 1, 4, 3, 7, 6, t.channel_type) = v_channel; COMMIT; av_return := '0'; av_syserr := '执行成功'; EXCEPTION WHEN OTHERS THEN ROLLBACK; dbms_output.put_line(av_task_plan_id || ' , ' || an_region_id || ' , ' || av_append_info); av_return := SQLCODE; av_syserr := SQLERRM; RETURN; END zx_test_satisfy_analyse; / oracle@zen-VirtualBox:~$ wrap iname=test_oracle_wrap6.sql PL/SQL Wrapper: Release 11.2.0.1.0- 64bit Production on Fri Feb 21 16:43:38 2014 Copyright (c) 1993, 2009, Oracle. All rights reserved. Processing test_oracle_wrap6.sql to test_oracle_wrap6.plb oracle@zen-VirtualBox:~$ ls -l | grep test_oracle -rw-r--r-- 1 oracle oinstall 1789 Feb 21 15:06 test_oracle_wrap2.plb -rw-r--r-- 1 oracle oinstall 23316 Feb 21 15:03 test_oracle_wrap2.sql -rw-r--r-- 1 oracle oinstall 1106 Feb 21 16:04 test_oracle_wrap3.plb -rw-r--r-- 1 oracle oinstall 3492 Feb 21 16:03 test_oracle_wrap3.sql -rw-r--r-- 1 oracle oinstall 2727 Feb 21 16:09 test_oracle_wrap4.plb -rw-r--r-- 1 oracle oinstall 3490 Feb 21 16:08 test_oracle_wrap4.sql -rw-r--r-- 1 oracle oinstall 163 Feb 21 16:24 test_oracle_wrap5.plb -rw-r--r-- 1 oracle oinstall 167 Feb 21 16:23 test_oracle_wrap5.sql -rw-r--r-- 1 oracle oinstall 1267 Feb 21 16:43 test_oracle_wrap6.plb -rw-r--r-- 1 oracle oinstall 3655 Feb 21 16:42 test_oracle_wrap6.sql -rw-r--r-- 1 oracle oinstall 552 Feb 21 15:00 test_oracle_wrap.plb -rw-r--r-- 1 oracle oinstall 692 Feb 21 14:59 test_oracle_wrap.sql oracle@zen-VirtualBox:~$ cat test_oracle_wrap6.plb DELETE FROM report_service_satisfy_total t WHERE t.acct_day = v_date AND decode(t.channel_type, 9, 1, 4, 3, 7, 6, t.channel_type) = v_channel; COMMIT; CREATE OR REPLACE PROCEDURE zx_test_satisfy_analyse wrapped a000000 354 abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd 7 8d4 3b0 UsqjIEQoNf3Spb35eItCneQalf4wgztc10rrfC+LbrB1BRsJw3mqYURRAFP0BQ3fLdYAPrrR PBxvvb1SKRxU2x2mBt39ytx6iNUc1t7x6FJ4ogwCjSwFT8ETJPYbscWLoWutzzAgICDfxJ/H EPjJTpL/h52YPxLEvgjPKKk7VmXH3k6JodXTL9MrWcYfWdAVYlY7iwxr8l85r8KFi3EHjdg1 QN84hfZQ90yTp8FxGyuFAvysKsbmDE1AxaUvePZqDbnd7aZGa4KHghEZIyvwLtq9td4vjaY7 Gl3ezWCijsfAaXvmcnKkmnGvnn5Cb82geJ/d2ZncUsb7bYZilav/9p9k9QL6NtMcCkb4V01C YuLHHBUDL0CoWeYEB3yZOKusEmYWJvyQTQlJFAK9KQeeIXKNYAz91fPH9gSeA1S6w4UlCb6d Az5GGwOoU2DYVMXT8debif2jwO3iWDauFZcHbLYl0Y1m7qJ1APzYFmpHFq2KaKQ9WaRQU15w Jb4SM7/s3rDdVhZEdK5CXzKHYThIAoZ81S3FFJJqDitc8DNojlgPF6A7/44+p71jwSHnRe86 0H6MQHlPrAvXe2IpfdoUe0nw0HBWXUEDnusZs/8MuI7BTdDH9XbBU9DHz3bBGtDHwHbBkyzB MVJu6yL/KQtXUlCqnOwLKI1yn5faDXI0pzwCK4hx0acp1qOnuJFmRNjLHImjxY9IOzvlYjFf lN9+ILXQZnGchDsYdosevHvCYrEtl2U3zDE+M6TII5f6KaFd9e3NGXPBT52qz+210s2xnLs2 EuTFi63TmOp70TWoviCpvMKghAohkAPOZcXq+nqCp5LJh4+phxOe+nNqSrUI725nWpYubp+a 4YN4r6RI1XTF7UoLi+lFssDWilXvRsmTAsPh94DDpfyXZni0kW6hp/HD0l0o0BWImw== /
印证了Oracle官方文档的说法
Wrapping PL/SQL Code with wrap Utility
The wrap utility processes an input SQL file and wraps only the PL/SQL units in the file,
such as a package specification, package body, function, procedure, type specification, or type body.
It does not wrap PL/SQL content in anonymous blocks or triggers or non-PL/SQL code.
我想是通过在文件中查找开始位置的CREATE OR REPLACE PROCEDURE(function,package body,type)来判断加密开始和结束的位置