Re: possible to set OMF inside of PL/SQL?

Ryan Gaffuri wrote:
>
> I'm getting errors see below. nothing on metalink. I have a feeling
> this hasn't been added to the PL/SQL parser yet... Anyone ever get
> this to work? Its 9.2.0.3.
> I have the privileges. I'm running it as sysdba.
>
> DECLARE
> CURSOR crFilePath IS
> SELECT
> SUBSTR (file_name,1,INSTR (file_name,'/',1,LENGTH (file_name)
> - LENGTH (REPLACE (file_name,'/',NULL))) - 1) path
> FROM dba_data_files
> WHERE TABLESPACE_NAME like '%SYSTEM%';
> cSqlerr VARCHAR2(1999);
> BEGIN
>
> FOR rFilePath IN crFilePath LOOP
> EXECUTE IMMEDIATE 'ALTER SYSTEM SET
> DB_CREATE_FILE_DEST='||rFilePath.path;
> END LOOP;
> EXCEPTION
> WHEN OTHERS
> THEN cSqlerr := SQLERRM;
> DBMS_OUTPUT.PUT_LINE ('Failed To SET DB_CREATE_FILE_DEST
> '||cSqlerr);
> END;
> /
>
> Failed To SET DB_CREATE_FILE_DEST ORA-02065: illegal option for ALTER
> SYSTEM
Don't you need quotes?

exec immediate 'alter system set db... = '''||path||'''';

-- 
-------------------------------
Connor McDonald
http://www.oracledba.co.uk
Co-Author: "Mastering Oracle PL/SQL - Practical Solutions"