Home » Infrastructure » Unix » Shellscripting or PL/SQL program (10.2.0.3.0)
Shellscripting or PL/SQL program [message #401685] Wed, 06 May 2009 03:06 Go to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member
Hi All,

I have written the following query.
SELECT c.cardnbr, co_id
  FROM tmp.ccard__gen g,
       tmp.ccard_tmp  c,
       dsn.contract_phonenr p
 WHERE flag         = 'P'
   AND c.cardnbr       = g.cardnbr
   AND '87' || p.num   = c.cardnbr
   AND c.amtbal > 0
   AND c.pprofil = 'TEL';


O/P :
cardnbr        Co_id
------------     ------------
87654327     980
87897687     876
87654309     123
.....


but now the requirement is if I execute this query it should return the above o/p
as well as it should generate a file called "balance.csv" with O/P
as
MSNNUM ;CO_ID

-------------------
87654327; 980
87897687; 876
87654309; 123
.....
...
...


can any one please let me know is it possible to write a procedure/function or let me know how to write a shell scripting .

Thank you
Re: Shellscripting or PL/SQL program [message #401688 is a reply to message #401685] Wed, 06 May 2009 03:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
set colsep ';'

Regards
Michel
Re: Shellscripting or PL/SQL program [message #401689 is a reply to message #401688] Wed, 06 May 2009 03:18 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member
Michel,

I have written as follows.
set heading off
set pages 0
 
set colsep ";"
 
spool balance.csv
 
select 'MSNNUM ;CO_ID' from dual;
select '-----------------------' from dual;
 
SELECT c.cardnbr, co_id
  FROM tmp.ccard__gen g,
       tmp.ccard_tmp  c,
       dsn.contract_phonenr p
 WHERE flag         = 'P'
   AND c.cardnbr       = g.cardnbr
   AND '87' || p.num   = c.cardnbr
   AND c.amtbal > 0
   AND c.pprofil = "TEL';
 
spool off

but the query return O/P will be stored in a file "balance.dat"
and the "MSNNUM;CO_ID" O/P will be stored in the "Balances.csv" files.
so the script should create two two files 1. balance.dat 2. balances.csv

please look into this.

Thank you
Re: Shellscripting or PL/SQL program [message #401703 is a reply to message #401689] Wed, 06 May 2009 03:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use 2 spool commands

Regards
Michel
Re: Shellscripting or PL/SQL program [message #401718 is a reply to message #401703] Wed, 06 May 2009 04:28 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member
Michel,

Please give the syntax for spooling those two O/P into 2 files in the above mentioned script..

Thank you
Re: Shellscripting or PL/SQL program [message #401727 is a reply to message #401718] Wed, 06 May 2009 04:43 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
user71408,

Can you please tell me which tickets to book when I want to fly to Tahiti and Hawaii at the same time?

Thank you.
Re: Shellscripting or PL/SQL program [message #401759 is a reply to message #401718] Wed, 06 May 2009 06:31 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member
Hi,

Please look into the requirement and let me know how to write shell script for this.

2.2 extract_balance

2.2.1 Component Type (Data Manipulation)
• Create a flat file with the following name :
BALANCE_YYYYMMDD.csv
Field separator to use : ;
e.g. MSNNUM;co_id
Where
MSNNUM is the MSNNUM of the card expressed in international format
(87xxxxxxxxx)
co_id is the contract_id corresponding to that MSNNUM in SCOTT

2.2.2 Business rules
rules_id                           Description
-----------------------------------------------------------
SELECT c.cardnbr, co_id
  FROM tmp.ccard__gen g,
       tmp.ccard_tmp  c,     
       dsn.contract_phonenr p
 WHERE flag         = 'P'
   AND c.cardnbr    = g.cardnbr
   AND '87' || p.num = c.cardnbr
   AND c.amtbal > 0
   AND c.pprofil = "TEL';              
                               
(this is the description)

With this query, all SEF prepaid 
card with a profile Migrate to Postpaid and still remain some credits in the cards will be generated with the contract_id from SCOTT.  (DESCRIPTION)
                                


Thank you,

[Updated on: Wed, 06 May 2009 06:39]

Report message to a moderator

Re: Shellscripting or PL/SQL program [message #401770 is a reply to message #401703] Wed, 06 May 2009 07:10 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member
Hi ,

Please find the following one give me your ideas...
SELECT dn_num, l.lng_shdes
  FROM sc.ccontact_all cc,
           sc.customer_all cu,
           sc.LANGUAGE l,
           sc.contr_services_cap cos_c,
           sc.contr_services COS,
           sc.directory_number dn,
           sc.contract_all ca
 WHERE COS.co_id      = cos_c.co_id
   AND cos_c.dn_id    = dn.dn_id
   AND COS.co_id      = ca.co_id
   AND ca.customer_id = cu.customer_id
   AND cc.customer_id = ca.customer_id
   AND COS.scode     = 1                
   AND COS.cs_stat_chng LIKE ('%a')
   AND LENGTH (COS.cs_stat_chng) = 7
   AND TO_DATE (SUBSTR (COS.cs_stat_chng, 1, 6), 'YYMMDD')
          BETWEEN (SELECT TO_DATE (txt, 'YYYYMMDD')
                     FROM tmp.SRM
                    WHERE srm_id_d = 'ACTIVE_DATE'
                      AND srm_id_h = 'ASSURE')
                      AND TRUNC (SYSDATE - 1)
   AND cu.pcode IN (10, 20, 30)         
   AND cc.ccbill = 'X'
   AND cos_c.code = 1
   AND cos_c.cs_deactiv_date IS NULL
   AND l.lng_id = cc.cclanguage;


we need to write the following UPDATE statement.
UPDATE SRM set srm_id_h = 'ASSURE' where  srm_id_d = 'ACTIVE_DATE';

This select return the MSNNUM and the LANGUAGE where to send SMS. For this selection, we will use the TMP.SRM table in order to store date to treat as in several other application stuff.
To "decode" the language, we can use table "sc.language".

This text will be added into each SMS send.
This text can be stored into SRM table too.

how to write the decode statement to insert the SMS text for different languages into SRM table.

Please let me know how to write a query( adding the update in the above mentioned SELECT statement )
or pl/sql code for this requirement.

please let me know if any queries.

Thank you
Re: Shellscripting or PL/SQL program [message #401803 is a reply to message #401685] Wed, 06 May 2009 08:28 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Post DDL for tables.
Post DML for test data.

Post expected/desired results.
Re: Shellscripting or PL/SQL program [message #401804 is a reply to message #401770] Wed, 06 May 2009 08:29 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
It is quite scary that you seem to be doing stuff that has something to do with credit card numbers.

The first idea that comes to my mind concerning that would be that there is another financial crisis just waiting to happen.

Re: Shellscripting or PL/SQL program [message #401806 is a reply to message #401685] Wed, 06 May 2009 08:35 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
SELECT dn_num, l.lng_shdes
  FROM sc.ccontact_all cc,
           sc.customer_all cu,
           sc.LANGUAGE l,
           sc.contr_services_cap cos_c,
           sc.contr_services COS,
           sc.directory_number dn,
           sc.contract_all ca

Only sc.LANGUAGE l & sc.directory_number dn should be in FROM clause; only 2 table returning data to SELECT clause.
All other tables should be subordinated into WHERE clause.
Re: Shellscripting or PL/SQL program [message #402058 is a reply to message #401703] Thu, 07 May 2009 07:52 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member
Michel,

In shellscript I have written balance.csv.
But I want to spool this data as "balance_YYYYMMDD.csv"
can you please let me know how can write the syntax to spool the data as mentioned .

Thank you,
Re: Shellscripting or PL/SQL program [message #402062 is a reply to message #402058] Thu, 07 May 2009 08:06 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
That would (as usual) depend on a lot of things that you (as usual) didn't tell us.
Re: Shellscripting or PL/SQL program [message #402080 is a reply to message #401685] Thu, 07 May 2009 09:06 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
SQL> SPOOL balance_YYYYMMDD.csv
Re: Shellscripting or PL/SQL program [message #402212 is a reply to message #402080] Fri, 08 May 2009 01:57 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member
the data would spooled with system generated date.
if we are spooling today then O/P wouild be balance_20090508.csv.

This is the required o/P
so please let me know how to write it..

Thank you
Re: Shellscripting or PL/SQL program [message #402260 is a reply to message #402212] Fri, 08 May 2009 04:21 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
user71408 wrote on Fri, 08 May 2009 08:57
the data would spooled with system generated date.
if we are spooling today then O/P wouild be balance_20090508.csv.

This is the required o/P
so please let me know how to write it..

Thank you

This has been replied several times.
I bet if you put your question "date in spool file name" in search field or in Google you will find the answer.

Regards
Michel

Previous Topic: NFS Inquery?
Next Topic: Adding Text in Shellscripting which is having XML code
Goto Forum:
  


Current Time: Thu Mar 28 20:32:10 CDT 2024