i'm working on a project right now using Oracle Forms 6.0 and Oracle 9i.
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 somehow upload the data in the text file to another database.
i will also need to create the text file for multiple records.
can someone help me please????
You may want to look at the utl_file supplied Oracle package. I don't know if you can use it from within Oracle Forms though.
Oracle supports "SELECT INTO OUTFILE ...", you can use this to export.
If the other database is Oracle 9i you can use EXTERNAL TABLES to load data from a file.
Another way could be (on Oracle) to use a dblink to the other database and update target tables directly.
Hope this helps
thanks! i'll give it a try!
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 = :SIR.SIR_TRANS_NO and SIR_COMPANY = :SIR.SIR_COMPANY;
rProcess cuProcess%ROWTYPE;
cOut VARCHAR2(2000);
N_FILE VARCHAR2(2000);
BEGIN
UPDATE SIR
SET SIR_REQUEST_SENT = 'Y'
WHERE SIR_TRANS_NO = :SIR.SIR_TRANS_NO AND SIR_COMPANY = :SIR.SIR_COMPANY;
COMMIT;
OPEN cuProcess;
FETCH cuProcess INTO rProcess;
WHILE cuProcess%FOUND LOOP
FETCH cuProcess INTO rProcess;
EXCEPTION
WHEN OTHERS THEN
IF cuProcess%ISOPEN THEN
CLOSE cuProcess;
END IF;
END;
then i have a simple procedure that creates the text file:
/*CREATE_TEXT */
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 question:
is there another way that i can first update SIR_REQUEST_SENT and then use a cursor to SELECT * and then write to the text file?
also, after the text file is created, how can i load it using sqlloader?
i appreciate any help! thanks again!
pabloj,
I am interested in learning more about the 'SELECT INTO OUTFILE' command.
I am running an SQL statement that connects to an Oracle database and would like to export the output into a dBase file.
Can this be done??
Thanks for any input.
Select into outfile is for a csv file, I'm quite shure that it won't work with dBase.
You should try something else
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 = :SIR.SIR_TRANS_NO and SIR_COMPANY = :SIR.SIR_COMPANY;
rProcess cuProcess%ROWTYPE;
cOut VARCHAR2(2000);
N_FILE VARCHAR2(2000);
BEGIN
UPDATE SIR
SET SIR_REQUEST_SENT = 'Y'
WHERE SIR_TRANS_NO = :SIR.SIR_TRANS_NO AND SIR_COMPANY = :SIR.SIR_COMPANY;
COMMIT;
OPEN cuProcess;
FETCH cuProcess INTO rProcess;
WHILE cuProcess%FOUND LOOP
FETCH cuProcess INTO rProcess;
EXCEPTION
WHEN OTHERS THEN
IF cuProcess%ISOPEN THEN
CLOSE cuProcess;
END IF;
END;
then i have a simple procedure that creates the text file:
/*CREATE_TEXT */
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 question:
is there another way that i can first update SIR_REQUEST_SENT and then use a cursor to SELECT * and then write to the text file?
also, after the text file is created, how can i load it using sqlloader?
i appreciate any help! thanks again!
when i try your code i face this problems
Compilation errors for PROCEDURE SCOTT.CREATE_TEXT
Error: PLS-00201: identifier 'TEXT_IO.FILE_TYPE' must be declared
Line: 6
Text: BEGIN
Error: PL/SQL: Item ignored
Line: 6
Text: BEGIN
Error: PLS-00320: the declaration of the type of this expression is incomplete or malformed
Line: 10
Text: TEXT_IO.PUT_LINE(N_FILE, selected);
could you help me because i need this way in my form
or if you could give me some advice
my form is about
exporting the source code of an object(package , procedure , ...) into a text file in C:\
by one click in a button