Results 1 to 5 of 5

Thread: Parse a list

  1. #1
    Join Date
    Jan 2003
    Posts
    67

    Unanswered: Parse a list

    I need to take a list of zip codes and return the closest office to each zip code in a result set. I am trying to find the best way to handle this. I am running oracle 8i. My thoughts are something like this…

    PROCEDURE S_ZIPSEARCH_ LIST (P_DISTANCE IN NUMBER, P_ZIPCODE IN VARCHAR2, P_MAXRETURNROWS IN NUMBER,
    P_RESULTS OUT SEARCH_RESULTS_RECORD_LIST)
    AS
    BEGIN
    OPEN P_RESULTS FOR
    LOOP
    <<<< run a query on each zip code >>>>>
    EXIT WHEN <<<some logic>>;
    END LOOP;
    END;

    So I guess my question is what is the best way to build the result list? Has anyone ever had to do something like this?

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    You could try something like this:
    Code:
    PROCEDURE S_ZIPSEARCH_ LIST (P_DISTANCE IN NUMBER, P_ZIPCODE IN VARCHAR2, P_MAXRETURNROWS IN NUMBER,
    P_RESULTS OUT SEARCH_RESULTS_RECORD_LIST)
    AS
    BEGIN
    ...
    EXECUTE IMMEDIATE 'SELECT office_nbr FROM OFFICES '
                    ||' WHERE ZIP_FROM = :1 AND ZIP_DISTANCE <= :2'
                    ||' AND ROWNUM <= :3'
               USING P_ZIPCODE, P_DISTANCE, P_MAXRETURNROWS
         BULK COLLECT INTO P_RESULTS;
    ...
    END;
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Jan 2003
    Posts
    67

    Parse a file

    Thanks I'll give that a try.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Or even, to edit LKBrwn_DBA's answer slightly:
    Code:
    PROCEDURE S_ZIPSEARCH_ LIST (P_DISTANCE IN NUMBER, P_ZIPCODE IN VARCHAR2, P_MAXRETURNROWS IN NUMBER,
    P_RESULTS OUT SEARCH_RESULTS_RECORD_LIST)
    AS
    BEGIN
    ...
    SELECT office_nbr FROM OFFICES
    BULK COLLECT INTO P_RESULTS
    WHERE ZIP_FROM = p_zipcode
    AND ZIP_DISTANCE <= p_distance
    AND ROWNUM <= p_maxreturnrows;
    ...
    END;
    Wouldn't that work? i.e. without the dynamic SQL.

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713
    True, no need for dynamic sql.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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