SQL & PL/SQL

Hi All,

I have a cursor which I am trying to open multiple times, I found some strange issue. Just wondering if I am doing anything wrong or is it how it's supposed to work

CREATE OR REPLACE FUNCTION XXC_CONTROL_STMTFormula

RETURN CHAR

IS

CURSOR c_eu_country (

  p\_country    VARCHAR2)

IS

  SELECT 'Y'

    FROM fnd\_lookup\_values

   WHERE lookup\_type = 'SYM\_SHIP\_DOCS\_TC'

    AND attribute3 = 'Y'                           --Intra EU Country

    AND tag = p\_country;

l_ctrl_stmt VARCHAR2 (4000);

l_warehouse_code VARCHAR2 (100);

l_count NUMBER := 0;

l_eu_country VARCHAR2 (1) := 'N';

BEGIN

SELECT ORGANIZATION_CODE

 INTO l\_warehouse\_code

 FROM org\_organization\_definitions

WHERE organization\_id = 6793;

OPEN c_eu_country ('GB');

FETCH c_eu_country INTO l_eu_country;

DBMS\_OUTPUT.PUT\_LINE('In CP\_CONTROL\_STMTFormula,l\_eu\_country1 : '||l\_eu\_country);   

CLOSE c_eu_country;

IF NVL(l_eu_country,'N') = 'N'

THEN

 DBMS\_OUTPUT.PUT\_LINE('2');

  OPEN c\_eu\_country ('GB');

  FETCH c\_eu\_country INTO l\_eu\_country;

  DBMS\_OUTPUT.PUT\_LINE('In CP\_CONTROL\_STMTFormula,l\_eu\_country2 : '||l\_eu\_country);   

END IF;

DBMS\_OUTPUT.PUT\_LINE('Before Close,l\_eu\_country1 : '||l\_eu\_country); 

CLOSE c\_eu\_country;

DBMS_OUTPUT.PUT_LINE('In CP_CONTROL_STMTFormula,After Close : '||l_eu_country);

IF NVL(l_eu_country,'N') = 'Y'

THEN

  DBMS\_OUTPUT.PUT\_LINE('In CP\_CONTROL\_STMTFormula,Inside If : '||l\_eu\_country);

  SELECT COUNT (\*)

    INTO l\_count

    FROM oe\_order\_lines\_all oel,

         wsh\_delivery\_details det,

         wsh\_delivery\_assignments das,

         mtl\_system\_items\_b msi,

         xxc\_product\_tc\_attributes xpta

   WHERE     det.source\_line\_id = oel.line\_id

         AND det.delivery\_detail\_id = das.delivery\_detail\_id

         AND det.inventory\_item\_id = msi.inventory\_item\_id

         AND oel.ship\_from\_org\_id = msi.organization\_id

         AND das.delivery\_id = 943033866

         AND oel.inventory\_item\_id = xpta.inventory\_item\_id

         AND xpta.attribute\_name = 'IE\_ECCN'

         AND UPPER (xpta.control\_value) \<> 'NOCLASS';

   DBMS\_OUTPUT.PUT\_LINE('In CP\_CONTROL\_STMTFormula,l\_count : '||l\_count);   

   IF l\_count > 0

   THEN

    SELECT XXC\_TC\_LIC\_PKG.get\_control\_statement ('PS','CONTROL',l\_warehouse\_code,'GB')

    INTO l\_ctrl\_stmt

    FROM DUAL;

   END IF;

  DBMS\_OUTPUT.PUT\_LINE('In CP\_CONTROL\_STMTFormula,l\_ctrl\_stmt : '||l\_ctrl\_stmt);

  RETURN l\_ctrl\_stmt;

END IF;

EXCEPTION

WHEN OTHERS

THEN

  RETURN NULL;

END;

Execution:

declare

l_statement varchar2(4000);

begin

l_statement := XXC_CONTROL_STMTFormula;

end;

Output:

In CP_CONTROL_STMTFormula,l_eu_country1 : Y

Before Close,l_eu_country1 : Y

Question: Why is control stopping at line 42? When I used the cursor attribute %ISOPEN at line 44 to close the cursor then control is going forward. Can't we close the cursor just close cursor; in instances where we are using a same cursor multiple times?

Any suggestions are appreciated.

Thanks!