Data Pump PL/SQL error when called via procedure

July 18, 2008  |  dba, pl/sql

Do you remember my data pump code sample post about PL/SQL partial export? Btw, It’s on the top 5 most read in my blog. So today I’ve found that if that code is run directly, on SQLPLUS or any other tool like SQL DEVELOPER, SQL NAVIGATOR, in the following way:

DECLARE
variables;
BEGIN
pl_sql_code;
END;

It runs flawlessly… NO PROBLEMO!

But when I put this code inside a stored procedure and try to run it, I got this error:

[1]: (Error): ORA-31626: a tarefa não existe
ORA-06512: na “SYS.DBMS_SYS_ERROR”, linha 79
ORA-06512: na “SYS.DBMS_DATAPUMP”, linha 938
ORA-06512: na “SYS.DBMS_DATAPUMP”, linha 4592
ORA-06512: na “NEO.BACKUP_EMPRESA”, linha 19 ORA-06512: na linha 3

After googling for half an hour, I’ve decided try Metalink! There I found that I need to grant directly, and not by role, some priviledges… like this:

GRANT CREATE SESSION, CREATE TABLE, CREATE PROCEDURE TO myuser;
GRANT EXP_FULL_DATABASE, IMP_FULL_DATABASE TO myuser;

At least to me sounds stupid that running code outside a procedure does not need those GRANTS… and inside a procedure it needs! It’s also strange that a post with thousand readers did not had any comment about this issue!

Cya,
João


1 Comment


  1. Have you try put this statement in package declaration: AUTHID CURRENT USER
    ?

    To me this solved problems of grant directly some privileges.

    Nando.

Leave a Reply