Insufficient privileges to create table in PL/SQL

Posted on 2007-07-25
Last Modified: 2013-12-19

I have the issue with creating a table from pl/sql stored procedure  (Oracle 9i).

When I am trying to create a table from a procedure I get the following error :

exec RecInitialize ( 'TableName' );

ORA-01031: insufficient privileges
ORA-06512: at "POSINT.RECINITIALIZE", line 82
ORA-06512: at line 1

But If I run the same statement from TOAD directly it works without any issues:

    execute immediate   'drop table TableName';
    execute immediate   'create table TableName
                            BUSINESS_DATE   date               null,
                            STORE_NUMBER    number  ( 22, 5  ) null,
                            ACCOUNT_TYPE    varchar2( 32     ) null,
                            GL_CODE         varchar2( 6      ) null,
                            LINE_NUMBER     varchar2( 32     ) null,
                            ACCOUNT_CODE    varchar2( 32     ) null,
                            AMOUNT          number  ( 22, 10 ) null          

The same login.

Thanks for any help.
Question by:GoodName
    LVL 8

    Expert Comment

    try adding
    authid current_user in the procedure's declaration.
    LVL 8

    Accepted Solution

    Actually a good explanation can be found in:

    If you have the required privileges through a role, (such as resource for example) you need to use invokers rights (i.e. AUTHID CURRENT_USER). The other option is to grant system privs (i.e CREATE TABLE or CREATE ANY TABLE) directly and not through a role.

    Author Comment

    Thanks! With authid current_user it works fine.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
    I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
    This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
    This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

    779 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

    11 Experts available now in Live!

    Get 1:1 Help Now