-
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;
/
-
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!
Cheers!
OraKid.
-
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;
/
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|