1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Transfer the data from Oracle Forms to MS Word.

Discussion in 'Oracle Forms and Reports' started by s_omer78, Mar 10, 2011.

  1. s_omer78

    s_omer78 Guest

    Dynamic Data Exchange (DDE) is a function in MS Windows that allows
    applications to communicate and exchange Data. DDE client support is
    provided in Oracle Forms as a procedural option, that can be accessed
    through PL/SQL procedures and triggers.

    The following is a sample trigger for using DDE to an MS Word Template.

    /* DECLARE VARIABLES */
    DECLARE
    APPID PLS_INTEGER;
    CONVID PLS_INTEGER;
    DOCID PLS_INTEGER;
    SYS_DATE CHAR(8);
    EMP_NAME CHAR(40);

    /* BEGIN TRIGGER DDE SAMPLE */
    BEGIN
    /* SET DATE */
    SELECT TO_CHAR(SYSDATE,'MM/DD/YY') INTO SYS_DATE FROM DUAL;


    /* SET EMPLOYEE_NAME */
    -- EMP_NAME := 'TESTING 1 2 3 4';
    SELECT ENAME INTO EMP_NAME FROM EMP where job = 'PRESIDENT';

    /* START WORD */
    APPID := DDE.APP_BEGIN('h:\WINWORD\WINWORD.EXE',
    DDE.APP_MODE_MINIMIZED);

    /* INITIATE CONVERSATION WITH WORD */
    CONVID := DDE.INITIATE('WINWORD','SYSTEM');

    /* OPEN WORD TEMPLATE */
    DDE.EXECUTE(CONVID,'[FileOpen "h:\winword\SAMPLE.DOT"]', 10000);

    /* INITIATE CONVERSATION WITH WORD TEMPLATE */
    DOCID := DDE.INITIATE('WINWORD','h:\winword\SAMPLE.DOT');

    /* BEGIN TRANSFER TO WORD */
    DDE.POKE(DOCID,'BOOKMARK1', SYS_DATE,DDE.CF_TEXT,10000);
    DDE.POKE(DOCID,'BOOKMARK2', EMP_NAME,DDE.CF_TEXT,10000);

    /* SAVE THE TEMPLATE */
    DDE.EXECUTE(DOCID,'[FileSaveAs "f:\winword\output.doc"]', 10000);

    /*END TRANSFER TO WORD */
    DDE.TERMINATE(CONVID);

    /* HANDLE EXCEPTIONS */
    EXCEPTION
    WHEN DDE.DDE_APP_FAILURE THEN
    MESSAGE('WORD FOR WINDOWS CANNOT START.');
    WHEN DDE.DDE_PARAM_ERR THEN
    MESSAGE('A NULL VALUE WAS PASSED TO DDE');
    WHEN DDE.DMLERR_NO_CONV_ESTABLISHED THEN
    MESSAGE('DDE CANNOT ESTABLISH A CONVERSATION');
    WHEN DDE.DMLERR_NOTPROCESSED THEN
    MESSAGE('A TRANSACTION FAILED');


    /* END OF TRIGGER */
    END;

    CREATING A DEFAULT FORM
    ------------------------
    1. Startup Oracle Forms Designer and create a default form.
    2. Add a button through the layout editor.
    3. Create a WHEN-BUTTON-PRESSED Trigger. add the code listed in this
    document in the trigger.
    4. Compile the trigger and generate the form.

    CREATING A MS-WORD TEMPLATE
    ---------------------------
    Once you have successfully generated the form you will need to
    create a word template.

    1. Startup MS Word and select the file menu then new.
    2. Select template and click on the OK button.
    3. Add the text and other formatting to the template.
    4. Save the new template with the name sample.dot.

    ADDING DDE LINKS
    -----------------
    Now you need to add the DDE links to Oracle Forms.

    1. Place your cursor in the sample.dot template were you want a field
    to be added.
    2. Select the edit menu and then bookmark.
    3. Create a bookmark with the name BOOKMARK1 and click on the OK
    button.
    4. Move your cursor to the next location that you want to link to
    Oracle Forms and create another bookmark with the name BOOKMARK2.
    5. Save the changes and exit MS Word.

    You are now ready to run the DDE sample form. Run the form and
    engage the trigger, by pressing on the button. It should then startup
    MS Word and transfer the data from Oracle Forms to MS Word.
     
  2. arafa

    arafa Guest

    if i have a more than one record it's give me one record only
    this my code
    Code (SQL):

    /* DECLARE VARIABLES */
    DECLARE
    APPID PLS_INTEGER;
    CONVID PLS_INTEGER;
    DOCID PLS_INTEGER;
    SYS_DATE CHAR(8);
    V_CODE    CHAR(40);
    V_ARB_NAME CHAR(250);
    J          NUMBER(5):=1          ;
    --ConvID     PLS_INTEGER           ;

    CURSOR C_ITEMS IS SELECT CODE , ARB_NAME FROM TBL_WHS_ITEMS WHERE ROWNUM < 6 ;
    /* BEGIN TRIGGER DDE SAMPLE */
    BEGIN
    /* SET DATE */
    --SELECT TO_CHAR(SYSDATE,'MM/DD/YY') INTO SYS_DATE FROM DUAL;


    /* SET EMPLOYEE_NAME */
    -- EMP_NAME := 'TESTING 1 2 3 4';
    /*SELECT CODE , ARB_NAME
    INTO V_CODE , V_ARB_NAME
    FROM TBL_WHS_ITEMS
    where CODE = '202LA20244860'; */


    /* START WORD */
    APPID := DDE.APP_BEGIN('C:\Program Files\Microsoft Office\Office14\WINWORD.EXE', DDE.APP_MODE_MINIMIZED);

    /* INITIATE CONVERSATION WITH WORD */
    CONVID := DDE.INITIATE('WINWORD','SYSTEM');

    /* OPEN WORD TEMPLATE */
    DDE.EXECUTE(CONVID,'[FileOpen "C:\Program Files\Microsoft Office\Office14\SAMPLE.DOT.docx"]', 10000);

    /* INITIATE CONVERSATION WITH WORD TEMPLATE */
    DOCID := DDE.INITIATE('WINWORD','C:\Program Files\Microsoft Office\Office14\SAMPLE.DOT.docx');

    /* BEGIN TRANSFER TO WORD */
    --DDE.POKE(DOCID,'BOOKMARK1', SYS_DATE,DDE.CF_TEXT,10000);
    --DDE.POKE(ConvID,'R'||J||'C1',R.CODE,DDE.CF_TEXT,1000);
    FOR REC IN C_ITEMS LOOP
    DDE.POKE(DOCID,'BOOKMARK1', REC.CODE,DDE.CF_TEXT,10000);
    DDE.POKE(DOCID,'BOOKMARK2', REC.ARB_NAME,DDE.CF_TEXT,10000);
    END LOOP;
    /* SAVE THE TEMPLATE */
    DDE.EXECUTE(DOCID,'[FileSaveAs "C:\Users\ahmed.arafa\Desktop\output.doc"]', 10000);

    /*END TRANSFER TO WORD */
    DDE.TERMINATE(CONVID);

    /* HANDLE EXCEPTIONS */
    EXCEPTION
    WHEN DDE.DDE_APP_FAILURE THEN
    MESSAGE('WORD FOR WINDOWS CANNOT START.');
    WHEN DDE.DDE_PARAM_ERR THEN
    MESSAGE('A NULL VALUE WAS PASSED TO DDE');
    WHEN DDE.DMLERR_NO_CONV_ESTABLISHED THEN
    MESSAGE('DDE CANNOT ESTABLISH A CONVERSATION');
    WHEN DDE.DMLERR_NOTPROCESSED THEN
    MESSAGE('A TRANSACTION FAILED');


    /* END OF TRIGGER */
    END;