-
Hi,
Is there a way where you can export/import only the stored procedures.
Thanks
Ronnie
Ronnie
ronnie_yours@yahoo.com
You can if you think you can.
-
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.
Santosh Jadhav
8i OCP DBA
-
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.
Jeff Hunter
-
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.
rows=n will still export the table structures and during import if those tables are not there it will create them also.
Am I right
Ronnie
ronnie_yours@yahoo.com
You can if you think you can.
-
-
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!
-
Two mistakes in the above post:
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 Modic
ASCII 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 Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|