Create a materialized view using a procedure

Question

I trying to create a materialized view using a procedure, but I did something wrong. I need this because I will schedulle the job on the server. Example... My script is:

    create or replace procedure proc_teste_delago
    is
    begin
      execute immediate 'create materialized view mv_test01
    refresh force on demand
    as
    select
      campo1,
      campo2,
      (case campo3
      when 'aa' then 'AA'
      when 'xx' then 'XX'
      when 'ym' then 'YM'
      else
        'OO' as campo4
     from mv_table01_test'
    end;

The error received is: ORA-06550: linha 2, coluna 7: PLS-00905: object PROC_TESTE_DELAGO is invalid ORA-06550: linha 2, coluna 7: PL/SQL: Statement ignored

Some help will be good. Obs.: It's my first time working with Oracle.

Answer 1

It is always better to use alternate quoting mechanism , becomes easier to understand. https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements003.htm#SQLRF00218

CREATE OR REPLACE PROCEDURE proc_teste_delago
    IS
BEGIN
    EXECUTE IMMEDIATE q'{CREATE MATERIALIZED VIEW mv_test01
    REFRESH
        FORCE
        ON DEMAND
AS
    SELECT
        campo1,
        campo2,
        (
            CASE campo3
                WHEN 'aa'   THEN 'AA'
                WHEN 'xx'   THEN 'XX'
                WHEN 'ym'   THEN 'YM'
                ELSE 'OO'
            END
        ) AS campo4
    FROM
        mv_table01_test}';
END;
/

Procedure PROC_TESTE_DELAGO compiled

Also, you could simplify your case expression as

CASE
    WHEN campo3 IN ('aa','xx','ym') THEN upper(campo3)
    ELSE '00'
END

Answer 2

• Replace all single quotes(') with doubled quotes(''), since they ruin the outermost quotes.
  • Alternatively, surround the ddl with extended quotes, e.g. q'[ create materialized view ... end; ]' (this way you don't need to change all the quotes)

• Do not forget to add semi-colon at the end of this great string prepared for execute immediate :

• Do not alias(as campo4) the literal '00'

• End the case statement, and alias it (end) campo3)

  create or replace procedure proc_teste_delago
  is
  begin
    execute immediate 'create materialized view mv_test01
    refresh force on demand
    as
    select
    campo1,
    campo2,
   (case campo3
    when ''aa'' then ''AA''
    when ''xx'' then ''XX''
    when ''ym'' then ''YM''
    else
    ''OO'' 
    end) campo3
    from mv_table01_test';
  end;