Procedure - Personal I need help

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Post Reply
mariogus
Rank: Analista Sênior
Rank: Analista Sênior
Posts: 157
Joined: Fri, 30 Nov 2007 1:17 pm
Location: Porto Alegre - RS

How can I do so that the result of this query is exported to the * CSV format?
Tineks
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 365
Joined: Tue, 24 May 2005 2:24 pm
Location: Araraquara - SP
Cristiano (Tineks)
Araraquara - SP

Are you using which tool for consultation? SQLPLUS? PL SQL Developer, ...?

[] 's
mariogus
Rank: Analista Sênior
Rank: Analista Sênior
Posts: 157
Joined: Fri, 30 Nov 2007 1:17 pm
Location: Porto Alegre - RS

SQL Developer.
Tineks
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 365
Joined: Tue, 24 May 2005 2:24 pm
Location: Araraquara - SP
Cristiano (Tineks)
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
luizinho
Rank: Estagiário Júnior
Rank: Estagiário Júnior
Posts: 1
Joined: Fri, 23 Nov 2007 11:08 am
Location: RJ

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
mariogus
Rank: Analista Sênior
Rank: Analista Sênior
Posts: 157
Joined: Fri, 30 Nov 2007 1:17 pm
Location: Porto Alegre - RS

Okay ... I already do - but I wish you had a job that sent the file in this format for a folder / backup / cpmf ...
mariogus
Rank: Analista Sênior
Rank: Analista Sênior
Posts: 157
Joined: Fri, 30 Nov 2007 1:17 pm
Location: Porto Alegre - RS

Automatically ...
Tineks
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 365
Joined: Tue, 24 May 2005 2:24 pm
Location: Araraquara - SP
Cristiano (Tineks)
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
mariogus
Rank: Analista Sênior
Rank: Analista Sênior
Posts: 157
Joined: Fri, 30 Nov 2007 1:17 pm
Location: Porto Alegre - RS

Could you exemplify me?
mariogus
Rank: Analista Sênior
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?

Select all

 
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; 
Tineks
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 365
Joined: Tue, 24 May 2005 2:24 pm
Location: Araraquara - SP
Cristiano (Tineks)
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.

Select all

 
/* 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; 
[] 's
Post Reply
  • Information
  • Who is online

    Users browsing this forum: Bing [Bot], Google [Bot] and 1 guest