If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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.

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.

One, who thinks that the other one who thinks that know and does not know, does not know either!

Originally posted by BV1963 When you import you will have to say ignore = yes, otherwise import will fail since all tables already exist.

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.

If you say ignore = yes it will not replaced old stored procedires with new ones since they 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.

Jurij ModicASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?

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

One, who thinks that the other one who thinks that know and does not know, does not know either!

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.

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, ...).

Jurij ModicASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?