Re: How do I transfer stored procedures between databases

"Douglas C. Hay" <doughay_at_compusmart.ab.ca> wrote:
>Is there any way to export/import only the stored procedures for a specific
>user. I am currently developing a system in a Windows95/Sun-Unix
>environment at work. I am trying Personal Oracle at home to allow me to
>continue work at home when required. I am working quite extensively with
>the stored procedures and would like to be able to simply export a copy to
>transfer between home and work. I currently export individual procedures
>to text files and transfer one at a time. This is becoming increasingly
>painstaking as the number increases.
>
>Maybe someone has a utility or is there a specific table that could be
>exported. (NOTE: I would prefer not to have to export and import all the
>procedures associated with the designer repository(different user).
>
>THNX
>doughay_at_compusmart.ab.ca

Hi there,

I had the same problem. I performed the following steps.

1. From database 1, export the data with the following options

    exp scott/tiger file=expdat.dmp rows=n grants=n constraints=n

2. On database 2, drop all the stored procedures and import the above file

    imp user2/pwd2 file=expdat.dmp ignore=n

This will create all stored procedures in db2.

We need to specify ignore=n, because SEQUENCES and VIEWS would be present in DB2 and we need not disturb it. In this case all the sequences will not be created whereus all stored procedures will be created.

Hope this helps to you,

Regards

N.Prabhakar Received on Wed Nov 20 1996 - 00:00:00 CST