How to wrap oracle procedure

I need to wrap all my oracle procedure (around 500), please let me know if there is any way to achive this.
SyedhamidAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Naveen KumarProduction Manager / Application Support ManagerCommented:
it you have all the source code of all the procedures in one single .sql file, it is very very simple to do it with wrap utility.

The other way is to use dbms_ddl package.

just open the link and read through :

http://www.oracle-base.com/articles/10g/WrapAndDBMS_DDL_10gR2.php
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SyedhamidAuthor Commented:
Thank for your reply.  I am using below code

declare
      cursor c1 is
      select object_name, owner
      from   dba_objects
      where  owner in ('LENDING' )
      and    object_type in ('PROCEDURE' );

      cursor c2 (p_owner varchar2 , p_name varchar2) is
      select text
      from   dba_source
      where  name   = p_name
      and    owner  = p_owner;

      l_wrap dba_source.text%type;
      l_text dba_source.text%type;

      BEGIN
       for i in c1
       loop
         for j in c2 (i.owner, i.object_name)
          loop
            l_text := 'CREATE OR REPLACE ';
            l_text := l_text || ' ' ||j.text;
            l_wrap := SYS.DBMS_DDL.WRAP(ddl => l_text);
         end loop;
      end loop;
      END;
while executing the above code I am getting below error -

ORA-24230: input to DBMS_DDL.WRAP is not a legal PL/SQL unit
ORA-06512: at "SYS.DBMS_DDL", line 595
ORA-06512: at line 26

Please help !! Thanks
0
Naveen KumarProduction Manager / Application Support ManagerCommented:
just use dbms_output.put_line(..) to see what is there in it before you pass that to wrap procedure call...

may be procedure keyword is not there, or some other syntax issues should be there.

try it out.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

slightwv (䄆 Netminder) Commented:
I just wanted to ask if you have all your code saved off before you wrap everything.  There is no unwrap command.
0
SyedhamidAuthor Commented:
Yes I have taken backup.. Thanks
0
Naveen KumarProduction Manager / Application Support ManagerCommented:
is it still not working or you have found that issue ?
0
SyedhamidAuthor Commented:
Sorry was out of town, yes still not working, tried lot of option but no use.
0
Naveen KumarProduction Manager / Application Support ManagerCommented:
try the attached code.. it works for me..i tested it for one sample procedure.

declare
      cursor c1 is
      select object_name, owner
      from   dba_objects
      where  owner in ('LENDING' )
      and    object_type in ('PROCEDURE' )
      AND OBJECT_NAME ='TEST_PROC'; -- first test for one procedure.

      cursor c2 (p_owner varchar2 , p_name varchar2) is
      select text
      from   dba_source
      where  name   = p_name
      and    owner  = p_owner
      ORDER BY LINE; -- this is required here to ensure the correct order of text.

      l_wrap dba_source.text%type;
      l_text dba_source.text%type;

      BEGIN
       for i in c1
       loop
            l_text := 'CREATE OR REPLACE ';
         for j in c2 (i.owner, i.object_name)
          loop
            l_text := l_text || ' ' ||j.text;
         end loop;
          DBMS_OUTPUT.PUT_LINE(L_TEXT); -- to see how the text looks before we wrap it
                -- comment the above debug line if not required for you.
            l_wrap := SYS.DBMS_DDL.WRAP(ddl => l_text);

          DBMS_OUTPUT.PUT_LINE(L_WRAP);
             -- to see how the wrapped procedure looks like.
      end loop;
EXCEPTION WHEN OTHERS THEN
   DBMS_OUTPUT.PUT_LINE('IN EXCEPTION');
   DBMS_OUTPUT.PUT_LINE('SQLCODE:'||SQLCODE);
   DBMS_OUTPUT.PUT_LINE('SQLERRM:'||SQLerrm);
-- should have an exception section to catch any errors.
      END;
/
0
SyedhamidAuthor Commented:
Hi Thanks, Now I am getting Numeric or Value Error.. in l_text  variable.
0
Naveen KumarProduction Manager / Application Support ManagerCommented:
you are getting that error because l_text is declared as _text dba_source.text%type which is nothing but 4000 bytes and your procedure text length is going more than that.

see the link i had posted in my first update.

you need to use the length as shown... It will work as long as your procedure text length does not exceed 32767.

  l_source  VARCHAR2(32767);
  l_wrap    VARCHAR2(32767);

If you still get error even after changing it to 32767, you need to use the other methods listed in the url to do the wrapping.

Thanks,
0
Naveen KumarProduction Manager / Application Support ManagerCommented:
I believe i had provided enough evidence and comments/feedback for the requestor but the user did not respond for my last feedback.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.