Concatenate year + month + day and use TO_DATE

[suelana]

I wonder if there is how to set up a date with 3 courses in a variable date;
Example:


This example is OK within SQL Tools, but when concatene inside a package called by a Report, it gives error :

Select all

ORA-01861: o literal não corresponde à string de formato

I discovered that the dates format is 'DD-mon-yyyy' in Report.

How to control this? I would like to set the date format at the time of the creation of W_Data so as not to have such arrangements, if you change the user profile.

[thiago_r_f]

Although it is optional, would not it be better to put the mask?

Select all

 
w_data := To_Date( p_ano||'/'||To_Char(p_mês,'00')||'/'||to_char(p_dia,'00'), 'yyyy/mm/dd' ) ; 

Check the field datatype on the table!
ABS.

[nandosistemas]

Thiago, with the mask does not work when I put in a variable. See the procedure below the first line of the WHERE.

Select all

CREATE OR REPLACE PROCEDURE sp_tempos_elevados (cv_1 IN OUT SYS_REFCURSOR,  
HIERARQUIA IN VARCHAR2,  
mês_ANO IN VARCHAR2 ,  
HORAS IN INT,  
ORDENACAO INT)  
IS  
 
SQUERY VARCHAR(30000);  
 
BEGIN  
 
SQUERY:=  
'SELECT h.cod_hierarq AS codLocal,  
Round((i.qtd_cons_ating+i.qtd_cons_rur_ating+i.qtd_cons_prim_atingi)*i.min_duracao,0) AS INDEXADOR,  
i.num_id_interrupcao,  
i.num_documento,  
(f.dat_inicio) AS dat_inicio,  
(i.dat_termino)AS dat_termino,  
Trunc(((i.min_duracao/60)/24),0) AS dia_duracao,  
Trunc(i.min_duracao/60,0) AS hor_duracao,  
Round(i.min_duracao,0) AS min_duracao,  
i.qtd_cons_rur_ating AS ConsRural,  
i.qtd_cons_prim_atingi AS ConsPri,  
i.qtd_cons_ating AS ConsUrbano  
 
FROM tinter_intertrecho f, titensinter i, thierarquia h, ttransferenciaconint t,  
 
(SELECT l.COD_LOCAL,s.COD_LOCAL AS super,r.COD_LOCAL AS reg FROM tOrdemHierarquia s  
inner join tOrdemHierarquia r ON (s.COD_LOCAL=r.NUM_ID_PAI)  
inner join tOrdemHierarquia l ON (r.COD_LOCAL=l.NUM_ID_PAI)  
WHERE s.COD_LOCAL=NVL2('|| HIERARQUIA||',(SELECT DISTINCT num_id_hierarquia FROM thierarquia WHERE cod_Hierarq=CAST(' || HIERARQUIA ||' AS INT)),s.COD_LOCAL)  
 
UNION  
 
SELECT l.COD_LOCAL,s.COD_LOCAL AS super, r.COD_LOCAL AS reg FROM tOrdemHierarquia s  
inner join tOrdemHierarquia r ON (s.COD_LOCAL=r.NUM_ID_PAI)  
inner join tOrdemHierarquia l ON (r.COD_LOCAL=l.NUM_ID_PAI)  
WHERE r.COD_LOCAL=NVL2('|| HIERARQUIA||',(SELECT DISTINCT num_id_hierarquia FROM thierarquia WHERE cod_Hierarq=CAST('|| HIERARQUIA||' AS INT)),r.COD_LOCAL)  
 
UNION  
 
SELECT l.COD_LOCAL, s.COD_LOCAL AS super, r.COD_LOCAL AS reg FROM tOrdemHierarquia s  
inner join tOrdemHierarquia r ON (s.COD_LOCAL=r.NUM_ID_PAI)  
inner join tOrdemHierarquia l ON (r.COD_LOCAL=l.NUM_ID_PAI)  
WHERE l.COD_LOCAL=NVL2('|| HIERARQUIA||',(SELECT DISTINCT num_id_hierarquia FROM thierarquia WHERE cod_Hierarq=CAST('|| HIERARQUIA||' AS INT)),l.COD_LOCAL)  
)l  
 
WHERE TO_DATE(f.dat_inicio,' || 'mm/yyyy'||') = '||mês_ANO||'  
AND (i.min_duracao-Nvl(t.min_duracao,0)) >' || HORAS || ' * 60  
AND f.num_id_interrupcao = i.num_id_interrupcao  
AND i.num_id_hierarquia = h.num_id_hierarquia  
AND h.num_id_hierarquia = l.cod_local  
AND i.num_id_interrupcao = t.num_id_interrupcao(+)  
AND i.num_id_item = t.num_id_item(+)';  
 
 
 
OPEN cv_1 FOR SQUERY;  
 
END;  
/ 

When I try to execute it, an error is returned, informing me that the 'YYYY' identifier is invalid. This identifier is what is on the first line of the WHERE.

to_date (f.dat_inicio, '||' mm / yyyy '||') = '|| mês_ano ||'


The question is: How do I concatenate the mask in this case using to_date? I'vê tried everything and I could not.

I need the query in a variable, as this query will be dynamic and pieces will be included according to the selected option by the user.

From now on, thank you.

[fsitja]

Do you need to use even a dynamic SELECT? It seems that all you want there gives to be passed by parameters in a simple cursor or by variable bind. For example:

Select all

 
SQL> set serveroutput on; 
SQL> declare 
  2    cursor c_test(p_num in number) is 
  3      with t as (select 1 num, 'AAA' as nome from dual 
  4                 union all select 2 num, 'BBB' nome from dual 
  5                 union all select 3 num, 'CCC' nome from dual) 
  6      select nome 
  7        from t 
  8       where t.num = p_num; 
  9    w_num varchar2(10); 
 10  begin 
 11    open c_test(3); 
 12    fetch c_test 
 13      into w_num; 
 14    close c_test; 
 15    dbms_output.put_line('Selected ' || w_num || '!'); 
 16  end; 
 17  / 
Selected CCC! 
 
Procedimento PL/SQL concluído com sucesso. 
 
SQL>  

You can pass direct via cursor parameter, which even makes execution faster because SQL is optimized to be reused.

If you choose to use even dynamic SQL, no problem, but change the variables inside for BIND VARIABLES, as in the example below: http://download.oracle.com/docs/cd/B193 ... sthref1135
You win Performance and also prevents that your query is exploited with SQL Injection, one of the most recurring problems of software security that exist.

The problem in To_Date is that you are placing MM / YYYY as if they were variable, and they are constant (character). Also, f.dat_inicio is not a date? If it is, your to_date should be on the other side of equality

Select all

 
f.dat_inicio = to_date('|| mês_ANO || ', ''mm/yyyy'') 

http://download.oracle.com/docs/cd/B193 ... SQLRF06132 http://download.oracle.com/docs/cd/B193 ... #sthref373

if you paint doubts, send.

Embrace. [/url]

[nandosistemas]

First of all thanks for the tips. It's good to talk to who really understands the subject.

The use of the variable bind will help a lot here. And the other links I will read as soon as possible.

I really need a dynamic SELECT. What I posted here was just a tiny consultation near the rest.

But the initial problem I still do not sound solve, which is the problem of the mask. The query of the previous post works perfectly when straight, without putting query in a string variable.

Putting the query in a variable I can not perform the procedure.

When I make the change that you proposed:

Select all

f.dat_inicio = to_date('|| mês_ANO || ', ''mm/yyyy'') 

An error message is returned: ORA-01858: Non-numeric Character WAS Found Where Numeric WAS Expected.

It is important to stress that I am running this direct proceeding from a report developed in Reporting Service. If I retreat from the consultation the mês_o field and step a constant ('01 / 2009 ', for example), the execution proceeds perfectly.

The other variables that step tb work perfectly. Just the date is giving me work.

Any suggestion of what it may be ???

I will try to run this procedure directly from SQL Plus to see if it runs.


Thanks again.

[nandosistemas]

The problem I found. As I said, I'm using the Reporting Service to call the procedure. In it, aau step the date as string for procedure. However, when I execute the procedure, this date is being treated as a number.

To be clearer:

I passed the date '02 / 2009 'in the mês_ano parameter. I put this parameter in my select to check, and it returned the value 0.0009955. Did you realize? It's exactly division of 2 per 2009. That is, it's treating my string as a number. Now I'm trying to make a cast or something like that, but it has not worked out yet.

Suggestions?

[fsitja]

My, man, I forgot a small detail: OOPS:

Select all

 
f.dat_inicio = to_date('''|| mês_ANO || ''', ''mm/yyyy'')  

The mês_ano must be contained between apostrophes.

[fsitja]

Or in the example below, using variable bind instead of concatenar in SQL (much more elegant and fast):

Select all

 
SQL> set serveroutput on; 
SQL> declare 
  2    c_test sys_refcursor; 
  3    p_dat varchar2(8) := '04/2009'; 
  4    w_num varchar2(10); 
  5  begin 
  6    open c_test for 
  7    'with t as (select 1 num, ''AAA'' as nome from dual 
  8                 union all select 2 num, ''BBB'' nome from dual 
  9                 union all select 3 num, ''CCC'' nome from dual) 
 10      select nome 
 11        from t 
 12       where t.num = 2 
 13       and trunc(sysdate, ''MM'') = to_date(:p_in_data, ''mm/yyyy'')' 
 14    using p_dat; 
 15    fetch c_test 
 16      into w_num; 
 17    close c_test; 
 18    dbms_output.put_line('Selected ' || w_num || '!'); 
 19  end; 
 20  / 
Selected BBB! 
 
Procedimento PL/SQL concluído com sucesso. 
 
SQL>  

[nandosistemas]

Dude, thank you very much. Worked perfectly.

But since we are in the discussion, let's think of performance term.

The queries I have here are very large. They have several sub questions involved. With this example you passed me, I was able to isolate the subquery of a subquery, which was repeated at various points. I think it's just going to get better there.

But take a look at the query below. She is great but it is just to exemplify the structure of the queries I have. Notice that there are several subcursions to generate my result.




Do you think, given this structure, is there any way to improve performance by using cursors or any other resource for subcursions? ?

Thanks again.

[nandosistemas]

Oh, I was forgetting. The motive of Query is dynamic is that I can insert and remove pieces of the query according to the parameters passed by the user. But I tried to do using the variable bind but I was not very happy. I grabbed one thing. Can I insert these pieces using IF and Else?

in a very wrong, follows more or less what I want:

Select all

OPEN cv_1 FOR 
 
   'with h as ( SELECT l.COD_LOCAL FROM tOrdemHierarquia s 
                inner join tOrdemHierarquia r ON ( s.COD_LOCAL=  r.NUM_ID_PAI ) 
                inner join tOrdemHierarquia l ON ( r.COD_LOCAL = l.NUM_ID_PAI ) 
                WHERE s.COD_LOCAL= (SELECT DISTINCT num_id_hierarquia FROM thierarquia WHERE cod_Hierarq=CAST(HIERARQUIA AS INT)) 
 
                UNION 
 
                SELECT l.COD_LOCAL FROM tOrdemHierarquia s 
                inner join tOrdemHierarquia r ON ( s.COD_LOCAL=  r.NUM_ID_PAI ) 
                inner join tOrdemHierarquia l ON ( r.COD_LOCAL = l.NUM_ID_PAI ) 
                WHERE r.COD_LOCAL= (SELECT DISTINCT num_id_hierarquia FROM thierarquia WHERE cod_Hierarq=CAST(HIERARQUIA AS INT)) 
 
                UNION 
 
                SELECT l.COD_LOCAL FROM tOrdemHierarquia s 
                inner join tOrdemHierarquia r ON ( s.COD_LOCAL=  r.NUM_ID_PAI ) 
                inner join tOrdemHierarquia l ON ( r.COD_LOCAL = l.NUM_ID_PAI ) 
                WHERE l.COD_LOCAL= (SELECT DISTINCT num_id_hierarquia FROM thierarquia WHERE cod_Hierarq=CAST(HIERARQUIA AS INT) 
              ) 
           
     SELECT To_Char(aux.dat_inicio,''DD/MM/YYYY'')AS dat_inicio, 
      Round( sum((NVL (fecdecProg.valdec,0) - NVL (transfProg.valtrans,0)) / DECODE (cc.pot,NULL,1,0,1,cc.pot)),10) AS Dec_Prog, 
      Round(sum ((NVL (fecdecAcid.valdec,0) - NVL (transfAcid.valtrans,0)) / DECODE (cc.pot,NULL,1,0,1,cc.pot) ),10) AS Dec_Acid, 
      Round ( Sum (((NVL (fecdecProg.valdec,0) - NVL (transfProg.valtrans,0)) / DECODE (cc.pot,NULL,1,0,1,cc.pot) ) + 
           ((NVL (fecdecAcid.valdec,0) - NVL (transfAcid.valtrans,0)) / DECODE (cc.pot,NULL,1,0,1,cc.pot))),10) AS DEC_Total, 
 
 
      FROM ttipotrecho tr,       
      (SELECT  (SUM (qtd_cons_distr +qtd_cons_prim +qtd_cons_rur) /1) pot, 
       Extract( MONTH FROM dat_refer ) AS mês, 
        extract( YEAR FROM dat_refer ) AS ano' || 
 
      IF(OPCAO = 2 AND HIERARQUIA IS NOT NULL) THEN 
 
       'Concaneta alguma string'. 
          
       END IF; 
 
  ||   'Continua concatenando a consulta' 
    
 END;

How do I do this? ? If I concatenate the consultation in a string I can do this perfectly. But using the variable bind with the cursor I get it ?? If I do I'll win in performance and security.

Thanks.