How to pass variable with sys_date in oracle

Question

I want to pass sysdate into V_TODAY variable which i can use it for other anounymous block or during spooling. I am writing below code which is giving an error has V_TODAY must be declare. For variable with value NAME and ID i am able to pass the value.

SET ECHO OFF;
SET FEEDBACK OFF;
SET HEAD OFF;
SET LIN 256;
SET TRIMSPOOL ON;
SET WRAP OFF;
SET PAGES 100;
SET TERM OFF;
SET SERVEROUTPUT ON;

SPOOL F:\LATEST\Loop_TRE.sql;

PROMPT VAR NAME VARCHAR2(100);
PROMPT VAR ID VARCHAR2(100);
PROMPT VAR V_TODAY date;

BEGIN  
FOR TARGET_POINTER IN (SELECT NAME, ID from D_URL)
    LOOP
    DBMS_OUTPUT.PUT_LINE('DEFINE TARGET = '''||TARGET_POINTER.NAME||''';');
    DBMS_OUTPUT.PUT_LINE('EXEC :NAME  := '''||TARGET_POINTER.NAME||'''; ');
    DBMS_OUTPUT.PUT_LINE('DEFINE TARGET1 = '''||TARGET_POINTER.ID||''';');
    DBMS_OUTPUT.PUT_LINE('EXEC :ID  := '''||TARGET_POINTER.ID||'''; ');
    DBMS_OUTPUT.PUT_LINE('@@TGT_DOP.sql;');
  END LOOP;
  Select SYS_DATE INTO V_TODAY from DUAL;
  DBMS_OUTPUT.PUT_LINE('DEFINE V_TODAY = '''||V_TODAY||''';');
  DBMS_OUTPUT.PUT_LINE('EXEC :V_TODAY  := '''||V_TODAY||'''; ');
  DBMS_OUTPUT.PUT_LINE('@@Loop_TST.sql;');
END;
/

SPOOL OFF;

@@Loop_TRE.sql;

Did you try declaring `V_TODAY` in your anonymous block instead of prompting? — fen1x, Nov 17 '17 at 09:49
no i am not declaring just prompting. i can use variable value for name and id in TGT_DOP.sql — Andrew, Nov 17 '17 at 09:50
You have sys_date not sysdste, and tend to agree vtoday should be a variable for the into to work, and you have no where clause — Ab Bennett, Nov 17 '17 at 09:56
Not related to your question but your where clause is not ending properly? — Kaushik Nayak, Nov 17 '17 at 10:00
Use the `accept variable prompt ""` syntax and use &variable inside the block — Kaushik Nayak, Nov 17 '17 at 10:17
Possible duplicate of [SQL\*Plus how to accept text variable from prompt?](https://stackoverflow.com/questions/16674252/sqlplus-how-to-accept-text-variable-from-prompt) — XING, Nov 17 '17 at 10:39

score 1 · Answer 1 · answered Nov 17 '17 at 09:56

1

Declare V_TODAY in Declare block and use SYSDATE instead of SYS_DATE

in Reference with your code:

SET ECHO OFF;

.
.
PROMPT VAR NAME VARCHAR2(100);
PROMPT VAR ID VARCHAR2(100);

DECLARE
V_TODAY DATE;
BEGIN
....
...
END LOOP;
Select SYSDATE INTO V_TODAY from DUAL;

...
...

answered Nov 17 '17 at 09:56

Bikash Ranjan Bhoi

518
2
10

i cannot use declare here because i am using spooling and it will throw error. As you can see i cann pass the variable for Name and ID without any issue and use it in TGT_DOP.sql. So not understanding why i cannot V_TODAY variable. I have also changed from SYS_DATE to SYSDATE still the same error – Andrew Nov 17 '17 at 10:07
@Andrew : Name and Id you are able to use as these are the attributes of TARGET_POINTER by default. These aren't from the declaration. – Bikash Ranjan Bhoi Nov 17 '17 at 11:14

How to pass variable with sys_date in oracle

1 Answers1