-
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.
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
|
|