Results 1 to 15 of 15
  1. #1
    Join Date
    May 2003
    Posts
    15

    Unanswered: better way to update then select

    since i can't update before i declare the cursor, is there a better way so i can first update SIR_REQUEST_SENT and then use a cursor to SELECT * and then write to the text file? (using Oracle Forms)

    if I don’t include the update…then a text file can be created. If I include the update, the column is updated but the text file isn’t created.

    i'm not sure what to do... any suggestions?

    a WHEN-BUTTON-PRESSEd trigger calls this procedure:

    PROCEDURE SEND_BY_DATE(fromdate in date, todate in date) IS

    CURSOR dateProcess IS
    SELECT *
    FROM SIR
    WHERE SIR_REQUEST_DATE BETWEEN fromdate AND todate
    AND SIR_REQUEST_SENT = 'N'


    rProcess dateProcess%ROWTYPE;

    cOut VARCHAR2(2000);
    myfile VARCHAR2(255);
    mypath VARCHAR2(255);

    N_FILE text_io.file_type;


    BEGIN


    UPDATE SIR
    SET SIR_REQUEST_SENT = 'Y'
    WHERE SIR_REQUEST_DATE BETWEEN fromdate AND todate;
    COMMIT;


    OPEN dateProcess;

    myfile := sysdate||'_'||'batch'||fromdate||'to'||todate||'_' ||rProcess.SIR_COMPANY;
    --myfile := sysdate||'_'||rProcess.SIR_COMPANY;
    mypath := 'C:\request';

    N_FILE := TEXT_IO.FOPEN(mypath||'\'||myfile||'.TXT', 'W');


    LOOP
    FETCH dateProcess INTO rProcess;
    EXIT WHEN dateProcess%NOTFOUND;

    cOut := rProcess.SIR_TRANS_NO || ';'
    || rProcess.SIR_COMPANY || ';'
    || rProcess.SIR_PROJECT || ';'
    || rProcess.SIR_APPL || ';'
    || rProcess.SIR_BUS_FUN || ';'
    || rProcess.SIR_REPORTED_BY || ';'
    || rProcess.SIR_HANDLED_BY || ';'
    || rProcess.SIR_PHASE || ';'
    || rProcess.SIR_TYPE || ';'
    || rProcess.SIR_CAUSE || ';'
    || rProcess.SIR_CLASSIFICATION || ';'
    || rProcess.SIR_DESCRIPTION || ';'
    || rProcess.SIR_REASON || ';'
    || rProcess.SIR_REMARKS || ';'
    || rProcess.SIR_STATUS || ';'
    || rProcess.SIR_REQUEST_DATE || ';'
    || rProcess.SIR_RECEIVED_DATE || ';'
    || rProcess.SIR_START_DATE || ';'
    || rProcess.SIR_CLOSE_DATE || ';'
    || rProcess.SIR_TARGET_DATE || ';'
    || rProcess.SIR_ESTIMATED_MANHRS || ';'
    || rProcess.SIR_ACTUAL_MANHRS || ';'
    || rProcess.SIR_BILLABLE_MANHRS || ';'
    || rProcess.SIR_ATTACHMENT || ';'
    || rProcess.SIR_REQUEST_SENT || ';'
    || rProcess.SIR_CRE_USERID || ';'
    || rProcess.SIR_CRE_DATE || ';'
    || rProcess.SIR_UPD_USERID || ';'
    || rProcess.SIR_UPD_DATE;

    TEXT_IO.PUT_LINE(N_FILE, cOut);

    END LOOP BeginLoop;
    TEXT_IO.FCLOSE(N_FILE);
    CLOSE dateProcess;

    EXCEPTION
    WHEN OTHERS THEN
    IF dateProcess%ISOPEN THEN
    CLOSE dateProcess;
    END IF;

    END;

  2. #2
    Join Date
    May 2003
    Location
    Dublin, Ireland
    Posts
    44

    Re: better way to update then select

    I'm no expert on Oracle Forms but have you tried to create file before commit not after. Do the update, select data and create file, commit. You are in the same transaction, so you will be able to see your newly update data.

    Hope it helps.



    Originally posted by alram
    since i can't update before i declare the cursor, is there a better way so i can first update SIR_REQUEST_SENT and then use a cursor to SELECT * and then write to the text file? (using Oracle Forms)

    if I don’t include the update…then a text file can be created. If I include the update, the column is updated but the text file isn’t created.

    i'm not sure what to do... any suggestions?

    a WHEN-BUTTON-PRESSEd trigger calls this procedure:

    PROCEDURE SEND_BY_DATE(fromdate in date, todate in date) IS

    CURSOR dateProcess IS
    SELECT *
    FROM SIR
    WHERE SIR_REQUEST_DATE BETWEEN fromdate AND todate
    AND SIR_REQUEST_SENT = 'N'


    rProcess dateProcess%ROWTYPE;

    cOut VARCHAR2(2000);
    myfile VARCHAR2(255);
    mypath VARCHAR2(255);

    N_FILE text_io.file_type;


    BEGIN


    UPDATE SIR
    SET SIR_REQUEST_SENT = 'Y'
    WHERE SIR_REQUEST_DATE BETWEEN fromdate AND todate;
    COMMIT;


    OPEN dateProcess;

    myfile := sysdate||'_'||'batch'||fromdate||'to'||todate||'_' ||rProcess.SIR_COMPANY;
    --myfile := sysdate||'_'||rProcess.SIR_COMPANY;
    mypath := 'C:\request';

    N_FILE := TEXT_IO.FOPEN(mypath||'\'||myfile||'.TXT', 'W');


    LOOP
    FETCH dateProcess INTO rProcess;
    EXIT WHEN dateProcess%NOTFOUND;

    cOut := rProcess.SIR_TRANS_NO || ';'
    || rProcess.SIR_COMPANY || ';'
    || rProcess.SIR_PROJECT || ';'
    || rProcess.SIR_APPL || ';'
    || rProcess.SIR_BUS_FUN || ';'
    || rProcess.SIR_REPORTED_BY || ';'
    || rProcess.SIR_HANDLED_BY || ';'
    || rProcess.SIR_PHASE || ';'
    || rProcess.SIR_TYPE || ';'
    || rProcess.SIR_CAUSE || ';'
    || rProcess.SIR_CLASSIFICATION || ';'
    || rProcess.SIR_DESCRIPTION || ';'
    || rProcess.SIR_REASON || ';'
    || rProcess.SIR_REMARKS || ';'
    || rProcess.SIR_STATUS || ';'
    || rProcess.SIR_REQUEST_DATE || ';'
    || rProcess.SIR_RECEIVED_DATE || ';'
    || rProcess.SIR_START_DATE || ';'
    || rProcess.SIR_CLOSE_DATE || ';'
    || rProcess.SIR_TARGET_DATE || ';'
    || rProcess.SIR_ESTIMATED_MANHRS || ';'
    || rProcess.SIR_ACTUAL_MANHRS || ';'
    || rProcess.SIR_BILLABLE_MANHRS || ';'
    || rProcess.SIR_ATTACHMENT || ';'
    || rProcess.SIR_REQUEST_SENT || ';'
    || rProcess.SIR_CRE_USERID || ';'
    || rProcess.SIR_CRE_DATE || ';'
    || rProcess.SIR_UPD_USERID || ';'
    || rProcess.SIR_UPD_DATE;

    TEXT_IO.PUT_LINE(N_FILE, cOut);

    END LOOP BeginLoop;
    TEXT_IO.FCLOSE(N_FILE);
    CLOSE dateProcess;

    EXCEPTION
    WHEN OTHERS THEN
    IF dateProcess%ISOPEN THEN
    CLOSE dateProcess;
    END IF;

    END;

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

    Re: better way to update then select

    Some alternatives:

    1) You could create the file BEFORE you do the update. The problem you have is that if someone else inserts and commits another record into the table between your opening the cursor and doing the update, then you could end up updating some records that you didn't actually put in the file. This can be overcome using "Alter session set isolation_level=serializable" before opening the cursor.

    2) You could do the update first as you do now, but in the update also set a new column e.g. batch_id to identify the records you updated. Then use this batch_id in the cursor:

    Code:
    DECLARE
      v_batch_id INTEGER;
      CURSOR dateProcess( p_batch_id INTEGER ) IS
    	SELECT * 
    	FROM SIR
    	WHERE batch_id = p_batch_id;
    BEGIN
      SELECT batch_seq.NEXTVAL INTO v_batch_id FROM DUAL;
    
      UPDATE SIR 
      SET SIR_REQUEST_SENT = 'Y',
             BATCH_ID = v_batch_id
      WHERE SIR_REQUEST_DATE BETWEEN fromdate AND todate; 
      COMMIT;
    
      FOR rec IN dateProcess( v_batch_id )
      LOOP
        -- Output record to file
        ...
      END LOOP;
      ...
    3) You could read and update them at the same time:

    Code:
    PROCEDURE ... IS
    	CURSOR dateProcess IS
    	SELECT * 
    	FROM SIR
    	WHERE SIR_REQUEST_DATE BETWEEN fromdate AND todate
    	AND SIR_REQUEST_SENT = 'N'
    	FOR UPDATE OF SIR_REQUEST_SENT;
    ...
    BEGIN
      FOR rec IN dateProcess LOOP
        -- Output record to file
        ...
        UPDATE SIR 
        SET SIR_REQUEST_SENT = 'Y'
        WHERE CURRENT OF dateProcess;
      END LOOP;
      ...
    END;

  4. #4
    Join Date
    May 2003
    Posts
    15

    thanks!

    Thank you for the replies! Very helpful!

    sir andrewst,
    I was just wondering, is there a way to update first without creating the batch i.d. column?

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

    Re: thanks!

    Originally posted by alram
    Thank you for the replies! Very helpful!

    sir andrewst,
    I was just wondering, is there a way to update first without creating the batch i.d. column?
    Yes, if you really want to you can do this:

    Code:
    declare
      cursor c is select * from t where status='N';
      r c%ROWTYPE;
    begin
      open c;
      update t set status='Y';
      loop
        fetch c into r;
        exit when c%NOTFOUND;
        dbms_output.put_line('=='||r.id);
      end loop;
      close c;
    end;
    The key point is to OPEN the cursor BEFORE doing the UPDATE.

    You should still use "Alter session set isolation_level=serializable" before opening the cursor to avoid possibility of updating a different set of records to the set selected.

  6. #6
    Join Date
    May 2003
    Posts
    15

    Smile

    sir andrewst, thanks again for your help!!

  7. #7
    Join Date
    May 2003
    Posts
    15

    Re: thanks!

    Originally posted by andrewst

    You should still use "Alter session set isolation_level=serializable" before opening the cursor to avoid possibility of updating a different set of records to the set selected.
    sir andrewst, i can update successfully, but i tried to do the alter session and Oracle Forms does not recognize the isolation_level. It only recognizes the ff:

    nls_language
    nls_territory
    nls_date_format
    nls_date_language
    nls_numeric_characters
    nls_iso_currency
    nls_iso_currency
    nls_sort
    nls_calendar

    i've been looking for other commands that i can use to ensure serializability but no such luck so far.

  8. #8
    Join Date
    May 2003
    Location
    Dublin, Ireland
    Posts
    44

    Re: thanks!

    If you put spaces arounf equal sign this command will be recognised:

    alter session set isolation_level = serializable;


    Originally posted by alram
    sir andrewst, i can update successfully, but i tried to do the alter session and Oracle Forms does not recognize the isolation_level. It only recognizes the ff:

    nls_language
    nls_territory
    nls_date_format
    nls_date_language
    nls_numeric_characters
    nls_iso_currency
    nls_iso_currency
    nls_sort
    nls_calendar

    i've been looking for other commands that i can use to ensure serializability but no such luck so far.

  9. #9
    Join Date
    May 2003
    Posts
    15

    Re: thanks!

    Originally posted by sjacek
    If you put spaces arounf equal sign this command will be recognised:

    alter session set isolation_level = serializable;

    i gave that a try too, before opening the cursor but the error that comes out during compilation is this:

    Encountered the symbol 'alter' when expecting one of the following:

    begin declare end exception exit for goto if.....(etc.)

  10. #10
    Join Date
    May 2003
    Location
    Dublin, Ireland
    Posts
    44

    Re: thanks!

    You cannot use 'alter session' directly in PL/SQL. Use native dynamic sql:

    execute immediate 'alter session set isolation_level = serializable';

    This should work.


    Originally posted by alram
    i gave that a try too, before opening the cursor but the error that comes out during compilation is this:

    Encountered the symbol 'alter' when expecting one of the following:

    begin declare end exception exit for goto if.....(etc.)

  11. #11
    Join Date
    May 2003
    Posts
    15

    Re: thanks!

    Originally posted by sjacek
    You cannot use 'alter session' directly in PL/SQL. Use native dynamic sql:

    execute immediate 'alter session set isolation_level = serializable';

    This should work.
    sir, i tried just that. but i still encountered an error:

    Encountered the symbol "IMMEDIATE" when expecting one of the ff:

    := . [ @ % ;

    The symbol ".= was inserted before "IMMEDIATE" to continue.

    what am i doing wrong?

  12. #12
    Join Date
    May 2003
    Posts
    15

    if.. else - not working?

    i have this problem: i need to check if the SIR_REQUEST_SENT flag is 'Y'. if it is, then the text/dat file should not be created anymore.

    my question is, is this statement possible and where should i put it?

    IF rProcess.SIR_REQUEST_SENT = 'N' THEN
    -- retrieve the records
    -- create the file

    ELSE
    CLOSE dateProcess;

    here is my code:
    --------------------

    PROCEDURE SEND_BY_DATE(fromdate in date, todate in date) IS

    CURSOR dateProcess IS
    SELECT *
    FROM SIR
    WHERE SIR_REQUEST_DATE BETWEEN fromdate AND todate
    AND SIR_REQUEST_SENT = 'N'
    ORDER BY SIR_TRANS_NO;

    rProcess dateProcess%ROWTYPE;

    cOut LONG;
    myfile VARCHAR2(255);
    mypath VARCHAR2(255);

    N_FILE text_io.file_type;

    sir_columns VARCHAR2(2000) := 'SIR_TRANS_NO,SIR_COMPANY,SIR_PROJECT,SIR_APPL,SIR _BUS_FUN,SIR_REPORTED_BY,
    SIR_TYPE,SIR_CLASSIFICATION,SIR_DSCRIPTION,SIR_REA SON,
    SIR_REQUEST_DATE,SIR_ATTACHMENT,SIR_REQUEST_SENT,
    SIR_CRE_USERID,SIR_CRE_DATE';


    BEGIN

    OPEN dateProcess;

    myfile := sysdate||'_'||'from'||'_'||fromdate||'_'||'to'||'_ '||todate||'_'||rProcess.SIR_COMPANY;
    mypath := 'C:\request';

    N_FILE := TEXT_IO.FOPEN(mypath||'\'||myfile||'.dat', 'W');


    LOOP

    UPDATE SIR
    SET SIR_REQUEST_SENT = 'Y'
    WHERE SIR_REQUEST_DATE BETWEEN fromdate AND todate;
    YSTEM.MESSAGE_LEVEL := 5;
    COMMIT;
    YSTEM.MESSAGE_LEVEL := 0;

    FETCH dateProcess INTO rProcess;
    EXIT WHEN dateProcess%NOTFOUND;

    cOut := rProcess.SIR_TRANS_NO || ';'
    || rProcess.SIR_COMPANY || ';'
    || rProcess.SIR_PROJECT || ';'
    || rProcess.SIR_APPL || ';'
    || rProcess.SIR_BUS_FUN || ';'
    || rProcess.SIR_REPORTED_BY || ';'
    || rProcess.SIR_TYPE || ';'
    || rProcess.SIR_CLASSIFICATION || ';'
    || rProcess.SIR_DESCRIPTION || ';'
    || rProcess.SIR_REASON || ';'
    || rProcess.SIR_REQUEST_DATE || ';'
    || rProcess.SIR_ATTACHMENT || ';'
    || rProcess.SIR_REQUEST_SENT || ';'
    || rProcess.SIR_CRE_USERID || ';'
    || rProcess.SIR_CRE_DATE || ';';

    TEXT_IO.PUT_LINE(N_FILE, cOut);

    END LOOP BeginLoop;
    TEXT_IO.FCLOSE(N_FILE);
    CLOSE dateProcess;

    -- CREATE A CONTROL FILE
    n_file := TEXT_IO.FOPEN(mypath||'\'||myfile||'.ctl','W');

    -- INSERT THE TEXT ENTRY TO THE CONTROL FILE
    TEXT_IO.PUT_LINE( N_FILE ,'LOAD DATA' );
    TEXT_IO.PUT_LINE( N_FILE ,'INFILE '||''''||myfile||'''');
    TEXT_IO.PUT_LINE( N_FILE ,'APPEND'||' INTO TABLE '||'SIR');
    TEXT_IO.PUT_LINE( N_FILE ,'FIELDS TERMINATED BY '';'' OPTIONALLY ENCLOSED BY ''"''
    TRAILING NULLCOLS
    ('||sir_columns||' )
    ');

    if text_io.is_open( n_file ) then
    TEXT_IO.FCLOSE(N_FILE);
    end if;

    EXCEPTION
    WHEN OTHERS THEN
    IF dateProcess%ISOPEN THEN
    CLOSE dateProcess;
    END IF;

    END;
    ----------------


    i have tried putting the IF statement right after i open the cursor, but even if there are records with SIR_REQUEST_SENT = 'N', the file is not created anymore even though it should. i figure that it is only checking the first record.

    any help would be appreciated! thank you in advance!
    Last edited by alram; 05-29-03 at 23:38.

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

    Re: if.. else - not working?

    I'm not following your requirement here. You say "i need to check if the SIR_REQUEST_SENT flag is 'Y'". What - on any record? On one particular record? On ALL records?

    Guessing that you probably mean: if ALL records have the flag='Y' then there is no need to create a file. In that case the logic should be:

    Code:
    FOR rProcess IN dateProcess LOOP
      IF NOT TEXT_IO.ISOPEN(n_file) THEN
        -- Open the file
        n_file := TEXT_IO.FOPEN(mypath||'\'||myfile||'.dat', 'W');
      END IF;
      -- Output record to file
      ...
    END LOOP;
    
    -- If file was opened, then complete process
    IF v_file_opened THEN
      TEXT_IO.FCLOSE(n_file);
    
      -- Update records
      UPDATE SIR 
      SET SIR_REQUEST_SENT = 'Y'
      WHERE SIR_REQUEST_DATE BETWEEN fromdate AND todate
      AND SIR_REQUEST_SENT = 'N';
    
      -- Create control file
      ...
      -- 
    END IF;

  14. #14
    Join Date
    May 2003
    Posts
    15

    Re: if.. else - not working?

    Originally posted by andrewst
    I'm not following your requirement here. You say "i need to check if the SIR_REQUEST_SENT flag is 'Y'". What - on any record? On one particular record? On ALL records?

    Guessing that you probably mean: if ALL records have the flag='Y' then there is no need to create a file. In that case the logic should be:

    sir andrewst, sorry about that..

    to make things clearer, these are my steps:

    - Input a from_date and to_date.

    - check for records that have SIR_REQUEST_SENT = 'N' that fall between the from_date and to_date. If there are records found, then update the SIR_REQUEST_SENT flag of the found records to 'Y' and create the text file.

    - If the SIR_REQUEST_SENT flag is 'Y' for all records between the 2 dates, then I should not create the text file at all.


    again, thank you for your help! i'm really appreciate it!

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

    Re: if.. else - not working?

    Originally posted by alram
    sir andrewst, sorry about that..

    to make things clearer, these are my steps:

    - Input a from_date and to_date.

    - check for records that have SIR_REQUEST_SENT = 'N' that fall between the from_date and to_date. If there are records found, then update the SIR_REQUEST_SENT flag of the found records to 'Y' and create the text file.

    - If the SIR_REQUEST_SENT flag is 'Y' for all records between the 2 dates, then I should not create the text file at all.


    again, thank you for your help! i'm really appreciate it!
    OK, in that case the logic I showed should work for you.

Posting Permissions

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