Using more than one value for list item

hi
i have form , there is trigger to fill items in the list box like this
 ADD_LIST_ELEMENT('LST_ALPHA',1,RPAD(RTRIM(bs.QTY),10,' ')||'- ' ||RPAD(RTRIM( to_char(bs.tr_date, 'dd-mm-yyyy')),15,' ')||'- ' ||RPAD(RTRIM(bs.av_cost),10,' ')||'- ' ||RPAD(RTRIM(bs.tr_no1),10,' ')||'- ' ||RPAD(RTRIM(bs.sup_name),20,' '),bs.tr_no1);
  	  

Open in new window


now the value of each item in the list box is equal to bs.tr_no1
i have another button in same form will call a database procedure to perform some codes required  additional values from this list item such as :
qty , tr-date , av-cost , sup_name
how i can get all those values in one time
NiceMan331Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NiceMan331Author Commented:
I forget to explain that  table provided the items of list item don't have uniqu item , I mean I cannot select from it the related record depend of tran_no1 , it will not solve , only need all those values which cursor return it in the list item
0
Mark GeerlingsDatabase AdministratorCommented:
We don't have enough information from you to answer your question.  

My first question is: is this type of Form item the best way to solve this business problem in Oracle Forms?  The easiest way to populate items in an Oracle Form is to use "text" items in a database block and have Forms populate them via a query of a table or view.  If you choose to use other item types, and/or have the items *NOT* be database items, that makes the programming you have to do in Forms *MUCH* more complex!

My second question is: since you say that the table you need to retrieve these values from doesn't have a unique key, how do you plan to retrieve these items?  We don't know your database table structure or your application.  What criteria can be used to retrieve these items from the database?
0
NiceMan331Author Commented:
ok , i got think about one solution , but i need the code
i will concatenate the value of list item, like this

bs.tr_no1|| '-' ||qty|| '-' || tr-date|| '-' || av-cost || '-' || sup_name

Open in new window


now when i used value of the list item in the procedure , how i can split that string and sliced it by 5 values considering '-' between each one , i think instr & susbtr ?
but how ?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Helena Markováprogrammer-analystCommented:
There are many better examples than this one is:
declare
s_var VARCHAR2(200);
s_var_1 VARCHAR2(200);
n_length  PLS_INTEGER;
n_pos  PLS_INTEGER;
n_pos_1   PLS_INTEGER;
BEGIN
s_var:='a111b-a22222b-a5555b-a6666666666b-a888888888888888888b';
dbms_output.put_line(s_var);
n_pos_1:=1;
for i in 1 .. 4 loop
  n_pos:=instr(s_var,'-',1,i);
  n_length:= n_pos- n_pos_1;
  s_var_1:=substr(s_var,n_pos_1,n_length);
  dbms_output.put_line(s_var_1);
  n_pos_1:=n_pos + 1;
  IF i=4 THEN
    s_var_1:=substr(s_var,n_pos_1);
    dbms_output.put_line(s_var_1);  
  END IF;
end loop;
end;

This is an idea, you can write your own function.
0
NiceMan331Author Commented:
Thanx , let me try it
0
NiceMan331Author Commented:
yes correct
final question
your code answered my question if i know that i have 4 variables , ok , but in general , if i don't know how many variables , how can replace instead of 4 by x ?
0
Helena Markováprogrammer-analystCommented:
Here are some modifications:
declare
 s_var VARCHAR2(200);
 s_var_1 VARCHAR2(200);
 n_length  PLS_INTEGER;
 n_pos  PLS_INTEGER;
 n_pos_1   PLS_INTEGER;
 n_occurance  PLS_INTEGER;
 n_length_var  PLS_INTEGER;
 BEGIN
 s_var:='a111b-a22222b-a5555b-a6666666666b-a888888888888888888b-a44444b';
 dbms_output.put_line(s_var);
 n_length_var:=length(s_var);
 n_occurance:=0;
 FOR j in 1 .. n_length_var loop
  IF instr(s_var,'-',1,j)>0 THEN
    n_occurance:=n_occurance + 1;
  END IF;
 end loop;
 dbms_output.put_line(n_occurance);
 n_pos_1:=1;
 for i in 1 .. n_occurance loop
   n_pos:=instr(s_var,'-',1,i);
   n_length:= n_pos- n_pos_1;
   s_var_1:=substr(s_var,n_pos_1,n_length);
   dbms_output.put_line(s_var_1);
   n_pos_1:=n_pos + 1;
   IF i=n_occurance THEN
     s_var_1:=substr(s_var,n_pos_1);
     dbms_output.put_line(s_var_1);  
   END IF;
 end loop;
 end;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NiceMan331Author Commented:
sorry henka
the output not working with me
so please give me small time to solve this issue
http://www.experts-exchange.com/Software/Misc/Q_28524421.html
thanx alot
0
NiceMan331Author Commented:
yes henka
it is correct\
thanx
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.