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 ([email protected])'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 ([email protected])
||
|| 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;
/

The line : DBMS_OUTPUT.PUT_LINE('TOKEN COUNT', V_TOTAL);

has to be:

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

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

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

the get_number has a out parameter, remove it and compile and then run.
hth
-dharma

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

You have to edit the Package specification also. and compile
-dharma

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!

That's exactly what I did. The code is below. Still get the error.

CREATE OR REPLACE PACKAGE tokenize
/*
/*
|| Author: Matthew Oldham ([email protected])
||
|| 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;
/

Do This:
It works fine for me.

Create this package and execute:
--------------------------------
CREATE OR REPLACE PACKAGE tokenize
/*
/*
|| Author: Matthew Oldham ([email protected])
||
|| 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!

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 ([email protected])
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

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

sure its a version problem.

select * from v$version;
chk ur version

Thanks, it works.