Results 1 to 9 of 9

Thread: Stored Procedure: Date IN Parameter

  1. #1
    Join Date
    Feb 2007
    Posts
    45

    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.

  2. #2
    Join Date
    Jun 2007
    Posts
    41
    dtStartDate and dtEndDate have incorrect data type, the must be DATE type.

    dtStartDate DATE;
    dtEndDate DATE;

  3. #3
    Join Date
    Feb 2007
    Posts
    45
    Thanks for replying.

    I am sorry. That was a mistake when posting here. I have:
    dtStartDate Date;
    dtEndDate Date;
    in the code.

  4. #4
    Join Date
    Jun 2007
    Posts
    41
    strInsert := 'Insert into tbl_Records (Select * from tbl_admissions where recordDate >= ' || dtStartDate || ' AND
    recordDate < ' || dtEndDate
    )';

  5. #5
    Join Date
    Feb 2007
    Posts
    45
    Thanks for replying shamshe.

    I get "JUN" as invalid identifier now when i put:

    execute sp_getRecords ('2006-01-01', '2006-06-01')

  6. #6
    Join Date
    Jun 2007
    Posts
    41
    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;

  7. #7
    Join Date
    Feb 2007
    Posts
    45
    that worked like a charm!!!

    Thanks a lot!!!...

    if i had more input variables needed, i can just add :1, :2, :3 etc?

  8. #8
    Join Date
    Jun 2007
    Posts
    41
    you are welcome.

    yes sure you can use more variables

  9. #9
    Join Date
    Feb 2007
    Posts
    45
    Thanks alot!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •