Solved

External Table creation inside PLSQL Package error...

Posted on 2004-08-18
4
968 Views
Last Modified: 2008-01-09
Hi Experts..


I am trying to create an External Table inside PLSQL Package but an error "Insufficient Priviledges" occurs...

DECLARE
issuccess BOOLEAN;
BEGIN

-- Now call the stored program
  my_package.proc_load_file('INCOMING','SA202_PPD_WARRANTY_20041010_09101010.txt',issuccess);

-- Output the results
IF issuccess THEN
  dbms_output.put_line('issuccess = TRUE');
ELSE
  dbms_output.put_line('issuccess = FALSE');
END IF;

EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line(SubStr('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM, 1, 255));
RAISE;
END;



Here is my Package...

PACKAGE BODY MY_PACKAGE AS
PROCEDURE PROC_LOAD_FILE (
        varFileDirectory        IN VARCHAR2
        , varFile               IN VARCHAR2
        , isSuccess             OUT BOOLEAN)
IS
      varTransposedPhoneNumber    VARCHAR2(30) := '';
    varMobileNumber             VARCHAR2(30) := '';
    varSourceTransactionDate    VARCHAR2(15) := '';
    varTransactionIdentifier    VARCHAR2(30) := '';
   
    varDelimiter                VARCHAR2(1) := ',';
    varSQL                      VARCHAR2(1000);
BEGIN
    BEGIN
        varSQL :=
            'CREATE TABLE PREPAID_WARRANTY_EXT_TBL
            (
                TRANSPOSED_PHONE_NUMBER VARCHAR2(30),
                MOBILE_NUMBER VARCHAR2(30),
                SOURCE_TRANSACTION_DATE VARCHAR2(15),
                TRANSACTION_IDENTIFIER VARCHAR2(30)
            )
            ORGANIZATION EXTERNAL
            (
                TYPE ORACLE_LOADER
                DEFAULT DIRECTORY ' || varFileDirectory || '
                ACCESS PARAMETERS
                (
                    FIELDS TERMINATED BY ' || '''' || varDelimiter || '''' || '
                )
                LOCATION (' || '''' || varFile || '''' || ')
            )';
        --DBMS_OUTPUT.PUT_LINE('varSQL is [' || varSQL || ']');
        EXECUTE IMMEDIATE(varSQL);
        COMMIT;
        DBMS_OUTPUT.PUT_LINE('successfull create table');
    --EXCEPTION
        --WHEN OTHERS THEN
           --DBMS_OUTPUT.PUT_LINE('other exceptions create table');
    END;
END PROC_LOAD_FILE;
END MY_PACKAGE;



however, if I am to create an External Table outside of PLSQL Package, it is successfull...

CREATE TABLE PREPAID_WARRANTY_EXT_TBL
    (
         TRANSPOSED_PHONE_NUMBER    VARCHAR2(30),
         MOBILE_NUMBER              VARCHAR2(30),
         SOURCE_TRANSACTION_DATE    VARCHAR2(15),
         TRANSACTION_IDENTIFIER     VARCHAR2(30)
    )
    ORGANIZATION EXTERNAL
    (
         TYPE ORACLE_LOADER
         DEFAULT DIRECTORY INCOMING
         ACCESS PARAMETERS
         (
            FIELDS TERMINATED BY ','
         )
         LOCATION ('SA202_PPD_WARRANTY_20041010_09101010.txt')
    );



May I ask what do I need to set or configure with regards to priviledges...?


Thanks in advanced experts...   =)
0
Comment
Question by:aris_datuin
  • 2
4 Comments
 
LVL 8

Assisted Solution

by:baonguyen1
baonguyen1 earned 25 total points
ID: 11828108
Hi aris_datuin,

If you grant priviledge to user via role you may get "insufficient Priviledges" when executing PLSQL package. What you need to do is grant Create Any Table to that use directly then try again;

SQL>grant create any table to <schema>;

Hope this helps
0
 
LVL 8

Accepted Solution

by:
Pierrick LOUBIER earned 100 total points
ID: 11828148
Add AUTHID CURRENT_USER to your procedure :

PROCEDURE PROC_LOAD_FILE (
        varFileDirectory        IN VARCHAR2
        , varFile               IN VARCHAR2
        , isSuccess             OUT BOOLEAN) AUTHID CURRENT_USER
IS
0
 

Author Comment

by:aris_datuin
ID: 11828250
That was fast...!   And it worked already...

I would just like to ask, what does AUTHID CURRENT_USER is for...?


Thanks again experts...   =)
0
 
LVL 8

Expert Comment

by:Pierrick LOUBIER
ID: 11828319
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now