How to pass date parameter inside the oracle procedure?

Question

Below is the sample procedure code,

create or replace procedure pro_test(start_date date, end_date date)
is 
begin

insert into test1 select col1, col2, col3 from main where range_date between start_date and end_date;

exception

< what are the exception I need to capture >

end;
/

Q1 : Is this right way to pass date directly inside the procedure? Q2 : If not, can I pass varchar inside the procedure , to convert the date in declaration part? Q3 : In begin part am using between operator, can i pass procedure parameter directly ?

While executing this procedure, exec pro_test('10102015','30102015'); What i need to mention in between sentence? between start_date and end_date is this enough or i need to mask date format?

can someone help me to clear on this?

Answer 1

Q1 : Is this right way to pass date directly inside the procedure?

Yes.

Q3 : In begin part am using between operator, can i pass procedure parameter directly ?

Not sure what you mean by this, but your insert statement is fine. You are passing a DATE as parameter and inserting into the table.

In my opinion, all this could be done in a single INSERT..SELECT statement in pure SQL.

insert into test1 
select col1, col2, col3 
from main 
where range_date 
between TO_DATE(<date_literal>,<format mask>)
and TO_DATE(<date_literal>,<format mask>);

---

UPDATE Per OP's comment:

While executing this procedure, exec pro_test('10102015','30102015'); What i need to mention in between sentence? between start_date and end_date is this enough or i need to mask date format?

'10102015' is not a DATE, it is a string literal. You must pass it as DATE, so you must either use TO_DATE with proper format mask or ANSI Date literal as you do not have any time portion. ANSI Date literal uses a fixed format 'YYYY-MM-DD'.

For example,

Using TO_DATE:

EXEC pro_test(TO_DATE('10102015','DDMMYYYY'),TO_DATE('30102015','DDMMYYYY'));

Using ANSI Date literal:

EXEC pro_test(DATE '2015-10-10', DATE '2015-10-30');

Answer 2

Q1. You need say procedure what type of parameters input or output(adding in or out), for your procedure it is input:

create or replace procedure pro_test(start_date in date, end_date in date)

Everything else in your procedure fine.