Last active
August 29, 2015 13:58
-
-
Save lifecube/9931228 to your computer and use it in GitHub Desktop.
oracle sqlplus sample to generate report
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| SET echo OFF | |
| SET feedback OFF | |
| SET verify OFF | |
| SET heading OFF | |
| SET termout OFF | |
| SET linesize 32767 | |
| SET trimspool ON | |
| SET trimout ON | |
| SET serveroutput ON | |
| COL filename new_value filename; | |
| SELECT './reports/'||TRANSLATE(C_NAME,' */''','___') | |
| ||'_' | |
| || | |
| &1 | |
| ||'_bill_report.csv' filename | |
| FROM GMCCMG_CAMPAIGNS | |
| WHERE N_CAMPAIGN_ID= | |
| &1; | |
| spool &filename; | |
| DECLARE | |
| TYPE Varchar2Tab | |
| IS | |
| TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER; | |
| TYPE Varchar2List | |
| IS | |
| TABLE OF VARCHAR2(30); | |
| billStatuses Varchar2List :=Varchar2List('NOT_APPLICABLE', 'REQUESTED', 'CONFIRMED', 'REFUSED', 'ERROR'); | |
| billTypes Varchar2List :=Varchar2List('SMS', 'CUSTOM', 'NONE'); | |
| campaignTypes Varchar2List :=Varchar2List('PULL', 'PUSH', 'QRCODE', 'UNKNOWN'); | |
| offerTypes Varchar2List :=Varchar2List('COUPON', 'DEAL', 'APP'); | |
| CURSOR billCur | |
| IS | |
| SELECT consumer.C_MSISDN, | |
| billing.N_CAMPAIGN_ID, | |
| campaign.C_CAMPAIGN_REF, | |
| campaign.C_NAME C_CAMP_NAME, | |
| campaign.N_TYPE N_CAMP_TYPE, | |
| issuer.C_TIMEZONE, | |
| billing.N_OFFER_ID, | |
| offer.C_OFFER_REF, | |
| offer.C_NAME C_OFFER_NAME, | |
| offer.N_OFFER_TYPE, | |
| billing.N_BILLING_TYPE, | |
| billing.N_APPLIED_PRICE, | |
| billing.N_STATUS, | |
| billing.N_ERROR_CODE, | |
| TO_CHAR(CAST((FROM_TZ(CAST(billing.D_BILLING_DATE AS TIMESTAMP),'+00:00') AT TIME ZONE issuer.c_timezone) AS DATE), 'YYYY-MM-DD HH24:MI:SS') D_BILLING_DATE, | |
| billing.C_REASON | |
| FROM GMCCRP_CONSUMER_BILLINGS billing | |
| INNER JOIN gmccop_consumers consumer | |
| ON consumer.N_CONSUMER_ID = billing.N_CONSUMER_ID | |
| INNER JOIN GMCCOM_CAMPAIGNS campaign | |
| ON campaign.N_CAMPAIGN_ID = billing.N_CAMPAIGN_ID | |
| INNER JOIN GMCCOM_OFFERS offer | |
| ON offer.N_OFFER_ID = billing.N_OFFER_ID | |
| INNER JOIN GMCCMG_OFFER_ISSUERS issuer | |
| ON issuer.N_OFFER_ISSUER_ID = offer.N_OFFER_ISSUER_ID | |
| WHERE billing.N_CAMPAIGN_ID =&1; | |
| BEGIN | |
| DBMS_OUTPUT.ENABLE (buffer_size => NULL); | |
| --output headers | |
| dbms_output.put_line('"MSISDN","Campaign ID","Campaign Reference","Campaign Name","Campaign Type","Campaign TimeZone","Offer ID","Offer Reference","Offer Name","Offer Type","Billing Type","Applied Price","Billing Status","Billing Status Code","Billing Date","Billing Reason"'); | |
| --loop to generate the report content | |
| FOR line IN billCur | |
| LOOP | |
| dbms_output.put_line('"'||line.C_MSISDN||'",'||line.N_CAMPAIGN_ID||',"'||line.C_CAMPAIGN_REF||'","' | |
| ||line.C_CAMP_NAME||'",'||campaignTypes(line.N_CAMP_TYPE+1)||',"'||line.C_TIMEZONE||'",'||line.N_OFFER_ID||',"' | |
| ||line.C_OFFER_REF||'","'||line.C_OFFER_NAME||'",'||offerTypes(line.N_OFFER_TYPE+1)||','||billTypes(line.N_BILLING_TYPE+1)||',' | |
| ||line.N_APPLIED_PRICE||','||billStatuses(line.N_STATUS+1)||','||line.N_ERROR_CODE||','||line.D_BILLING_DATE||',"'||line.C_REASON||'"'); | |
| END LOOP; | |
| END; | |
| / | |
| spool OFF |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment