PL/SQL: USING clause and dynamic variables

Question

The following stored procedure is meant to take in a parameter my_seconds, create a date variable my_date which is the current date plus (or minus) the number in my_seconds, and print the date.

CREATE OR REPLACE PROCEDURE test (my_seconds NUMBER)
IS    
my_date DATE;
BEGIN    
   execute immediate 'select sysdate + interval '':1'' second from dual' into my_date USING &my_seconds;
   DBMS_OUTPUT.PUT_LINE('DATE:' || my_date);  
END

But what happens is that when compiling the procedure (using Oracle SQL developer), it prompts for the value of my_seconds, and the procedure is not created correctly.

How should this line:

execute immediate 'select sysdate + interval '':1'' second from dual' into my_date USING &my_seconds ;

be re-written?

Thanks

Once you get your code to compile you'll encounter an error when trying to execute your dynamic SQL. interval ':1' second is an interval string literal. As such you can't put a variable inside the quotation marks, it needs to be a constant value. instead you'll need to use either the NUMTODSINTERVAL or NUMTOYMINTERVAL function to convert a numeric value to an interval. — Sentinel
– Sentinel, Commented Aug 4, 2015 at 16:45

StephaneM · Accepted Answer · 2015-08-04 14:27:04Z

5

You can write your procedure like this, without execute immediate:

CREATE OR REPLACE PROCEDURE test (my_seconds NUMBER)
IS    
my_date DATE;
BEGIN    
   select sysdate + my_seconds * interval '1' second into my_date from dual;
   DBMS_OUTPUT.PUT_LINE('DATE:' || my_date);  
END

answered Aug 4, 2015 at 14:27

StephaneM

4,8791 gold badge19 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Wernfried Domscheit Over a year ago

Even shorter: my_date := sysdate + my_seconds * interval '1' second;

user272735 Over a year ago

Even shorter: my_date constant date := sysdate + my_seconds * interval '1' second;

Sentinel · Accepted Answer · 2015-08-04 15:50:30Z

You can forgo the SQL statement all together and use this:

CREATE OR REPLACE PROCEDURE test( my_seconds NUMBER )
IS
  my_date DATE;
BEGIN
  my_date := sysdate + numtodsinterval( my_seconds, 'second' );
  DBMS_OUTPUT.PUT_LINE( 'DATE:' || my_date );
END;

Doing so will avoid switching contexts from PL/SQL to SQL and back again.

However, to answer your original question of how to compile the code in SQL Developer and avoid the prompts for bind variables caused by the colons in your code, use the Run Script command instead of the Run Statement command (F5 instead of F9 with default key mappings or click the icon that looks like a page with a green triangle instead of the icon of just a green triangle).

Alternatively right click the Procedures node in the connections tree, choose New Procedure, fill out the blanks as appropriate, and click OK, then paste your code into the procedure editor that was opened. From here you can click the two gears icon or type CTRL-S to compile your code.

Avrajit Roy · Accepted Answer · 2015-08-06 12:53:29Z

0

You can try this too. It works

CREATE OR REPLACE PROCEDURE test_AV_PROC (my_seconds NUMBER)
IS    
my_date TIMESTAMP;
BEGIN    
   execute immediate 'select :1 + INTERVAL''' ||my_seconds||''''||'SECOND'|| ' from dual ' into my_date USING SYSTIMESTAMP;
   DBMS_OUTPUT.PUT_LINE('DATE:' || my_date);  
END;

answered Aug 6, 2015 at 12:53

Avrajit Roy

3,3131 gold badge16 silver badges25 bronze badges

Collectives™ on Stack Overflow

PL/SQL: USING clause and dynamic variables

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related