Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2005
    Posts
    1

    Unanswered: Truncate -> Insufficient Privillege

    Hi,

    Can any one help?
    I'm trying to truncate a table in VB but it gives me error Ora-01031 Insufficient Privillege.

    I also grant DBA profile to the user but still getting the same error... saw in other forum that I should grant the user with "DROP ANY TABLE" privillege.. but should DBA also have that privillege already??


    Thanks & Regards,
    Kokuryo

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Once again, again, again....

    Privs acquired via ROLEs (such as DBA) do NOT apply within stored procedures.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    If the user is working in their own schema, then DROP TABLE, else they'll need DROP ANY TABLE.

    You might instead look into using a specific grant on the table, using GRANT DELETE ON <tablename> TO <user>. If you're concerned about the logging activity, and that's why you wanted TRUNCATE rights in the first place, you could try DELETE /*+ NOLOGGING +/ FROM <tablename>.

    -Chuck

  4. #4
    Join Date
    Aug 2005
    Posts
    16

    Question Help with PL/SQL datatransfer from Oracle to SQL server

    Greetings,
    I have been trying this for a few days still could not make it work. I am new here and please help! I am not sure which problems I have, on Oracle PL/SQL side or SQL 2000 DTS side. Any suggestions/comments are highly appreciated!

    I created a simple DTS package in Enterprise Mgr to select a data set from Oracle server and transfer them to SQL 2000 server. Here is the select query for data transfer.
    Code:
    select * from ord_fx where trade_date='7-Oct-2005'
    It works fine and transfer the correct data set.

    Now I want to have updated data transfered on daily basis and revise the query:
    Code:
    declare CurrDate date :=round(sysdate);
    select * from ord_FX
    where status_Flag is null
    and to_date(Entry_date, 'DD-MM-YYYY'>='CurrDate';
    However, I got the following error message when I click on Preview... button.
    Attached Files Attached Files

  5. #5
    Join Date
    Oct 2004
    Posts
    145
    declare CurrDate date :=round(sysdate);
    select * from ord_FX
    where status_Flag is null
    and to_date(Entry_date, 'DD-MM-YYYY'>='CurrDate';

    Change to

    select * from ord_FX
    where status_Flag is null
    and to_date(Entry_date, 'DD-MM-YYYY') >= round(sysdate);

    Assuming Entry_date is in proper format you have specified.

  6. #6
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    Actually you need to change ROUND() to TRUNC():
    Code:
    select * from ord_FX
     where status_Flag is null
       and to_date(Entry_date, 'DD-MM-YYYY') >= TRUNC(sysdate);

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  7. #7
    Join Date
    Aug 2005
    Posts
    16
    Thank you very much for both help! It worked!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •