date parameter in Oracle Stored Procedure


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: date parameter in Oracle Stored Procedure

  1. #1
    Naveen Guest

    date parameter in Oracle Stored Procedure


    Hi

    Pardon the length of the message.

    I use a stored procedure in Oracle 7.3 from a VB app. This stored procedure
    accepts two input parameters of date data type carries out some calculation
    and inserts the result in an Oracle table along with the two date parameters.

    This is done correctly.

    But the problem is that the date field in the resulting Oracle table does
    not filter on date values.

    The following SQL statements were used all of which returned the message
    'no rows' .

    SQL > select * from A where qtr_start_date = to_date ('1999-01-01','yyyy-mm-dd');

    SQL > select * from A where
    qtr_start_date = to_date ('2000-01-01','yyyy-mm-dd');

    SQL > select * from A where qtr_start_date = to_date ('01-JAN-99')

    However if I were to execute the same procedure directly from SQL * Plus
    the result table can be filtered correctly on date values.

    The problem seems to be in the VB app that is passing the date parameter
    to the stored procedure. The stored proc uses the date parameter to calculate
    correctly but somehow when the same value is inserted into the database ,
    there is a problem.

    This is really intriguing.

    Could some one please help me out on this.

    Thanks in advance


  2. #2
    Naveen Guest

    Re: date parameter in Oracle Stored Procedure


    "Naveen" <thalanki_naveen@rocketmail.com> wrote:
    >
    >Hi
    >
    >Pardon the length of the message.
    >
    >I use a stored procedure in Oracle 7.3 from a VB app. This stored procedure
    >accepts two input parameters of date data type carries out some calculation
    >and inserts the result in an Oracle table along with the two date parameters.
    >
    >This is done correctly.
    >
    >But the problem is that the date field in the resulting Oracle table does
    >not filter on date values.
    >
    >The following SQL statements were used all of which returned the message
    >'no rows' .
    >
    >SQL > select * from A where qtr_start_date = to_date ('1999-01-01','yyyy-mm-dd');
    >
    >SQL > select * from A where
    >qtr_start_date = to_date ('2000-01-01','yyyy-mm-dd');
    >
    >SQL > select * from A where qtr_start_date = to_date ('01-JAN-99')
    >
    >However if I were to execute the same procedure directly from SQL * Plus
    >the result table can be filtered correctly on date values.
    >
    >The problem seems to be in the VB app that is passing the date parameter
    >to the stored procedure. The stored proc uses the date parameter to calculate
    >correctly but somehow when the same value is inserted into the database

    ,
    >there is a problem.
    >
    >This is really intriguing.
    >
    >Could some one please help me out on this.
    >
    >Thanks in advance
    >


    I finally found the solution.

    I had earlier executed the stored procedures without using the parameters
    collection using the following syntax:

    cmd.execute ,parameters,options

    I dropped this statement and instead used the parameters collection as in
    below:

    cmd.parameters.append cmd.createparameter (blah. blah..)

    It works fine , I do not know why though

    Naveen

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center