DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
  ( job       => X 
   ,what      => 'dbms_refresh.refresh(''"SCOTT"."REFRESH_5MIN"'');'
   ,next_date => to_date('21/07/2010 20:25:11','dd/mm/yyyy hh24:mi:ss')
   ,interval  => '/*5:Mins*/ sysdate + 5/(60*24)'
   ,no_parse  => FALSE
  );
  SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
COMMIT;
END;

BEGIN SYS.DBMS_JOB.SUBMIT(
      JOB=> :jobno,
      WHAT=> 'dbms_refresh.refresh(''"SCOTT"."REFRESH_5MIN"'');',
      NEXT_DATE=>TO_DATE('2010-07-21 03:17:06','YYYY-MM-DD:HH24:MI:SS'),
      INTERVAL=> '/*5:Mins*/ sysdate + 5/(60*24)',
      NO_PARSE=> TRUE);
      END; 

1 declare
2 abc varchar2(500);
3 BEGIN
4 FOR I IN (SELECT DDL_AND_MORE_FOR_DISCUSSION FROM scott.DDI_DEPLOYMENT_TRC_DET) LOOP
5    abc := I.DDL_AND_MORE_FOR_DISCUSSION;
6    EXECUTE IMMEDIATE abc USING 50;
7 END LOOP;
8 END;

ORA-06536: IN bind variable bound to an OUT position
ORA-06512: at line 6

SQL> declare
  2  abc varchar2(500);
  3  BEGIN
  4  FOR I IN (SELECT DDL_AND_MORE_FOR_DISCUSSION FROM SCOTT.DDI_DEPLOYMENT_TRC_DET) LOOP
  5      abc := I.DDL_AND_MORE_FOR_DISCUSSION;
  6      DBMS_OUTPUT.PUT_LINE(ABC);
  7      EXECUTE IMMEDIATE abc USING 50;
  8  END LOOP;
  9  END;
 10  /
BEGIN SYS.DBMS_JOB.SUBMIT(
      JOB=> :jobno,
      WHAT=>
'dbms_refresh.refresh(''"SCOTT"."REFRESH_5MIN"'');',

NEXT_DATE=>TO_DATE('2010-07-21 03:17:06','YYYY-MM-DD:HH24:MI:SS'),

INTERVAL=> '/*5:Mins*/ sysdate + 5/(60*24)',
      NO_PARSE=> TRUE);
      END;

 

declare
*
ERROR at line 1:
ORA-06536: IN bind variable bound to an OUT position
ORA-06512: at line 7

ORA-06536: IN bind variable bound to an OUT position
ORA-06512: at line 6

declare
abc varchar2(500);
V_NO NUMBER ;
BEGIN
FOR I IN (SELECT DDL_AND_MORE_FOR_DISCUSSION FROM SCOTT.DDI_DEPLOYMENT_TRC_DET) LOOP
    abc := I.DDL_AND_MORE_FOR_DISCUSSION;
    DBMS_OUTPUT.PUT_LINE(ABC);
    EXECUTE IMMEDIATE abc USING OUT V_No;
END LOOP;
END;

CREATE OR REPLACE PROCEDURE SCOTT1.P_JOB
AUTHID CURRENT_USER
AS
abc varchar2(500);
V_NO NUMBER ;
BEGIN
FOR I IN (SELECT DDL_AND_MORE_FOR_DISCUSSION FROM SCOTT.DDI_DEPLOYMENT_TRC_DET) LOOP
    abc := I.DDL_AND_MORE_FOR_DISCUSSION;
    DBMS_OUTPUT.PUT_LINE(ABC);
    EXECUTE IMMEDIATE abc USING OUT V_No;
END LOOP;
END;

CREATE TABLE SCOTT.DDI_DEPLOYMENT_TRC_DET
(
  DEPLOYMENT_ID                NUMBER(38)       NOT NULL,
  DEPLOYMENT_STEP              NUMBER(38)       NOT NULL,
  OBJ_ID                       NUMBER(38),
  ORACLE_USR                   VARCHAR2(32 BYTE) NOT NULL,
  OS_USR                       VARCHAR2(32 BYTE) NOT NULL,
  APP                          VARCHAR2(500 BYTE),
  DDL_AND_MORE_FOR_DISCUSSION  CLOB
);

Insert into SCOTT.DDI_DEPLOYMENT_TRC_DET
   (DEPLOYMENT_ID, DEPLOYMENT_STEP, OBJ_ID, ORACLE_USR, OS_USR, APP, DDL_AND_MORE_FOR_DISCUSSION)
 Values
   (1, 1, 24096, 'SCOTT', 'SCOTT', 'TOAD', ' BEGIN DBMS_JOB.SUBMIT(
      JOB=> :jobno,
      WHAT=> ''dbms_refresh.refresh(''''"SCOTT"."REFRESH_5MIN"'''');'',
      NEXT_DATE=>TO_DATE(''2010-07-21 03:17:06'',''YYYY-MM-DD:HH24:MI:SS''),
      INTERVAL=> ''/*5:Mins*/ sysdate + 5/(60*24)'',
      NO_PARSE=> TRUE);
      END;');
Insert into SCOTT.DDI_DEPLOYMENT_TRC_DET
   (DEPLOYMENT_ID, DEPLOYMENT_STEP, OBJ_ID, ORACLE_USR, OS_USR, APP, DDL_AND_MORE_FOR_DISCUSSION)
 Values
   (1, 1, 24093, 'SCOTT', 'SCOTT', 'TOAD', ' BEGIN SYS.DBMS_JOB.SUBMIT(
      JOB=> :jobno,
      WHAT=> ''dbms_refresh.refresh(''''"SCOTT1"."RMS_RM_RDMP_STG_STS_T"'''');'',
      NEXT_DATE=>TO_DATE(''4000-01-01 00:00:00'',''YYYY-MM-DD:HH24:MI:SS''),
      INTERVAL=> ''sysdate + 15/(60*24)   '',
      NO_PARSE=> TRUE);
      END;');
      
Insert into SCOTT.DDI_DEPLOYMENT_TRC_DET
   (DEPLOYMENT_ID, DEPLOYMENT_STEP, OBJ_ID, ORACLE_USR, OS_USR, APP, DDL_AND_MORE_FOR_DISCUSSION)
 Values
   (1, 1, 24094, 'SCOTT', 'SCOTT', 'TOAD', ' BEGIN SYS.DBMS_JOB.SUBMIT(
      JOB=> :jobno,
      WHAT=> ''dbms_refresh.refresh(''''"SCOTT2"."SEC_ONL_USR_T"'''');'',
      NEXT_DATE=>TO_DATE(''4000-01-01 00:00:00'',''YYYY-MM-DD:HH24:MI:SS''),
      INTERVAL=> ''sysdate + 1/(60*24) '',
      NO_PARSE=> TRUE);
      END;');
COMMIT;