CODEWITHSUNDEEP
sqloracle-databasevariablesplsqlparameter-passing
Marcus
Marcus

Reputation: 3869

How to pass variable with sys_date in oracle

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;

Upvotes: 0

Views: 485

Answers (1)

Bikash Ranjan Bhoi
Bikash Ranjan Bhoi

Reputation: 516

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;

...
...

Upvotes: 1

Related Questions