Hi,
Is there a way where you can export/import only the stored procedures.
Thanks
Ronnie
Printable View
Hi,
Is there a way where you can export/import only the stored procedures.
Thanks
Ronnie
no
we cannot EXPORT just stored procedures. for that purpose we have to use other methods. like using dictionaries extracting text into text file.
if u want to use export then u have to export full schema.
Importing Stored Procedures, Functions, and Packages
When a local stored procedure, function, or package is imported, it retains its original specification timestamp. The procedure, function, or package is recompiled upon import. If the compilation is successful, it can be accessed by remote procedures without error.
Procedures are exported after tables, views, and synonyms, therefore they usually compile successfully since all dependencies will already exist. However, procedures, functions, and packages are not exported in dependency order. If a procedure, function, or package depends on a procedure, function, or package that is stored later in the Export dump file, it will not compile successfully. Later use of the procedure, function, or package will automatically cause a recompile and, if successful, will change the timestamp. This may cause errors in the remote procedures that call it.
We'll take that as a "yes, it can be done".
Basically, you export with full or user and rows=n to get your schema empty and then import.
rows=n will still export the table structures and during import if those tables are not there it will create them also.Quote:
Originally posted by marist89
We'll take that as a "yes, it can be done".
Basically, you export with full or user and rows=n to get your schema empty and then import.
Am I right
yup
When you import you will have to say ignore = yes, otherwise import will fail since all tables already exist. If you say ignore = yes it will not replaced old stored procedires with new ones since they already exist!
My take is that you will have to drop all procedures that need to be replaced first. Hope that someone can correct me if I am wrong. It was a while since I did that kind of import.
Two mistakes in the above post:
You don't have to (and should not have to for the purpose specified) use IGNORE=Y, you should use IGNORE=N. IGNORE=N will not abort the import process when it encounters allready existing object. It will simply report the object creation error in the log file, skip processing that object entirely, and continue the task with the next object. While setting IGNORE=Y will merely ignore the object creation error, but will continue to load data for that object! And I don't think this is desireable when you only want to import PL/SQL objects.Quote:
Originally posted by BV1963
When you import you will have to say ignore = yes, otherwise import will fail since all tables already exist.
That's not true either. Import will simply replace the old procedures with the ones contained in dump file. That's because procedures, packages and other PL/SQL stuf (as well as views) are defined with "CREATE OR REPLACE ...." in the dump file. So Oracle will simply REPLACE old objects, there will be no errors to ignore. With segments (tables, indexes,...) it is different, because there is no "CRATE OR REPLACE TABLE ...." command available in SQL - that's why you have to use IGNORE=Y/N for those objects. But it only aplies to those kind of objects, it has no effect on procedures, functions, packages, triggers, views etc.Quote:
If you say ignore = yes it will not replaced old stored procedires with new ones since they already exist!
I agree that for purpose of thi example you do need to say ignore=yes. However regardless of this parameter it will not overwrite existed procedires because export file does not have create or replace, it only has create statement.
I did create a user and create procedure under this user schema and actually browse dump file and did not find create or replace statement.
Conlusion :
You have to drop all your procedures and function, otherwise it will not replace existed procedures, but will bring new ones and results will be difficult to reconcile.
Just want to mention that was a schema level export and full database export could work differently
Yep, you are right about that one (and I was wrong). I just checked it - it realy doesn't rewrite any stored objects (PL/SQL, views, ...).Quote:
Originally posted by BV1963
I agree that for purpose of thi example you do need to say ignore=yes. However regardless of this parameter it will not overwrite existed procedires because export file does not have create or replace, it only has create statement.