Home » RDBMS Server » Server Utilities » How to export all of my procedure and packeges from my schema?
How to export all of my procedure and packeges from my schema? [message #636842] Mon, 04 May 2015 06:05 Go to next message
netanel
Messages: 13
Registered: May 2015
Junior Member
Hello,
I am new on oracle DB.
I didnt find a command that let me to export all of my procedure and packeges from my schema
Do you have any suggestiuons?
Re: How to export all of my procedure and packeges from my schema? [message #636843 is a reply to message #636842] Mon, 04 May 2015 06:19 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3146
Registered: May 2013
Location: World Wide on the Web
Senior Member
Welcome to the forum!

Please read and follow the OraFAQ Forum Guide and How to use [code] tags, to enable us to help you.

What exactly is your question? You want to exclude objects other than Procedures and Packages? Or you just want to know how to export? Read Data Pump Export
Re: How to export all of my procedure and packeges from my schema? [message #636846 is a reply to message #636843] Mon, 04 May 2015 06:45 Go to previous messageGo to next message
netanel
Messages: 13
Registered: May 2015
Junior Member
Hello Lalit Kumar B,
I would like to export all of my packages that on my schema.
What is the command to do it?
Re: How to export all of my procedure and packeges from my schema? [message #636847 is a reply to message #636842] Mon, 04 May 2015 06:45 Go to previous messageGo to next message
Michel Cadot
Messages: 66450
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

if export is the correct term (that is get an Oracle dump for this):

expdp user/psw schemas=user include=procedure include=package include=function

You can also use dbms_metadata.get_ddl to get a SQL file for these.

[Updated on: Mon, 04 May 2015 06:46]

Report message to a moderator

Re: How to export all of my procedure and packeges from my schema? [message #636849 is a reply to message #636847] Mon, 04 May 2015 06:59 Go to previous messageGo to next message
netanel
Messages: 13
Registered: May 2015
Junior Member
Hello All,
i tried the expdp command but i didn't succeed.
Can anyone help?

C:\Users\Administrator>expdp wtc/wtc include=procedure include=package include=function directory=C:\oraclexe\app\oracle\admin\XE\dpdump dumpfile=a.dmp logfile=b.dmp

Export: Release 11.2.0.2.0 - Production on Mon May 4 14:58:24 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
UDE-00014: invalid value for parameter, 'directory'.
Re: How to export all of my procedure and packeges from my schema? [message #636850 is a reply to message #636849] Mon, 04 May 2015 07:04 Go to previous messageGo to next message
netanel
Messages: 13
Registered: May 2015
Junior Member
i also investigate the dbms_metadata.get_ddl command.
but it seems to be that on this command i can refer each time for one procedure\package

am i correct in here?
can i use this command to export all the packages in one time?
Re: How to export all of my procedure and packeges from my schema? [message #636851 is a reply to message #636849] Mon, 04 May 2015 07:11 Go to previous messageGo to next message
Michel Cadot
Messages: 66450
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
netanel wrote on Mon, 04 May 2015 13:59
Hello All,
i tried the expdp command but i didn't succeed.
Can anyone help?

C:\Users\Administrator>expdp wtc/wtc include=procedure include=package include=function directory=C:\oraclexe\app\oracle\admin\XE\dpdump dumpfile=a.dmp logfile=b.dmp

Export: Release 11.2.0.2.0 - Production on Mon May 4 14:58:24 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
UDE-00014: invalid value for parameter, 'directory'.


You have to read the documentation about your wrong "directory" parameter value.
You MUST give an ORACLE directory not an OS one.

And I confirm, the command I gave works:
expdp michel/michel schemas=michel include=procedure include=package include=function

Export: Release 11.2.0.3.0 - Production on Lun. Mai 4 14:09:42 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "MICHEL"."SYS_EXPORT_SCHEMA_01":  michel/******** schemas=michel include=procedure include=package include=function

Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Master table "MICHEL"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MICHEL.SYS_EXPORT_SCHEMA_01 is:
  D:\USERS\CADOT\ORACLE\112\RDBMS\LOG\EXPDAT.DMP
Job "MICHEL"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:09:54

And when you have an error you MUST post as I did not just tell what you think you did.




Re: How to export all of my procedure and packeges from my schema? [message #636852 is a reply to message #636850] Mon, 04 May 2015 07:12 Go to previous messageGo to next message
Michel Cadot
Messages: 66450
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
netanel wrote on Mon, 04 May 2015 14:04
i also investigate the dbms_metadata.get_ddl command.
but it seems to be that on this command i can refer each time for one procedure\package

am i correct in here?
can i use this command to export all the packages in one time?


You are not correct.
Yes you can.

Post what you tried and post it formatted as explain in How to use [code] tags and make your code easier to read.

Re: How to export all of my procedure and packeges from my schema? [message #636854 is a reply to message #636852] Mon, 04 May 2015 07:23 Go to previous messageGo to next message
netanel
Messages: 13
Registered: May 2015
Junior Member
Hello Michel,
Thank you for your quick reply.

As i said Oracle is new to me.
The path that i choose is where my Oracle DB install why its not good?


in addition what is the syntax for bms_metadata.get_ddl command to exp all the procedure and function in my schema?

Thank in advance!
Re: How to export all of my procedure and packeges from my schema? [message #636855 is a reply to message #636854] Mon, 04 May 2015 07:29 Go to previous messageGo to next message
Michel Cadot
Messages: 66450
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As I said you have to read the documentation.
Oracle directory <> OS directory.
Oracle directory is an Oracle object like a table.

For the syntax of dbms_metadata you have to make a little effort and 1) search (there are many examples on the web including mines), 2) try, 3) come back with your tries if you can't do it.

Re: How to export all of my procedure and packeges from my schema? [message #636861 is a reply to message #636855] Mon, 04 May 2015 08:08 Go to previous messageGo to next message
netanel
Messages: 13
Registered: May 2015
Junior Member
Hello Michel'

i found this example:

select dbms_metadata.get_ddl('PACKAGE','SPI_xxx_PCK',USER) from dual;

but i dont want to export one specific package i want to export all the packages with the spec body and all the function that inside
Re: How to export all of my procedure and packeges from my schema? [message #636862 is a reply to message #636861] Mon, 04 May 2015 08:16 Go to previous messageGo to next message
Michel Cadot
Messages: 66450
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So do not query from dual but from user_objects and do not put the package name as constant but as object_name column.

Re: How to export all of my procedure and packeges from my schema? [message #636863 is a reply to message #636862] Mon, 04 May 2015 08:48 Go to previous messageGo to next message
netanel
Messages: 13
Registered: May 2015
Junior Member
Hello Michel,

Can you please show me the syntax for exporting with dbms_metadata.get_ddl?
It is not clear to me.


As i said Oracle is new to me.
but now i understand what was my problem with expdp.
after you explain to me what is Oracle directory.
i created new directory but i still got the same ERR.
than i understand that i am need privilage to write so i enter as sys and grant my usr wtc that grant.
and now it work well this is my syntax for expdp:
after enter to cmd:

expdp wtc/wtc include=procedure include=package include=function directory=test dumpfile=a.dmp logfile log.dmp



Re: How to export all of my procedure and packeges from my schema? [message #636867 is a reply to message #636863] Mon, 04 May 2015 09:50 Go to previous messageGo to next message
Michel Cadot
Messages: 66450
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
i created new directory but i still got the same ERR.


Which help in any way to know what it the problem.

Post what you did and got in the required format as I showed you and as explain in the link I provided you.
Being new in Oracle is not an excuse to not follow the forum rules.

Re: How to export all of my procedure and packeges from my schema? [message #636882 is a reply to message #636867] Tue, 05 May 2015 02:33 Go to previous messageGo to next message
netanel
Messages: 13
Registered: May 2015
Junior Member
Hello Michel,
i am trying to execute the command but i dont know what to set on the #2 parameter of the function.

select dbms_metadata.get_ddl(object_name,?,USER) from user_objects

i want package, package body,function and only for my user/schema wtc.
i am kind of lost please help.

Re: How to export all of my procedure and packeges from my schema? [message #636883 is a reply to message #636882] Tue, 05 May 2015 02:38 Go to previous messageGo to next message
Michel Cadot
Messages: 66450
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Did you read the documentation about this package?
What is the first parameter?
What is the second parameter?
What is the third parameter?

Re: How to export all of my procedure and packeges from my schema? [message #636886 is a reply to message #636883] Tue, 05 May 2015 03:29 Go to previous messageGo to next message
netanel
Messages: 13
Registered: May 2015
Junior Member
i tried to read the documetation
http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_metada.htm#BABBCHHJ

but its very confusing and i dont understand what each parameter represent.

if you can please just copy paste the definition of each parameter i cant find it Sad
Re: How to export all of my procedure and packeges from my schema? [message #636888 is a reply to message #636886] Tue, 05 May 2015 03:33 Go to previous messageGo to next message
cookiemonster
Messages: 13630
Registered: September 2008
Location: Rainy Manchester
Senior Member
The third mention of get_ddl in that documentation page lists all the parameters.
Re: How to export all of my procedure and packeges from my schema? [message #636889 is a reply to message #636886] Tue, 05 May 2015 03:34 Go to previous messageGo to next message
Michel Cadot
Messages: 66450
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
if you can please just copy paste the definition of each parameter i cant find it


Just scroll down the page to GET_DDL.

Re: How to export all of my procedure and packeges from my schema? [message #636904 is a reply to message #636889] Tue, 05 May 2015 06:20 Go to previous messageGo to next message
netanel
Messages: 13
Registered: May 2015
Junior Member
Did you mean that?

DBMS_METADATA.GET_DDL (
object_type IN VARCHAR2,
name IN VARCHAR2,
schema IN VARCHAR2 DEFAULT NULL,
version IN VARCHAR2 DEFAULT 'COMPATIBLE',
model IN VARCHAR2 DEFAULT 'ORACLE',
transform IN VARCHAR2 DEFAULT 'DDL')
RETURN CLOB;


let say that i choose:
parameter1:'package'
parameter2:?
parameter3:wtc

what should i put i 2# parameter to get all of my packages?

[Updated on: Tue, 05 May 2015 06:21]

Report message to a moderator

Re: How to export all of my procedure and packeges from my schema? [message #636906 is a reply to message #636904] Tue, 05 May 2015 07:00 Go to previous messageGo to next message
Michel Cadot
Messages: 66450
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Michel Cadot wrote on Mon, 04 May 2015 15:16

So do not query from dual but from user_objects and do not put the package name as constant but as object_name column.


Re: How to export all of my procedure and packeges from my schema? [message #636908 is a reply to message #636906] Tue, 05 May 2015 07:28 Go to previous messageGo to next message
netanel
Messages: 13
Registered: May 2015
Junior Member
Did you mean thisd?

select dbms_metadata.get_ddl('package',object_name ,'wtc') from user_objects

becauese i cant run it.

[Updated on: Tue, 05 May 2015 07:30]

Report message to a moderator

Re: How to export all of my procedure and packeges from my schema? [message #636909 is a reply to message #636908] Tue, 05 May 2015 07:33 Go to previous messageGo to next message
Michel Cadot
Messages: 66450
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

1/ Oracle works with UPPER case names.
2/ If you want just the packages from the query then you have to restrict the query to the object type PACKAGE.


Re: How to export all of my procedure and packeges from my schema? [message #636912 is a reply to message #636909] Tue, 05 May 2015 09:15 Go to previous messageGo to next message
netanel
Messages: 13
Registered: May 2015
Junior Member
whent i try to run this command:

"select dbms_metadata.get_ddl('PACKAGE',object_name ,'SYS') from user_objects"

i got an err:
"ORA-31603: object "PIE_SCALAR_TYPE" of type PACKAGE not found in schema "SYS"
ORA-06512: at "SYS.DBMS_METADATA", line 4018
ORA-06512: at "SYS.DBMS_METADATA", line 5843
ORA-06512: at line 1"


the same on wtc

any suggestions?
Re: How to export all of my procedure and packeges from my schema? [message #636913 is a reply to message #636912] Tue, 05 May 2015 09:26 Go to previous messageGo to next message
BlackSwan
Messages: 26547
Registered: January 2009
Location: SoCal
Senior Member

  1  SELECT 'DBMS_METADATA.GET_DDL(''PACKAGE'','
  2                ||object_name
  3                ||',''SCOTT'') FROM DUAL;'
  4  FROM   user_objects
  5* WHERE  object_type = 'PACKAGE'
SQL> /

no rows selected

SQL> show user
USER is "SCOTT"

Re: How to export all of my procedure and packeges from my schema? [message #636914 is a reply to message #636912] Tue, 05 May 2015 09:56 Go to previous messageGo to next message
Michel Cadot
Messages: 66450
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
any suggestions?

Is your user "SYS" or "wtc"?

You have to think a little bit and try to understand what you write.

[Updated on: Tue, 05 May 2015 09:57]

Report message to a moderator

Re: How to export all of my procedure and packeges from my schema? [message #636916 is a reply to message #636914] Tue, 05 May 2015 10:27 Go to previous messageGo to next message
cookiemonster
Messages: 13630
Registered: September 2008
Location: Rainy Manchester
Senior Member
And is your user called "wtc" or "WTC"?
Check what sho user shows when logged in.
Re: How to export all of my procedure and packeges from my schema? [message #636918 is a reply to message #636842] Tue, 05 May 2015 10:31 Go to previous message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
You can also use the SQLFILE option with Datapump once you've got it working Smile
Previous Topic: export from 11g and import to 10g
Next Topic: Load data using sql loader..
Goto Forum:
  


Current Time: Wed Jun 26 21:33:00 CDT 2019