Re: Can't create VIEW using Dynamic SQL from SP. Why ???

From: Lando <MarkL_at_quebim.com>
Date: 1997/12/10
Message-ID: <348F0ACF.69BA814D_at_quebim.com>#1/1


More than likely you were not granted the "create view" privilige.

Sergei Didur 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;
>
> --
> Sergei Didur
Received on Wed Dec 10 1997 - 00:00:00 CET

Original text of this message