Re: Can't create VIEW using Dynamic SQL from SP. Why ???
Date: 1997/12/10
Message-ID: <348f013a.13059949_at_inet16>#1/1
roles are never enabled in stored procedures. You cannot create a stored object that relies on priveleges from a role. You probably have the "create view" grant from a role such as RESOURCE or DBA.
Try this in plus:
SQL> set role none;
SQL> create view foo as select * from dual;
If that fails with 1031, you have create view from a role. You must have create view granted directly to you...
On 10 Dec 1997 18:00:52 GMT, "Sergei Didur" <sergei_didur_at_yahoo.com> wrote:
>Hi Everybody !
>
>I wrote SP, that shoul create a view using DBMS_SQL dynamic SQL.
>When I run it Oracle says: ORA-01031: insufficient privileges, but
>I CAN create THIS view directly from SQL Plus.
>I CAN also create table with this SP (just change CREATE VIEW to CREATE
>TABLE).
>It is really strange.
>
>Does somebody know what does it mean ???
>
>/************************************************/
>/* Create view for msg_log table */
>/************************************************/
>CREATE OR REPLACE PROCEDURE create_view (
> table_ID IN INTEGER)
>
>AS
>
> ret_code INTEGER;
> cur_code INTEGER;
> table_date DATE;
> view_sql VARCHAR2(255);
>
>BEGIN
>
>/* Read table date */
> SELECT logDate INTO table_date
> FROM msg_log_info
> WHERE logID = table_ID;
>
>/* Build view statement */
> view_sql := 'CREATE VIEW MSG_LOG_' || TO_CHAR(table_date, 'yyyymmdd');
> view_sql := view_sql || ' AS SELECT * FROM MSG_LOG_' ||
>TO_CHAR(table_ID);
>
> DBMS_OUTPUT.PUT_LINE(view_sql);
>
>/* Create view */
> cur_code := DBMS_SQL.OPEN_CURSOR;
> DBMS_SQL.PARSE(cur_code, view_sql, DBMS_SQL.V7);
> ret_code := DBMS_SQL.EXECUTE(cur_code);
> DBMS_SQL.CLOSE_CURSOR(cur_code);
>
>EXCEPTION
> WHEN OTHERS THEN
> IF DBMS_SQL.IS_OPEN(cur_code) THEN
> DBMS_SQL.CLOSE_CURSOR(cur_code);
> END IF;
>
>END create_view;
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
Opinions are mine and do not necessarily reflect those of Oracle Corporation
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Dec 10 1997 - 00:00:00 CET