Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2004
    Location
    Manchester, UK
    Posts
    143

    Unanswered: Oracle package to return a result set

    Hi all,

    I need to write a package which returns a result set, so that I can call it from SQL Server using a linked server and openquery. However, I have never written a package before. I got the example below from the web, and adjusted it to suit my needs, but I get an error straight away when trying to create it. Please can someone spot the obvious error that I cannot recognise? The error I get is:

    Code:
    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    6/1      PLS-00103: Encountered the symbol "SAON" when expecting one of
             the following:
             := . ) , @ % default character

    The package looks like this:

    Code:
    CREATE PACKAGE WRAPPACK
    AS
    TYPE MSLINK_W IS TABLE OF NUMBER
    INDEX BY BINARY_INTEGER;
    TYPE SAON_W IS TABLE OF VARCHAR2(100)
    INDEX BY BINARY_INTEGER;
    TYPE PAON_W IS TABLE OF VARCHAR2(100)
    INDEX BY BINARY_INTEGER;
    TYPE STREET_DESCRIPTOR_W IS TABLE OF VARCHAR2(100)
    INDEX BY BINARY_INTEGER;
    TYPE LOCALITY_NAME_W IS TABLE OF VARCHAR2(35)
    INDEX BY BINARY_INTEGER;
    TYPE TOWN_NAME_W IS TABLE OF VARCHAR2(30)
    INDEX BY BINARY_INTEGER;
    TYPE COUNTY_NAME_W IS TABLE OF VARCHAR2(30)
    INDEX BY BINARY_INTEGER;
    TYPE POSTCODE_W IS TABLE OF VARCHAR2(8)
    INDEX BY BINARY_INTEGER;
    PROCEDURE WRAPPACKSP
    (
    MSLINK OUT MSLINK_W
    SAON OUT SAON_W
    PAON OUT PAON_W
    STREET_DESCRIPTOR OUT STREET_DESCRIPTOR_W
    LOCALITY_NAME OUT LOCALITY_NAME_W
    TOWN_NAME OUT TOWN_NAME_W
    COUNTY_NAME OUT COUNTY_NAME_W
    POSTCODE OUT POSTCODE_W
    );
    END WRAPPACK;
    /
    CREATE PACKAGE BODY WRAPPACK
    AS
    PROCEDURE WRAPPACKSP
    (
    MSLINK OUT MSLINK_W
    SAON OUT SAON_W
    PAON OUT PAON_W
    STREET_DESCRIPTOR OUT STREET_DESCRIPTOR_W
    LOCALITY_NAME OUT LOCALITY_NAME_W
    TOWN_NAME OUT TOWN_NAME_W
    COUNTY_NAME OUT COUNTY_NAME_W
    POSTCODE OUT POSTCODE_W
    )
    IS
    	NLPG_COUNT NUMBER DEFAULT 1;
    	CURSOR NLPGCUR IS
    		SELECT TO_NUMBER(SUBSTR(TO_CHAR(LOC.MSLINK),1,8)),
     		LTRIM(L.SAON),
    		 LTRIM(L.PAON),
    		 S.STREET_DESCRIPTOR,
    		 S.LOCALITY_NAME,
    		 S.TOWN_NAME,
    		 S.COUNTY_NAME,
    		 L.POSTCODE
    		FROM NLPG_LPI L, NSG_STREET S,NLPG_BLPU B, FEATURE_CROSS_REFS FCR, LOCATIONS LOC
    		WHERE S.USRN=L.USRN
    		AND   B.UPRN=L.UPRN
    		AND FCR.FEATURE_ID=B.NLPG_ID
    		AND LOC.MSLINK=FCR.MSLINK;
    BEGIN
    	FOR NLPG IN NLPGCUR
    	LOOP
    		MSLINK_W(NLPG_COUNT) :=LOC.MSLINK;
    		SAON_W(NLPG_COUNT) :=L.SAON;
    		PAON_W(NLPG_COUNT):=L.PAON;
    		STREET_DESCRIPTOR_W(NLPG_COUNT):=S.STREET_DESCRIPTOR;
    		LOCALITY_NAME_W(NLPG_COUNT):=S.LOCALITY_NAME;
    		TOWN_NAME_W(NLPG_COUNT):=S.TOWN_NAME;
    		COUNTY_NAME_W(NLPG_COUNT):=S.COUNTY_NAME;
    		POSTCODE_W(NLPG_COUNT):=L.POSTCODE;
    		NLPG_COUNT:=NLPG_COUNT + 1;
    	END LOOP;
    END WRAPPACKSP;
    END WRAPPACK;
    /

    Many thanks!

    Paula.

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    You need commas between the parameters in the procedure declaration. Looks like you only have a statement of fact here (no parameters passed in), why not just use the SQL itself you have in the cursor (perhaps wrap it into a view) and use that on SQLServer ?
    Last edited by JMartinez; 10-26-06 at 11:32.

  3. #3
    Join Date
    Aug 2004
    Location
    Manchester, UK
    Posts
    143
    Ok, thanks for that. Now I got further but I'm more confused. Now I get:

    Code:
    33/3     PLS-00321: expression 'MSLINK_W' is inappropriate as the left
             hand side of an assignment statement
    
    33/3     PL/SQL: Statement ignored
    I altered my code to look like this:

    Code:
    CREATE PACKAGE WRAPPACK
    AS
    TYPE MSLINK_W IS TABLE OF NUMBER
    INDEX BY BINARY_INTEGER;
    TYPE SAON_W IS TABLE OF VARCHAR2(100)
    INDEX BY BINARY_INTEGER;
    TYPE PAON_W IS TABLE OF VARCHAR2(100)
    INDEX BY BINARY_INTEGER;
    TYPE STREET_DESCRIPTOR_W IS TABLE OF VARCHAR2(100)
    INDEX BY BINARY_INTEGER;
    TYPE LOCALITY_NAME_W IS TABLE OF VARCHAR2(35)
    INDEX BY BINARY_INTEGER;
    TYPE TOWN_NAME_W IS TABLE OF VARCHAR2(30)
    INDEX BY BINARY_INTEGER;
    TYPE COUNTY_NAME_W IS TABLE OF VARCHAR2(30)
    INDEX BY BINARY_INTEGER;
    TYPE POSTCODE_W IS TABLE OF VARCHAR2(8)
    INDEX BY BINARY_INTEGER;
    PROCEDURE WRAPPACKSP
    (
    MSLINK OUT MSLINK_W,
    SAON OUT SAON_W,
    PAON OUT PAON_W,
    STREET_DESCRIPTOR OUT STREET_DESCRIPTOR_W,
    LOCALITY_NAME OUT LOCALITY_NAME_W,
    TOWN_NAME OUT TOWN_NAME_W,
    COUNTY_NAME OUT COUNTY_NAME_W,
    POSTCODE OUT POSTCODE_W
    );
    END WRAPPACK;
    /
    CREATE PACKAGE BODY WRAPPACK
    AS
    PROCEDURE WRAPPACKSP
    (
    MSLINK OUT MSLINK_W,
    SAON OUT SAON_W,
    PAON OUT PAON_W,
    STREET_DESCRIPTOR OUT STREET_DESCRIPTOR_W,
    LOCALITY_NAME OUT LOCALITY_NAME_W,
    TOWN_NAME OUT TOWN_NAME_W,
    COUNTY_NAME OUT COUNTY_NAME_W,
    POSTCODE OUT POSTCODE_W
    )
    IS
    	NLPG_COUNT NUMBER DEFAULT 1;
    	CURSOR NLPGCUR IS
    		SELECT TO_NUMBER(SUBSTR(TO_CHAR(LOC.MSLINK),1,8)) AS MSLINK,
     		LTRIM(L.SAON) AS SAON,
    		 LTRIM(L.PAON) AS PAON,
    		 S.STREET_DESCRIPTOR AS STREET_DESCRIPTOR,
    		 S.LOCALITY_NAME AS LOCALITY_NAME,
    		 S.TOWN_NAME AS TOWN_NAME,
    		 S.COUNTY_NAME AS COUNTY_NAME,
    		 L.POSTCODE AS POSTCODE
    		FROM NLPG_LPI L, NSG_STREET S,NLPG_BLPU B, FEATURE_CROSS_REFS FCR, LOCATIONS LOC
    		WHERE S.USRN=L.USRN
    		AND   B.UPRN=L.UPRN
    		AND FCR.FEATURE_ID=B.NLPG_ID
    		AND LOC.MSLINK=FCR.MSLINK;
    BEGIN
    	FOR NLPG IN NLPGCUR
    	LOOP
    		MSLINK_W(NLPG_COUNT) :=MSLINK;
    		SAON_W(NLPG_COUNT) :=SAON;
    		PAON_W(NLPG_COUNT):=PAON;
    		STREET_DESCRIPTOR_W(NLPG_COUNT):=STREET_DESCRIPTOR;
    		LOCALITY_NAME_W(NLPG_COUNT):=LOCALITY_NAME;
    		TOWN_NAME_W(NLPG_COUNT):=TOWN_NAME;
    		COUNTY_NAME_W(NLPG_COUNT):=COUNTY_NAME;
    		POSTCODE_W(NLPG_COUNT):=POSTCODE;
    		NLPG_COUNT:=NLPG_COUNT + 1;
    	END LOOP;
    END WRAPPACKSP;
    END WRAPPACK;
    /
    So the select statement now has aliases (as it didn't like that), and there are commas in with the parameters. Other than that it's the same. It seems to not like the _W fields in the LOOP statement, but I thought they had to match with the _W fields defined at the top (i.e. the result fields I want to pass back). Am I missing something (or everything maybe??)

    Thanks

  4. #4
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Something like this
    Code:
    ..
    MSLINK OUT MSLINK_W,
    ..
    is interpreted as.. variable MSLINK of OUT direction of DATATYPE MSLINK_W. And you're doing something like this
    Code:
    ..
    MSLINK_W(NLPG_COUNT) :=MSLINK;
    ..
    in your code. So, the error message is clear: you just can't assign values to a datatype. It is MSLINK that should be there. And even there, that would entirely fail, since you are using PL/SQL types that SQLServer "wont" see. You will have to use SQL types for that. Did you read what I said about doing this in some other way ?

  5. #5
    Join Date
    Aug 2004
    Location
    Manchester, UK
    Posts
    143
    Thanks for this. I already did it as a view but the response time is really bad, because it is through a linked server. After doing some searching on the web it turns out it is a common problem and the suggested solution is this way, with a package. I managed to get the package created now btw, without errors, but haven't ran it yet. If I get it to run and do the right thing, I'll post it on here.

    Thanks,
    Paula.

Posting Permissions

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