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.

Jeffrey Kemp · Accepted Answer · 2018-06-20 02:39:48Z

3

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;

edited Jun 20, 2018 at 2:39

Jeffrey Kemp

60.5k15 gold badges111 silver badges161 bronze badges

answered Jun 19, 2018 at 21:22

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Fernando Delago Over a year ago

Thanks Jeffrey for the answer. Now I try to create the procedure to make the materialized view using a join with 3 tables. At the finish I put my results.

Fernando Delago Over a year ago

Thank you people! My procedure works perfectly. I created a procedure using 3 join tables and a lot of case clause for a dashboard. I tried to execute a REFRESH on my materialized view, but is very slowly, so the create is more faster. Now is time do investigate why the REFRESH is slow. Thanks again.

Kaushik Nayak · Accepted Answer · 2018-06-20 03:44:58Z

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

Collectives™ on Stack Overflow

Create a materialized view using a procedure

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related