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

    Unanswered: creating a text file from the contents of the database?

    please help!!

    i'm working on a project right now using Oracle Forms 6.0 and Oracle9i.

    after i create a record and save the data in the table, how can i generate/create a text file of that particular record? i need this text file in order to run it in another computer and upload the data in the text file to another database (also Oracle).

    i will also need to create the text file for multiple records.

    can someone help me please???? i read something about SELECT INTO OUTFILE... how exactly does this work?

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

    Lightbulb use UTL_FILE package to spool records

    Hello,

    use UTL_FILE package to spool records into a file via PL/SQL.
    In AlligatorSQL you can use a template "How to spool a ...".

    See at http://www.alligatorsql.com/download/alligator116.zip

    But if you wish I can post an example again (it has been already posted in this forum)

    Hope that helps ?

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

  3. #3
    Join Date
    May 2003
    Posts
    15
    oh i see! thanks, i found the thread on extracting. will post again if i have any problems!

  4. #4
    Join Date
    May 2003
    Posts
    15

    Unhappy text_io?

    sir manfred,

    would it be possible to use TEXT_IO instead of UTL_FILE? Oracle Forms does not have the UTL_FILE package. i had a bit of difficulty following your examples (sorry!) as i am just a beginner with pl/sql.

    this is what i have to do:
    - save the information that was entered in Oracle Forms (this is finished)
    - when a button is pressed, update the REQUEST_SENT flag and create the text file (of that same form which was just saved)


    This is what i have done so far:


    /*WHEN-BUTTON-PRESSED trigger*/

    DECLARE

    CURSOR cuProcess IS
    SELECT *
    FROM SIR
    WHERE SIR_TRANS_NO = IR.SIR_TRANS_NO and SIR_COMPANY = IR.SIR_COMPANY;

    rProcess cuProcess%ROWTYPE;
    cOut VARCHAR2(2000);

    N_FILE VARCHAR2(2000);

    BEGIN

    UPDATE SIR
    SET SIR_REQUEST_SENT = 'Y'
    WHERE SIR_TRANS_NO = IR.SIR_TRANS_NO AND SIR_COMPANY = IR.SIR_COMPANY;
    COMMIT;

    OPEN cuProcess;
    FETCH cuProcess INTO rProcess;

    WHILE cuProcess%FOUND LOOP
    FETCH cuProcess INTO rProcess;

    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;
    END LOOP BeginLoop;

    CLOSE cuProcess;

    CREATE_TEXT('filename', cOut);

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

    END;


    then i have a simple procedure that creates the text file:

    PROCEDURE CREATE_TEXT (pfilename IN VARCHAR2, selected IN VARCHAR2) IS

    N_FILE text_io.file_type;

    BEGIN
    N_FILE := TEXT_IO.FOPEN(pfilename||'.TXT', 'W');
    TEXT_IO.PUT_LINE(N_FILE, selected);
    TEXT_IO.FCLOSE(N_FILE);

    END;


    my problem is that i have to press the button twice for the update to happen. is there another way that i can first update SIR_REQUEST_SENT and then use a cursor to SELECT * ?
    also, after the text file is created, how can i load it using sqlloader?

    also, how can i specify the path where the text file will be saved? the TEXT_IO.FOPEN accepts only 2 parameters, the filename and the mode unlike UTL_FILE.FOPEN

    i appreciate the help! thanks again!
    Last edited by alram; 05-22-03 at 01:37.

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

    Red face not so familiar with Forms

    Hello,

    sorry, but I am not so familiar with Oracle forms. But I know, that you can call PL/SQL routines from Forms.

    Sorry again.

    Manfred Peter
    (Alligator Company Software 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
  •