escape single quote in stored proc in oracle

Question

I have a requirement where I have extact the ddl for materialized view and execute it. I have create script for a materialized view as follows :

 CREATE MATERIALIZED VIEW "XXX"."AUD_MV_DCST" ("BUCKET", "ID", "SUM")
  ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" 
  BUILD IMMEDIATE
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" 
  REFRESH FAST ON COMMIT
  USING DEFAULT LOCAL ROLLBACK SEGMENT
  USING ENFORCED CONSTRAINTS ENABLE QUERY REWRITE
  AS SELECT TRUNC(STATUS_TIMESTAMP, 'HH24') BUCKET, ID, SUM(1) 
  ..... 

I tried the below replace statement, but it does not work:

select replace(create_script, '''','''''') from dual

What is breaking this is

'HH24')

The opening and closing braces. I get the error:

ORA-00907: missing right parenthesis
ORA-06512: at "XXX.createProc", line 67
ORA-06512: at line 2

Here is the stored procedure that is executing this :

create or replace PROCEDURE procPrintOutput
IS
  -- var declarations ....

  BEGIN

    DECLARE
      -- Get the list of Materialized views.
      CURSOR LIST_OF_MatViews
      IS select *
         from user_mviews
         where MVIEW_NAME = 'TEAM_AUD_MV_NR';
      CURSOR CREATE_MatViews
      IS SELECT *
         FROM temp;
    BEGIN
      FOR Mat_View IN LIST_OF_MatViews
      LOOP
        BEGIN
          DBMS_OUTPUT.PUT_LINE('owner : ' || Mat_View.OWNER || ' - name : ' || Mat_View.MVIEW_NAME);

          -- save the create script of views to temp table.
          l_stmt := 'insert into temp SELECT SYS.DBMS_METADATA.get_ddl (''MATERIALIZED_VIEW'', '''
                    || Mat_View.MVIEW_NAME || ''') from dual';

          EXECUTE IMMEDIATE (l_stmt);
          DBMS_OUTPUT.PUT_LINE('inserted ... ');
          -- drop the view.
          --EXECUTE IMMEDIATE (drop_mat_view_logs_stmt || Mat_View.MVIEW_NAME);
          --DBMS_OUTPUT.PUT_LINE('view log dropped ... ');
        END;
      END LOOP;

      -- recreate view using the create script generated in the previous step.
      FOR MV_CREATE_SCRIPT IN CREATE_MatViews
      LOOP
        BEGIN
          DBMS_OUTPUT.PUT_LINE('SCRIPT : ' || MV_CREATE_SCRIPT.matViewLogQuery);
          replace_stmt := 'select replace(''' || MV_CREATE_SCRIPT.matViewLogQuery || ''','';'','' '') from dual';
          DBMS_OUTPUT.PUT_LINE('replace_stmt : ' || replace_stmt);

          EXECUTE IMMEDIATE (replace_stmt) INTO updated_stmt;
          DBMS_OUTPUT.PUT_LINE('Updated SCRIPT ?? ' || updated_stmt);
          --EXECUTE IMMEDIATE(updated_stmt); 
          --DBMS_OUTPUT.PUT_LINE('view recreated ... ');
        END;
      END LOOP;
    END;
  END;

Any suggestions as to how this can be worked out ??

Answer 1

I tried the following and had no issues:

SQL> select trunc(current_timestamp, 'HH24') BUCKET from dual;

BUCKET
------------------
20-NOV-15

SQL>

Try just leaving off the precision specifier and use:

SQL> select trunc(current_timestamp) BUCKET from dual;

BUCKET
------------------
20-NOV-15

SQL>

HTH