Home » Developer & Programmer » Reports & Discoverer » Re: Calling Oracle Reports from Web Logic
Re: Calling Oracle Reports from Web Logic [message #87907] Fri, 26 October 2001 08:54
warren
Messages: 4
Registered: October 2001
Junior Member
Try this it worked for me ! HTML
---------------------------------

PROCEDURE gasreport
IS

CURSOR c1 IS

SELECT DISTINCT
THT.TRADE_ID trade_id ,
TO_CHAR(THT.TRADE_TIME,'HH24:MI:SS') TIMEE,
DECODE(THT.TRADING_TYPE,'TAKE','BUYER','HIT','SELLER') TAKE_BUYER,
OT1.NAME AGGRESSOR,
UST1.USERNAME USERNAME1,
DECODE(THT.TRADING_TYPE,'TAKE','SELLER','HIT','BUYER') HIT_SELLER,
OT2.NAME PASSOR,
UST2.USERNAME USERNAME2,
LT.LINE_STRING CONTRACT,
PDT.PRICE PRICE,
THT.TRADING_SIZE SIZEE,
THT.USER_GROUP_ID THT_USER_GROUP_ID,
PDT.USER_GROUP_ID PDT_USER_GROUP_ID,
tht.EXT_TRADE_NO THT_EXT_TRADE_NO
FROM
GTSOWNER.ORGANISATION_TAB OT1,
GTSOWNER.ORGANISATION_TAB OT2,
GTSOWNER.USER_GROUP_TAB UGT1,
GTSOWNER.USER_GROUP_TAB UGT2,
GTSOWNER.TRADING_HISTORY_TAB THT,
GTSOWNER.PRICE_DETAILS_TAB PDT,
GTSOWNER.TRADING_CODE_TAB TCT,
GTSOWNER.LINES_TAB LT,
GTSOWNER.USER_SEC_TAB UST1,
GTSOWNER.USER_SEC_TAB UST2,
GTSOWNER.SESSION_HISTORY_TAB SHT1,
GTSOWNER.SESSION_HISTORY_TAB SHT2
WHERE THT.USER_GROUP_ID = UGT1.USER_GROUP_ID
AND OT1.ORG_ID = UGT1.ORG_ID
AND PDT.USER_GROUP_ID = UGT2.USER_GROUP_ID
AND OT2.ORG_ID = UGT2.ORG_ID
AND THT.LINE_ID = PDT.LINE_ID
AND THT.EXT_PRICE_NO = PDT.EXT_PRICE_NO
AND THT.PRICE_VERSION = PDT.PRICE_VERSION
AND PDT.CRN = SHT2.SESSION_ID
AND UST2.SECURITY_ID = SHT2.SECURITY_ID
AND LT.LINE_ID = PDT.LINE_ID
AND TCT.TCODE_ID = LT.TCODE_ID
AND THT.CRN = SHT1.SESSION_ID
AND SHT1.SECURITY_ID = UST1.SECURITY_ID
AND TCT.TRADING_CODE IN ('NBPG','NBFG','ZEFG','ZEPG','BUPG','BUFG')
AND PDT.PRICE IS NOT NULL
ORDER BY TRADE_ID DESC;

c1_rec c1%ROWTYPE;

-----------------------------------------------------------------------------------------

CURSOR c2(p_tht_user_group_id IN VARCHAR2,p_tht_ext_trade_no IN NUMBER) IS

SELECT
TAB.ACK_STATUS Confirmed
FROM
TRADE_ACKNOWLEDGE_TAB TAB
WHERE TAB.EXT_TRADE_NO = p_tht_ext_trade_no
AND TAB.USER_GROUP_ID = p_tht_user_group_id;

c2_rec c2%ROWTYPE;

------------------------------------------------------------------

CURSOR c3(p_pdt_user_group_id IN VARCHAR2,p_tht_ext_trade_no IN NUMBER) IS

SELECT
TAB.ACK_STATUS Confirmed
FROM
TRADE_ACKNOWLEDGE_TAB TAB
WHERE tab.EXT_TRADE_NO = p_tht_ext_trade_no
AND TAB.USER_GROUP_ID = p_pdt_user_group_id;

c3_rec c3%ROWTYPE;

-------------------------------------------------------------------

v_filehandle utl_file.file_type;
v_path VARCHAR2(15) := '/tmp';
v_write CHAR(1) := 'w';
v_fileName VARCHAR2(50) :='gasreport.htm';

v_DateTime VARCHAR2(25) := TO_CHAR(SYSDATE,'dd/mm/yy'||' '||'HH24:MI:SS');
v_fcolor VARCHAR2(30) := '#0000FF';
line VARCHAR2(255);

BEGIN

OPEN c1;

v_filehandle := utl_file.FOPEN(v_path, v_fileName ,v_write);

line := '<HTML>';
utl_file.PUT_LINE( v_filehandle, line );

line := '<HEAD>';
utl_file.PUT_LINE( v_filehandle, line );

line := '<TITLE>GAS REPORT</TITLE>';
utl_file.PUT_LINE( v_filehandle, line );

line := '</HEAD>';
utl_file.PUT_LINE( v_filehandle, line );

line := '<BODY>';
utl_file.PUT_LINE( v_filehandle, line );

line := ' PREBON GAS REPORT:'||' '||v_DateTime ||'';
utl_file.PUT_LINE( v_filehandle, line );

line := '
';
utl_file.PUT_LINE( v_filehandle, line );

line := ' - TRADE_ID - ';
utl_file.PUT_LINE( v_filehandle, line );

line := ' - TIME - ';
utl_file.PUT_LINE( v_filehandle, line );

line := ' - TYPE - ';
utl_file.PUT_LINE( v_filehandle, line );

line := ' - AGGRESSOR - ';
utl_file.PUT_LINE( v_filehandle, line );

line := ' - USERNAME - ';
utl_file.PUT_LINE( v_filehandle, line );

line := ' - CONF - ';
utl_file.PUT_LINE( v_filehandle, line );

line := ' - TYPE - ';
utl_file.PUT_LINE( v_filehandle, line );

line := ' - PASSOR - ';
utl_file.PUT_LINE( v_filehandle, line );

line := ' - USERNAME - ';
utl_file.PUT_LINE( v_filehandle, line );

line := ' - CONF - ';
utl_file.PUT_LINE( v_filehandle, line );

line := ' - CONTRACT - ';
utl_file.PUT_LINE( v_filehandle, line );

line := ' - PRICE - ';
utl_file.PUT_LINE( v_filehandle, line );

line := ' - SIZE - ';
utl_file.PUT_LINE( v_filehandle, line );

LOOP

FETCH c1 INTO c1_rec;
EXIT WHEN c1%NOTFOUND;

IF c1%FOUND THEN

OPEN c2(c1_rec.tht_user_group_id,c1_rec.tht_ext_trade_no);
FETCH c2 INTO c2_rec;

OPEN c3(c1_rec.pdt_user_group_id,c1_rec.tht_ext_trade_no);
FETCH c3 INTO c3_rec;

line := '
';
utl_file.PUT_LINE( v_filehandle, line );

line := ' - ' || NVL(c1_rec.trade_id, ' ') || ' - ';
utl_file.PUT_LINE( v_filehandle, line );

line := ' - ' || NVL(c1_rec.timee, ' ') || ' - ';
utl_file.PUT_LINE( v_filehandle, line );

line := ' - ' || NVL(c1_rec.take_buyer, ' ') || ' - ';
utl_file.PUT_LINE( v_filehandle, line );

line := ' - ' || NVL(c1_rec.aggressor, ' ') || ' - ';
utl_file.PUT_LINE( v_filehandle, line );

line := ' - ' || NVL(c1_rec.username1, ' ') || ' - ';
utl_file.PUT_LINE( v_filehandle, line );

line := ' - ' || NVL(c2_rec.confirmed, ' ') || ' - ';
utl_file.PUT_LINE( v_filehandle, line );

line := ' - ' || NVL(c1_rec.hit_seller, ' ') || ' - ';
utl_file.PUT_LINE( v_filehandle, line );

line := ' - ' || NVL(c1_rec.passor, ' ') || ' - ';
utl_file.PUT_LINE( v_filehandle, line );

line := ' - ' || NVL(c1_rec.username2, ' ') || ' - ';
utl_file.PUT_LINE( v_filehandle, line );

line := ' - ' || NVL(c3_rec.confirmed, ' ') || ' - ';
utl_file.PUT_LINE( v_filehandle, line );

line := ' - ' || NVL(c1_rec.Contract, ' ') || ' - ';
utl_file.PUT_LINE( v_filehandle, line );

line := ' - ' || NVL(c1_rec.price, ' ') || ' - ';
utl_file.PUT_LINE( v_filehandle, line );

line := ' - ' || NVL(c1_rec.sizee, ' ') || ' - ';
utl_file.PUT_LINE( v_filehandle, line );

line := '
';
utl_file.PUT_LINE( v_filehandle, line );

END IF;

CLOSE c2;
CLOSE c3;

END LOOP;

line := '
';
utl_file.PUT_LINE( v_filehandle, line );

line := '</BODY>';
utl_file.PUT_LINE( v_filehandle, line );

line := '</HTML>';
utl_file.PUT_LINE( v_filehandle, line );

CLOSE c1;

utl_file.FCLOSE(v_filehandle);

EXCEPTION

WHEN utl_file.invalid_filehandle THEN
dbms_output.put_line('utl_file.invalid_filename');

WHEN utl_file.invalid_operation THEN
dbms_output.put_line('utl_file.invalid_operation');

WHEN utl_file.read_error THEN
dbms_output.put_line('utl_file.VALUE_ERROR');

WHEN utl_file.write_error THEN
dbms_output.put_line('utl_file.write_error');

WHEN utl_file.internal_error THEN
dbms_output.put_line('utl_file.internal_error');

WHEN utl_file.invalid_path THEN
dbms_output.put_line('utl_file.invalid_path');

WHEN OTHERS THEN

dbms_output.put_line(SQLCODE||' '||SUBSTR(SQLERRM,1,200));

NULL;

END gasreport;

----------------------------------------------------------------------
Previous Topic: Re: oracle discoverer 3.1
Next Topic: Html report using plsql
Goto Forum:
  


Current Time: Thu Mar 28 05:43:30 CDT 2024