View Poll Results: help in execute immediate

Voters
0. You may not vote on this poll
  • by using execute immediate the sql stmts are not working.somehow they are working by dbms.output

    0 0%
  • is there any problem with the statement prepared

    0 0%
Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2002
    Posts
    6

    Unhappy Unanswered: execute immediate --- help

    CREATE OR REPLACE PROCEDURE BPBIE_CHECK_REJECTION
    (tname in varchar2)
    IS
    v_filehandle UTL_FILE.FILE_TYPE;
    v_filedir sys.v_$parameter.value%TYPE;
    v_filename VARCHAR2(60) := 'CHK_TO_PROCEED';
    v_stage2_batch_id stage1_load_cntl.batch_id%TYPE;
    v_stage2_count number :=0;
    v_stage1_load_cntl_cnt number :=0;
    STR VARCHAR2(1000);
    BEGIN
    --EXECUTE IMMEDIATE
    DBMS_OUTPUT.PUT_LINE('SELECT MAX(batch_id) INTO v_stage2_batch_id FROM '|| tname);
    --EXECUTE IMMEDIATE
    DBMS_OUTPUT.PUT_LINE('SELECT count(*) INTO v_stage2_count FROM '|| tname || ' where batch_id = v_stage2_batch_id');
    SELECT count(*) INTO v_stage1_load_cntl_cnt
    FROM stage1_load_cntl where batch_id = v_stage2_batch_id;
    /*SELECT value
    INTO v_filedir
    FROM sys.v_$parameter
    WHERE name='utl_file_dir'; */
    v_filedir := '/bie/bietl/log';
    IF v_stage2_count = v_stage1_load_cntl_cnt
    THEN
    v_filehandle := UTL_FILE.FOPEN(v_filedir, v_filename, 'a');
    UTL_FILE.FFLUSH(v_filehandle);
    END IF;
    UTL_FILE.FCLOSE(v_filehandle);
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    Null;
    when utl_file.invalid_path then
    dbms_output.put_line('Invalid path');
    when utl_file.invalid_mode then
    dbms_output.put_line('Invalid mode');
    when utl_file.invalid_operation then
    dbms_output.put_line('Unable to open file');
    when utl_file.invalid_filehandle then
    dbms_output.put_line('Invalid Handle');
    when utl_file.write_error then
    dbms_output.put_line('Unable to write file');
    utl_file.fclose(v_filehandle);
    when others then
    if utl_file.is_open(v_filehandle) then
    utl_file.put_line(v_filehandle,sqlcode || sqlerrm);
    utl_file.fclose(v_filehandle);
    end if;
    END BPBIE_CHECK_REJECTION;
    /

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: execute immediate --- help

    If you just replace "DBMS_OUTPUT.PUT_LINE" with "EXECUTE IMMEDIATE", your code will not work. For example, the first statement needs to be coded as:

    EXECUTE IMMEDIATE 'SELECT count(*) FROM '|| tname || ' where batch_id = :1'
    INTO v_stage2_count
    USING v_stage2_batch_id;

    Note the use of bind variables for values in the WHERE clause - you should never hard-code (concatenate) data values into dynamic SQL.

Posting Permissions

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