Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    5

    Unanswered: Stored Proc Date Problem

    Ok I think I've tried just about everthing I can think of. This isn't my first time working with dates but I've never had problems like this before. It should be a slam dunk but it's not Also another problem I seem to be having is when I use default values for my input parms Oracle seems to ignore them I was trying to set all defaults to '%' so that if a parm wasn't passed I'd get back all data. Right now I'm most concerned with getting the dates working.

    Here's my code

    CREATE OR REPLACE PACKAGE pkgSearch
    AS
    TYPE SearchCur IS REF CURSOR;

    PROCEDURE SearchContracts
    (
    i_sKeywords IN VARCHAR2,
    i_sComments IN VARCHAR2,
    i_sAgreementType IN VARCHAR2,
    i_sFirstName IN VARCHAR2,
    i_sLastName IN VARCHAR2,
    i_sContractID IN VARCHAR2,
    i_sEffectiveDateFrom IN VARCHAR2,
    i_sEffectiveDateTo IN VARCHAR2,
    i_sFileLocation IN VARCHAR2,
    i_sParty IN VARCHAR2,
    o_SearchCur OUT SearchCur
    );

    END pkgSearch;
    /

    CREATE OR REPLACE PACKAGE BODY pkgSearch
    AS

    PROCEDURE SearchContracts
    (
    i_sKeywords IN VARCHAR2,
    i_sComments IN VARCHAR2,
    i_sAgreementType IN VARCHAR2,
    i_sFirstName IN VARCHAR2,
    i_sLastName IN VARCHAR2,
    i_sContractID IN VARCHAR2,
    i_sEffectiveDateFrom IN VARCHAR2,
    i_sEffectiveDateTo IN VARCHAR2,
    i_sFileLocation IN VARCHAR2,
    i_sParty IN VARCHAR2,
    o_SearchCur OUT SearchCur
    )

    IS

    sKeywords VARCHAR2(515);
    sComments VARCHAR2(515);
    sAgreementType VARCHAR2(25);
    sFirstName VARCHAR2(30);
    sLastName VARCHAR2(50);
    sContractID VARCHAR2(30);
    sFileLocation VARCHAR2(105);
    sParty VARCHAR2(50);
    dtEffectiveDateFrom VARCHAR2(12);
    dtEffectiveDateTo VARCHAR2(12);

    BEGIN

    IF i_sAgreementType IS NULL THEN
    sAgreementType := '%';
    ELSE
    sAgreementType := i_sAgreementType;
    END IF;

    IF i_sContractID IS NULL THEN
    sContractID := '%';
    ELSE
    sContractID := i_sContractID;
    END IF;

    IF i_sParty IS NULL THEN
    sParty := '%';
    ELSE
    sParty := i_sParty;
    END IF;

    IF i_sKeywords IS NULL THEN
    sKeywords := '%';
    ELSE
    sKeywords := i_sKeywords;
    END IF;

    IF i_sComments IS NULL THEN
    sComments := '%';
    ELSE
    sComments := i_sComments;
    END IF;

    IF i_sFileLocation IS NULL THEN
    sFileLocation := '%';
    ELSE
    sFileLocation := i_sFileLocation;
    END IF;

    IF i_sFirstName IS NULL THEN
    sFirstName := '%';
    ELSE
    sFirstName := i_sFirstName;
    END IF;

    IF i_sLastName IS NULL THEN
    sLastName := '%';
    ELSE
    sLastName := i_sLastName;
    END IF;

    IF i_sEffectiveDateFrom IS NULL THEN
    dtEffectiveDateFrom := '01-JAN-1900';
    ELSE
    dtEffectiveDateFrom := TO_CHAR(TO_DATE(dtEffectiveDateFrom,'DD-MON-YYYY'));
    END IF;

    IF i_sEffectiveDateTo IS NULL THEN
    dtEffectiveDateTo := '01-JAN-2040';
    ELSE
    dtEffectiveDateTo := TO_CHAR(TO_DATE(i_sEffectiveDateTo,'DD-MON-YYYY'));
    END IF;

    IF (i_sFirstName IS NULL AND i_sLastName IS NULL) THEN
    OPEN o_SearchCur FOR

    SELECT DISTINCT contract_id AS "CONTRACT ID",
    comments AS "CONTRACT COMMENTS",
    keywords AS "CONTRACT KEYWORDS",
    file_location AS "FILE LOCATION",
    effective_date AS "EFFECTIVE DATE"
    FROM search_view_contracts
    WHERE UPPER(category) LIKE i_sAgreementType
    AND UPPER(party) Like i_sParty
    AND UPPER(contract_id) LIKE i_sContractID
    AND UPPER(keywords) LIKE i_sKeywords
    AND UPPER(comments) LIKE i_sComments
    AND effective_date BETWEEN dtEffectiveDateFrom AND dtEffectiveDateTo
    AND UPPER(file_location) LIKE i_sFileLocation;
    ELSE

    OPEN o_SearchCur FOR

    SELECT DISTINCT contract_id AS "CONTRACT ID",
    -- category,
    last_name || ', ' || first_name || ' ' || middle_name AS "CONTACT NAME",
    comments AS "CONTRACT COMMENTS",
    keywords AS "CONTRACT KEYWORDS",
    file_location AS "FILE LOCATION",
    effective_date AS "EFFECTIVE DATE"
    FROM search_view
    WHERE UPPER(category) LIKE i_sAgreementType
    AND UPPER(party) Like i_sParty
    AND UPPER(contract_id) LIKE i_sContractID
    AND UPPER(keywords) LIKE i_sKeywords
    AND UPPER(comments) LIKE i_sComments
    AND UPPER(first_name) LIKE i_sFirstName
    AND UPPER(last_name) LIKE i_sLastName
    AND effective_date BETWEEN dtEffectiveDateFrom AND dtEffectiveDateTo
    AND UPPER(file_location) LIKE i_sFileLocation;
    END IF;
    END SearchContracts;
    END pkgSearch;
    /


    I'm calling this from VB via ADO. It works as long as I comment out the date stuff. I've tried converting the dates every way I can imagine. One last thing. If I run the select statement in SQL Plus or Toad using strings for dates it works...sigh

  2. #2
    Join Date
    Mar 2004
    Posts
    5
    Oh theres a typo in my code

    dtEffectiveDateFrom := TO_CHAR(TO_DATE(dtEffectiveDateFrom,'DD-MON-YYYY'));

    should read

    dtEffectiveDateFrom := TO_CHAR(TO_DATE(i_sEffectiveDateFrom ,'DD-MON-YYYY'));

    Sorry...it still won't work though

  3. #3
    Join Date
    Jan 2004
    Posts
    370
    What error do you get when it fails?
    If I run the select statement in SQL Plus or Toad using strings for dates it works.
    Which particular dates are you replacing with strings?

  4. #4
    Join Date
    Mar 2004
    Posts
    5
    I don't get any error I just get no records when there should be.

    The dates im trying to use are:

    SP Input Parms
    i_sEffectiveDateFrom IN VARCHAR2,
    i_sEffectiveDateTo IN VARCHAR2,

    Local Variables

    dtEffectiveDateFrom VARCHAR2(12);
    dtEffectiveDateTo VARCHAR2(12);


    Code to change em:

    IF i_sEffectiveDateFrom IS NULL THEN
    dtEffectiveDateFrom := '01-JAN-1900';
    ELSE
    dtEffectiveDateFrom := TO_CHAR(TO_DATE(i_sEffectiveDateFrom,'DD-MON-YYYY'));
    END IF;

    IF i_sEffectiveDateTo IS NULL THEN
    dtEffectiveDateTo := '01-JAN-2040';
    ELSE
    dtEffectiveDateTo := TO_CHAR(TO_DATE(i_sEffectiveDateTo,'DD-MON-YYYY'));
    END IF;

    SELECT statement (works with string '01-JAN-1900' and Sysdate in toad or sql plus - e.g. effective_date BETWEEN '01-JAN-1900' AND SYSDATE)

    SELECT DISTINCT contract_id AS "CONTRACT ID",
    comments AS "CONTRACT COMMENTS",
    keywords AS "CONTRACT KEYWORDS",
    file_location AS "FILE LOCATION",
    effective_date AS "EFFECTIVE DATE"
    FROM search_view_contracts
    WHERE UPPER(category) LIKE i_sAgreementType
    AND UPPER(party) Like i_sParty
    AND UPPER(contract_id) LIKE i_sContractID
    AND UPPER(keywords) LIKE i_sKeywords
    AND UPPER(comments) LIKE i_sComments
    AND effective_date BETWEEN dtEffectiveDateFrom AND dtEffectiveDateTo
    AND UPPER(file_location) LIKE i_sFileLocation;

    Originally posted by SkyWriter
    What error do you get when it fails?


    Which particular dates are you replacing with strings?

  5. #5
    Join Date
    Jan 2004
    Posts
    370
    I assume effective_date is of type DATE.

    AND effective_date BETWEEN TO_DATE(dtEffectiveDateFrom) AND TO_DATE(dtEffectiveDateTo)

  6. #6
    Join Date
    Mar 2004
    Posts
    5
    OK I got the dates working. Any idea why Oracle isn't using my parameter defaults when I pass in null?

    For example:

    i_sKeywords IN VARCHAR2 := '%',
    i_sComments IN VARCHAR2 := '%',
    i_sAgreementType IN VARCHAR2 := '%',
    i_sFirstName IN VARCHAR2 := '%',
    i_sLastName IN VARCHAR2 := '%',
    i_sContractID IN VARCHAR2 := '%',
    i_sEffectiveDateFrom IN VARCHAR2 := '%',
    i_sEffectiveDateTo IN VARCHAR2 := '%',
    i_sFileLocation IN VARCHAR2 := '%',
    i_sParty IN VARCHAR2 := '%',
    o_SearchCur OUT SearchCur

    Now if I passed in nulls for all parameters I would expect to get all records back but I get none. Shouldn't this work?

Posting Permissions

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