-
Stored Procedure: Date IN Parameter
Hello,
I am writing a stored procedure that has to get records between certain dates. These dates will be entered as parameter with the sp.
execute sp_getRecords ('2006-01-01', 2006-06-01')
Here is a snippet:
Create or Replace procedure sp_getRecords (startDate IN Varchar2, endDate IN Varchar2)
AS
Begin
Declare
dtStartDate long;
dtEndDate long;
strInsert long;
Begin
dtStartDate := to_date(startDate,'YYYY-MM-DD');
dtEndDate := to_date(endDate,'YYYY-MM-DD');
strInsert := 'Insert into tbl_Records (Select * from tbl_admissions where recordDate >= dtStartDate AND
recordDate < dtEndDate
)';
execute immediate strInsert;
end
end;
---
When i run this, i get an error saying "invalid identifier" at dtEndDate in Insert record command.
Edited at 10am on Sep 19, 2007:
dtEndDate := to_date(endDate,'YYYY-MM-DD');
ERROR at line 1:
ORA-00904: "DTENDDATE": invalid identifier
ORA-06512: at "adm.SP_GETRECORDS", line 30
ORA-06512: at line 1
If i delete 'dtenddate' and only leave 'dtstartdate' in. I get the same error on dtStartDate.
Any help is much appreciated.
Thanks,
J
Last edited by junOOni; 09-19-2007 at 09:04 AM.
-
dtStartDate and dtEndDate have incorrect data type, the must be DATE type.
dtStartDate DATE;
dtEndDate DATE;
-
Thanks for replying.
I am sorry. That was a mistake when posting here. I have:
dtStartDate Date;
dtEndDate Date;
in the code.
-
strInsert := 'Insert into tbl_Records (Select * from tbl_admissions where recordDate >= ' || dtStartDate || ' AND
recordDate < ' || dtEndDate
)';
-
Thanks for replying shamshe.
I get "JUN" as invalid identifier now when i put:
execute sp_getRecords ('2006-01-01', '2006-06-01')
-
You can use bind variables:
strInsert := 'Insert into tbl_Records (Select * from tbl_admissions where recordDate >= :1 AND
recordDate < :2 )';
execute immediate strInsert using dtStartDate ,dtEndDate;
-
that worked like a charm!!!
Thanks a lot!!!...
if i had more input variables needed, i can just add :1, :2, :3 etc?
-
you are welcome.
yes sure you can use more variables
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|