OraFAQ Forum: SQL & PL/SQL » Concatenation problem in stored procedure (merged by LF)

Home » SQL & PL/SQL » SQL & PL/SQL » Concatenation problem in stored procedure (merged by LF)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Concatenation problem in stored procedure (merged by LF) [message #255309]

Tue, 31 July 2007 06:04

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

balassvasan
Messages: 3
Registered: July 2007

Junior Member

pls change op as type varchar2 instead of number.

Report message to a moderator

Re: concatenation problem with proc [message #255312 is a reply to message #255309]

Tue, 31 July 2007 06:16

ThomasG
Messages: 3212
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>

Report message to a moderator

Tue, 31 July 2007 07:41

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

Report message to a moderator

Tue, 31 July 2007 08:45

Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Previous Topic:	procedures
Next Topic:	Swap 2 variables without using a temperory variable

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Dec 09 19:58:52 CST 2024