1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Better Alternative Apart From Oracle Function calling in the select statemet

Discussion in 'SQL PL/SQL' started by ashokkumar, Aug 4, 2009.

  1. ashokkumar

    ashokkumar Guest

    Hi,

    I have written a oracle package in which for getting PortName and DischargePort for the input CalculationNumber, I am calling a function in the ref_cursor select statement.

    But for the fields to get populated..it is taking more than 1 min for 11,000 records..(front-end application as DOT-NET)
    The Part of the function is it concatenates the port names if it has more then one port.

    For your reference:
    Code (SQL):

    FUNCTION UFN_ConcatVoySearch_DischPort
    (
    CalculationNumber IN INT
    )
    RETURN VARCHAR2
    AS
    l_DischargePortName_tmp VARCHAR2(5000) ;
    l_DischargePortName VARCHAR2(5000) ;
    l_CalcNumber NUMBER(13) ;
    --Cursors
    CURSOR cur_dischport(in_calcnumber INT)
    IS
    SELECT PORT_NAME
    FROM T465_CARGOPORTS
    WHERE PORT_NAME IS NOT NULL
    AND CALCNO = in_calcnumber
    AND PORT_TYPE = 'D'
    GROUP BY PORT_NAME
    ORDER BY MAX(PORTNO) DESC;
    BEGIN

    l_CalcNumber := CalculationNumber;
    l_DischargePortName := NULL;

    FOR cur_dischport_rec IN
    cur_dischport(l_CalcNumber)
    LOOP
    l_DischargePortName_tmp := NULL;
    l_DischargePortName_tmp := cur_dischport_rec.PORT_NAME;

    IF l_DischargePortName IS NULL
    THEN
    l_DischargePortName := l_DischargePortName || l_DischargePortName_tmp;
    ELSE
    l_DischargePortName := l_DischargePortName || '/' || l_DischargePortName_tmp;
    END IF;
    END LOOP;

    RETURN l_DischargePortName;

    END UFN_ConcatVoySearch_DischPort;
    Below Reference Cursor I am using in the Procedure of the Package Body whose ouput will be in the form of record set as the output they need in the form of record/result set.
    Please ignore the local variables..as I am passing the values as NULL to get all the records from the table..

    Code (SQL):
    OPEN ret_cursor FOR
    SELECT a.CALCNR AS CalculationNumber
    , a.CALCTYPE AS CalculationType
    , a.NAME_SHIP AS VesselName
    , a.DATE_INS AS EstimationDate
    , a.USER_INS AS UserId
    , a.EstimateDesc AS EstimateDesc
    , a.EstimateGroupDesc AS EstimateGroupDesc
    , UFN_ConcatVoySearch_LoadPort(a.CALCNR) AS LoadPort
    , UFN_ConcatVoySearch_DischPort(a.CALCNR) AS DischargePort
    , '' AS Charterer
    , '' AS Commodity
    , a.STATUS AS STATUS
    FROM T460_voycalc a
    WHERE a.CALCNR LIKE NVL(l_CalculationNumber,'%')
    AND a.CALCTYPE LIKE NVL(l_CalculationType,'%')
    AND NVL(a.NAME_SHIP,' ') LIKE NVL(l_VesselName,'%')
    AND NVL(a.USER_INS,' ') LIKE NVL(l_UserId,'%')
    AND NVL(a.EstimateDesc,' ') LIKE NVL(l_EstimateDesc,'%')
    AND NVL(a.EstimateGroupDesc,' ') LIKE NVL(l_EstimateGroupDesc,'%')
    GROUP BY a.CALCNR
    , a.CALCTYPE
    , a.NAME_SHIP
    , a.DATE_INS
    , a.USER_INS
    , a.EstimateDesc
    , a.EstimateGroupDesc
    , a.STATUS;

    Please let me know the best alternative apart from writing the function and calling it.

    Thanks,
    Ashok
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Why do you want to replace the function with SQL statement ? Anyway there is another alternative with SYS_Connect_by.
    You refer the following code.

    Code (SQL):

    SQL> SELECT deptno,ename FROM EMP WHERE deptno=10 ORDER BY 2;

        DEPTNO ENAME
    ---------- ----------
            10 CLARK
            10 KING
            10 MILLER

    SQL> SELECT
      2     deptno,
      3     SUBSTR(SYS_CONNECT_BY_PATH(ename, '/'),2) name_list
      4  FROM
      5     ( SELECT ename,deptno,
      6              COUNT(*) OVER ( PARTITION BY deptno ) cnt,
      7              ROW_NUMBER () OVER ( PARTITION BY deptno ORDER BY ename) seq
      8        FROM EMP
      9       WHERE deptno IS NOT NULL)
     10        WHERE seq=cnt
     11          AND deptno=10
     12   START WITH seq=1
     13   CONNECT BY PRIOR seq+1=seq
     14          AND PRIOR deptno=deptno;

        DEPTNO NAME_LIST
    ---------- ------------------------------
            10 CLARK/KING/MILLER

    SQL>