CODEWITHSUNDEEP
oracle-databasedateplsql
Soji
Soji

Reputation: 269

DATES IN PL/SQL

I have an issue when managing dates in PL/SQL.

I want to create a statement with the date as an input.

Here is the critical part of my PL/SQL code :

stmt := 'INSERT INTO TMPWASMOVING (LOCATIONCODE, ITEMCODE, DATEBACK, TYPE)
         SELECT CODE, "bpart_id", 
         TO_DATE(SUBSTR("receive_dt",0,10),''yyyy-mm-dd'')
         - TO_DATE('||dst||',''yyyy-mm-dd''), ''Healthy'''
....
EXECUTE IMMEDIATE stmt;

With dst a VARCHAR2 that I input using the following format '2015-09-01' for example. In that case I get an "input value not long enough for date format" error.

If I change my code to: 

stmt := 'INSERT INTO TMPWASMOVING (LOCATIONCODE, ITEMCODE, DATEBACK, TYPE)
         SELECT CODE, "bpart_id",
         TO_DATE(SUBSTR("receive_dt",0,10),''yyyy-mm-dd'')
         - TO_DATE(''2015_09-01'',''yyyy-mm-dd''), ''Healthy'''
....
EXECUTE IMMEDIATE stmt;

Then It is working perfectly fine.

Any advices / ideas ?

Upvotes: 0

Views: 164

Answers (3)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

I don't see why you're doing this using dynamic SQL as there doesn't seem to be anything "dynamic" about it. You could accomplish the same using a static INSERT statement:

INSERT INTO TMPWASMOVING (LOCATIONCODE, ITEMCODE, DATEBACK, TYPE)
  SELECT CODE,
         "bpart_id",
         TO_DATE(SUBSTR("receive_dt", 0, 10), 'yyyy-mm-dd') 
           - TO_DATE(dst, 'yyyy-mm-dd'),
         'Healthy');

Best of luck.

Upvotes: 3

diziaq
diziaq

Reputation: 7785

You can also pass a date with USING clause.

DECLARE
  l_date DATE := to_date(dst, 'yyyy-mm-dd');
BEGIN
  stmt := ' INSERT INTO TMPWASMOVING (LOCATIONCODE, ITEMCODE, DATEBACK, TYPE)
            SELECT CODE,"bpart_id", TO_DATE(SUBSTR("receive_dt",0,10),''yyyy-mm-dd'') - :1,''Healthy''';

  EXECUTE IMMEDIATE stmt USING l_date;
END;

PS. And before you got blind of quotes try to use quoting literals. (highlighting here is poor, so try it in any Oracle client)

DECLARE
   s VARCHAR2(1024);
BEGIN
   s := q'[Can't breathe 'cause it's beautiful.]';
   DBMS_OUTPUT.PUT_LINE(s);
END;

Upvotes: 2

user359040
user359040

Reputation:

You have omitted the quotation marks around the value of dst in your code - the following should work:

stmt := ' INSERT INTO TMPWASMOVING (LOCATIONCODE, ITEMCODE, DATEBACK, TYPE)
SELECT CODE,"bpart_id",TO_DATE(SUBSTR("receive_dt",0,10),''yyyy-mm-dd'')-     TO_DATE('''||dst||''',''yyyy-mm-dd''),''Healthy''
...

Upvotes: 3

Related Questions