Dynamic SQL within PL/SQL procedure and ORA-00905

[PAVB]

Ok... I'll teach you how to fish otherwise you are going to keep asking for a fish a couple of times a day.

First thing to do is to convert your code below...

v_sql_stmt := 'CREATE MATERIALIZED VIEW '||v_schema||'.'||mv_record.mv_name|| mv_record.tabspace|| mv_record.ref_method|| mv_record.ref_key|| mv_record.sql_string;

...into something like this:

select  'CREATE MATERIALIZED VIEW '||'v_schema'||'.'||'mv_record.mv_name'||'mv_record.tabspace'||'mv_record.ref_method'||'mv_record.ref_key'||'mv_record.sql_string'
from    dual;

Please execute it and tell me if what it returns looks like the right syntax for the Create Materialized View command.