Home » SQL & PL/SQL » SQL & PL/SQL » Datapump can't run in stored procedure
Datapump can't run in stored procedure [message #171086] Mon, 08 May 2006 05:37 Go to next message
alex.tam
Messages: 2
Registered: May 2006
Junior Member
The following script can be executed successfully:

    Declare
      hand number;
    Begin
      hand :=
         dbms_datapump.OPEN (operation      => 'EXPORT',
                             job_mode       => 'FULL',
                             job_name       => 'TEST',
                             VERSION        => 'COMPATIBLE'
                            );
    End;
    /


However, when I put these code into stored procedure and run it:


  PROCEDURE exportdump
   IS
      hand   NUMBER;
   BEGIN
   
      hand :=
         dbms_datapump.OPEN (operation      => 'EXPORT',
                             job_mode       => 'FULL',
                             job_name       => 'TEST',
                             VERSION        => 'COMPATIBLE'
                            );					
   END;



it gives the following error:

ORA-31626: job does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 911
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4356
ORA-06512: at "GOEX_ADMIN.BO_TEST_ALEX_PKG", line 8
ORA-06512: at line 2

what's wrong with it?
I'm using Oracle 10G R2
Re: Datapump can't run in stored procedure [message #171088 is a reply to message #171086] Mon, 08 May 2006 05:41 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
do you have all the necessary rights granted directly to your user (not via a role)?
Re: Datapump can't run in stored procedure [message #171180 is a reply to message #171088] Mon, 08 May 2006 20:52 Go to previous messageGo to next message
alex.tam
Messages: 2
Registered: May 2006
Junior Member
should be yes,
I use the same account to run the script and the stored procedure, and the script can be executed successfully.
Re: Datapump can't run in stored procedure [message #171192 is a reply to message #171180] Mon, 08 May 2006 23:28 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The anonymous block uses rights granted to roles, whereas when you create a stored procedure, only rights that are granted directly to your user are used.
See this page for more information. Read it until you understand, and you will never forget it.
Re: Datapump can't run in stored procedure [message #240232 is a reply to message #171192] Thu, 24 May 2007 07:42 Go to previous messageGo to next message
dbnewlon
Messages: 1
Registered: May 2007
Junior Member
I got this same error. The resolution is to give the user create table in their schema. I use a script like this...

drop directory dpump_dir1;
create directory dpump_dir1 as '/some/path/name';

grant create session, create table, create procedure, exp_full_database, imp_full_database to user;
grant read, write on directory dpump_dir1 to user;

After doing this I was able to run exports with dbms_datapump from a proc owned by this user.
Re: Datapump can't run in stored procedure [message #240238 is a reply to message #240232] Thu, 24 May 2007 07:49 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Thanks for the feedback, but I think the original poster found a solution by now.
Post is over a year old.
Previous Topic: How to create a simple join query?
Next Topic: Formatting data in phone format in SELECT
Goto Forum:
  


Current Time: Fri Dec 09 11:34:44 CST 2016

Total time taken to generate the page: 0.11260 seconds