| Send query result to an Excel file. [message #174571] |
Mon, 29 May 2006 15:59  |
orate
Messages: 16
Registered: September 2005
|
Junior Member |
|
|
I have a form in which the result of the query is not supposed to be shown in the screen, it is sent to an Excel file.
The following code executes an .sql file containing an sql query and puts the result in an Excel file:
in_file := TEXT_IO.FOPEN('c:\query.sql', 'w');
TEXT_IO.PUT_LINE(in_file, 'SET UNDERLINE OFF');
TEXT_IO.PUT_LINE(in_file, 'SET HEADING OFF');
TEXT_IO.PUT_LINE(in_file, 'SET PAGESIZE 10000');
TEXT_IO.PUT_LINE(in_file, 'SET LINESIZE 10000');
TEXT_IO.PUT_LINE(in_file, 'SET FEEDBACK OFF');
TEXT_IO.PUT_LINE(in_file, 'SET VERIFY OFF');
TEXT_IO.PUT_LINE(in_file, 'SET SCAN ON');
TEXT_IO.PUT_LINE(in_file, 'SPOOL c:\result.csv');
TEXT_IO.PUT_LINE(in_file, the_query);
TEXT_IO.PUT_LINE(in_file, '/');
TEXT_IO.PUT_LINE(in_file, 'SPOOL OFF');
TEXT_IO.PUT_LINE(in_file, 'EXIT');
TEXT_IO.FCLOSE(in_file);
HOST(path_of_sqlplus_executable||' '||GET_APPLICATION_PROPERTY(USERNAME)||'/'||GET_APPLICATION_PROPERTY(PASSWORD)||'@'||GET_APPLICATION_PROPERTY(CONNECT_STRING)||'@c:\q uery.sql', NO_SCREEN);
This works well with a query without parameters.
How can I do the same with a query with parameters?
(For instance, select * from emp where salary > &1 )
|
|
|
|
| Re: Send query result to an Excel file. [message #174623 is a reply to message #174571] |
Tue, 30 May 2006 02:14  |
jowahl
Messages: 82
Registered: May 2006
|
Member |
|
|
you can work with placeholders in the *.sql file, eg.
SELECT COUNT(*)
FROM my_table
WHERE id BETWEEN &1 AND &2;
in the command line calling the sql file pass the parameters, eg.
HOST(path_of_sqlplus_executable||' '||GET_APPLICATION_PROPERTY(USERNAME)||'/'||GET_APPLICATION_PROPERTY(PASSWORD)||'@'||GET_APPLICATION_PROPERTY(CONNECT_STRING)||'@c:\q uery.sql 1 1000', NO_SCREEN);
note:
in *sql-file non-numeric expressions have to be enclosed by delimters ('SAMPLE') ...
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
