Can't create VIEW using Dynamic SQL from SP. Why ???
Date: 1997/12/10
Message-ID: <01bd0594$8917d0e0$79a06080_at_sdidur-1>#1/1
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).
Does somebody know what does it mean ???
AS
BEGIN
SELECT logDate INTO table_date
/* 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;
It is really strange.
/************************************************/
/* Create view for msg_log table */
/************************************************/
CREATE OR REPLACE PROCEDURE create_view (
table_ID IN INTEGER)
ret_code INTEGER;
cur_code INTEGER;
table_date DATE;
view_sql VARCHAR2(255);
/* Read table date */
FROM msg_log_info
WHERE logID = table_ID;
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 DidurReceived on Wed Dec 10 1997 - 00:00:00 CET