{"id":7152,"date":"2016-06-26T23:44:05","date_gmt":"2016-06-26T21:44:05","guid":{"rendered":"https:\/\/www.salvis.com\/blog\/?p=7152"},"modified":"2024-03-27T10:53:44","modified_gmt":"2024-03-27T09:53:44","slug":"plsql-bulk-unwrap","status":"publish","type":"post","link":"https:\/\/www.salvis.com\/blog\/2016\/06\/26\/plsql-bulk-unwrap\/","title":{"rendered":"PL\/SQL Bulk Unwrap"},"content":{"rendered":"\n

406 days ago I released PL\/SQL Unwrapper for SQL Developer version 0.1.1 and blogged about it. With this extension, you can unwrap the content of a SQL Developer window. Time for an update. With the new version 1.0 you can unwrap multiple selected objects with a few mouse clicks. In this blog post, I show how.<\/p>\n\n\n\n

1. Install Extensions<\/h2>\n\n\n\n

I assume that you are already using SQL Developer 4.0.2 or higher. If not then it is about time that you grab the latest version from here<\/a> and install it. It’s important to note that the extensions won’t run in older versions of SQL Developer.<\/p>\n\n\n\n

Configure the update center https:\/\/update.salvis.com\/<\/a> and https:\/\/update.oddgen.org\/<\/a> to install the extensions for SQL Developer:<\/p>\n\n\n\n

\"updates_oddgen_unwrapper\"<\/a><\/figure>\n\n\n\n

If you cannot use the update center because your company’s network restricts internet access then download oddgen<\/a>, unwrapper<\/a> and install them from file<\/a>.<\/p>\n\n\n\n

Why download oddgen for SQL Developer? Because the bulk unwrap feature is implemented as oddgen plugin. Unwrapping an editor content works without oddgen, but for bulk unwrap you need oddgen<\/a>.<\/p>\n\n\n\n

2. Setup Test Environment<\/h2>\n\n\n\n

If you have a schema in your Oracle database with wrapped code you may skip this step and use this schema for bulk unwrap.<\/p>\n\n\n\n

For the test environment, I’ve used Morten Braten’s Alexandria PL\/SQL Utility Library<\/a>. Clone or download the library from GitHub<\/a>. To install the library you need a dedicated user. Create such a user as SYS on your Oracle database instance as follows:<\/p>\n\n\n\n

Create User AX for Alexandria Library<\/span><\/path><\/path><\/svg><\/span>
CREATE<\/span> <\/span>USER<\/span> <\/span>ax<\/span> IDENTIFIED <\/span>BY<\/span> ax <\/span><\/span>\nDEFAULT<\/span> TABLESPACE users<\/span><\/span>\nTEMPORARY TABLESPACE temp;<\/span><\/span>\n<\/span>\nALTER<\/span> <\/span>USER<\/span> ax QUOTA <\/span>UNLIMITED<\/span> <\/span>ON<\/span> users;<\/span><\/span>\n<\/span>\nGRANT<\/span> <\/span>connect<\/span>, <\/span>resource<\/span> <\/span>TO<\/span> ax;<\/span><\/span>\nGRANT<\/span> <\/span>execute<\/span> <\/span>ON<\/span> dbms_crypto <\/span>TO<\/span> ax;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Then run the install.sql script in the setup directory of the Alexandria PL\/SQL Utility Library as user AX.<\/p>\n\n\n\n
Install Alexandria PL\/SQL Utility Library in Schema AX<\/span><\/path><\/path><\/svg><\/span>
@install.<\/span>sql<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Wrap PL\/SQL Code<\/span><\/path><\/path><\/svg><\/span>
SET<\/span> SERVEROUTPUT <\/span>ON<\/span><\/span>\nDECLARE<\/span><\/span>\n   <\/span>PROCEDURE<\/span> <\/span>wrap_and_install<\/span>(in_code <\/span>IN<\/span> <\/span>CLOB<\/span>) <\/span>IS<\/span><\/span>\n      co_chunksize <\/span>INTEGER<\/span> := <\/span>8196<\/span>;<\/span><\/span>\n      <\/span>l_array<\/span>      <\/span>dbms_sql.<\/span>varchar2a<\/span>;<\/span><\/span>\n      <\/span>l_lb<\/span>         <\/span>INTEGER<\/span> := <\/span>1<\/span>;<\/span><\/span>\n      <\/span>l_ub<\/span>         <\/span>INTEGER<\/span>;<\/span><\/span>\n      <\/span>l_cursor<\/span>     <\/span>PLS_INTEGER<\/span>;<\/span><\/span>\n   <\/span>BEGIN<\/span><\/span>\n      <\/span>l_ub<\/span> := <\/span>ceil<\/span>(sys.<\/span>dbms_lob.<\/span>getlength<\/span>(in_code) \/ co_chunksize);<\/span><\/span>\n      <<clob_chunks>><\/span><\/span>\n      <\/span>FOR<\/span> i <\/span>IN<\/span> <\/span>l_lb<\/span> .. <\/span>l_ub<\/span><\/span>\n      <\/span>LOOP<\/span><\/span>\n         <\/span>l_array<\/span>(i) := sys.<\/span>dbms_lob.<\/span>substr<\/span>(lob_loc => in_code,<\/span><\/span>\n                                           offset  => (i - <\/span>1<\/span>) * co_chunksize + <\/span>1<\/span>,<\/span><\/span>\n                                           amount  => co_chunksize);<\/span><\/span>\n      <\/span>END LOOP<\/span> clob_chunks;<\/span><\/span>\n      <\/span>l_cursor<\/span> := sys.<\/span>dbms_sql.<\/span>open_cursor<\/span>;<\/span><\/span>\n      sys.<\/span>dbms_sql.<\/span>parse<\/span>(<\/span>c<\/span>             => <\/span>l_cursor<\/span>,<\/span><\/span>\n                         STATEMENT     => <\/span>l_array<\/span>,<\/span><\/span>\n                         lb            => <\/span>l_lb<\/span>,<\/span><\/span>\n                         ub            => <\/span>l_ub<\/span>,<\/span><\/span>\n                         lfflg         => <\/span>FALSE<\/span>,<\/span><\/span>\n                         language_flag => sys.<\/span>dbms_sql.<\/span>native<\/span>);<\/span><\/span>\n      sys.<\/span>dbms_ddl.<\/span>create_wrapped<\/span>(ddl => <\/span>l_array<\/span>, lb => <\/span>l_lb<\/span>, ub => <\/span>l_ub<\/span>);<\/span><\/span>\n      sys.<\/span>dbms_sql.<\/span>close_cursor<\/span>(<\/span>l_cursor<\/span>);<\/span><\/span>\n   <\/span>END<\/span> wrap_and_install;<\/span><\/span>\nBEGIN<\/span><\/span>\n   <<unwrapped_sources>><\/span><\/span>\n   <\/span>FOR<\/span> <\/span>l_rec<\/span> <\/span>IN<\/span> (<\/span>SELECT<\/span> <\/span>TYPE<\/span> <\/span>AS<\/span> original_type,<\/span><\/span>\n                        <\/span>CASE<\/span> <\/span>TYPE<\/span><\/span>\n                           <\/span>WHEN<\/span> <\/span>'PACKAGE'<\/span> <\/span>THEN<\/span><\/span>\n                            <\/span>'PACKAGE_SPEC'<\/span><\/span>\n                           <\/span>WHEN<\/span> <\/span>'PACKAGE BODY'<\/span> <\/span>THEN<\/span><\/span>\n                            <\/span>'PACKAGE_BODY'<\/span><\/span>\n                           <\/span>WHEN<\/span> <\/span>'TYPE'<\/span> <\/span>THEN<\/span><\/span>\n                            <\/span>'TYPE_SPEC'<\/span><\/span>\n                           <\/span>WHEN<\/span> <\/span>'TYPE BODY'<\/span> <\/span>THEN<\/span><\/span>\n                            <\/span>'TYPE_BODY'<\/span><\/span>\n                           <\/span>ELSE<\/span><\/span>\n                            <\/span>TYPE<\/span><\/span>\n                        <\/span>END<\/span> <\/span>AS<\/span> object_type,<\/span><\/span>\n                        <\/span>NAME<\/span> <\/span>AS<\/span> object_name<\/span><\/span>\n                   <\/span>FROM<\/span> user_source<\/span><\/span>\n                  <\/span>WHERE<\/span> <\/span>TYPE<\/span> <\/span>IN<\/span> (<\/span>'FUNCTION'<\/span>,<\/span><\/span>\n                                 <\/span>'PROCEDURE'<\/span>,<\/span><\/span>\n                                 <\/span>-- 'PACKAGE',<\/span><\/span>\n                                 <\/span>'PACKAGE BODY'<\/span>,<\/span><\/span>\n                                 <\/span>-- 'TYPE',<\/span><\/span>\n                                 <\/span>'TYPE BODY'<\/span>,<\/span><\/span>\n                                 <\/span>'LIBRARY'<\/span>)<\/span><\/span>\n                        <\/span>AND<\/span> line = <\/span>1<\/span><\/span>\n                        <\/span>AND<\/span> <\/span>lower<\/span>(text) <\/span>NOT<\/span> <\/span>LIKE<\/span> <\/span>'% wrapped%'<\/span><\/span>\n                  <\/span>ORDER BY<\/span> <\/span>type<\/span>, <\/span>name<\/span>)<\/span><\/span>\n   <\/span>LOOP<\/span><\/span>\n      <<try_to_wrap>><\/span><\/span>\n      <\/span>DECLARE<\/span><\/span>\n         <\/span>l_ddl<\/span>  <\/span>CLOB<\/span>;<\/span><\/span>\n         <\/span>l_code<\/span> <\/span>CLOB<\/span>;<\/span><\/span>\n      <\/span>BEGIN<\/span><\/span>\n         <\/span>l_ddl<\/span> := sys.<\/span>dbms_metadata.<\/span>get_ddl<\/span>(object_type => <\/span>l_rec<\/span>.object_type,<\/span><\/span>\n                                            <\/span>NAME<\/span>        => <\/span>l_rec<\/span>.object_name,<\/span><\/span>\n                                            SCHEMA      => <\/span>USER<\/span>);<\/span><\/span>\n         <\/span>-- wrap with EDITIONABLE clause fails on 12.1.0.2.0 with: <\/span><\/span>\n         <\/span>--    ORA-24230: input to DBMS_DDL.WRAP is not a legal PL\/SQL unit<\/span><\/span>\n         <\/span>l_code<\/span> := <\/span>REPLACE<\/span>(<\/span>l_ddl<\/span>, <\/span>'CREATE OR REPLACE EDITIONABLE '<\/span>, <\/span>'CREATE OR REPLACE '<\/span>);<\/span><\/span>\n         <\/span>-- wrapping within database supported through DBMS_DDL since 10gR1<\/span><\/span>\n         wrap_and_install(in_code => <\/span>l_code<\/span>);<\/span><\/span>\n         sys.<\/span>dbms_output.<\/span>put_line<\/span>(<\/span>l_rec<\/span>.original_type || <\/span>' '<\/span> || <\/span>l_rec<\/span>.object_name ||<\/span><\/span>\n                                  <\/span>' wrapped.'<\/span>);<\/span><\/span>\n      <\/span>EXCEPTION<\/span><\/span>\n         <\/span>WHEN<\/span> <\/span>OTHERS<\/span> <\/span>THEN<\/span><\/span>\n            sys.<\/span>dbms_output.<\/span>put_line<\/span>(<\/span>l_rec<\/span>.original_type || <\/span>' '<\/span> || <\/span>l_rec<\/span>.object_name ||<\/span><\/span>\n                                     <\/span>' not wrapped because of '<\/span> || <\/span>SQLERRM<\/span>);<\/span><\/span>\n      <\/span>END<\/span> try_to_wrap;<\/span><\/span>\n   <\/span>END LOOP<\/span> unwrapped_sources;<\/span><\/span>\nEND<\/span>;<\/span><\/span>\n\/<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
3. Bulk Unwrap<\/h2>\n\n\n\n
Start SQL Developer and open a connection as user AX on your database.<\/p>\n\n\n\n
If the oddgen window is not visible then select “Generators” from the “View” menu as shown in the following picture:<\/p>\n\n\n\n
\"menu_view_generators\"<\/a><\/figure>\n\n\n\n
Afterwards, the Generators window appears in the lower left corner within SQL Developer.<\/p>\n\n\n\n
\"generators\"<\/a><\/figure>\n\n\n\n
Select the open connection in the combo box of the Generator window. Open the “PL\/SQL Unwrapper” node and the “Package Bodies” node to show all wrapped package body names.<\/p>\n\n\n\n
\"generators2\"<\/a><\/figure>\n\n\n\n
Select some or all package body nodes and press Return to generate the unwrapped code in a new worksheet. Afterwards, you just may execute the generated code. Add “SET DEFINE OFF” at the start of the script to ensure unwrapped code containing ampersand (&) characters is processed correctly. Another option is to configure a connection startup script (login.sql) to change the default behaviour.<\/p>\n\n\n\n
The following audio-less video shows in just 56 seconds the whole bulk unwrapping process in detail.<\/p>\n\n\n\n
Wrap the PL\/SQL code except package and type specifications in schema AX by running the script wrap_schema.sql<\/a>:<\/p>\n\n\n\n