How can I do so that the result of this query is exported to the * CSV format?
Procedure - Personal I need help
-
- Rank: DBA Sênior
- Posts: 365
- Joined: Tue, 24 May 2005 2:24 pm
- Location: Araraquara - SP
Cristiano (Tineks)
Araraquara - SP
Araraquara - SP
When you perform a query in the developer appears a grid in the bottom of the window, in this grid has the result of your select, if you click the result with the right button of the mouse appears a window, among the options it has exists the " Export ", and within Export has the option to save as CSV.
[] 's
[] 's
You have an option in PL SQL Developer: Export Query Results.
I do not know the version of yours, but here in mine is on top of the outcome of Query where there is a button series.
then you choose the CSV File option
I do not know the version of yours, but here in mine is on top of the outcome of Query where there is a button series.
then you choose the CSV File option
-
- Rank: DBA Sênior
- Posts: 365
- Joined: Tue, 24 May 2005 2:24 pm
- Location: Araraquara - SP
Cristiano (Tineks)
Araraquara - SP
Araraquara - SP
And ai mario, beleza ??
to generate a file automatically on the server you can create a job, and program it to run within the interval that you want (5 min, 1 hour, 1 day) ...
This job can call a procedure, q will be responsible for the file creation on the server .. from one sought here in the forum on UTL_File, between the parameters that the UTL_File has one that indicates What folder it will save the file ...
What was missing was to talk about the CSV itself, in case the CSV is nothing more of a single record informed in a line and This record has a delimiter, q is usually used the ";" .. this assembly is easy because it is only you concatenating your records and between them put a ";".
returning in the file saving, inside the loop of your records, after you mount your line, you should use the utl_file.put_line to record the line in your file ..
If you have any questions, just talk.
[] 's
to generate a file automatically on the server you can create a job, and program it to run within the interval that you want (5 min, 1 hour, 1 day) ...
This job can call a procedure, q will be responsible for the file creation on the server .. from one sought here in the forum on UTL_File, between the parameters that the UTL_File has one that indicates What folder it will save the file ...
What was missing was to talk about the CSV itself, in case the CSV is nothing more of a single record informed in a line and This record has a delimiter, q is usually used the ";" .. this assembly is easy because it is only you concatenating your records and between them put a ";".
returning in the file saving, inside the loop of your records, after you mount your line, you should use the utl_file.put_line to record the line in your file ..
If you have any questions, just talk.
[] 's
-
- Rank: Analista Sênior
- Posts: 157
- Joined: Fri, 30 Nov 2007 1:17 pm
- Location: Porto Alegre - RS
Is this the script can exemplify me how to save this file in a folder?
CREATE OR REPLACE PROCEDURE prc_emprestimo_gf (
prc_con_num_est OUT NUMBER,
prc_principal_pago OUT NUMBER,
prc_juros_pago OUT NUMBER,
prc_mora_paga OUT NUMBER,
prc_iof_pago OUT NUMBER,
prc_multa_paga OUT NUMBER
)
IS
v_con_num_est NUMBER (8);
v_principal_pago NUMBER (14, 4);
v_juros_pago NUMBER (14, 4);
v_mora_paga NUMBER (14, 4);
v_iof_pago NUMBER (14, 4);
v_multa_paga NUMBER (14, 4);
BEGIN
SELECT c.con_num_est, TO_DATE (t.pgt_dat, 'DD-MM-YYYY'),
SUM (t.ppcvalpriantpgt - t.ppcvalpripospgt),
SUM (t.ppcvaljurantpgt - t.ppcvaljurpospgt),
SUM (t.ppcvalmorantpgt - t.ppcvalmorpospgt),
SUM (t.ppcvaliofantpgt - t.ppcvaliofpospgt), SUM
( t.ppcvalmulantpgt
- t.ppcvalmulpospgt
)
INTO v_con_num_est, v_principal_pago,
v_juros_pago,
v_mora_paga,
v_iof_pago,
v_multa_paga
FROM emptblppc t, tb_con c, tb_pgt p
WHERE t.con_num = c.con_num
AND t.ppcdthalt = c.con_dat_alt
AND t.con_num = p.con_num
AND t.ppcdthalt = p.pgt_dat_alt
AND t.pgt_dat = p.pgt_dat
AND t.ppcdthalt = '01 jan 1960'
AND TO_CHAR (p.DATA, 'DD-MM-YYYY') = '23-11-2007'
GROUP BY c.con_num_est, TO_DATE (t.pgt_dat, 'DD-MM-YYYY');
prc_con_num_est := nvl(v_con_num_est,0);
prc_principal_pago := nvl(v_principal_pago,0);
prc_juros_pago := nvl(v_juros_pago,0);
prc_mora_paga := nvl(v_mora_paga,0);
prc_iof_pago := nvl(v_iof_pago,0);
prc_multa_paga := nvl(v_multa_paga,0);
END;
-
- Rank: DBA Sênior
- Posts: 365
- Joined: Tue, 24 May 2005 2:24 pm
- Location: Araraquara - SP
Cristiano (Tineks)
Araraquara - SP
Araraquara - SP
Friend, follow an example of what you need. If you need more examples of one searched here in the forum, you will find a lot about UTL_File.
[] 's
/* Formatted on 2007/12/07 08:14 (Formatter Plus v4.8.7) */
CREATE OR REPLACE PROCEDURE prc_emprestimo_gf (
prc_con_num_est OUT NUMBER,
prc_principal_pago OUT NUMBER,
prc_juros_pago OUT NUMBER,
prc_mora_paga OUT NUMBER,
prc_iof_pago OUT NUMBER,
prc_multa_paga OUT NUMBER
)
IS
v_con_num_est NUMBER (14, 4);
v_principal_pago NUMBER (14, 4);
v_juros_pago NUMBER (14, 4);
v_mora_paga NUMBER (14, 4);
v_iof_pago NUMBER (14, 4);
v_multa_paga NUMBER (14, 4);
v_linha VARCHAR2 (2000) := '';
v_arquivo UTL_FILE.file_type;
BEGIN
SELECT c.con_num_est, TO_DATE (t.pgt_dat, 'DD-MM-YYYY'),
SUM (t.ppcvalpriantpgt - t.ppcvalpripospgt),
SUM (t.ppcvaljurantpgt - t.ppcvaljurpospgt),
SUM (t.ppcvalmorantpgt - t.ppcvalmorpospgt),
SUM (t.ppcvaliofantpgt - t.ppcvaliofpospgt), SUM
( t.ppcvalmulantpgt
- t.ppcvalmulpospgt
)
INTO v_con_num_est, v_principal_pago,
v_juros_pago,
v_mora_paga,
v_iof_pago,
v_multa_paga
FROM emptblppc t, tb_con c, tb_pgt p
WHERE t.con_num = c.con_num
AND t.ppcdthalt = c.con_dat_alt
AND t.con_num = p.con_num
AND t.ppcdthalt = p.pgt_dat_alt
AND t.pgt_dat = p.pgt_dat
AND t.ppcdthalt = '01 jan 1960'
AND TO_CHAR (p.DATA, 'DD-MM-YYYY') = '23-11-2007'
GROUP BY c.con_num_est, TO_DATE (t.pgt_dat, 'DD-MM-YYYY');
v_arquivo := UTL_FILE.fopen ('C:\DIRETORIO\', 'ARQUIVO.TXT', 'W');
UTL_FILE.put_line (v_arquivo, 'v_con_num_est : '||NVL (v_con_num_est, 0));
UTL_FILE.put_line (v_arquivo, 'v_principal_pago : '||NVL (v_principal_pago, 0));
UTL_FILE.put_line (v_arquivo, 'v_juros_pago : '||NVL (v_juros_pago, 0));
UTL_FILE.put_line (v_arquivo, 'v_mora_paga : '||NVL (v_mora_paga, 0));
UTL_FILE.put_line (v_arquivo, 'v_iof_pago : '||NVL (v_iof_pago, 0));
UTL_FILE.put_line (v_arquivo, 'v_multa_paga : '||NVL (v_multa_paga, 0));
UTL_FILE.fclose (v_arquivo);
END;
-
- Information
-
Who is online
Users browsing this forum: Bing [Bot], Google [Bot] and 1 guest