Bind variable in parameter's list of value

use of Bind variable in parameter's list of value.

1. First need to create a Global Temporary table .
scrift for Global Temporary table .
this table is useful for global user

CREATE GLOBAL TEMPORARY TABLE STGLOBTM
(
COLMN1 NUMBER(18,2),
COLMN2 NUMBER(18,2),
COLMN3 NUMBER(18,2),
COLMN4 NUMBER(18,4),
COLMN5 NUMBER(18,2),
COLMN6 NUMBER(18,2),
COLMN7 NUMBER(18,2),
COLMN8 NUMBER(18,2),
COLMN9 NUMBER(18,2),
COLMN10 NUMBER(18,2),
COLMN11 NUMBER(18,4),
COLMN12 NUMBER(18,2),
COLMN13 NUMBER(18,2),
COLMN14 NUMBER(18,2),
COLMN15 NUMBER(18,2),
COLMC1 VARCHAR2(25 BYTE),
COLMC2 VARCHAR2(25 BYTE),
COLMC3 VARCHAR2(25 BYTE),
COLMC4 VARCHAR2(50 BYTE),
COLMC5 VARCHAR2(50 BYTE),
COLMC6 VARCHAR2(50 BYTE),
COLMC7 VARCHAR2(200 BYTE),
COLMC8 VARCHAR2(200 BYTE),
COLMC9 VARCHAR2(200 BYTE),
COLMC10 VARCHAR2(50 BYTE),
COLMC11 VARCHAR2(50 BYTE),
COLMC12 VARCHAR2(50 BYTE),
COLMC13 VARCHAR2(50 BYTE),
COLMC14 VARCHAR2(50 BYTE),
COLMC15 VARCHAR2(50 BYTE),
COLMD1 DATE,
COLMD2 DATE,
COLMD3 DATE,
COLMD4 DATE,
COLMD5 DATE,
COLMD6 DATE,
COLMD7 DATE,
COLMD8 DATE,
COLMD9 DATE
)
ON COMMIT PRESERVE ROWS
NOCACHE;


2. Before parameter form trigger

to do that first need to write following (example) query
(list of values query using bind variable :p0) in "before parameter form"
trigger in oracle report builder.
--------------------------------

function BeforePForm return boolean is
Begin
srw.do_sql('delete from stglobtm') ;
for i in (select a.lcnumb,b.acttit
from stilcmas a,stfacmas b
where a.brancd = :p0
and a.lcnumb is not null
and a.actype = 'T01'
and a.brancd = b.brancd
and a.actype = b.actype
and a.actnum=b.actnum)
loop
insert into stglobtm (colmc1,colmc7) values (i.lcnumb,i.acttit) ;
end loop ;

Return (TRUE);
End;
----------------------

3.

then write query in the report parameter's list of value
like following example.


select colmc1,colmc7 from stglobtm

Stglobtm is a Global Temporary table .

First need to create a Global Temporary table .
scrift for Global Temporary table .
this table is useful for global user

CREATE GLOBAL TEMPORARY TABLE STGLOBTM
(
COLMN1 NUMBER(18,2),
COLMN2 NUMBER(18,2),
COLMN3 NUMBER(18,2),
COLMN4 NUMBER(18,4),
COLMN5 NUMBER(18,2),
COLMN6 NUMBER(18,2),
COLMN7 NUMBER(18,2),
COLMN8 NUMBER(18,2),
COLMN9 NUMBER(18,2),
COLMN10 NUMBER(18,2),
COLMN11 NUMBER(18,4),
COLMN12 NUMBER(18,2),
COLMN13 NUMBER(18,2),
COLMN14 NUMBER(18,2),
COLMN15 NUMBER(18,2),
COLMC1 VARCHAR2(25 BYTE),
COLMC2 VARCHAR2(25 BYTE),
COLMC3 VARCHAR2(25 BYTE),
COLMC4 VARCHAR2(50 BYTE),
COLMC5 VARCHAR2(50 BYTE),
COLMC6 VARCHAR2(50 BYTE),
COLMC7 VARCHAR2(200 BYTE),
COLMC8 VARCHAR2(200 BYTE),
COLMC9 VARCHAR2(200 BYTE),
COLMC10 VARCHAR2(50 BYTE),
COLMC11 VARCHAR2(50 BYTE),
COLMC12 VARCHAR2(50 BYTE),
COLMC13 VARCHAR2(50 BYTE),
COLMC14 VARCHAR2(50 BYTE),
COLMC15 VARCHAR2(50 BYTE),
COLMD1 DATE,
COLMD2 DATE,
COLMD3 DATE,
COLMD4 DATE,
COLMD5 DATE,
COLMD6 DATE,
COLMD7 DATE,
COLMD8 DATE,
COLMD9 DATE
)
ON COMMIT PRESERVE ROWS
NOCACHE;