Home » SQL & PL/SQL » SQL & PL/SQL » Retriving Package (Oracle 9i)
Retriving Package [message #320090] Wed, 14 May 2008 01:28 Go to next message
catchme_lenin
Messages: 35
Registered: January 2008
Location: Dubai
Member
Hi Gurus,
I wrongly compiled a Package Body in a exisiting Package body's name in TOAD. It was over written.
Now I need to retive the old package. Is it possible in TOAD?

We have weekly backup of the database. Can I retrive the particular package body from the backup? if so what's the process? Can you tell me the command please?

thank you in advance.

Yours,
Lenin.
Re: Retriving Package [message #320107 is a reply to message #320090] Wed, 14 May 2008 02:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is it possible in TOAD?

quoting Ana (anacedent):
Those who live by the GUI, die by the GUI.


Quote:
Can I retrive the particular package body from the backup?

Create a new database with the backup, retrieve the package code using dbms_metadata and apply it to the current database.

But of course the correct way is that you should have the code history or creation script somewhere.

Regards
Michel

Re: Retriving Package [message #320120 is a reply to message #320090] Wed, 14 May 2008 02:31 Go to previous messageGo to next message
catchme_lenin
Messages: 35
Registered: January 2008
Location: Dubai
Member
Dear Michel,
thank you for your reply. Since my question was related to Backup & Recovery, I posted my question again in the Backup & Recovery topic. Sorry for posting twice.

Well. The backup is 25 GB and we have no space in server. If I can retrive ONLY THE PACKAGE BODY FROM THE BACKUP (Not restoring the full database), it will be very much helpful. Is it possible to do so? If yes, how?

Thnx.
Yours,
Lenin.
Re: Retriving Package [message #320123 is a reply to message #320120] Wed, 14 May 2008 02:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The way I gave is the ONLY one.

Regards
Michel
Re: Retriving Package [message #320153 is a reply to message #320123] Wed, 14 May 2008 03:24 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, this will probably not help, but - as many people call EXPORT files a "backup", do you, perhaps, have an export file somewhere around? If so, you could try with
IMP un/pw@db FILE=exprot_file.dmp LOG=logfile.txt SHOW=Y
It would not import anything (so that you shouldn't worry), but create a TXT file which you may edit and extract (copy-paste technique) code you're interested in.
Re: Retriving Package [message #320156 is a reply to message #320153] Wed, 14 May 2008 03:31 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Here's a thought.

If you have Flashback enabled, would you be able to query the ALL_SOURCE view to see what the package looked like at a point in the past?
icon10.gif  Re: Retriving Package [message #320171 is a reply to message #320156] Wed, 14 May 2008 04:25 Go to previous messageGo to next message
priyamalhotra
Messages: 43
Registered: July 2006
Location: none
Member




Hi,

If only the ALL_SOURCE table is imported in the test database that might be useful too.


Thanks,
Priya.

Re: Retriving Package [message #320178 is a reply to message #320171] Wed, 14 May 2008 04:36 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
ALL_SOURCE isn't a table. It's a view on 3 SYS owned tables, and an x$ table.
Re: Retriving Package [message #320188 is a reply to message #320120] Wed, 14 May 2008 04:51 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:
Since my question was related to Backup & Recovery

I don't think this is about backup and recovery. What you are fundamentally missing is a source code repository. I think it is high time for you to realise not to use your operational database as a source code repository. I am finding difficult to digest that you have lost the source code of the package and trying to retrieve it from a database object. I hope the package you are trying to retrieve is not created using the wrap utility.

I know that I have not given you any help in retrieving the contents of the package but I think this will be good learning point for you to use proper source code control tools like PVCS, CVS, etc.

Regards

Raj
Re: Retriving Package [message #320200 is a reply to message #320153] Wed, 14 May 2008 05:19 Go to previous messageGo to next message
catchme_lenin
Messages: 35
Registered: January 2008
Location: Dubai
Member
Hi Littlefoot,
thank you for your reply.
Unfortunately, it is not exported dump. It is Cold Backup, taken in tape. Have to restore the full database, to retrive the package body. So, I have decided to re coding again.
From today, i will take a copy of every file, which I am coding, on everyday. It is a lesson for me.
Yours,
Lenin.
Re: Retriving Package [message #320203 is a reply to message #320200] Wed, 14 May 2008 05:23 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Might I suggest that some Version Control Software would be a smart idea.
Re: Retriving Package [message #320206 is a reply to message #320188] Wed, 14 May 2008 05:31 Go to previous messageGo to next message
catchme_lenin
Messages: 35
Registered: January 2008
Location: Dubai
Member
Hi Raj,
Yes. It is a lesson for me. I usually take copy of procedure,function,package,etc. files, when only it is completed and finalised. This is the first time I lost a huge package files (around 1500 lines), due to my mistake. It's ok. I know the logic and it will take 4 days to re code the package body.
We are using the PVCS for COMPLETED/READY TO USE files only. I will keep the on-going files into my system itself.

thank you for your kind advice.

Yours,
Lenin.
Re: Retriving Package [message #320207 is a reply to message #320206] Wed, 14 May 2008 05:37 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
At my previous work place we are not allowed to create/store files in our local drive. Anything related to work our first point of creation will be in PVCS and we should maintain the entire audit trail (amendment made to the code as a result of code review or whatever). If PVCS is configured properly you could easily do it (complete software lifecycle). So I would suggest you to create even the working version in PVCS.

Regards

Raj
Re: Retriving Package [message #320301 is a reply to message #320207] Wed, 14 May 2008 11:26 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
http://technology.amis.nl/blog/?p=846
Previous Topic: Drop partitioned table
Next Topic: utl_smtp and national charset
Goto Forum:
  


Current Time: Fri Dec 02 21:02:31 CST 2016

Total time taken to generate the page: 0.09609 seconds