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;
----------------------------------------------------------------------
|
|
|
|
Goto Forum:
Current Time: Fri Jan 03 14:56:24 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
