better way to update then select

alram · #1 (**permalink**) 05-23-03, 02:22

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;

sjacek · #2 (**permalink**) 05-23-03, 04:45

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.

Quote:

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;

andrewst · #3 (**permalink**) 05-23-03, 06:40

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;

alram · #4 (**permalink**) 05-25-03, 20:48

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?

andrewst · #5 (**permalink**) 05-26-03, 09:50

Quote:

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.

alram · #6 (**permalink**) 05-26-03, 09:59

sir andrewst, thanks again for your help!!

alram · #7 (**permalink**) 05-26-03, 23:49

Quote:

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.

sjacek · #8 (**permalink**) 05-27-03, 04:17

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

alter session set isolation_level = serializable;

Quote:

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.

alram · #9 (**permalink**) 05-27-03, 04:51

Quote:

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.)

sjacek · #10 (**permalink**) 05-27-03, 05:09

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

execute immediate 'alter session set isolation_level = serializable';

This should work.

Quote:

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.)

alram · #11 (**permalink**) 05-28-03, 21:52

Quote:

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?

alram · #12 (**permalink**) 05-29-03, 23:27

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!

andrewst · #13 (**permalink**) 05-30-03, 06:44

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;

alram · #14 (**permalink**) 06-01-03, 12:50

Quote:

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!

andrewst · #15 (**permalink**) 06-02-03, 05:47

Quote:

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.