Bailey,
I can create a view. I don't know why it not allow me to create view using DBMS_SQL within Procedure. Can you give me one example system privilege granted directly to them to create a view? Thanks Truong
SQL> create or replace view mytest
2 as
3 select * from tbl_file_definitions;
View created.
SQL> select count(*) from mytest;
COUNT(*)
----------
641
From: [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: Re: create view using DBMS.SQL Date: Thu, 02 Jan 2003 08:34:56 -0800Donate: Does the owner of the procedure have system privilege granted directly to them to create a view? Not with a role , but with the system privilege granted directly to the procedure owner. Remember, roles are disabled inside a stored procedure. RWB "Donate Clothes" <[EMAIL PROTECTED]>@fatcity.com on 01/02/2003 09:30:13 AM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Dear All, I can CREATE and EXECUTE others procedures however can not execute procedure with DBMS_SQL. I'm created procedure has parameters to create a view using DBMS_SQL. Oracle gave me an error insufficent privileges. Can give some hints how make it works or any example. TIA Truong. SQL> exec proc_refresh_view('010-03'); BEGIN proc_refresh_view('010-03'); END; * ERROR at line 1: ORA-01031: insufficient privileges ORA-06512: at "SYS.DBMS_SYS_SQL", line 826 ORA-06512: at "SYS.DBMS_SQL", line 32 ORA-06512: at "AISDB.PROC_REFRESH_VIEW", line 17 ORA-06512: at line 1 CREATE OR REPLACE PROCEDURE PROC_REFRESH_VIEW(pfileno IN VARCHAR2 DEFAULT NULL, pfilename IN VARCHAR2 DEFAULT NULL, pfileyear IN NUMBER DEFAULT NULL, precordtype IN VARCHAR2 DEFAULT NULL) IS cursor_name INTEGER; cursor_id INTEGER; BEGIN cursor_name := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(cursor_name,'CREATE OR REPLACE VIEW V_FILTER AS ' ||'SELECT * FROM TBL_FILE_DEFINITIONS ' ||'WHERE FILE_NUMBER_TX = '||''''||NVL(pfileno,'DUMMY')||'''' ||' OR FILE_NAME_TX = '||''''||NVL(pfilename,'DUMMY')||'''' ||' OR FILE_YEAR_NR = '||NVL(pfileyear,1800) ||' OR REC_TYPE_TX = '||''''||NVL(precordtype,'DUMMY') ||'''',DBMS_SQL.NATIVE); cursor_id := DBMS_SQL.EXECUTE(cursor_name); DBMS_SQL.CLOSE_CURSOR(cursor_name); END; / SHOW ERROR SQL>Procedure created. SQL>No errors. SQL> exec proc_refresh_view('010-03'); BEGIN proc_refresh_view('010-03'); END; * ERROR at line 1: ORA-01031: insufficient privileges ORA-06512: at "SYS.DBMS_SYS_SQL", line 826 ORA-06512: at "SYS.DBMS_SQL", line 32 ORA-06512: at "AISDB.PROC_REFRESH_VIEW", line 17 ORA-06512: at line 1 _________________________________________________________________ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Donate Clothes INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
_________________________________________________________________
MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Donate Clothes
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
