Home » SQL & PL/SQL » SQL & PL/SQL » pl/sql procedure compile error PLS-00103 (11.2.0.4)
pl/sql procedure compile error PLS-00103 [message #675830] Mon, 22 April 2019 16:18 Go to next message
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 Go to previous messageGo to next message
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 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
calgary2019 wrote on Mon, 22 April 2019 14:18
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;

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
Previous Topic: optimizer_feature_enable Hint
Next Topic: Query taking 40 hours to execute
Goto Forum:
  


Current Time: Thu Mar 28 19:41:48 CDT 2024