Hi,

     I designed one simple tabular form report.It will take list of values and displays their details.
For example we will consider emp table,my sql query is
"select * from emp where empno in ('100E','1002E',
'1003M','1004S','1005H')"
This query will retrive all details basing on the  IN clause.Coming to the report,I wrote query like  this
"select * from emp where  &p_empno"When I run this report in paper layout it will take values with single codes like
('100E','1002E','1003M','1004S','1005H') and it displays details basing on this list.
But If want to run through URL how can I pass these single coded values to the report.

Any assistance will be appreciated.
Thanks in advance
GP

first you would need to create a function that will break up the string to individual records into a user defined type.

so what would happen is

select * from emp where empno in (select column_value from the (select cast (ParseStringToTable(:emplist,',') as StringTable from dual))

emplist variable value will be '100E,1002E,1003M,1004S,1005H'

you will need to create the ParseStringToTable Function as below

CREATE OR REPLACE FUNCTION TOM.parsestringtotable (
   inlist      VARCHAR2,
   delimiter   VARCHAR2
)
   RETURN matheson_string_table
AS
   -- define maximum varchar2 field
   clist      VARCHAR2 (32767)      DEFAULT inlist;
   ipos       NUMBER;
   rtntable   StringTable := StringTable ();
BEGIN
   -- jae: 4/21/2006 10:47 am
   -- generic parsing routine, should replace varchartotable, stringToTable functions
   -- as this will pass a delimiter
   IF (SUBSTR (clist, LENGTH (clist), 1) <> delimiter)
   THEN
      clist := clist || delimiter;
   END IF;

   LOOP
      ipos := INSTR (clist, delimiter);
      -- exit if not found
      EXIT WHEN (NVL (ipos, 0) = 0);
      -- add another element to the table
      rtntable.EXTEND;
      -- add value to the table
      rtntable (rtntable.COUNT) :=
                                  LTRIM (RTRIM (SUBSTR (clist, 1, ipos - 1)));
      -- update cList variable with next inlist until no more
      clist := SUBSTR (clist, ipos + 1);
   END LOOP;

   RETURN rtntable;
END parsestringtotable;
/

then create the User Defined Type
CREATE OR REPLACE
TYPE     STRINGTABLE AS TABLE OF  VARCHAR2(100)
/

hth
joel

Hi,
I am not sure what you meant by running the report through URL. I am assuming that you have a web application and are using Oracle reports or you are running the report through Oracle Applications.

I would sugges that instead of using th quotes, you can use replace command to replace ',' with '',' (quote and a comma).

I have used it and it worked for me. I think you can do the same. I have a trigger in after parameter form.
Below is the code
-----------------------
If instr(:P_empno, ',')>0 then
     :P_empno := ' and emp.empno in ( ''' || replace(:P_empno, ',' , ''',''') || ''')';
  elsif :P_empno is null then
      :P_empno := ' and 1 = 1 ';
  else
      :P_empno := ' and emp.empno in (''' || :P_empno ||''' )';  
  end if;
  
  srw.message(1,:P_empno);
----------------
Hope this helps.

Kiran