title: escape single quote in stored proc in oracle
body: <p>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 :</p>
  
  <pre><code> 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) 
    ..... 
  </code></pre>
  
  <p>I tried the below replace statement, but it does not work:</p>
  
  <pre><code>select replace(create_script, '''','''''') from dual
  </code></pre>
  
  <p>What is breaking this is  </p>
  
  <pre><code>'HH24')
  </code></pre>
  
  <p>The opening and closing braces. I get the error:</p>
  
  <pre><code>ORA-00907: missing right parenthesis
  ORA-06512: at "XXX.createProc", line 67
  ORA-06512: at line 2
  </code></pre>
  
  <p>Here is the stored procedure that is executing this :</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>Any suggestions as to how this can be worked out ?? </p>