| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: PL/SQL: Altering tablespace in a function
A copy of this was sent to Magnus Lassi <lassi_at_home.se>
(if that email address didn't require changing)
On Tue, 26 Jan 1999 10:16:52 +0000, you wrote:
>hi!
>
>I am trying to ALTER tablespace into read write mode and into read only
>mode via scripts/functions but I am getting ORA-01031 error.
>
>First I created a very simple script like this exampe:
>
>ScriptA.sql:
>alter tablespace A read write;
>alter tablespace B read write;
>alter tablespace C read write;
>
>----------------------
>That worked like a charm. I then wanted to create a script where I also
>would do a bunch of other stuff and add error handling. I created a
>function (stored on the database) and tried calling it from a script but
>I always get an error (ORA-01031 - insufficient privileges).
>
roles are never enabled during the execution of a procedure.
Try this:
SQL> set role none;
SQL> "statement you want to test to see if it'll work in a procedure"
If you can do it in plus with no roles you can do it in a procedure. If you can't, you must have the privelege from a role and hence won't be able to do it in a procedure.
You probably have the privelege to do what you are trying to do in the procedure via a role. Grant the privelege directly to the owner of the procedure and it'll work.
grant alter tablespace to <procedure owner>;
>The first script above I ran as user SYSTEM and I also ran the other
>script below as SYSTEM and I also created the function in schema SYSTEM.
>
>the script is similar to this:
>
>scriptB.sql
>DECLARE
> v_rc INTEGER;
>BEGIN
> v_rc := f_ReadWrite;
>EXCEPTION
> when others then
> dbms_output.put_line(SQLERRM);
>END;
>
>the function is similar to this:
>
>CREATE OR REPLACE FUNCTION f_ReadWrite
>IS
> v_cursor NUMBER := DBMS_SQL.OPEN_CURSOR;
> v_rc INTEGER;
> v_text VARCHAR2(100) := 'ALTER tablespace A read write';
>BEGIN
> DBMS_SQL.PARSE(v_cursor, v_text, DBMS_SQL.V7);
> v_rc := DBMS_SQL.EXECUTE(v_cursor);
> DBMS_SQL.CLOSE_CURSOR(v_cursor);
>EXCEPTION
> bunch of stuff
>END f_ReadWrite;
>------------------------
>As I mentioned, it always gives me ORA-01031 error. I used this function
>in the same script (using keyword Function and having everything in the
>same script) and that also worked perfectly.
>
>What am I doing wrong? I would really appreciate any advice you can give
>me..
>
>TIA,
>Magnus Lassi
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
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 Tue Jan 26 1999 - 07:34:14 CST
 |
 |