PL/SQL: Altering tablespace in a function

From: Magnus Lassi <lassi_at_home.se>
Date: Tue, 26 Jan 1999 10:16:52 +0000
Message-ID: <36AD9614.A30FA401@home.se>

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).

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 Received on Tue Jan 26 1999 - 04:16:52 CST