Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2004
    Posts
    22

    Unhappy Unanswered: UTL_FILE in FORALL clause

    Hello there!

    I have this procedure which is givin me the same errors for last 2 hours.
    Please go through it and let me know how would i fix this error ?

    Thanks much.


    CREATE OR REPLACE PROCEDURE SYNC_PROD_HIERARCHY_TESTING2 AS

    /* This procedure declares PL/SQL tables and it also uses dynamic cursor
    REF CURSOR
    */

    v_utlfile UTL_FILE.FILE_TYPE;
    err_num PLS_INTEGER;
    err_msg VARCHAR2(100);

    TYPE prd_hr_dim_curtyp IS REF CURSOR;
    prd_hr_dim_cv prd_hr_dim_curtyp;

    TYPE department_number_typ IS TABLE OF VARCHAR2(15);
    department_number_tab department_number_typ;

    TYPE department_description_typ IS TABLE OF VARCHAR2(50);
    department_description_tab department_description_typ;

    TYPE date_added_typ IS TABLE OF DATE;
    date_added_tab date_added_typ;

    TYPE date_last_modified_typ IS TABLE OF DATE;
    date_last_modified_tab date_last_modified_typ;

    TYPE status_code_typ IS TABLE OF VARCHAR2(1);
    status_code_tab status_code_typ;

    BEGIN
    -- Department
    OPEN prd_hr_dim_cv FOR
    SELECT DISTINCT department_number, department_description, date_added, date_last_modified, status_code
    FROM ddw.product_hierarchy_dim@ddw2u_link
    WHERE date_added = SYSDATE OR date_last_modified = SYSDATE;

    LOOP
    FETCH prd_hr_dim_cv BULK COLLECT INTO
    department_number_tab, department_description_tab, date_added_tab, date_last_modified_tab, status_code_tab LIMIT 75;

    FORALL i IN 1 .. department_number_tab.COUNT
    IF date_added_tab(i) = sysdate THEN
    UTL_FILE.put_line( v_utlfile, 'DP' || 'A'
    || department_number_tab(i))
    || department_description_tab(i)
    IF date_last_modified_tab(i) = sysdate THEN
    UTL_FILE.put_line( v_utlfile, 'DP' || 'R'
    || department_number_tab(i)
    || department_description_tab(i)
    END IF;

    EXIT WHEN prd_hr_dim_cv%NOTFOUND;

    END LOOP;

    CLOSE prd_hr_dim_cv;

    UTL_FILE.FCLOSE(v_utlfile);

    EXCEPTION

    WHEN OTHERS THEN
    err_num := SQLCODE;
    err_msg := SUBSTR(SQLERRM, 1, 100);
    DBMS_OUTPUT.put_line('ERRNUM= ' || err_num || ', ERRMSG= '|| err_msg );

    END SYNC_PROD_HIERARCHY_TESTING2;

    -------------------------------------
    *****************************

    ERRORS

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    32/3 PLS-00103: Encountered the symbol "IF" when expecting one of the
    following:
    . ( * @ % & - + / at mod rem select update <an exponent (**)>
    delete insert ||

    39/4 PLS-00103: Encountered the symbol "IF" when expecting one of the
    following:
    . ( * @ % & - + / at mod rem select update <an exponent (**)>
    delete insert ||

    50/2 PLS-00103: Encountered the symbol "CLOSE" when expecting one of
    the following:
    begin function package pragma procedure form
    The symbol "begin was inserted before "CLOSE" to continue.

    62/0 PLS-00103: Encountered the symbol "end-of-file" when expecting
    one of the following:
    begin function package pragma procedure form

  2. #2
    Join Date
    Apr 2004
    Posts
    22

    Revised Code... Help Needed !!!

    1 CREATE OR REPLACE PROCEDURE SYNC_PROD_HIERARCHY_TESTING2 AS
    2 /* This procedure declares PL/SQL tables and it also uses dynamic cursor
    3 REF CURSOR
    4 */
    5 v_utlfile UTL_FILE.FILE_TYPE;
    6 cnt PLS_INTEGER;
    7 err_num PLS_INTEGER;
    8 err_msg VARCHAR2(100);
    9 TYPE prd_hr_dim_curtyp IS REF CURSOR;
    10 prd_hr_dim_cv prd_hr_dim_curtyp;
    11 TYPE department_number_typ IS TABLE OF VARCHAR2(15);
    12 department_number_tab department_number_typ;
    13 TYPE department_description_typ IS TABLE OF VARCHAR2(50);
    14 department_description_tab department_description_typ;
    15 TYPE date_added_typ IS TABLE OF DATE;
    16 date_added_tab date_added_typ;
    17 TYPE date_last_modified_typ IS TABLE OF DATE;
    18 date_last_modified_tab date_last_modified_typ;
    19 TYPE status_code_typ IS TABLE OF VARCHAR2(1);
    20 status_code_tab status_code_typ;
    21 BEGIN
    22 cnt := 1;
    23 v_utlfile := UTL_FILE.FOPEN('/dsg/mkt/crm/dat', 'MI_USFF_SYNC.txt', 'W');
    24 -- Department
    25 OPEN prd_hr_dim_cv FOR
    26 SELECT DISTINCT department_number, department_description, date_added, date_last_modified, sta
    27 FROM ddw.product_hierarchy_dim@ddw2u_link
    28 WHERE date_added = SYSDATE OR date_last_modified = SYSDATE;
    29 LOOP
    30 FETCH prd_hr_dim_cv BULK COLLECT INTO
    31 department_number_tab, department_description_tab, date_added_tab, date_last_modified_tab, st
    32 EXIT WHEN prd_hr_dim_cv%NOTFOUND;
    33 FORALL i IN 1 .. department_number_tab.COUNT
    34 IF date_added_tab(i) = sysdate THEN
    35 UTL_FILE.put_line( v_utlfile, 'DP' || 'A'
    36 || LPAD(TRIM(' ' FROM department_number_tab(i)),3,0)
    37 || department_description_tab(i)
    38 || RPAD( department_description_tab(i), 12, ' ' ) );
    39 ELSIF date_last_modified_tab(i) = sysdate THEN
    40 UTL_FILE.put_line( v_utlfile, 'DP' || 'R'
    41 || LPAD(TRIM(' ' FROM department_number_tab(i)),3,0)
    42 || department_description_tab(i)
    43 || RPAD( department_description_tab(i), 12, ' ' ) );
    44 END IF;
    45 END LOOP;
    46 CLOSE prd_hr_dim_cv;
    47 UTL_FILE.FCLOSE(v_utlfile);
    48 EXCEPTION
    49 WHEN OTHERS THEN
    50 err_num := SQLCODE;
    51 err_msg := SUBSTR(SQLERRM, 1, 100);
    52 DBMS_OUTPUT.put_line('ERRNUM= ' || err_num || ', ERRMSG= '|| err_msg );
    53* END;
    SQL> /

    Warning: Procedure created with compilation errors.

    SQL> SHOW ERRORS
    Errors for PROCEDURE SYNC_PROD_HIERARCHY_TESTING2:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    34/3 PLS-00103: Encountered the symbol "IF" when expecting one of the
    following:
    . ( * @ % & - + / at mod rem select update <an exponent (**)>
    delete insert ||

    46/2 PLS-00103: Encountered the symbol "CLOSE" when expecting one of
    the following:
    begin function package pragma procedure form
    The symbol "begin was inserted before "CLOSE" to continue.

    54/0 PLS-00103: Encountered the symbol "end-of-file" when expecting

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    one of the following:
    begin function package pragma procedure form

  3. #3
    Join Date
    Apr 2004
    Location
    USA
    Posts
    33
    Unfortunately, I could not run the procedure and see it for myself, but try if the following works:

    FORALL i IN 1 .. department_number_tab.COUNT
    BEGIN
    IF
    ELSEIF
    ENDIF;
    END;

  4. #4
    Join Date
    Apr 2004
    Posts
    22

    Unhappy Could Not Fix The Error

    IT DID NOT WORK ...STILL THE SAME ERROR !!!

    PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following:

    . ( * @ % & - + / at mod rem select update <an exponent (**)>

    PLS-00103: Encountered the symbol "END" when expecting one of the following:

    begin function package pragma procedure form

    ---------------------

    PLEASE HELP !!!

  5. #5
    Join Date
    Apr 2004
    Posts
    246
    The FORALL only works on DML (insert, update, delete). You cannot use PL/SQL in a FORALL. So, the answer is: don't do that.

  6. #6
    Join Date
    May 2004
    Location
    Redwood Shores, CA
    Posts
    68
    Quote Originally Posted by shoblock
    The FORALL only works on DML (insert, update, delete). You cannot use PL/SQL in a FORALL. So, the answer is: don't do that.
    Hah, shoblock hits the bullseye as allways.

    Change line 33
    FORALL i IN 1 .. department_number_tab.COUNT
    to

    FOR i IN 1 .. department_number_tab.COUNT LOOP

    and it should work unless you have another unknown clever trick hidden in there.
    My way or the highway. Yeah

  7. #7
    Join Date
    May 2004
    Location
    Redwood Shores, CA
    Posts
    68
    PHP Code:
    11 TYPE department_number_typ IS TABLE OF VARCHAR2(15);
    12 department_number_tab department_number_typ;
    13 TYPE department_description_typ IS TABLE OF VARCHAR2(50);
    14 department_description_tab department_description_typ;
    15 TYPE date_added_typ IS TABLE OF DATE;
    16 date_added_tab date_added_typ;
    17 TYPE date_last_modified_typ IS TABLE OF DATE;
    18 date_last_modified_tab date_last_modified_typ;
    19 TYPE status_code_typ IS TABLE OF VARCHAR2(1);
    20 status_code_tab status_code_typ
    You don't need to declare so many types.
    See below.

    11 TYPE varchar50_typ IS TABLE OF VARCHAR2(50);
    15 TYPE date_typ IS TABLE OF DATE;
    12 department_number_tab varchar50_typ;
    14 department_description_tab varchar50_typ;
    16 date_added_tab date_typ;
    18 date_last_modified_tab date_typ;
    20 status_code_tab varchar50_typ;
    My way or the highway. Yeah

Posting Permissions

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