Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Aug 2004
    Location
    Manchester, UK
    Posts
    143

    Unanswered: Calling an Oracle package with output parameters

    Hi all,

    I am trying to call a package that returns output fields. I have tried:

    Call wrappack.wrappacksp (output1, output2)

    but it doesn't work. Does anyone know how to do this as I have searched the web but can't seem to get a definitive answer.

    Thanks

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Declare
    String Varchar2(20);
    Begin
    Wrappack.wrappacksp(string);
    End;
    /
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Aug 2004
    Location
    Manchester, UK
    Posts
    143
    I'm obviosuly doing something totally stupid here, it really can't be that difficult surely.

    In SQLPLUS I tried this :

    Code:
    DECLARE
    MSLINK NUMBER;
    SAON VARCHAR2(100);
    PAON VARCHAR2(100);
    STREET VARCHAR2(100);
    LOCALITY VARCHAR2(35);
    TOWN VARCHAR2(30);
    COUNTY VARCHAR2(30);
    POSTCODE VARCHAR2(8);
    BEGIN
    WRAPPACK.WRAPPACKSP(MSLINK,SAON,PAON,STREET,LOCALITY,TOWN,COUNTY,POSTCODE);
    END;
    /
    which lists all my output parameters.

    Then I tried this:

    Code:
    DECLARE
    STRING VARCHAR2(450);
    BEGIN
    WRAPPACK.WRAPPACKSP(STRING);
    END;
    /
    which I hoped might put all my output parms in one string, but I just get error messages like this:

    Code:
    ERROR at line 4:
    ORA-06550: line 4, column 1:
    PLS-00306: wrong number or types of arguments in call to 'WRAPPACKSP'
    ORA-06550: line 4, column 1:
    PL/SQL: Statement ignored
    Am I doing something really obviously wrong.

    Once I have this working in SQLPLUS I need to run it from a view on SQL SERVER using a linked server.

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    How do you know that it doesn't work? If you want to see your output parameters (which by default you will not)do the following

    Code:
    set serveroutput on
    
    DECLARE
      MSLINK NUMBER;
      SAON VARCHAR2(100);
      PAON VARCHAR2(100);
      STREET VARCHAR2(100);
      LOCALITY VARCHAR2(35);
      TOWN VARCHAR2(30);
      COUNTY VARCHAR2(30);
      POSTCODE VARCHAR2(8);
    BEGIN
      WRAPPACK.WRAPPACKSP(MSLINK,SAON,PAON,STREET,LOCALITY,TOWN,COUNTY,POSTCODE);
      dbms_output.put_line('MSLINK   : '||to_char(MSLINK));
      dbms_output.put_line('SAON     :'||SAON);
      dbms_output.put_line('PAON     :'||PAON);
      dbms_output.put_line('STREET   :'||STREET);
      dbms_output.put_line('LOCALITY :'||LOCALITY);
      dbms_output.put_line('TOWN     :'||TOWN);
      dbms_output.put_line('COUNTY   :'||COUNTY);
      dbms_output.put_line('POSTCODE :'||POSTCODE);
    END;
    /
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Aug 2004
    Location
    Manchester, UK
    Posts
    143
    I know it doesn't work because I get the error message above. I tried this last piece of code, with set serveroutput on, but still get the same error. I definitely have my list of output parms correct though, and so I'm still confused.

    I'll keep trying.

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    If your procedure has 8 distinct parameters, what makes you think that you can use only one and it will magically combine the output parameters into one return string.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Aug 2004
    Location
    Manchester, UK
    Posts
    143
    I did not assume it would 'magically' put them into a string, it's called clutching at straws. As you can see from my previous post I have tried all the other alternatives.

    Just because I do not know how to do this does not indicate a lack of general intelligence. People post on this site because they need assistance. And sometimes they can offer assistance. Nobody needs to be given abuse. I'd rather no-one reply to my message than someone reply with a comment aimed at making me feel small.

    If there is anybody else out there who could offer some assistance it would be very much appreciated.

  8. #8
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    In SQL*Plus enter

    DESCRIBE WRAPPACK

    Copy the part that describes the WRAPPACKSP procedure and post it here.

  9. #9
    Join Date
    Aug 2004
    Location
    Manchester, UK
    Posts
    143
    Code:
    PROCEDURE WRAPPACKSP
     Argument Name                  Type                    In/Out Default?
     ------------------------------ ----------------------- ------ --------
     MSLINK_RES                     TABLE OF NUMBER         OUT
     SAON_RES                       TABLE OF VARCHAR2(100)  OUT
     PAON_RES                       TABLE OF VARCHAR2(100)  OUT
     STREET_DESCRIPTOR_RES          TABLE OF VARCHAR2(100)  OUT
     LOCALITY_NAME_RES              TABLE OF VARCHAR2(35)   OUT
     TOWN_NAME_RES                  TABLE OF VARCHAR2(30)   OUT
     COUNTY_NAME_RES                TABLE OF VARCHAR2(30)   OUT
     POSTCODE_RES                   TABLE OF VARCHAR2(8)    OUT
    Thank you,
    Paula

  10. #10
    Join Date
    Aug 2004
    Location
    Manchester, UK
    Posts
    143
    Here is the last version I tried: But still no luck, same error - wrong number or type of argument.

    Code:
    set serveroutput on
    
    DECLARE
      MSLINK_RES NUMBER;
      SAON_RES VARCHAR2(100);
      PAON_RES VARCHAR2(100);
      STREET_DESCRIPTOR_RES VARCHAR2(100);
      LOCALITY_NAME_RES VARCHAR2(35);
      TOWN_NAME_RES VARCHAR2(30);
      COUNTY_NAME_RES VARCHAR2(30);
      POSTCODE_RES VARCHAR2(8);
    BEGIN
    	WRAPPACK.WRAPPACKSP(MSLINK_RES,SAON_RES,PAON_RES,STREET_DESCRIPTOR_RES,LOCALITY_NAME_RES,TOWN_NAME_RES,COUNTY_NAME_RES,POSTCODE_RES);
      dbms_output.put_line('MSLINK_RES   : '||to_char(MSLINK_RES));
      dbms_output.put_line('SAON_RES     :'||SAON_RES);
      dbms_output.put_line('PAON_RES     :'||PAON_RES);
      dbms_output.put_line('STREET_DESCRIPTOR_RES   :'||STREET_DESCRIPTOR_RES);
      dbms_output.put_line('LOCALITY_NAME_RES :'||LOCALITY_NAME_RES);
      dbms_output.put_line('TOWN_NAME_RES     :'||TOWN_NAME_RES);
      dbms_output.put_line('COUNTY_NAME_RES   :'||COUNTY_NAME_RES);
      dbms_output.put_line('POSTCODE_RES :'||POSTCODE_RES);
    END;
    /set serveroutput on
    
    DECLARE
      MSLINK_RES NUMBER;
      SAON_RES VARCHAR2(100);
      PAON_RES VARCHAR2(100);
      STREET_DESCRIPTOR_RES VARCHAR2(100);
      LOCALITY_NAME_RES VARCHAR2(35);
      TOWN_NAME_RES VARCHAR2(30);
      COUNTY_NAME_RES VARCHAR2(30);
      POSTCODE_RES VARCHAR2(8);
    BEGIN
    	WRAPPACK.WRAPPACKSP(MSLINK_RES,SAON_RES,PAON_RES,STREET_DESCRIPTOR_RES,LOCALITY_NAME_RES,TOWN_NAME_RES,COUNTY_NAME_RES,POSTCODE_RES);
      dbms_output.put_line('MSLINK_RES   : '||to_char(MSLINK_RES));
      dbms_output.put_line('SAON_RES     :'||SAON_RES);
      dbms_output.put_line('PAON_RES     :'||PAON_RES);
      dbms_output.put_line('STREET_DESCRIPTOR_RES   :'||STREET_DESCRIPTOR_RES);
      dbms_output.put_line('LOCALITY_NAME_RES :'||LOCALITY_NAME_RES);
      dbms_output.put_line('TOWN_NAME_RES     :'||TOWN_NAME_RES);
      dbms_output.put_line('COUNTY_NAME_RES   :'||COUNTY_NAME_RES);
      dbms_output.put_line('POSTCODE_RES :'||POSTCODE_RES);
    END;
    /

  11. #11
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    TABLE OF NUMBER is not the same as NUMBER. For example:

    Code:
    CREATE OR REPLACE PACKAGE banana
    AS
        TYPE number_tt IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
        TYPE varchar2_tt IS TABLE OF VARCHAR2(5000) INDEX BY PLS_INTEGER;
    
        PROCEDURE test
            ( p_number   OUT NUMBER
            , p_numbers  OUT number_tt
            , p_strings  OUT varchar2_tt );
    END banana;
    /
    
    
    SQL> desc banana
    PROCEDURE TEST
     Argument Name                  Type                    In/Out Default?
     ------------------------------ ----------------------- ------ --------
     P_NUMBER                       NUMBER                  OUT
     P_NUMBERS                      TABLE OF NUMBER         OUT
     P_STRINGS                      TABLE OF VARCHAR2(5000) OUT
    The variables you pass need to be the types that are declared in the package. In my example that would be something like

    Code:
    DECLARE
        v_number  NUMBER;
        v_numbers banana.number_tt;
        v_strings banana.varchar2_tt;
    BEGIN
        banana.test
        ( v_number
        , v_numbers
        , v_strings );
    END;
    /

  12. #12
    Join Date
    Aug 2004
    Location
    Manchester, UK
    Posts
    143
    Ok, I think I'm starting to get it, but I'm still not getting it to work. I have made the amendments as you suggest but still get an error. Here is the package and package body etc:

    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_RES OUT MSLINK_W,
    SAON_RES OUT SAON_W,
    PAON_RES OUT PAON_W,
    STREET_DESCRIPTOR_RES OUT STREET_DESCRIPTOR_W,
    LOCALITY_NAME_RES OUT LOCALITY_NAME_W,
    TOWN_NAME_RES OUT TOWN_NAME_W,
    COUNTY_NAME_RES OUT COUNTY_NAME_W,
    POSTCODE_RES OUT POSTCODE_W
    );
    END WRAPPACK;
    /
    CREATE PACKAGE BODY WRAPPACK
    AS
    PROCEDURE WRAPPACKSP
    (
    MSLINK_RES OUT MSLINK_W,
    SAON_RES OUT SAON_W,
    PAON_RES OUT PAON_W,
    STREET_DESCRIPTOR_RES OUT STREET_DESCRIPTOR_W,
    LOCALITY_NAME_RES OUT LOCALITY_NAME_W,
    TOWN_NAME_RES OUT TOWN_NAME_W,
    COUNTY_NAME_RES OUT COUNTY_NAME_W,
    POSTCODE_RES 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_RES(NLPG_COUNT) :=NLPG.MSLINK;
    		SAON_RES(NLPG_COUNT) :=NLPG.SAON;
    		PAON_RES(NLPG_COUNT):=NLPG.PAON;
    		STREET_DESCRIPTOR_RES(NLPG_COUNT):=NLPG.STREET_DESCRIPTOR;
    		LOCALITY_NAME_RES(NLPG_COUNT):=NLPG.LOCALITY_NAME;
    		TOWN_NAME_RES(NLPG_COUNT):=NLPG.TOWN_NAME;
    		COUNTY_NAME_RES(NLPG_COUNT):=NLPG.COUNTY_NAME;
    		POSTCODE_RES(NLPG_COUNT):=NLPG.POSTCODE;
    		NLPG_COUNT:=NLPG_COUNT + 1;
    	END LOOP;
    END WRAPPACKSP;
    END WRAPPACK;
    /
    And here is the call to it:

    Code:
    set serveroutput on
    
    DECLARE
      MSLINK MSLINK_W;
      SAON SAON_W;
      PAON PAON_W;
      STREET_DESCRIPTOR STREET_DESCRIPTOR_W;
      LOCALITY_NAME LOCALITY_NAME_W;
      TOWN_NAME TOWN_NAME_W;
      COUNTY_NAME COUNTY_NAME_W;
      POSTCODE POSTCODE_W;
    BEGIN
    	WRAPPACK.WRAPPACKSP(MSLINK,SAON,PAON,STREET_DESCRIPTOR,LOCALITY_NAME,TOWN_NAME,COUNTY_NAME,POSTCODE);
      END;
    /

    And here is the error message:

    Code:
    ERROR at line 2:
    ORA-06550: line 2, column 10:
    PLS-00201: identifier 'MSLINK_W' must be declared
    ORA-06550: line 2, column 10:
    PL/SQL: Item ignored
    It does not recognise the variables but as far as I can see I have declared them properly.

    Any more ideas?

    Many thanks for your patience here!

    Paula

  13. #13
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    'MSLINK_W' has to be qualified as WRAPPACK.MSLINK_W. For example,

    Code:
    DECLARE
        v_mslink_res            wrappack.mslink_w;
        v_saon_res              wrappack.saon_w;
        v_paon_res              wrappack.paon_w;
        v_street_descriptor_res wrappack.street_descriptor_w;
        v_locality_name_res     wrappack.locality_name_w;
        v_town_name_res         wrappack.town_name_w;
        v_county_name_res       wrappack.county_name_w;
        v_postcode_res          wrappack.postcode_w;
    BEGIN
        wrappack.wrappacksp
        ( v_mslink_res
        , v_saon_res
        , v_paon_res
        , v_street_descriptor_res
        , v_locality_name_res
        , v_town_name_res
        , v_county_name_res
        , v_postcode_res );
    END;
    btw looking at the procedure, I'm wondering why it returns eight separate arrays instead of one array of records.

  14. #14
    Join Date
    Aug 2004
    Location
    Manchester, UK
    Posts
    143
    Ok, I did as you suggested and it now runs fine. Except it still does not output any data.

    Also, I don't really know why it does separate arrays, I've taken it from the web and modified it, but I can't say I have much of an understanding of PL SQL programming as you may have guessed by now.

    How would I do it as one array of records? Just one 'table of' statement?

    Could this be why it won't display the results to screen?

  15. #15
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    It doesn't write anything to screen because unless I'm missing it there is no code to do so.

    An "array of records" version might look something like this:

    Code:
    CREATE OR REPLACE PACKAGE wrappack AS
        TYPE address_type IS RECORD
        ( mslink            NUMBER
        , saon              VARCHAR2(100)
        , paon              VARCHAR2(100)
        , street_descriptor VARCHAR2(100)
        , locality_name     VARCHAR2(35)
        , town_name         VARCHAR2(30)
        , county_name       VARCHAR2(30)
        , postcode          VARCHAR2(8) );
    
        -- PLS_INTEGER was theoretically the successor to BINARY_INTEGER,
        -- though in practice the compiler substitutes PLS_INTEGER anyway.
        -- PLS_INTEGER still gives a more sophisticated feel IMHO.
        TYPE address_tt IS TABLE OF address_type INDEX BY PLS_INTEGER;
    
        PROCEDURE wrappacksp
            ( p_addresses_out OUT address_tt );
    END wrappack;
    /
    
    CREATE OR REPLACE PACKAGE BODY wrappack AS
    
        PROCEDURE wrappacksp
            ( p_addresses_out OUT address_tt )
        IS
            v_nlpg_count PLS_INTEGER := 0;  -- Count starts at 0 not 1
        BEGIN
            -- Example of setting an element of an array of records:
            p_addresses_out(1).mslink := 42;
    
            -- Simple Example of BULK COLLECT
            -- (Note however there is no LIMIT clause with SELECT INTO:
            -- for any serious data volumes you would need to think about how much data
            -- you want to load into session memory at a time)
            SELECT 1
                 , 'hatstand'
                 , 'banana'
                 , 'lively'
                 , 'Harlesden'
                 , 'London'
                 , 'Hazzard'
                 , 'W94 1XY'
            BULK COLLECT INTO p_addresses_out
            FROM  dual;
    
            v_nlpg_count := p_addresses_out.COUNT;
    
            DBMS_OUTPUT.PUT_LINE('We captured ' || v_nlpg_count || ' records');
        END;
    END wrappack;
    /
    Test block:

    Code:
    DECLARE
        v_addresses wrappack.address_tt;
    BEGIN
        wrappack.wrappacksp(v_addresses);
        
        DBMS_OUTPUT.PUT_LINE('We retrieved ' || v_addresses.COUNT || ' records');
    END;
    /
    I've only shown dummy code for the procedure because I don't know what it is supposed to do and I don't have your tables.
    Last edited by WilliamR; 10-31-06 at 13:31.

Posting Permissions

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