export/import stored procedures
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: export/import stored procedures

Hybrid View

  1. #1
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    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.

  2. #2
    Join Date
    Dec 2001
    Posts
    221
    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

  3. #3
    Join Date
    May 2000
    Location
    Portsmouth, NH, USA
    Posts
    378
    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.

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    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
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    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.

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    yup
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  7. #7
    Join Date
    Nov 2001
    Posts
    335

    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!

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  9. #9
    Join Date
    Nov 2001
    Posts
    335

    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!

  10. #10
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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
  •  



Click Here to Expand Forum to Full Width