Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2002
    Posts
    162

    Cool Unanswered: Submitting job automatically through stored proc

    Hello all,

    I have this stored procedure I wrote. It basically accepts certain parameters, including the desired date for the job to run etc. without getting into details, this is my issue (i'll paste the proc first) :

    ************************************************** *******
    CREATE OR REPLACE PROCEDURE CONVERSION_JOB (v_module IN VARCHAR2, v_time IN VARCHAR2,from_ver IN NUMBER, from_var IN NUMBER, from_cyc IN NUMBER,
    to_ver IN NUMBER, to_var IN NUMBER, to_cyc IN NUMBER, p_status OUT NUMBER)

    AS

    v_sql_stmt VARCHAR2(300);
    v_final_checkup VARCHAR2(250);
    v_sql_timediff_stmt VARCHAR2(300);
    v_timediff NUMBER;
    v_count NUMBER;
    v_failure NUMBER;
    v_jobnum NUMBER;
    err_num NUMBER;
    err_msg VARCHAR2(100);

    BEGIN
    v_sql_timediff_stmt := 'SELECT ltrim(TO_NUMBER(ROUND(TO_DATE('''||v_time||''',''m m/dd/yyyy HH:MIS AM'') - TO_DATE((TO_CHAR(sysdate,''mm/dd/yyyy HH:MIS AM'')),''mm/dd/yyyy HH:MIS AM''),2))) FROM DUAL;';
    DBMS_OUTPUT.PUT_LINE(v_sql_timediff_stmt);

    EXECUTE IMMEDIATE v_sql_timediff_stmt INTO v_timediff;


    IF v_module = 'BF'
    THEN
    BEGIN
    v_sql_stmt := 'BEGIN DBMS_JOB.SUBMIT ('||v_jobnum||',sa.approve_budget('||from_ver||',' ||from_var||','||from_cyc||','||to_ver||','||to_va r||','||to_cyc||'),SYSDATE +'||v_timediff||'); END;';
    EXECUTE IMMEDIATE v_sql_stmt;
    END;
    END IF;

    /* COMMIT;
    EXCEPTION
    WHEN OTHERS
    THEN ROLLBACK; */
    END;
    ************************************************** ********

    when I run the procedure (keep in mind the proc is not finished) like so (in sqlpus)......

    set serveroutput on size 30000
    set echo on
    set feedback on
    set timing on
    VARIABLE status NUMBER

    execute conversion_job('BF','6/30/2004 9:00:00 PM',1,1,1,2,1,1,:status);

    I get this error:

    ERROR at line 1:
    ORA-00911: invalid character
    ORA-06512: at "SA.CONVERSION_JOB", line 21
    ORA-06512: at line 1


    line 21 is this line : EXECUTE IMMEDIATE v_sql_timediff_stmt INTO v_timediff;

    however I do a dbms_output.put_line on v_sql_timediff_stmt and I get this sql :

    SELECT ltrim(TO_NUMBER(ROUND(TO_DATE('6/30/2004 9:00:00 PM','mm/dd/yyyy HH:MIS
    AM') - TO_DATE((TO_CHAR(sysdate,'mm/dd/yyyy HH:MIS AM')),'mm/dd/yyyy HH:MIS
    AM'),2))) FROM DUAL;


    when I run it individually I get this result:

    LTRIM(TO_NUMBER(ROUND(TO_DATE('6/30/2004
    ----------------------------------------
    36.82

    which is exactly what I need, but when I do an execute immediate to putthat figure into the v_timediff variable it gives me the invalid character error??

    please what is the deal here???....I cant figure out what character could be invalid.

    The final objective is to use the number (36.82), add it to sysdate for the NEXT_DATE parameter for the dynamic dbms_job entry for the job.....

    please help

    'Wale

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You must remove the final ";" in the text string:

    v_sql_timediff_stmt := 'SELECT ltrim(TO_NUMBER(ROUND(TO_DATE('''||v_time||''',''m m/dd/yyyy HH:MIS AM'') - TO_DATE((TO_CHAR(sysdate,''mm/dd/yyyy HH:MIS AM'')),''mm/dd/yyyy HH:MIS AM''),2))) FROM DUAL';

    Also, you can simplify to this:

    v_sql_timediff_stmt := 'SELECT ltrim(TO_NUMBER(ROUND(TO_DATE('''||v_time||''',''m m/dd/yyyy HH:MIS AM'') - SYSDATE,2))) FROM DUAL';

    And you should be using bind variables:

    v_sql_timediff_stmt := 'SELECT ltrim(TO_NUMBER(ROUND(TO_DATE(:bvtime,''m m/dd/yyyy HH:MIS AM'') - SYSDATE,2))) FROM DUAL';

    EXECUTE IMMEDIATE v_sql_timediff_stmt INTO v_timediff USING v_time;

    But finally, having said all that, you don't need to use dynamic SQL at all :-

    v_timediff := ltrim(TO_NUMBER(ROUND(TO_DATE(v_time,'mm/dd/yyyy HH:MIS AM') - SYSDATE,2)));

  3. #3
    Join Date
    Mar 2002
    Posts
    162
    Thanks Tony. I'll try this out and let you know what the reslts are

  4. #4
    Join Date
    Mar 2002
    Posts
    162
    Thanks Tony. I've fixed my issue.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •