Home » SQL & PL/SQL » SQL & PL/SQL » error when executing proc
error when executing proc [message #260522] Mon, 20 August 2007 04:05 Go to next message
soujanya_srk
Messages: 111
Registered: November 2006
Location: HYDERABAD
Senior Member
i have a proc like thi


CREATE OR REPLACE PROCEDURE List_To_Table (
    n_List   IN VARCHAR2,
    n_Dlim   IN VARCHAR2,
    n_TabLen OUT BINARY_INTEGER,
    n_Tab    OUT DBMS_UTILITY.Uncl_Array
) AS
    l_Dlim   VARCHAR2(1);
    l_start  BINARY_INTEGER := 1;
    l_index  BINARY_INTEGER;
    l_count  BINARY_INTEGER := 0;
    l_escape varchar2(1) := '\';
    l_list   varchar2(32767);
BEGIN
    -- Return if list is null
    IF LTRIM(RTRIM(n_List)) IS NULL THEN
        n_TabLen := 0;
        RETURN;
    END IF;
    -- Interpret delimiter
    IF n_Dlim IS NULL THEN
        l_Dlim := ',';
    ELSE
        l_Dlim := SUBSTR(n_Dlim,1,1);
    END IF;
    -- escape character for delimiter field (if the delimiter character itself part of the word)
    l_list := replace(n_list, l_escape||n_Dlim , chr(9));
    -- Loop for each element
    LOOP
        l_index := NVL(INSTR(n_List,l_Dlim,l_start),0);
        IF l_index = 0 THEN
            EXIT;
        END IF;
        l_count := l_count + 1;
--        n_Tab(l_count) := REPLACE(SUBSTR(n_List, l_start, l_index-l_start),'''','''''');
        n_Tab(l_count) := SUBSTR(n_List, l_start, l_index-l_start);
        n_Tab(l_count) := replace(n_Tab(l_count), chr(9), n_Dlim);
        l_start := l_index + 1;
    END LOOP;
    -- Last element
    n_Tab(l_count+1) := SUBSTR(n_List, l_start);
    n_Tab(l_count+1) := replace(n_Tab(l_count+1), chr(9), n_Dlim);
    n_TabLen := l_count+1;
END List_To_Table;



so when we pass a string with any delimiters, it will give out
the token numbers and the text

i tried to execute it

variable g_TabLen number;
variable g_Tab DBMS_UTILITY.Uncl_Array
exec List_To_Table('"1","1","21178","ipay col","ipaycol1","acme1",,,,,',',',:g_tab);




error is
Usage: VAR[IABLE] [ <variable> [ NUMBER | CHAR | CHAR (n [CHAR|BYTE]) |
VARCHAR2 (n [CHAR|BYTE]) | NCHAR | NCHAR (n) |
NVARCHAR2 (n) | CLOB | NCLOB | REFCURSOR |
BINARY_FLOAT | BINARY_DOUBLE ] ]

BEGIN List_To_Table('"1","1","21178","ipay col","ipaycol1","acme1",,,,,',',',:g_tab); END;

when i use

variable g_TabLen number;
variable g_Tab varchar2(1000);
exec List_To_Table('"1","1","21178","ipay col","ipaycol1","acme1",,,,,',',',:g_tab);

then i get

ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'LIST_TO_TABLE'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored



can anybody help me to execute this proc??






Re: error when executing proc [message #260526 is a reply to message #260522] Mon, 20 August 2007 04:13 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
you are not passing enough parameters to the procedure.
If you parse this list of parameters:
('"1","1","21178","ipay col","ipaycol1","acme1",,,,,',',',:g_tab)

you get
1) '"1","1","21178","ipay col","ipaycol1","acme1",,,,,'
2) ','
3) :g_tab
as the parameters you're passing. You need an extra OUT parameter.
Re: error when executing proc [message #261327 is a reply to message #260522] Wed, 22 August 2007 05:57 Go to previous messageGo to next message
darshanmeel
Messages: 44
Registered: June 2007
Location: India
Member
Hi

I think you hvae missed third paratameter i.e. g_tablen.Please include that as well.Query should run fine.Please try the code below.

variable g_TabLen number;
variable g_Tab DBMS_UTILITY.Uncl_Array
exec List_To_Table('"1","1","21178","ipay col","ipaycol1","acme1",,,,,',',',:g_Tablen,:g_tab);

Re: error when executing proc [message #261329 is a reply to message #261327] Wed, 22 August 2007 05:59 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why don't you want to read and follow How to format your posts?

Regards
Michel
Previous Topic: procedure
Next Topic: Lowest Cache value
Goto Forum:
  


Current Time: Sun Dec 04 10:43:29 CST 2016

Total time taken to generate the page: 0.03795 seconds