2

I am new to pl/sql, I have a pkg_body and I want to make changes to it. But before doing that I want to take backup of the original pkg. So can I create a new pkg with name **_bkp and just copy the code into it.

I have found the below code when searched on net

set pagesize 0
set linesize 800
set trimspool on
set heading off
set feedback off
spool filename.pkb
select text from dba_source where name='objectname' and type='PACKAGE BODY';
spool off
exit

But should I do this?Cant I create a new pkg with different name and copy the original code into it hen make the changes?

Thanks

sandywho
  • 353
  • 1
  • 7
  • 16

2 Answers2

3

In PL/SQL developer the top menu bar has an item called Tools.

  • left click to select this
  • a dropdown appears select Export user Objects
  • change user to the owner of the package
  • click on the tiny blank square in the upper right corner of the Name column to sort by name
  • left click to select the package header and body
  • at the bottom right click on the folder icon and select a destination and name for your backup
  • click Export
  • close the window
  • to revert open the file in PL/SQL developer as a command window and run
kevinskio
  • 4,431
  • 1
  • 22
  • 36
3

This is what source code control (e.g., SVN, PVCS, etc) is for.

But, here's what you can do.

select dbms_metadata.get_ddl('PACKAGE_SPEC', 'your-package-name-here', user) from dual;

Then, edit the CLOB that is returned to change the name. Do the same thing for the package body.

select dbms_metadata.get_ddl('PACKAGE_BODY', 'your-package-name-here', user) from dual;

But again, you should be using SVN or something similar. To make a change, edit the DDL file you got from SVN and run it in the database. Don't use SQL*Navigator/TOAD abilities to edit packages directly and "save" them to the database. Unless maybe if you are the only developer on your team.

Matthew McPeak
  • 17,705
  • 2
  • 27
  • 59
  • Never use SQL GUI clients' abilities to edit packages directly in the database. (Unless you deliberately want to create artificial problems.) – user272735 Aug 14 '15 at 06:17