Oracle materialized view tuning

Question

I am trying to perform tuning of materialized views in my application. I set out to execute:

 dbms_advisor.tune_mview()

procedure, and wrote the following code into an SQL_Worksheet of Oracle SQL Developer:

variable mvtask varchar2(100); 
variable mvddl varchar2(4000);

execute :mvtask := 'MV_FOO_BAR';
execute select query into :mvddl from user_mviews where view_name = 'MV_FOO_BAR';

execute dbms_advisor.tune_mview(:mvtask, :mvddl);

select * from user_tune_mview;

Obviously, materialized view is already created (with refresh fast on commmit, which, however, runs unacceptably slow). Advisor reports a following error:

Error report:
ORA-13600: error encountered in Advisor
QSM-03112: Invalid CREATE MATERIALIZED VIEW statement
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.PRVT_ACCESS_ADVISOR", line 202
ORA-06512: at "SYS.PRVT_TUNE_MVIEW", line 1026
ORA-06512: at "SYS.DBMS_ADVISOR", line 754
ORA-06512: at line 1
13600. 00000 -  "error encountered in Advisor\n%s"
*Cause:    An error occurred in the Advisor.
           This message will be followed by a second message giving
           more details about the nature of the error.
*Action:   See the Advisor documentation
           for an explanation of the second error message.

But, there is nothing more reported, no trace of the second error message. Does anyone know what's the issue here? I'm all the more surprised since the MV already exists and works in the database, albeit slowly.

On a related note, does anyone know how to enter a multi-line string in SQL Developer. When I try something like:

execute :mvddl :='create materialized view MV_FOO_BAR
build immediate
refresh fast on commit
...
';

SQL Developer chokes on the first line. String concatenation crossed my mind, but I have several MVs of 100-150 lines and I would hate doing it manually for each one of them.

Answer 1

(1) When the text for error 13600 refers to the "second error message", it means the next error in the stack -- in this case QSM-03112.

(2) The reason for the error, I believe, is that the second parameter in the call to TUNE_MVIEW is meant to be the full text of a CREATE MATERIALIZED VIEW statement, but you are passing just the query text.

(3) For the multi-line issue, I expect it will work if you use an explicit PL/SQL anonymous block instead of "execute", e.g.:

BEGIN
  :mvddl := 'create materialized view MV_FOO_BAR
             build immediate
             refresh fast on commit
             ...
            ';
END;
/