Home » SQL & PL/SQL » SQL & PL/SQL » Concatenation problem in stored procedure (merged by LF)
Concatenation problem in stored procedure (merged by LF) [message #255309] Tue, 31 July 2007 06:04 Go to next message
gautamvv
Messages: 254
Registered: June 2005
Senior Member
create table pcom(col1 number(4),col2 number(4));

insert into pcom values(1,4);
insert into pcom values(1,8);
insert into pcom values(1,10);
insert into pcom values(1,12);
insert into pcom values(2,6);
insert into pcom values(2,12);

insert into pcom values(3,7);


create or replace procedure test(nid in number,op out number)
is
cursor c1 is select col2 from pcom where col1=nid;
begin
  for r1 in c1 loop
  op:=r1.col2;
  op:=op||','||r1.col2;
  end loop;
  end;




so when i say
exec test(1,:x)

i want to see the comma seperated list like 1,8,10,12 to be
set in output parameter

but i am getting error


ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "GAUTAM.TEST", line 7
ORA-06512: at line 1


how to get a comma seprated list of the output?


[Updated on: Tue, 31 July 2007 06:42] by Moderator

Report message to a moderator

Re: concatenation problem with proc [message #255311 is a reply to message #255309] Tue, 31 July 2007 06:12 Go to previous messageGo to next message
balassvasan
Messages: 3
Registered: July 2007
Junior Member
pls change op as type varchar2 instead of number.
Re: concatenation problem with proc [message #255312 is a reply to message #255309] Tue, 31 July 2007 06:16 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Op is defined as number, and you try to put characters in it.

Also, there is something wrong with the loop, it would alway return the two last values fetched.

Thanks for the table creation scripts and the sample data, so I could come up with :

SQL> create table pcom(col1 number(4),col2 number(4));

Table created.

SQL>
SQL> insert into pcom values(1,4);

1 row created.

SQL> insert into pcom values(1,8);

1 row created.

SQL> insert into pcom values(1,10);

1 row created.

SQL> insert into pcom values(1,12);

1 row created.

SQL> insert into pcom values(2,6);

1 row created.

SQL> insert into pcom values(2,12);

1 row created.

SQL>
SQL> insert into pcom values(3,7);

1 row created.

SQL>
SQL>
SQL> create or replace procedure test(nid in number,op out varchar2)
  2  is
  3  cursor c1 is select col2 from pcom where col1=nid;
  4  BEGIN
  5    op:='';
  6
  7    for r1 in c1 loop
  8        op:=op||','||r1.col2;
  9    end loop;
 10    op := LTrim(op,',');
 11    end;
 12  /

Procedure created.

SQL>
SQL> DECLARE
  2
  3  v_tmp varchar2(50);
  4
  5
  6  BEGIN
  7   test(2,v_tmp);
  8   Dbms_Output.put_line(v_tmp);
  9  END;
 10  /
6,12

PL/SQL procedure successfully completed.

SQL>
doubt related to the previous post of concatenation [message #255328 is a reply to message #255309] Tue, 31 July 2007 07:41 Go to previous messageGo to next message
gautamvv
Messages: 254
Registered: June 2005
Senior Member
@thomas

thank you , i have implemented the changes like this, but i
have a new doubt now


-- see which alerts were turned off
    l_status := 0;
    l_subscrstatus := -1;
    o_FunctIdList:='';

    for r_changed in c_changedalerts(l_status) loop
    -- CR 53380
        o_FunctIdList:=o_FunctIdList||','||r_changed.ecfn_funct_id;
        UserPreferences.UpdAlertSubscrSysStatus(r_changed.ecfn_funct_id,
                                                NULL,  -- customerid
                                                NULL,  -- userid
                                                NULL,  -- productid
                                                NULL,  -- roleid
                                                l_subscrstatus);
    end loop;
    o_FunctIdList := LTrim(o_FunctIdList,',');

    -- see which alerts have been turned back on
    l_status := 1;
    l_subscrstatus := 0;
    for r_changed1 in c_changedalerts(l_status) loop
    -- CR 53380
        o_FunctIdList:=o_FunctIdList||','||r_changed1.ecfn_funct_id;
        UserPreferences.UpdAlertSubscrSysStatus(r_changed1.ecfn_funct_id,
                                                NULL,  -- customerid
                                                NULL,  -- userid
                                                NULL,  -- productid
                                                NULL,  -- roleid
                                                l_subscrstatus);
    end loop;



the o_FunctIdList is our op, out parameter

now there are 2 loops, control may go into any of the loops,
my target is to concatenate the out parameter obtained
from both the loops in comma seperated list, and put this
o_FunctIdList as an out parameter..

if control goes to the 1st loop, and doesnt go to the second
loop...no problem

if control goes to both the loops/...no problem

but if control doesnt go to first loop, but goes to second loop,
there is a problem

i need to put

o_FunctIdList := LTrim(o_FunctIdList,',');



at the end of second loop
but that is based on a condition whether o_FunctIdList
is '' or not, but where to put and test this condition?

hope you are getting myh poing

Re: doubt related to the previous post of concatenation [message #255340 is a reply to message #255328] Tue, 31 July 2007 08:45 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
but that is based on a condition whether o_FunctIdList
is '' or not, but where to put and test this condition?

You can do it in both case, it does not matter.
You don't have to test if the parameter is null or not.

Regards
Michel
Previous Topic: procedures
Next Topic: Swap 2 variables without using a temperory variable
Goto Forum:
  


Current Time: Mon Dec 05 21:05:47 CST 2016

Total time taken to generate the page: 0.07370 seconds