Home -> Usenet -> c.d.o.tools -> Re: Forms 4.5 : can the SQLquery-statement generated by forms be retrieved ?

Re: Forms 4.5 : can the SQLquery-statement generated by forms be retrieved ?

From: Izabella <Izabella.Urbanek_at_afp.gov.au>
Date: Wed, 21 Oct 1998 08:15:53 +1000
Message-ID: <70j28s$31s$1_at_platinum.sge.net> 













Have a look at System.Last_Query system variable 
in the Forms help. Alternatively, here is a bit of code that I wrote a long time 
ago to pass where conditions to reports. It utilises the Get_Block_Property 
built in. Note that you can only pass a maximum of 255 characters as a parameter 
to reports.


 


PROCEDURE Run_Where_Report (pReportName in varchar2, 
pBlockName in varchar2) IS
  vParamList varchar2(500) := 
'Report';
  vID  ParamList;
  vWhere 
varchar2(4000);
  vOrder varchar2(200);
  nNum  NUMBER := 
1;
  nLength NUMBER;
  nPtr  NUMBER := 
1;
BEGIN
  vID := Get_Parameter_List (vParamList);
  if not 
ID_NULL (vID) then
     Destroy_Parameter_List 
(vParamList);
  end if;


 


  vID := Create_Parameter_List (vParamList);
  
Add_Parameter (vParamList, 'PARAMFORM', TEXT_PARAMETER, 'NO');
  /* 
Create where parameters - Limit of 255char per parameter */
  vWhere := 
Get_Block_Property (pBlockName, DEFAULT_WHERE);
  if vWhere is not null 
then vWhere := ' where ' || vWhere; end if;
  nLength := 
length(vWhere);
  while nPtr < (nLength + 255) 
loop
    Add_Parameter (vParamList, 'P_WHERE'||to_char(nNum), 
TEXT_PARAMETER, substr(vWhere, nPtr, 255));
    nNum := nNum + 
1;
    nPtr := nPtr + 256;
  end loop;
  /* 
Create the order by parameter */
  vOrder := Get_Block_Property 
(pBlockName, ORDER_BY);
  if vOrder is not null then vOrder := ' order 
by ' || vOrder; end if;
  Add_Parameter (vParamList, 'P_ORDER', 
TEXT_PARAMETER, vOrder);


 


  Run_Product (REPORTS, pReportName, ASYNCHRONOUS, 
RUNTIME, FILESYSTEM, vParamList, null);
END;



    
Bas Grondhuis wrote in message 
    <01bdfc71$b27d44e0$bd06eed4_at_default>...

    
Hello everybody,

Can someone 
    help me ? I want to have a report (reports 2.5) corresponding with a form 
    (forms 4.5). Meaning : when I perform a query in my form and choose one of 
    several order by options, I would like to have a report based on those same 
    query-paramters and have the same ordering. 
So if I could retrieve the 
    last query-statement generated by forms it self, save it to file with 
    text_io or tpu_file or something like that and then use that file as an 
    external query in my report it should get the trick done......only problem : 
    I have no idea how to retrieve that generated statement. 

N.B. I now 
    that I can retrieve the query-parameters in the pre-query trigger, but then 
    I would have to translate that myself into a query statement 
    =>

>300 would become where <column> > 300
RT% 
                          where 
    <column> like 'RT%'
12 
                             where 
    <column> = 12 

etc.   

That's a lot of work, 
    so I'd rather not work this out. And even if I would have to work this 
    out.....I think I'm getting obsessed with retrieving that generated 
    statement !

Greetings and thanks in advance,

Bas 
    Grondhuis

Received on Wed Oct 21 1998 - 00:15:53 CEST












Original text of this message