You could use the DDE package. Dynamic Data Exchange (DDE) is a mechanism by which applications can communicate and exchange data in Windows.
In this case, you would need to use the function App_Begin. This is the syntax:
The following code can help you specifically about how to implement th DDE package.
here is an example which uses <b>scott </b>schema. and DEPT Table.
First take a tabular database block connected to DEPT Table. Take two buttons on the canvas. write the following codes in the when-button-pressed trigger
DECLARE
filename VARCHAR2(256);
ConvID PLS_INTEGER;
AppID PLS_INTEGER;
Buffer VARCHAR2(5);
I NUMBER(3):=1;
J NUMBER(5):=1;
V_NUMBER VARCHAR2(5);
LEN NUMBER(5);
DNAME VARCHAR2(14);
LOC VARCHAR2(13);
BEGIN
filename := GET_FILE_NAME(File_Filter=> ‘EXCEL Files (*.XLS)|*.XLS|’);
AppID := DDE.App_Begin(‘C:\Program Files\Microsoft Office\Office10\EXCEL.EXE’||’ ‘||filename,DDE.APP_MODE_MINIMIZED);
ConvID := DDE.Initiate(‘EXCEL.EXE’,filename);
LOOP
BEGIN
BUFFER:=NULL;
V_NUMBER:=NULL;
I:=1;
DDE.Request(ConvID,’R’||J||’C1′,Buffer,DDE.CF_TEXT,1000);
DDE.Request(ConvID,’R’||J||’C2′,dname,DDE.CF_TEXT,1000);
DDE.Request(ConvID,’R’||J||’C3′,loc,DDE.CF_TEXT,1000);
IF ASCII(BUFFER)=13 THEN
EXIT;
END IF;
LEN:=LENGTH(Buffer);
LOOP
EXIT WHEN I>LEN;
IF SUBSTR(BUFFER,I,1) IN (‘0′,’1′,’2′,’3′,’4′,’5′,’6′,’7′,’8′,’9’) THEN
V_NUMBER:=V_NUMBER||SUBSTR(BUFFER,I,1);
I:=I+1;
ELSE
I:=I+1;
END IF;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
exit;
WHEN OTHERS THEN
MESSAGE(SQLERRM);
END;
:DEPTNO:=TO_NUMBER(V_NUMBER);
:DNAME:=DNAME;
:LOC:=LOC;
J:=J+1;
NEXT_RECORD;
END LOOP;
EXCEPTION
WHEN DDE.DDE_APP_NOT_FOUND THEN
MESSAGE(‘APP NOT FOUND’);
WHEN DDE.DDE_APP_FAILURE THEN
MESSAGE(‘APP FAILED’);
WHEN DDE.DDE_INIT_FAILED THEN
MESSAGE(‘INIT FAILED’);
WHEN DDE.DMLERR_NO_CONV_ESTABLISHED THEN
MESSAGE(‘CLIENT FAILED TO ESTABLISH CONVERSATION’);
WHEN OTHERS THEN
MESSAGE(SQLERRM);
END;
This will load Excel data on the oracle form, and after that everything is same as Oracle forms’ classic performance.
Now to create an Excel file from Database In another button write the following codes.
DECLARE
CURSOR C1 IS
SELECT DEPTNO,DNAME,LOC
FROM DEPT;
filename VARCHAR2(256);
ConvID PLS_INTEGER;
AppID PLS_INTEGER;
Buffer VARCHAR2(5);
I NUMBER(3):=1;
J NUMBER(5):=1;
V_NUMBER VARCHAR2(5);
LEN NUMBER(5);
DNAME VARCHAR2(14);
LOC VARCHAR2(13);
OUT_FILE TEXT_IO.FILE_TYPE;
BEGIN
filename := GET_FILE_NAME(File_Filter=> ‘EXCEL Files (*.XLS)|*.XLS|’,DIALOG_TYPE=>2);
OUT_FILE:=TEXT_IO.FOPEN(filename,’W’);
TEXT_IO.FCLOSE(OUT_FILE);
AppID := DDE.App_Begin(‘C:\Program Files\Microsoft Office\Office10\EXCEL.EXE’||’ ‘||filename,DDE.APP_MODE_MINIMIZED);
–DDE.APP_FOCUS(APPID);
ConvID := DDE.Initiate(‘EXCEL.EXE’,filename);
FOR R IN C1
LOOP
DDE.POKE(ConvID,’R’||J||’C1′,R.DEPTNO,DDE.CF_TEXT,1000);
DDE.POKE(ConvID,’R’||J||’C2′,R.DNAME,DDE.CF_TEXT,1000);
DDE.POKE(ConvID,’R’||J||’C3′,R.LOC,DDE.CF_TEXT,1000);
J:=J+1;
END LOOP;
TEXT_IO.FCLOSE(OUT_FILE);
EXCEPTION
WHEN DDE.DDE_APP_NOT_FOUND THEN
MESSAGE(‘APP NOT FOUND’);
WHEN DDE.DDE_APP_FAILURE THEN
MESSAGE(‘APP FAILED’);
WHEN DDE.DDE_INIT_FAILED THEN
MESSAGE(‘INIT FAILED’);
WHEN DDE.DMLERR_NO_CONV_ESTABLISHED THEN
MESSAGE(‘CLIENT FAILED TO ESTABLISH CONVERSATION’);
WHEN OTHERS THEN
MESSAGE(SQLERRM);
END;
Try this if you have any problem in implementing the code please mail me at<b> mamun_iuk@yahoo.com</b>
—————————————
DDE.Request devuelve al final del valor dos carácteres no imprimibles que se pueden quitar asi:
replace(replace(buffer,chr(13),”),chr(10),”)
13 es retorno de carro
10 es salto de linea
Discuss This Question: 4  Replies