pl/sql procedure compile error PLS-00103 [message #675830] |
Mon, 22 April 2019 16:18 |
|
calgary2019
Messages: 1 Registered: April 2019
|
Junior Member |
|
|
I try to create a procedure to set tablespace from read only to read write, in parameter is datafile name. but got below compile error. what is the problem?
Error(10,30): PLS-00103: Encountered the symbol "ALTER" when expecting one of the following: * & - + ; / at mod remainder rem <an exponent (**)> and or || multiset
create or replace procedure set_tablespace_rw(p_datafile in varchar2)
is
v_sql VARCHAR2(300);
v_cursorId pls_integer default dbms_sql.open_cursor;
v_tbsName VARCHAR2(100);
v_numRows pls_integer;
BEGIN
-- get tablespace name from In datafile name
v_sql := 'select t.tablespace_name from dba_data_files f,dba_tablespaces t where f.file_name like '|| CHR(39)||'%'||p_datafile||'''||'and f.tablespace_name=t.tablespace_name and t.status='||''READ_ONLY'';
EXECUTE IMMEDIATE v_sql INTO v_tbsName ;
dbms_sql.parse(v_cursorId,'ALTER TABLESPACE '||v_tbsName||' READ WRITE', dbms_sql.native);
v_numRows := dbms_sql.execute(v_cursorId);
dbms_sql.close_cursor(v_cursorId);
EXCEPTION
WHEN no_data_found THEN
RAISE_APPLICATION_ERROR(-20040, 'No read only tablespace for ' || p_datafile);
WHEN others THEN
DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
END;
|
|
|
Re: pl/sql procedure compile error PLS-00103 [message #675832 is a reply to message #675830] |
Mon, 22 April 2019 20:48 |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
There are unmatched parenthesis in the line assigning the V_SQL content. They should be in pairs, but I have counted 13.
Which leads to question: why do you call that query dynamically? There is nothing dynamic there, it may be called statically:
select t.tablespace_name into v_tbsName
from dba_data_files f, dba_tablespaces t
where f.file_name like '%'||p_datafile
and f.tablespace_name = t.tablespace_name
and t.status = 'READ_ONLY';
If you do not have to those DBA dictionary views, just GRANT the SELECT privilege on them directly. You may find its justification in this AskTom thread: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1065832643319
|
|
|
Re: pl/sql procedure compile error PLS-00103 [message #675833 is a reply to message #675830] |
Mon, 22 April 2019 21:30 |
|
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
calgary2019 wrote on Mon, 22 April 2019 14:18I try to create a procedure to set tablespace from read only to read write, in parameter is datafile name. but got below compile error. what is the problem?
Error(10,30): PLS-00103: Encountered the symbol "ALTER" when expecting one of the following: * & - + ; / at mod remainder rem <an exponent (**)> and or || multiset
create or replace procedure set_tablespace_rw(p_datafile in varchar2)
is
v_sql VARCHAR2(300);
v_cursorId pls_integer default dbms_sql.open_cursor;
v_tbsName VARCHAR2(100);
v_numRows pls_integer;
BEGIN
-- get tablespace name from In datafile name
v_sql := 'select t.tablespace_name from dba_data_files f,dba_tablespaces t where f.file_name like '|| CHR(39)||'%'||p_datafile||'''||'and f.tablespace_name=t.tablespace_name and t.status='||''READ_ONLY'';
EXECUTE IMMEDIATE v_sql INTO v_tbsName ;
dbms_sql.parse(v_cursorId,'ALTER TABLESPACE '||v_tbsName||' READ WRITE', dbms_sql.native);
v_numRows := dbms_sql.execute(v_cursorId);
dbms_sql.close_cursor(v_cursorId);
EXCEPTION
WHEN no_data_found THEN
RAISE_APPLICATION_ERROR(-20040, 'No read only tablespace for ' || p_datafile);
WHEN others THEN
DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
END;
When there is more than 1 READ ONLY tablespace code will fail during run time since "INTO v_tbsName" only holds 1 value.
WHEN OTHERS is a bug & should be totally eliminated & never used again.
http://www.orafaq.com/wiki/WHEN_OTHERS
|
|
|