Dear experts,

I need to spool the results of the following extraction from PL/SQL

For each invoice no in TRANSACTION table, loop:
Txn no, customer code, date etc etc, tab-delimited
Then for each invoice no, get all invoice lines in TPRODLINE table, loop:
Txn line, product, qty, sale price etc etc, tab-delimited
end invoice lines loop
end invoice no loop

TRANSACTION table is joined to the TPRODLINE table by the transaction_id in both tables.

My questions:

1) should I do 2 cursors in the loops. 1 cursor to read the required invoice lines and pass the transaction no to the 2nd cursor to extract the transaction header infor. Then write/append this to the text file. then go back to the 2 loops? Will that work?

2) I also need help to spool this to a text file. Will UTL_FILE be able to help me? If so, anything I need to be careful about?


Any advice greatly appreciated!

Cheers,

Pete






Cheers,

Pete

1) That depends on the size of table and you indexes.
I would create on SQL statement that joins all table and give me the result that I need. Look into the explain plan before executing.

2) Here is an example for using UTL_FILE

DECLARE
fHandle UTL_FILE.FILE_TYPE;
vTextIn varchar2(25);
vTextOut varchar2(25);
BEGIN
-- Might get INVALID_PATH, INVALID_MODE, or INVALID_OPERATION
fHandle := UTL_FILE.FOPEN('c:\','utlout.txt','w');

IF UTL_FILE.IS_OPEN(fHandle) THEN
DBMS_OUTPUT.PUT_LINE('File write open');
ELSE
DBMS_OUTPUT.PUT_LINE('File write not open');
END IF;

vTextIn := 'Hello World';
vTextOut := 'World Hello';

-- Might get INVALID_FILEHANDLE, INVALID_OPERATION or WRITE_ERROR
UTL_FILE.PUT_LINE(fHandle,vTextIn);

DBMS_OUTPUT.PUT_LINE('Value write: '||vTextIn);

-- Might get INVALID_FILEHANDLE or WRITE_ERROR
UTL_FILE.FCLOSE(fHandle);

-- Might get INVALID_PATH, INVALID_MODE, or INVALID_OPERATION
fHandle := UTL_FILE.FOPEN('c:\','utlout.txt','r');

IF UTL_FILE.IS_OPEN(fHandle) THEN
DBMS_OUTPUT.PUT_LINE('File read open');
ELSE
DBMS_OUTPUT.PUT_LINE('File read not open');
END IF;

-- Might get INVALID_FILEHANDLE, INVALID_OPERATION or READ_ERROR
-- NO_DATA_FOUND or VALUE_ERROR
UTL_FILE.GET_LINE(fHandle,vTextOut);

DBMS_OUTPUT.PUT_LINE('Value read: '||vTextOut);

-- Might get INVALID_FILEHANDLE or WRITE_ERROR
UTL_FILE.FCLOSE(fHandle);

DBMS_OUTPUT.PUT_LINE('Successful Completion');
EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
RAISE_APPLICATION_ERROR(-20100,'Invalid Path');

WHEN UTL_FILE.INVALID_MODE THEN
RAISE_APPLICATION_ERROR(-20101,'Invalid Mode');

WHEN UTL_FILE.INVALID_FILEHANDLE THEN
RAISE_APPLICATION_ERROR(-20102,'Invalid Filehandle');

WHEN UTL_FILE.INVALID_OPERATION THEN
RAISE_APPLICATION_ERROR(-20103,'Invalid Operation -- May signal a file locked by the OS');

WHEN UTL_FILE.READ_ERROR THEN
RAISE_APPLICATION_ERROR(-20104,'Read Error');

WHEN UTL_FILE.WRITE_ERROR THEN
RAISE_APPLICATION_ERROR(-20105,'Write Error');

WHEN UTL_FILE.INTERNAL_ERROR THEN
RAISE_APPLICATION_ERROR(-20106,'Internal Error');

WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20107,'No Data Found');

WHEN VALUE_ERROR THEN
RAISE_APPLICATION_ERROR(-20108,'Value Error');

WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20109,'Unknown UTL_FILE Error');
END;
/

3) Set the option UTL_FILE_DIR to access other files on your OS
If you want to use more directories, separate them with a ;

Hope that helps ?

Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com

Hi Manfred,

Thank you so much for your help! An addition to the Oracle manual!

I am still a bit stuck with the SQL though!

Cheers,

Pete

Hello,

when I understand your description you have two tables
One TRANSACTION table and one TPRODLINE table.
The reference is transaction_id ...

So just join with that transaction id

SELECT t.txnno, t.customercode, t.date,
p.txnline, p.product, p.qty, p.saleprice
FROM transaction t, tprodline p
WHERE t.transaction_id = p.transaction_id

Here is the cursor process (a little bit old style )

DECLARE
CURSOR cuProcess IS
SELECT t.txnno, t.customercode, t.date,
p.txnline, p.product, p.qty, p.saleprice
FROM transaction t, tprodline p
WHERE t.transaction_id = p.transaction_id

rProcess cuProcess%ROWTYPE; -- record structure
cOut VARCHAR2(2000);

BEGIN
OPEN cuProcess;
FETCH cuProcess INTO rProcess;


-- Open your file !!!

<<BeginLoop>>
WHILE cuProcess%FOUND LOOP

FETCH cuProcess INTO rProcess;

-- spool your datas
cOut := rProcess.txnno || CHR(9) || .................;



END LOOP BeginLoop;

CLOSE cuProcess;

--- close your file


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


Hope that helps ?

Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com

Hi Manfred,

The table join is simple.

But the required file consists of at least 3 lines for each invoice which makes it difficult.

Line 1:txn no, customer, address, date etc ...
Line 2 to nth:txn lineno, product code, unit price, qty, linetot
line nth+1:invoice totamt, standard greeting message
for invoice no 12345, customer = Burgerking with 2 invoice lines for products Whopper and fries, I need to product a text file of

Header,12345,burgerking,123 high street, 14-Apr-03(carriage return)
Lines,1,whopper,2.3,1,2.3(carriage return)
lines,2,fries,1.4,2,2.8(carriage return)
footer,5.1,thank you for buying from us(carriage return)
... (next invoice header)
header,12346
.....

Once all these have been extracted successfully, I will need to update a date field in the transaction table to mark the invoice as "printed/extracted" ...

That's why I was thinking of using 2 cursors!

Any advice greatly appreciated (coz not many hair left on my head)!

Cheers,

Pete

Hello,

that is something we call in germany "Gruppenwechsel" or group changing

You can use two cursor but you don´t have to do that.

Do it in this way

DECLARE

cCompareField VARCHAR2(200);

BEGIN
cCompareField := "";

OPEN cuProcess;
FETCH cuProcess INTO rProcess;

-- Open your file !!!

<<BeginLoop>>
WHILE cuProcess%FOUND LOOP

IF cCompareField <> rProcess.taxno THEN
... do your header thing
cCompareField := rProcess.taxno;
END IF;

.. do your normal invoice stuff

FETCH cuProcess INTO rProcess;


-- spool your datas
cOut := rProcess.txnno || CHR(9) || .................;



END LOOP BeginLoop;

CLOSE cuProcess;


Hope this is clear enough otherwise please let me know ?!?!

Best regards
Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com

Hi

Thanks again for your help. Just to make sure I understand your coding:

1) the 1 cursor that you have used will select rows (including txn_id) from the transaction lines table.

2) first loop select records from transaction table based on txn_id in the cursor. then assign it to variable cOut and set cCompare to the txn_id.

3) then select transaction line data from transaction line table and append it to cOUT.

4) and repeat from (2) until cursor NOT FOUND.

5) then use cOUT with UTL_FILE ...

Is that correct? Thanks!

Happy easter!

Pete

Hello,

that´s completly correct ... if you need more groups like the id,
you can define more CompareField variable like it is shown in the example.
This is the classical way to program "group changing" ...

Best regards

Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com

Dear Manfred,

You are a life saver!!

Thank you so much!!!


cheers,

Pete

Hi

This time round I have got some question on UTL_FILE.

I used your eg. and added a loop for generating a count and writing it to a text file on my local drive using UTL.

Run the block and it just say completed successfully. But no file is generated.

I do not have Oracle client installed on my local machine. I also know the UTL_FILE_DIR parameter is set to null.

Is there anything I have not done correctly?

Thanks for any advice!

Cheers,

Pete

Hello ...

can you please post the code ?

Best regards

Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com

Hi,

here is the code:

~~~~~~~~~~~~~~
DECLARE
fHandle UTL_FILE.FILE_TYPE;
cOut varchar2(25);
cCount NUMBER :=1;
BEGIN
--
-- Open file, Might get INVALID_PATH, INVALID_MODE, or INVALID_OPERATION here
--
fHandle := UTL_FILE.FOPEN('v:\Sweden\Post Office\','postoffice_test.txt','a');
dbms_output.put_line('File opened');
--
-- Append/write cOut into file. Might get INVALID_FILEHANDLE, INVALID_OPERATION or WRITE_ERROR here
--
WHILE cCount < 11 LOOP
cOut := 'The Count is ' || cCount;
UTL_FILE.PUT_LINE(fHandle,cOut);
dbms_output.put_line('The Count is ' || cCount);
cCount := cCount + 1;
END LOOP;
--
-- Close file. Might get INVALID_FILEHANDLE or WRITE_ERROR
--
UTL_FILE.FCLOSE(fHandle);
dbms_output.put_line('File opened');
--
-- Update PO_ERR to indicate successful extraction
--
-- INSERT INTO PO_ERR VALUES(sysdate,'COmpleted');
-- COMMIT;
EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
UTL_FILE.FCLOSE(fHandle);
-- INSERT INTO PO_ERR VALUES(sysdate,'-20100 Invalid path');
-- COMMIT;
WHEN UTL_FILE.INVALID_MODE THEN
UTL_FILE.FCLOSE(fHandle);
-- INSERT INTO PO_ERR VALUES(sysdate,'-20101 Invalid Mode');
-- COMMIT;
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
UTL_FILE.FCLOSE(fHandle);
-- INSERT INTO PO_ERR VALUES(sysdate,'-20102 Invalid Filehandle');
-- COMMIT;
WHEN UTL_FILE.INVALID_OPERATION THEN
--
-- May signal a file locked by the OS'
--
UTL_FILE.FCLOSE(fHandle);
-- INSERT INTO PO_ERR VALUES(sysdate,'-20103 Invalid Operation');
-- COMMIT;
WHEN UTL_FILE.READ_ERROR THEN
UTL_FILE.FCLOSE(fHandle);
-- INSERT INTO PO_ERR VALUES(sysdate,'-20104 Read Error');
-- COMMIT;
WHEN UTL_FILE.WRITE_ERROR THEN
UTL_FILE.FCLOSE(fHandle);
-- INSERT INTO PO_ERR VALUES(sysdate,'-20105 Write Error');
-- COMMIT;
WHEN UTL_FILE.INTERNAL_ERROR THEN
UTL_FILE.FCLOSE(fHandle);
-- INSERT INTO PO_ERR VALUES(sysdate,'-20106 Internal Error');
-- COMMIT;
WHEN NO_DATA_FOUND THEN
UTL_FILE.FCLOSE(fHandle);
-- INSERT INTO PO_ERR VALUES(sysdate,'-20107 No Data Found');
-- COMMIT;
WHEN VALUE_ERROR THEN
UTL_FILE.FCLOSE(fHandle);
-- INSERT INTO PO_ERR VALUES(sysdate,'-20108 Value Error');
-- COMMIT;
WHEN OTHERS THEN
UTL_FILE.FCLOSE(fHandle);
-- INSERT INTO PO_ERR VALUES(sysdate,'-20109 Unknown UTL_FILE Error');
-- COMMIT;
END;
/

Hello,

fHandle := UTL_FILE.FOPEN('v:\Sweden\Post Office\','postoffice_test.txt','a');

1) Remeber, that the path v:\Sweden\Post must be accessable on your server (not on your client).

2) Enter the path in your init.ora file under UTL_FILE_DIR

3) I did not test this ... but could it be that you must use the open mode
'w' for write not 'a' for append ????? Cause the file did not exist ?

4) Check that Post blank Office is a valid path ... sometimes you must reference v:\Sweden\Post~1 or something like that ....

5) For the first time ... I would use only filenames and directories that are not longer 8 characters. If this work, I try the orginal names ...

Hope that helps ?

Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com

Dear Manfred,

I am getting some resistance from our DBA to set the parameter in the init.ora bec they will have to reboot the server and that has to go thru a lot of bureacracy red tape. i.e. I cant really test my invoice extraction scripts.


Thus, I was wondering if you know of any other tools that I can use easily.

Cheers,

Pete

Hello,

they have to boot the server ? I though, that is is enough to start the instance and perhaps the listener ??? anyway ...

Anyway, what you can do is to leave the directory blank. Oracle will put this into the oracle_home directory ...

What do you mean by .... any other tool that you can use ?
UTL_FILE is the right way to do this and I do not know any other tecniques to write in a file on the server.
You can create your own DLL or SO to program it by yourself, but your DBA has to do the same thing than when chaning the init.ora

My suggestion is ... try your example with leaving the dir blank and
Change the open mode into "w"
search for the file on the server

Hope that helps ?

Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com