How not to lose Date informations by doing a query trough a varchar query in PL sql?-CodePudding

I am making a stored function that should trough a varchar make a query but I am loosing the hours in my Date variable.

This is a working query that should give me the following records.

SELECT
                RESERVATIONS.NUMERO,
                RESERVATIONS.DATE_DEBUT_PRECIS,
                RESERVATIONS.DATE_FIN_PRECIS
                FROM RESERVATIONS, LIGNES_RESERVATIONS, OBJETS, CLIENTS
                WHERE
                LIGNES_RESERVATIONS.OBJ_NUMERO = 261 AND
                LIGNES_RESERVATIONS.OBJ_SOCIETES_ID = 5 AND
                LIGNES_RESERVATIONS.SOCIETES_ID = 5 AND
                OBJETS.NUMERO = LIGNES_RESERVATIONS.OBJ_NUMERO AND
                OBJETS.SOCIETES_ID = LIGNES_RESERVATIONS.OBJ_SOCIETES_ID AND
                OBJETS.SOCIETES_ID = 5 AND
                RESERVATIONS.SOCIETES_ID = 5 AND
                RESERVATIONS.DEMANDE = 0 AND
                RESERVATIONS.ANNULER = 0 AND
                LIGNES_RESERVATIONS.RES_NUMERO = RESERVATIONS.NUMERO AND
                LIGNES_RESERVATIONS.RES_SOCIETES_ID = RESERVATIONS.SOCIETES_ID AND
                CLIENTS.NUMERO = RESERVATIONS.CLI_NUMERO AND
                CLIENTS.SOCIETES_ID = RESERVATIONS.CLI_SOCIETES_ID AND
                CLIENTS.SOCIETES_ID = 5 AND
                (TO_DATE('03.10.2022 23:00', 'dd.mm.YYYY hh24:mi') > RESERVATIONS.DATE_DEBUT_PRECIS AND TO_DATE('03.10.2022 07:00', 'dd.mm.YYYY hh24:mi') < RESERVATIONS.DATE_FIN_PRECIS)

NUMERO DATE_DEBUT DATE_FIN 94065 03.10.22 03.10.22

93995 03.10.22 03.10.22

The problem is that the given dates and time in the request are comming from a variable.

This is how I make my query in my function :

sql_stmt VARCHAR2(2000) := 'SELECT
                RESERVATIONS.NUMERO,
                RESERVATIONS.DATE_DEBUT_PRECIS,
                RESERVATIONS.DATE_FIN_PRECIS
                FROM RESERVATIONS, LIGNES_RESERVATIONS, OBJETS, CLIENTS
                WHERE
                LIGNES_RESERVATIONS.OBJ_NUMERO = '||P_OBJET||' AND
                LIGNES_RESERVATIONS.OBJ_SOCIETES_ID = '||P_SOCIETE||' AND
                LIGNES_RESERVATIONS.SOCIETES_ID = '||P_SOCIETE||' AND
                OBJETS.NUMERO = LIGNES_RESERVATIONS.OBJ_NUMERO AND
                OBJETS.SOCIETES_ID = LIGNES_RESERVATIONS.OBJ_SOCIETES_ID AND
                OBJETS.SOCIETES_ID = '||P_SOCIETE||' AND
                RESERVATIONS.SOCIETES_ID = '||P_SOCIETE||' AND
                RESERVATIONS.DEMANDE = 0 AND
                RESERVATIONS.ANNULER = 0 AND
                LIGNES_RESERVATIONS.RES_NUMERO = RESERVATIONS.NUMERO AND
                LIGNES_RESERVATIONS.RES_SOCIETES_ID = RESERVATIONS.SOCIETES_ID AND
                CLIENTS.NUMERO = RESERVATIONS.CLI_NUMERO AND
                CLIENTS.SOCIETES_ID = RESERVATIONS.CLI_SOCIETES_ID AND
                CLIENTS.SOCIETES_ID = '||P_SOCIETE||' AND
                '|| P_DATE_FIN ||' > RESERVATIONS.DATE_DEBUT_PRECIS AND '|| P_DATE_DEBUT ||' < RESERVATIONS.DATE_FIN_PRECIS';

But then, my query looks like this

SELECT
                RESERVATIONS.NUMERO,
                RESERVATIONS.DATE_DEBUT_PRECIS,
                RESERVATIONS.DATE_FIN_PRECIS
                FROM RESERVATIONS, LIGNES_RESERVATIONS, OBJETS, CLIENTS
                WHERE
                LIGNES_RESERVATIONS.OBJ_NUMERO = 261 AND
                LIGNES_RESERVATIONS.OBJ_SOCIETES_ID = 5 AND
                LIGNES_RESERVATIONS.SOCIETES_ID = 5 AND
                OBJETS.NUMERO = LIGNES_RESERVATIONS.OBJ_NUMERO AND
                OBJETS.SOCIETES_ID = LIGNES_RESERVATIONS.OBJ_SOCIETES_ID AND
                OBJETS.SOCIETES_ID = 5 AND
                RESERVATIONS.SOCIETES_ID = 5 AND
                RESERVATIONS.DEMANDE = 0 AND
                RESERVATIONS.ANNULER = 0 AND
                LIGNES_RESERVATIONS.RES_NUMERO = RESERVATIONS.NUMERO AND
                LIGNES_RESERVATIONS.RES_SOCIETES_ID = RESERVATIONS.SOCIETES_ID AND
                CLIENTS.NUMERO = RESERVATIONS.CLI_NUMERO AND
                CLIENTS.SOCIETES_ID = RESERVATIONS.CLI_SOCIETES_ID AND
                CLIENTS.SOCIETES_ID = 5 AND
                03.10.2022 > RESERVATIONS.DATE_DEBUT_PRECIS AND 03.10.2022 < RESERVATIONS.DATE_FIN_PRECIS

As we can see, there's no hours specification in the query so I tried to force it to be in the query by doing so : "TO_CHAR(P_DATE_FIN, 'dd.mm.YYYY hh24:mi')". However it didn't work and I couldn't get any results from my query so I tried to make it convert back into a Date value in my query like this : "TO_DATE('''|| TO_CHAR(P_DATE_FIN, 'dd.mm.YYYY hh24:mi')" (the TO_DATE function was supposed to be executed during the query but it just crashed my database.

CodePudding user response：

Can you try out using DBMS_SQL to parse the query with the types:

DECLARE
  lv_sql    VARCHAR2(500);
  l_objet   VARCHAR2(200);
  l_societe VARCHAR2(200);
  l_dt_deb  DATE;
  l_dt_fin  DATE;
  l_numero  VARCHAR2(200);
  l_debut_precis DATE;
  l_fin_precis   DATE;
  ln_cursor_id NUMBER;
  ln_rows_processed;
BEGIN
  l_objet   := p_objet;
  l_societe := p_societe;
  
  SELECT TO_DATE(P_DATE_FIN, 'DD.MM.YYYY HH24:MI'),TO_DATE(P_DATE_DEBUT, 'DD.MM.YYYY HH24:MI')
    INTO l_dt_fin, l_dt_deb
    FROM dual;

  lv_sql:='SELECT RESERVATIONS.NUMERO,
           RESERVATIONS.DATE_DEBUT_PRECIS,
           RESERVATIONS.DATE_FIN_PRECIS
           FROM RESERVATIONS, LIGNES_RESERVATIONS, OBJETS, CLIENTS
           WHERE
           LIGNES_RESERVATIONS.OBJ_NUMERO = :objet AND
           LIGNES_RESERVATIONS.OBJ_SOCIETES_ID = :societe AND
           LIGNES_RESERVATIONS.SOCIETES_ID = LIGNES_RESERVATIONS.OBJ_SOCIETES_ID AND
           OBJETS.NUMERO = LIGNES_RESERVATIONS.OBJ_NUMERO AND
           OBJETS.SOCIETES_ID = LIGNES_RESERVATIONS.OBJ_SOCIETES_ID AND
           RESERVATIONS.SOCIETES_ID = LIGNES_RESERVATIONS.OBJ_SOCIETES_ID AND
           RESERVATIONS.DEMANDE = 0 AND
           RESERVATIONS.ANNULER = 0 AND
           LIGNES_RESERVATIONS.RES_NUMERO = RESERVATIONS.NUMERO AND
           LIGNES_RESERVATIONS.RES_SOCIETES_ID = RESERVATIONS.SOCIETES_ID AND
           CLIENTS.NUMERO = RESERVATIONS.CLI_NUMERO AND
           CLIENTS.SOCIETES_ID = RESERVATIONS.CLI_SOCIETES_ID 
           AND :date_fin > RESERVATIONS.DATE_DEBUT_PRECIS AND :date_debut < RESERVATIONS.DATE_FIN_PRECIS';

  in_cursor_id:=DBMS_SQL.OPEN_CURSOR;
  
  DBMS_SQL.PARSE(ln_cursor_id, lv_sql, DBMS_SQL.NATIVE);
  
  DBMS_SQL.BIXD_VARLABLE(ln_cursor_id:'objet',l_objet);
  DBMS_SQL.BIXD_VARLABLE(ln_cursor_id:'societe',l_societe);
  DBMS_SQL.BIXD_VARLABLE(ln_cursor_id:'date_fin',l_dt_fin);
  DBMS_SQL.BIXD_VARLABLE(ln_cursor_id:'date_debut',l_dt_deb);

  DBMS_SQL.DEFINE_COLUMN(ln_cursor_id,1,l_numero);
  DBMS_SQL.DEFINE_COLUMN(ln_cursor_id,2,l_debut_precis);
  DBMS_SQL.DEFINE_COLUMN(ln_cursor_id,3,l_fin_precis);
  
  ln_rows_processed := DBMS_SQL.EXECUTE(ln_cursor_id);
  
  LOOP
    IF DBMS_SQL.FETCH_ROWS(ln_cursor_id)=0 THEN
      EXIT;
    ELSE
      DBMS_SQL.COLUMN_VALUE(ln_cursor_id,1,l_numero); 
      DBMS_SQL.COLUMN_VALUE(ln_cursor_id,2,l_debut_precis);
      DBMS_SQL.COLUMN_VALUE(ln_cursor_id,3,l_fin_precis);

      DBMS_OUTPUT.put_line(l_numero ||'|'|| TO_CHAR(l_debut_precis) ||'|'|| TO_CHAR(l_fin_precis));
    END IF;
  END LOOP;
  DBMS_SQL.CLOSE_ClIRSOR(ln_cursor_id);
END;

PS. I deleted the duplicate conditions from the query.

CodePudding user response：

I just changed the nls_date_format

execute immediate 'alter session set nls_date_format=''dd.mm.YYYY hh24:mi''';
sql_stmt := 'SELECT
                RESERVATIONS.NUMERO,
                RESERVATIONS.DATE_DEBUT_PRECIS,
                RESERVATIONS.DATE_FIN_PRECIS
                FROM RESERVATIONS, LIGNES_RESERVATIONS, OBJETS, CLIENTS
                WHERE
                LIGNES_RESERVATIONS.OBJ_NUMERO = '||P_OBJET||' AND
                LIGNES_RESERVATIONS.OBJ_SOCIETES_ID = '||P_SOCIETE||' AND
                LIGNES_RESERVATIONS.SOCIETES_ID = '||P_SOCIETE||' AND
                OBJETS.NUMERO = LIGNES_RESERVATIONS.OBJ_NUMERO AND
                OBJETS.SOCIETES_ID = LIGNES_RESERVATIONS.OBJ_SOCIETES_ID AND
                OBJETS.SOCIETES_ID = '||P_SOCIETE||' AND
                RESERVATIONS.SOCIETES_ID = '||P_SOCIETE||' AND
                RESERVATIONS.DEMANDE = 0 AND
                RESERVATIONS.ANNULER = 0 AND
                LIGNES_RESERVATIONS.RES_NUMERO = RESERVATIONS.NUMERO AND
                LIGNES_RESERVATIONS.RES_SOCIETES_ID = RESERVATIONS.SOCIETES_ID AND
                CLIENTS.NUMERO = RESERVATIONS.CLI_NUMERO AND
                CLIENTS.SOCIETES_ID = RESERVATIONS.CLI_SOCIETES_ID AND
                CLIENTS.SOCIETES_ID = '||P_SOCIETE||' AND
                ''' || P_DATE_FIN ||''' > RESERVATIONS.DATE_DEBUT_PRECIS AND ''' || TO_CHAR(P_DATE_DEBUT, 'dd.mm.YYYY hh24:mi') ||''' < RESERVATIONS.DATE_FIN_PRECIS';