| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: create view using DBMS.SQL
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: Reginald.W.Bailey_at_jpmorgan.com
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: Re: create view using DBMS.SQL
>Date: Thu, 02 Jan 2003 08:34:56 -0800
>
>
>Donate:
>
>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" <truongbatgioi_at_hotmail.com>@fatcity.com on 01/02/2003
>09:30:13 AM
>
>Please respond to ORACLE-L_at_fatcity.com
>
>
>
>Sent by: root_at_fatcity.com
>
>
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>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: truongbatgioi_at_hotmail.com
>
>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: ListGuru_at_fatcity.com (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: Reginald.W.Bailey_at_jpmorgan.com
>
>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: ListGuru_at_fatcity.com (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: Donate Clothes INET: truongbatgioi_at_hotmail.com 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: ListGuru_at_fatcity.com (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).Received on Thu Jan 02 2003 - 12:55:11 CST
 |
 |