Page 1 of 2 12 LastLast
Results 1 to 15 of 21

Thread: Extracting

  1. #1
    Join Date
    Apr 2003
    Posts
    19

    Unanswered: Extracting

    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

  2. #2
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189

    Hello

    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

  3. #3
    Join Date
    Apr 2003
    Posts
    19

    Talking

    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

  4. #4
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189

    SQL thoughs ....

    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

  5. #5
    Join Date
    Apr 2003
    Posts
    19

    sQL ...

    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

  6. #6
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189

    group changing

    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

  7. #7
    Join Date
    Apr 2003
    Posts
    19

    Re: group changing

    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

  8. #8
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189

    Yes ...

    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

  9. #9
    Join Date
    Apr 2003
    Posts
    19

    Talking Re: Yes ...

    Dear Manfred,

    You are a life saver!!

    Thank you so much!!!


    cheers,

    Pete

  10. #10
    Join Date
    Apr 2003
    Posts
    19

    Re: Hello

    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

  11. #11
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189

    Post it

    Hello ...

    can you please post the code ?

    Best regards

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

  12. #12
    Join Date
    Apr 2003
    Posts
    19
    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;
    /

  13. #13
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189

    Append

    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

  14. #14
    Join Date
    Apr 2003
    Posts
    19

    Tools other than UTL_FILE

    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

  15. #15
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189

    ??

    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

Posting Permissions

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