Home » SQL & PL/SQL » SQL & PL/SQL » Getting wrong type of arguments (Oracle 10.2.0.3)
Getting wrong type of arguments [message #359804] Tue, 18 November 2008 04:25 Go to next message
avik2009
Messages: 61
Registered: November 2008
Member

create or replace PROCEDURE Get_TXT_Count
   (
    in_txt       IN  varChar2,
    txtCount     OUT Number,
    o_msg       OUT VARCHAR2
   )
   IS
       v_words      varchar2(4000);
       v_count      Number;
       v_position        number;
   BEGIN
     
      IF substr(in_txt,LENGTH(in_txt),1) != ',' THEN
          v_words := in_txt||',';
      ELSE
          v_words := in_txt;
      END IF;
      v_count := 1;
      v_position := INSTR(v_words,',',1,v_count);
      WHILE v_position <> 0 LOOP
        BEGIN
            SELECT count(*)
            INTO txtCount(v_count)
            FROM VW_TXT_TBL
            WHERE word_txt = SUBSTR(UPPER(GET_ELEMENT(v_words,',',v_count))||
                                    '                              ',1,30);
            o_msg(v_count) := ' ';
        EXCEPTION
            WHEN others THEN
                txtCount(v_count) := 0;
                o_msg(v_count) := SQLERRM;
        END;
        v_count := v_count + 1;
        v_position := INSTR(v_words,',',1,v_count);
      END LOOP;
   EXCEPTION
       WHEN others THEN
           txtCount(1) := 0;
           o_msg(1) := SQLERRM;
   END Get_TXT_Count;



I tried to execute but getting error: Wrong type of arguments.
Xant figure out why.

    DECLARE 

    TYPE T_OutputNbr IS TABLE OF NUMBER
      INDEX BY BINARY_INTEGER;
    TYPE T_OutputTxt IS TABLE OF Varchar2(4000)
      INDEX BY BINARY_INTEGER;
    in_txt      varChar2(30);
    txtCount    T_OutputNbr;
    o_msg       T_OutputTxt;
    
    begin
    in_txt  := 'FGHJKUL';
    get_TXT_count(in_txt ,txtCount,o_msg );
    END;
    


Re: Getting wrong type of arguments [message #359807 is a reply to message #359804] Tue, 18 November 2008 04:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
txtCount OUT Number,

Quote:
txtCount T_OutputNbr;

Not the same type, doesn't it?

Regards
Michel
Re: Getting wrong type of arguments [message #359808 is a reply to message #359807] Tue, 18 November 2008 04:29 Go to previous messageGo to next message
avik2009
Messages: 61
Registered: November 2008
Member
Yes it is. What modifications need to be done.
I am not being able to figure it out.
Re: Getting wrong type of arguments [message #359813 is a reply to message #359808] Tue, 18 November 2008 04:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Change the one to fit the output of the procedure you want to write.

Regards
Michel
Re: Getting wrong type of arguments [message #359816 is a reply to message #359813] Tue, 18 November 2008 04:35 Go to previous messageGo to next message
avik2009
Messages: 61
Registered: November 2008
Member
You meant the Type defination should be removed?
Re: Getting wrong type of arguments [message #359823 is a reply to message #359816] Tue, 18 November 2008 05:04 Go to previous messageGo to next message
avik2009
Messages: 61
Registered: November 2008
Member
Do I need to modify the procedure or need to modify the calling pl/sql. Appreciate your response.
Re: Getting wrong type of arguments [message #359833 is a reply to message #359804] Tue, 18 November 2008 05:43 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
That would depend on whether you need to return a number or a table type. You can use either and we can't tell you which you need.
The important thing is that whichever you decide to use the type you pass to the procedure is the same as the type of it's parameter.
Re: Getting wrong type of arguments [message #359887 is a reply to message #359833] Tue, 18 November 2008 08:05 Go to previous messageGo to next message
avik2009
Messages: 61
Registered: November 2008
Member
Can I update the procedure with TYPE as IN parameter? What are the possible way or what code need to be modified in my procedure or calling pl/sql code?
Re: Getting wrong type of arguments [message #359889 is a reply to message #359887] Tue, 18 November 2008 08:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Only YOU know what you want to do with this procedure.
Until you explain this we can't help.

Regards
Michel
Re: Getting wrong type of arguments [message #359967 is a reply to message #359889] Wed, 19 November 2008 00:44 Go to previous messageGo to next message
avik2009
Messages: 61
Registered: November 2008
Member
I the procedure,What I want is that Parameter used for counts should have last character "comma"


Should I make modifications to the below code in procedure
and get replaced by defining type?

    in_txt       IN  varChar2,
    txtCount     OUT Number,
    o_msg       OUT VARCHAR2



Can it be done that way? Or what are the possible ways ? Appreciate your help
Re: Getting wrong type of arguments [message #359972 is a reply to message #359967] Wed, 19 November 2008 00:55 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 18 November 2008 15:11
Only YOU know what you want to do with this procedure.
Until you explain this we can't help.

Regards
Michel


Previous Topic: store table name in a variable
Next Topic: Why not in a single procedure???
Goto Forum:
  


Current Time: Fri Dec 09 21:33:24 CST 2016

Total time taken to generate the page: 0.11155 seconds