DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 14 of 14

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

  1. #11
    Join Date
    Nov 2001
    Posts
    16
    I copied and paste your scripts, still get error as follows. Is this
    related to version of Oracle? I use Oracle 8.0.5.

    Thanks for your help.

    SQL> CREATE OR REPLACE PACKAGE tokenize
    2 /*
    3 /*
    4 || Author: Matthew Oldham (m_oldham@yahoo.com)
    5 ||
    6 || Date: 11/05/2001
    7 ||
    8 || Overview: This package provides the ability to parse a given character value
    9 || and break it into tokens based on the given delimiter. The tokens
    10 || are stored in a PL/SQL table and accessed via a given index. An
    11 || initial call to TOKENIZE.SETTOKENS must be made to populate the
    12 || PL/SQL table with the desired string value followed by subsequent
    13 || calls to TOKENIZE.GETTOKEN to return the desired token value.
    14 || Example:
    15 || call TOKENIZE.SETTOKENS('This|is|a|string|','|'); --sets tokens
    16 || select TOKENIZE.GETTOKEN(1) from dual; -- returns 'This'
    17 || select TOKENIZE.GETTOKEN(4) from dual; -- returns 'string'
    18 || select TOKENIZE.GETTOKEN(5) from dual; -- returns NULL
    19 */
    20 IS
    21 TYPE tokentab IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
    22 PROCEDURE setTokens(p_string VARCHAR2, p_delimiter VARCHAR2);
    23 FUNCTION getToken(p_index PLS_INTEGER) RETURN VARCHAR2;
    24 FUNCTION getNumber RETURN NUMBER;
    25 PRAGMA RESTRICT_REFERENCES(getToken,WNDS,WNPS,RNDS);
    26 END tokenize;
    27 /
    SQL>
    SQL> CREATE OR REPLACE PACKAGE BODY tokenize
    2 IS
    3 tokens tokentab;
    4
    5 PROCEDURE setTokens
    6 (
    7 p_string VARCHAR2,
    8 p_delimiter VARCHAR2
    9 )
    10 IS
    11 v_token VARCHAR2(4000);
    12 v_ctr PLS_INTEGER := 1;
    13 BEGIN
    14 FOR i IN 1..LENGTH(p_string) LOOP
    15 IF SUBSTR(p_string,i,1) = p_delimiter THEN
    16 tokens(v_ctr) := v_token;
    17 v_ctr := v_ctr + 1;
    18 v_token := NULL;
    19 ELSIF i = LENGTH(p_string) THEN
    20 v_token := v_token || SUBSTR(p_string,i,1);
    21 tokens(v_ctr) := v_token;
    22 v_ctr := v_ctr + 1;
    23 v_token := NULL;
    24 ELSE
    25 v_token := v_token || SUBSTR(p_string,i,1);
    26 END IF;
    27 END LOOP;
    28 END setTokens;
    29
    30 FUNCTION getToken
    31 (
    32 p_index PLS_INTEGER
    33 )
    34 RETURN VARCHAR2
    35 IS
    36 BEGIN
    37 IF tokens.EXISTS(p_index) THEN
    38 RETURN tokens(p_index);
    39 ELSE
    40 RETURN NULL;
    41 END IF;
    42 END getToken;
    43
    44 FUNCTION getNumber
    45 RETURN NUMBER
    46 IS
    47 V_TOTAL NUMBER;
    48 BEGIN
    49 V_TOTAL:=tokens.COUNT;
    50 DBMS_OUTPUT.PUT_LINE('TOKEN COUNT'||to_char(V_TOTAL));
    51 return V_TOTAL;
    52 END getNumber;
    53
    54 END tokenize;
    55 /
    SQL> set serveroutput on
    SQL> execute TOKENIZE.SETTOKENS('This|is|a|string|','|');
    SQL>
    SQL> select tokenize.getnumber from dual;
    select tokenize.getnumber from dual
    *
    ERROR at line 1:
    ORA-06571: Function GETNUMBER does not guarantee not to update database

  2. #12
    Join Date
    Dec 2000
    Posts
    138
    yes it is. If you were in 8i (correct me if i am wrong) you wouldnt have faced this problem.

    Add this line in you package spec.
    PRAGMA RESTRICT_REFERENCES(getNumber,WNDS,WNPS,RNDS);
    and recompile.
    should work.
    hth

  3. #13
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    sure its a version problem.

    select * from v$version;
    chk ur version
    Cheers!
    OraKid.

  4. #14
    Join Date
    Nov 2001
    Posts
    16
    Thanks, it works.

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