PL SQL procedure to print all materialized views giving error

Question

I want to create a simple procedure that lists all the materialized views from the system and prints the status of those views in a tabular format.

When I execute the below procedure, it gives me error:

PLS-00103: Encountered the symbol "CREATE" when expecting one of the following: ( begin case declare end exception exit for goto if loop mod null pragma raise return select update while with << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge

Code:

create or replace PROCEDURE mview_status(

status OUT VARCHAR2,

view_name OUT VARCHAR2

)

AS BEGIN

-- Create a temporary table to hold the results          

CREATE TABLE mview_bkp

(

    view_name VARCHAR(255),

    status VARCHAR(30)

);     -- Insert data into the temporary table by querying the all_mviews view

INSERT INTO mview_bkp

SELECT

    mview_name,

    compile_state

FROM sys.all_mviews;     -- Print the results in a table format

SELECT

    view_name,

    IF compile_state = 'VALID' THEN status := 'Valid';

    ELSE status := 'Invalid';

    END IF;

FROM mview_bkp;     -- Drop the temporary table

DROP TABLE mview_bkp; END;

Any help on how the above error can be resolved? TIA

Answer 1

This would at least be valid syntax. The output may not be pretty (and I'm assuming no MV with a name longer than 30 characters-- if you have extended identifiers enabled that may not be a valid assumption). If this is something you expect to run interactively via SQL*Plus or some other IDE, this may be sufficient assuming you enable dbms_output. But it isn't appropriate if you are trying to return information to a client application.

BEGIN
  FOR mv IN (SELECT mview_name,
                    compile_state
               FROM sys.all_mviews)
  LOOP
    dbms_output.put_line( rpad(mv.mview_name,30,' ') || ' ' || 
                          (case mv.compile_state
                                when 'VALID' then 'Valid'
                                else 'Invalid' 
                            end) );
  END LOOP;
END;