PL/SQL TABLE wrong number or types of arguments
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: PL/SQL TABLE wrong number or types of arguments

  1. #1
    Join Date
    Nov 2001
    Posts
    16

    PL/SQL TABLE wrong number or types of arguments

    Hi there,

    I add one funtion to try to get the number of tokens based on the given delimiter. The funtion is based on Matthew Oldham (m_oldham@yahoo.com)'s function. when I tried to compile it, I got
    error. PLS-00306: wrong number or types of arguments in call to
    'PUT_LINE'. If I remove the debug line DBMS_OUTPUT.PUT_LINE, the
    package can be compiled successfully, but when I call the function
    TOKENIZE.GETNUMBER, I get the same error. Below is the code.
    tokens.COUNT is a numerical variable, I don't see any place why it does not working.

    Your help is very appreciated.

    Thanks

    solarsys


    CREATE OR REPLACE PACKAGE tokenize
    /*
    /*
    || Author: Matthew Oldham (m_oldham@yahoo.com)
    ||
    || Date: 11/05/2001
    ||
    || Overview: This package provides the ability to parse a given character value
    || and break it into tokens based on the given delimiter. The tokens
    || are stored in a PL/SQL table and accessed via a given index. An
    || initial call to TOKENIZE.SETTOKENS must be made to populate the
    || PL/SQL table with the desired string value followed by subsequent
    || calls to TOKENIZE.GETTOKEN to return the desired token value.
    || Example:
    || call TOKENIZE.SETTOKENS('This|is|a|string|','|'); --sets tokens
    || select TOKENIZE.GETTOKEN(1) from dual; -- returns 'This'
    || select TOKENIZE.GETTOKEN(4) from dual; -- returns 'string'
    || select TOKENIZE.GETTOKEN(5) from dual; -- returns NULL
    */
    IS
    TYPE tokentab IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
    PROCEDURE setTokens(p_string VARCHAR2, p_delimiter VARCHAR2);
    FUNCTION getToken(p_index PLS_INTEGER) RETURN VARCHAR2;
    FUNCTION getNumber(P_NUMBER OUT NUMBER) RETURN NUMBER;
    PRAGMA RESTRICT_REFERENCES(getToken,WNDS,WNPS,RNDS);
    END tokenize;
    /
    CREATE OR REPLACE PACKAGE BODY tokenize
    IS
    tokens tokentab;

    PROCEDURE setTokens
    (
    p_string VARCHAR2,
    p_delimiter VARCHAR2
    )
    IS
    v_token VARCHAR2(4000);
    v_ctr PLS_INTEGER := 1;
    BEGIN
    FOR i IN 1..LENGTH(p_string) LOOP
    IF SUBSTR(p_string,i,1) = p_delimiter THEN
    tokens(v_ctr) := v_token;
    v_ctr := v_ctr + 1;
    v_token := NULL;
    ELSIF i = LENGTH(p_string) THEN
    v_token := v_token || SUBSTR(p_string,i,1);
    tokens(v_ctr) := v_token;
    v_ctr := v_ctr + 1;
    v_token := NULL;
    ELSE
    v_token := v_token || SUBSTR(p_string,i,1);
    END IF;
    END LOOP;
    END setTokens;

    FUNCTION getToken
    (
    p_index PLS_INTEGER
    )
    RETURN VARCHAR2
    IS
    BEGIN
    IF tokens.EXISTS(p_index) THEN
    RETURN tokens(p_index);
    ELSE
    RETURN NULL;
    END IF;
    END getToken;

    FUNCTION getNumber
    (p_number OUT NUMBER)
    RETURN NUMBER
    IS
    V_TOTAL NUMBER;
    BEGIN
    V_TOTAL:=tokens.COUNT;
    DBMS_OUTPUT.PUT_LINE('TOKEN COUNT', V_TOTAL);
    return V_TOTAL;
    END getNumber;


    END tokenize;
    /

  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    The line : DBMS_OUTPUT.PUT_LINE('TOKEN COUNT', V_TOTAL);

    has to be:

    DBMS_OUTPUT.PUT_LINE('TOKEN COUNT'||to_char(V_TOTAL));

  3. #3
    Join Date
    Dec 2000
    Posts
    138
    DBMS_OUTPUT.PUT_LINE('TOKEN COUNT', V_TOTAL);
    change this to
    DBMS_OUTPUT.PUT_LINE('TOKEN COUNT: '|| V_TOTAL);
    and it would compile

    it works fine as it says in the how to at the begining of the code
    i did it like
    BEGIN
    TOKENIZE.SETTOKENS('This;is;a;string;',';');
    END;
    and it worked fine and am able to see the count.
    -dharma

  4. #4
    Join Date
    Nov 2001
    Posts
    16
    Thanks, it compiled. But when I tried to call getNumber, still get error.

    SQL> execute TOKENIZE.SETTOKENS('This|is|a|string|','|');

    PL/SQL procedure successfully completed.

    SQL> select tokenize.getnumber from dual;
    select tokenize.getnumber from dual
    *
    ERROR at line 1:
    ORA-06553: PLS-306: wrong number or types of arguments in call to 'GETNUMBER'


    SQL> select tokenize.gettoken(1) from dual;

    TOKENIZE.GETTOKEN(1)
    --------------------------------------------------------------------------------
    This

  5. #5
    Join Date
    Dec 2000
    Posts
    138
    the get_number has a out parameter, remove it and compile and then run.
    hth
    -dharma

  6. #6
    Join Date
    Nov 2001
    Posts
    16
    Removed OUT, compiled , still get error.

    SQL> execute TOKENIZE.SETTOKENS('This|is|a|string|','|');

    PL/SQL procedure successfully completed.

    SQL> select tokenize.getnumber from dual;
    select tokenize.getnumber from dual
    *
    ERROR at line 1:
    ORA-06553: PLS-306: wrong number or types of arguments in call to 'GETNUMBER'

    SQL> select tokenize.gettoken(2) from dual;

    TOKENIZE.GETTOKEN(2)
    --------------------------------------------------------------------------------
    is

  7. #7
    Join Date
    Dec 2000
    Posts
    138
    You have to edit the Package specification also. and compile
    -dharma

  8. #8
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    Check ur getNumber FUNCTION,

    Do this:

    REPLACE THIS "getNumber(P_NUMBER OUT NUMBER)" with
    "FUNCTION getNumber RETURN NUMBER;"
    in ur package header.

    CREATE OR REPLACE PACKAGE tokenize
    IS
    TYPE tokentab IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
    PROCEDURE setTokens(p_string VARCHAR2, p_delimiter VARCHAR2);
    FUNCTION getToken(p_index PLS_INTEGER) RETURN VARCHAR2;
    FUNCTION getNumber RETURN NUMBER;
    PRAGMA RESTRICT_REFERENCES(getToken,WNDS,WNPS,RNDS);
    END tokenize;
    /

    2)
    Paste this in ur package body

    REPLACE THIS "getNumber(P_NUMBER OUT NUMBER)"

    FUNCTION getNumber
    RETURN NUMBER
    IS
    V_TOTAL NUMBER;
    BEGIN
    V_TOTAL:=tokens.COUNT;
    DBMS_OUTPUT.PUT_LINE('TOKEN COUNT' || V_TOTAL);
    return V_TOTAL;
    END getNumber;

    INSTEAD of
    REMOVE this "(p_number OUT NUMBER)"

    FUNCTION getNumber
    (p_number OUT NUMBER)
    RETURN NUMBER
    IS
    V_TOTAL NUMBER;
    BEGIN
    V_TOTAL:=tokens.COUNT;
    DBMS_OUTPUT.PUT_LINE('TOKEN COUNT', V_TOTAL);
    return V_TOTAL;
    END getNumber;

    Cheers!
    Cheers!
    OraKid.

  9. #9
    Join Date
    Nov 2001
    Posts
    16
    That's exactly what I did. The code is below. Still get the error.

    CREATE OR REPLACE PACKAGE tokenize
    /*
    /*
    || Author: Matthew Oldham (m_oldham@yahoo.com)
    ||
    || Date: 11/05/2001
    ||
    || Overview: This package provides the ability to parse a given character value
    || and break it into tokens based on the given delimiter. The tokens
    || are stored in a PL/SQL table and accessed via a given index. An
    || initial call to TOKENIZE.SETTOKENS must be made to populate the
    || PL/SQL table with the desired string value followed by subsequent
    || calls to TOKENIZE.GETTOKEN to return the desired token value.
    || Example:
    || call TOKENIZE.SETTOKENS('This|is|a|string|','|'); --sets tokens
    || select TOKENIZE.GETTOKEN(1) from dual; -- returns 'This'
    || select TOKENIZE.GETTOKEN(4) from dual; -- returns 'string'
    || select TOKENIZE.GETTOKEN(5) from dual; -- returns NULL
    */
    IS
    TYPE tokentab IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
    PROCEDURE setTokens(p_string VARCHAR2, p_delimiter VARCHAR2);
    FUNCTION getToken(p_index PLS_INTEGER) RETURN VARCHAR2;
    FUNCTION getNumber(P_NUMBER NUMBER) RETURN NUMBER;
    PRAGMA RESTRICT_REFERENCES(getToken,WNDS,WNPS,RNDS);
    END tokenize;
    /
    CREATE OR REPLACE PACKAGE BODY tokenize
    IS
    tokens tokentab;

    PROCEDURE setTokens
    (
    p_string VARCHAR2,
    p_delimiter VARCHAR2
    )
    IS
    v_token VARCHAR2(4000);
    v_ctr PLS_INTEGER := 1;
    BEGIN
    FOR i IN 1..LENGTH(p_string) LOOP
    IF SUBSTR(p_string,i,1) = p_delimiter THEN
    tokens(v_ctr) := v_token;
    v_ctr := v_ctr + 1;
    v_token := NULL;
    ELSIF i = LENGTH(p_string) THEN
    v_token := v_token || SUBSTR(p_string,i,1);
    tokens(v_ctr) := v_token;
    v_ctr := v_ctr + 1;
    v_token := NULL;
    ELSE
    v_token := v_token || SUBSTR(p_string,i,1);
    END IF;
    END LOOP;
    END setTokens;

    FUNCTION getToken
    (
    p_index PLS_INTEGER
    )
    RETURN VARCHAR2
    IS
    BEGIN
    IF tokens.EXISTS(p_index) THEN
    RETURN tokens(p_index);
    ELSE
    RETURN NULL;
    END IF;
    END getToken;

    FUNCTION getNumber
    (p_number NUMBER)
    RETURN NUMBER
    IS
    V_TOTAL NUMBER;
    BEGIN
    V_TOTAL:=tokens.COUNT;
    DBMS_OUTPUT.PUT_LINE('TOKEN COUNT'||to_char(V_TOTAL));
    return V_TOTAL;
    END getNumber;


    END tokenize;
    /

  10. #10
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    Do This:
    It works fine for me.

    Create this package and execute:
    --------------------------------
    CREATE OR REPLACE PACKAGE tokenize
    /*
    /*
    || Author: Matthew Oldham (m_oldham@yahoo.com)
    ||
    || Date: 11/05/2001
    ||
    || Overview: This package provides the ability to parse a given character value
    || and break it into tokens based on the given delimiter. The tokens
    || are stored in a PL/SQL table and accessed via a given index. An
    || initial call to TOKENIZE.SETTOKENS must be made to populate the
    || PL/SQL table with the desired string value followed by subsequent
    || calls to TOKENIZE.GETTOKEN to return the desired token value.
    || Example:
    || call TOKENIZE.SETTOKENS('This|is|a|string|','|'); --sets tokens
    || select TOKENIZE.GETTOKEN(1) from dual; -- returns 'This'
    || select TOKENIZE.GETTOKEN(4) from dual; -- returns 'string'
    || select TOKENIZE.GETTOKEN(5) from dual; -- returns NULL
    */
    IS
    TYPE tokentab IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
    PROCEDURE setTokens(p_string VARCHAR2, p_delimiter VARCHAR2);
    FUNCTION getToken(p_index PLS_INTEGER) RETURN VARCHAR2;
    FUNCTION getNumber RETURN NUMBER;
    PRAGMA RESTRICT_REFERENCES(getToken,WNDS,WNPS,RNDS);
    END tokenize;
    /

    ge created.


    CREATE OR REPLACE PACKAGE BODY tokenize
    IS
    tokens tokentab;

    PROCEDURE setTokens
    (
    p_string VARCHAR2,
    p_delimiter VARCHAR2
    )
    IS
    v_token VARCHAR2(4000);
    v_ctr PLS_INTEGER := 1;
    BEGIN
    FOR i IN 1..LENGTH(p_string) LOOP
    IF SUBSTR(p_string,i,1) = p_delimiter THEN
    tokens(v_ctr) := v_token;
    v_ctr := v_ctr + 1;
    v_token := NULL;
    ELSIF i = LENGTH(p_string) THEN
    v_token := v_token || SUBSTR(p_string,i,1);
    tokens(v_ctr) := v_token;
    v_ctr := v_ctr + 1;
    v_token := NULL;
    ELSE
    v_token := v_token || SUBSTR(p_string,i,1);
    END IF;
    END LOOP;
    END setTokens;

    FUNCTION getToken
    (
    p_index PLS_INTEGER
    )
    RETURN VARCHAR2
    IS
    BEGIN
    IF tokens.EXISTS(p_index) THEN
    RETURN tokens(p_index);
    ELSE
    RETURN NULL;
    END IF;
    END getToken;

    FUNCTION getNumber
    RETURN NUMBER
    IS
    V_TOTAL NUMBER;
    BEGIN
    V_TOTAL:=tokens.COUNT;
    DBMS_OUTPUT.PUT_LINE('TOKEN COUNT'||to_char(V_TOTAL));
    return V_TOTAL;
    END getNumber;

    END tokenize;
    /

    Results after execution:
    ------------------------
    SQL> set serveroutput on
    SQL> execute TOKENIZE.SETTOKENS('This|is|a|string|','|');

    PL/SQL procedure successfully completed.

    SQL> select tokenize.getnumber from dual;

    GETNUMBER
    ----------
    4

    SQL> execute TOKENIZE.SETTOKENS('This|is|a|string||','|');
    TOKEN COUNT4

    PL/SQL procedure successfully completed.

    SQL> select tokenize.getnumber from dual;

    GETNUMBER
    ----------
    5

    SQL> execute TOKENIZE.SETTOKENS('This|is|a|string||','|');
    TOKEN COUNT5

    PL/SQL procedure successfully completed.

    SQL> select tokenize.gettoken(2) from dual;

    TOKENIZE.GETTOKEN(2)
    ----------------------------------------------------------------------------
    is

    U hvn't read my prev. mail properly

    Cheers!
    Cheers!
    OraKid.

Posting Permissions

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



Click Here to Expand Forum to Full Width