Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: dynamic SQL- create statement in stored procedure: insuf. priv.

Re: dynamic SQL- create statement in stored procedure: insuf. priv.

From: <jeanch_at_my-deja.com>
Date: 2000/05/24
Message-ID: <8ggqgu$dl2$1@nnrp1.deja.com>#1/1

In article <9gPW4.903$K4.645_at_telenews.teleline.es>,   "Manfred Tischendorf" <tischendorf_at_arkusa.de> wrote:
> My problem is the following message that I get executing the
 procedure test:
>
> SQLWKS> show errors;
> Keine Fehler bei PROCEDURE TEST
> SQLWKS>
> SQLWKS> execute o2o_test;
> ORA-01031: insufficient privileges
> ORA-06512: at "SYS.DBMS_SYS_SQL", line 491
> ORA-06512: at "SYS.DBMS_SQL", line 32
> ORA-06512: at "BAANNT.O2O_TEST", line 19
> ORA-06512: at line 2
>
> the source code is ...
>
> create or replace procedure test Is
>
> v_date char(9);
> v_name char(30);
> v_cursor number;
> v_statement varchar(2000);
> v_numrows integer;
>
> begin
>
> v_date := '_'||to_char(sysdate,'YYYYMMDD');
> v_name := 'MISC'||v_date;
> v_cursor := DBMS_SQL.OPEN_CURSOR;
> v_statement := 'create table ' || v_name|| ' as select * from TABLE1
> where...';
>
> DBMS_SQL.PARSE(v_Cursor, v_statement, DBMS_SQL.NATIVE);
> v_NumRows := DBMS_SQL.EXECUTE(v_Cursor);
> DBMS_SQL.CLOSE_CURSOR(v_Cursor);
> commit;
>
> end;
> /
>
> Any idea which privileges I have to set? I am using oracle 8 (NT)
where the
GRANT CREATE TABLE TO fred;
GRANT SELECT TO fred;

DO A SELECT * FROM DBA_SYS_PRIVS TO SEE WHAT PRIVS HAS BEEN GRANTED
> destination platform is Oracle 7.3.4 (NT).
> The user has the folowing rights:
> - connect, dba, exp_full_database, imp_full_database, resource
>
> Thanks for any help (if you want to answer fast, please use
 (additional) my
> email: tischendorf_at_arkusa.de)
>
> Manfred Tischendorf
>
> ---------------------------------------
> /\
> / a\ Manfred Tischendorf
> / s \
> / u \ Tunnelstrasse 14
> / k \ D 70469 Stuttgart-Feuerbach
> / r \ Deutschland / Germany
> \a /
> \ / Tel.: +49 711 81781-56
> \ / Fax: +49 711 81781-51
> \____/ E-Mail: tischendorf_at_arkusa.de
> \ / WWW: http://www.arkusa.de/
> \/
> -----------------------------
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed May 24 2000 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US