Tag Archives: SELECT

How to get sqlplus column header once in csv file?

By a1_win

Hi All,

Could anyoone please let me know how do I get sqlplus column header once in csv file

Scripts are below:

Code:

cat concreq.sh

#!/bin/bash

. $HOME/.profile

while [ 1 -eq 1 ]; do
sqlplus apps/pwd <<-EOF
set lines 100 pages 100
col "USER_CONCURRENT_QUEUE_NAME" format a40;
--set termout off
--set arraysize 5
set echo off
set verify off
set heading on
set feed off
set colsep,;
@/test1/concreq.sql
EOF
sleep 15
done
$


Code:

$cat concreq1.sql

column tm new_value file_time noprint
select to_char(sysdate, 'MMDDYYYY') tm from dual ;
prompt &file_time
spool /test1/TEST1_CM_&file_time..csv append
col running head "Running" format 9999999
select to_char(r.ACTUAL_START_DATE,'MM-DD-YYYY HH24:MI:SS'),sum(decode(r.phase_code,'R',1,0)) - sum(decode(r.status_code,'W',1,0)) running,sum(decode(r.phase_code,'P',1,0)) pending
from applsys.fnd_concurrent_requests r,applsys.fnd_concurrent_processes p,applsys.fnd_concurrent_queues q
Where r.controlling_manager (+) = p.concurrent_process_id
and p.queue_application_id = q.application_id
and p.concurrent_queue_id = q.concurrent_queue_id
and q.max_processes >= 0 and (r.phase_code in ('R','P','I'))
group by r.ACTUAL_START_DATE
UNION ALL
SELECT (to_char(sysdate,'MM/DD/YYYY HH24:MI:SS')),0,0 from dual
WHERE NOT EXISTS
(select to_char(r.ACTUAL_START_DATE,'MM-DD-YYYY HH24:MI:SS'),sum(decode(r.phase_code,'R',1,0)) - sum(decode(r.status_code,'W',1,0)) running,sum(decode(r.phase_code,'P',1,0)) pending
from applsys.fnd_concurrent_requests r,applsys.fnd_concurrent_processes p,applsys.fnd_concurrent_queues q
Where r.controlling_manager (+) = p.concurrent_process_id
and p.queue_application_id = q.application_id
and p.concurrent_queue_id = q.concurrent_queue_id
and q.max_processes >= 0 and (r.phase_code in ('R','P','I'))
group by r.ACTUAL_START_DATE);
spool off;
-bash-3.2$


Code:

Current output after executing the concreq.sh script:

-bash-3.2$ cat TEST1_CM_04102013.csv

TO_CHAR(R.ACTUAL_ST, Running, PENDING
-------------------,--------,----------
04-16-2013 19:03:32, 1, 0

TO_CHAR(R.ACTUAL_ST, Running, PENDING
-------------------,--------,----------
04-16-2013 19:03:32, 1, 0

TO_CHAR(R.ACTUAL_ST, Running, PENDING
-------------------,--------,----------
04-16-2013 19:03:32, 1, 0

$


Expected output should be:

Code:

-bash-3.2$ cat TEST1_CM_04102013.csv

TO_CHAR(R.ACTUAL_ST, Running, PENDING
-------------------,--------,----------
04-16-2013 19:03:32, 1, 0
04-16-2013 19:03:32, 1, 0
04-16-2013 19:03:32, 1, 0
$


Thanks for your time!

Regards,
a1_win

From: http://www.unix.com/shell-programming-scripting/221303-how-get-sqlplus-column-header-once-csv-file.html

Reading XML in bash and importing in Mysql

By garf0r

Hi,
I’m not a pro bashscript writer but I’m learning and want to learn about my mistakes.
In the next script I have an error on rule 6 but I can’t find what I’m doing wrong …
I daily receive a file xml.xml and have to import it in an mysql database in a few existing tables.

Code:

#!/bin/bash
FILE="/transfer/store/xml.xml"
RESULT=$(xmlstarlet sel -t -v "count(//Appointment)" $FILE)
_DB_TABLE_PERSONS="persons"
_DB_TABLE_INVITATIONS="invitiations"
FOR (( i=0; i<$RESULT; i++ )) ; DO
PFIRST_NAME="$(xmlstarlet sel -t -v //Appointment[${i}+1]/person-info/data1 $FILE)"
PLAST_NAME="$(xmlstarlet sel -t -v //Appointment[${i}+1]/person-info/data2 $FILE)"
PADDRESS="$(xmlstarlet sel -t -v //Appointment[${i}+1]/person-info/data3 $FILE)"
PTELEPHONE="$(xmlstarlet sel -t -v //Appointment[${i}+1]/person-info/data4 $FILE)"
PEMAIL="$(xmlstarlet sel -t -v //Appointment[${i}+1]/person-info/email $FILE)"
VALUES(NULL,'${FIRST_NAME}','${LAST_NAME}','${ADDRESS}','${TELEPHONE}','${EMAIL}');"
QUERY="INSERT INTO ${_DB_TABLE_PERSONS} ('id','first_name','last_name','address','telephone','email') VALUES(NULL,'${PFIRST_NAME}','${PLAST_NAME}','${PADDRESS}','${PTELEPHONE}','${PEMAIL}');"
mysql --host=10.1.12.69 --user=test --password=test --database=test < $QUERY
ID_PERSONS=$(mysql --host=10.1.12.69 --user=test --password=test --database=test -se "SELECT LAST_INSERT_ID()")
IREMARK="$(xmlstarlet sel -t -v //Appointment[${i}+1]/person-info/data5 $FILE)"
ELAST_NAME="$(xmlstarlet sel -t -v //Appointment[${i}+1]/transfer-info/name $FILE)"
ID_EMPLOYEES=$(mysql --host=10.1.12.69 --user=test --password=test --database=test -se "SELECT id FROM employees WHERE last_name=${ELAST_NAME}")
LNAME="$(xmlstarlet sel -t -v //Appointment[${i}+1]/transfer-info/locatiebenaming $FILE)"
ID_LOCATIONS=$(mysql --host=10.1.12.69 --user=test --password=test --database=test -se "SELECT id FROM locations WHERE UCASE(name) like '%${ELAST_NAME}%'")
ID_I_TEMP="$(xmlstarlet sel -t -v //Appointment[${i}+1]/Date $FILE)"
IT_I_TEMP="$(xmlstarlet sel -t -v //Appointment[${i}+1]/Time/Begin $FILE)"
IDATE_INVITATION=${ID_I_TEMP:0:4}-${ID_I_TEMP:4:2}-${ID_I_TEMP:6:2}
IEXPIRATION_DATE=${IT_I_TEMP:0:2}-${IT_I_TEMP:2:2}
QUERY="INSERT INTO ${_DB_TABLE_INVITATIONS} ('id','person','employee','location','date_invitation','time_invitation','notification','remark') VALUES(NULL,'${PFIRST_NAME}','${PLAST_NAME}','${PADDRESS}','${PTELEPHONE}','${PEMAIL}');"
mysql --host=10.1.12.69 --user=test --password=test --database=test < $QUERY
DONE


XML:

Code:

20130409

1830
1900

bla
m00
street 10
0000/000000
subject
remarks

BARBRE5
TESTPERSON
TESTLOCATION
3702
45784139

20130412

1600
1730

bla2
m002
street 666
0000000000
andere

mailaddress2

BIRBRE5
TESTPERSON2
TESTLOCATION2
5802
4578487


From: http://www.unix.com/shell-programming-scripting/220839-reading-xml-bash-importing-mysql.html

Delete if condition met in a column

By jazzyzha

i have a table like this:

Code:

id, senderNumber, blacklist
-----------------------------
1 0835636326 Y
2 0373562343 Y
3 0273646833 Y


and I want to delete automatically if a new inserted row on another table consist anything on senderNumber column above using a BASH Script

I already using this script:

Code:

BLOCKLIST="$( mysql -uroot -pabcde smsd -N -s -r -e "SELECT senderNumber FROM blacklist WHERE senderBlock='Y'" | tr 'n' ' ')"
mysql -uroot -pabcde smsd -e "DELETE FROM inbox WHERE senderNumber = '$BLOCKLIST'"


and this the output:

Code:

sh -x /etc/autodelete.sh
+ tr n
+ mysql -uroot -pabcde smsd -N -s -r -e SELECT senderNumber FROM blacklist WHERE senderBlock='Y'
+ BLOCKLIST=083808034690 08164853500
+ mysql -uroot -pabcde smsd -e DELETE FROM inbox WHERE senderNumber = '083808034690 08164853500 '


but no luck. any help would be very appreciated.

…read more

Source: FULL ARTICLE at The UNIX and Linux Forums

Biogen Hits Primary Endpoints in MS Study

By Dan Carroll, The Motley Fool

Filed under:

Biotech company Biogen Idec‘s developmental multiple sclerosis drug daclizumab succeeded in the SELECT phase 2b trial evaluating its safety and effectiveness in treating patients with relapse-remitting MS. The trial results, originally published in The Lancet and reported by Biogen in a recent press statement, showed that two injection treatment groups had annual relapse rates at least 50% lower than a placebo group after one year.

The two treatment groups also showed that daclizumab reduced MS-related brain lesions and improved quality of life in patients, as well as reducing disability progression.

Dr. Gilmore O’Neill, Biogen’s vice president of medical research, said of the results, “Based on these initial data from SELECT, we believe DAC HYP would complement our robust portfolio of four approved MS products by potentially offering people with MS a new treatment alternative.”

Daclizumab is Biogen’s latest developmental candidate in the multiple sclerosis market it has dominated. The company’s oral MS treatment, Tecfidera, won FDA approval in late March and is poised to compete in the promising oral MS market.

The article Biogen Hits Primary Endpoints in MS Study originally appeared on Fool.com.

Fool contributor Dan Carroll and The Motley Fool have no position in any of the stocks mentioned. Try any of our Foolish newsletter services free for 30 days. We Fools don’t all hold the same opinions, but we all believe that considering a diverse range of insights makes us better investors. The Motley Fool has a disclosure policy.

Copyright © 1995 – 2013 The Motley Fool, LLC. All rights reserved. The Motley Fool has a disclosure policy.

(function(c,a){window.mixpanel=a;var b,d,h,e;b=c.createElement(“script”);
b.type=”text/javascript”;b.async=!0;b.src=(“https:”===c.location.protocol?”https:”:”http:”)+
‘//cdn.mxpnl.com/libs/mixpanel-2.2.min.js’;d=c.getElementsByTagName(“script”)[0];
d.parentNode.insertBefore(b,d);a._i=[];a.init=function(b,c,f){function d(a,b){
var c=b.split(“.”);2==c.length&&(a=a[c[0]],b=c[1]);a[b]=function(){a.push([b].concat(
Array.prototype.slice.call(arguments,0)))}}var g=a;”undefined”!==typeof f?g=a[f]=[]:
f=”mixpanel”;g.people=g.people||[];h=[‘disable’,’track’,’track_pageview’,’track_links’,
‘track_forms’,’register’,’register_once’,’unregister’,’identify’,’alias’,’name_tag’,
‘set_config’,’people.set’,’people.increment’];for(e=0;e<h.length;e++)d(g,h[e]);
a._i.push([b,c,f])};a.__SV=1.2;})(document,window.mixpanel||[]);
mixpanel.init("9659875b92ba8fa639ba476aedbb73b9");

function addEvent(obj, evType, fn, useCapture){
if (obj.addEventListener){
obj.addEventListener(evType, fn, useCapture);
return true;
} else if (obj.attachEvent){
var r = obj.attachEvent("on"+evType, fn);
return r;
}
}

addEvent(window, "load", function(){new FoolVisualSciences();})
addEvent(window, "load", function(){new PickAd();})

var themeName = 'dailyfinance.com';
var _gaq = _gaq || [];
_gaq.push(['_setAccount', 'UA-24928199-1']);
_gaq.push(['_trackPageview']);

(function () {

var ga = document.createElement('script');
ga.type = 'text/javascript';
ga.async = true;
ga.src = ('https:' == document.location.protocol ? 'https://ssl' : 'http://www') + '.google-analytics.com/ga.js';

var s = document.getElementsByTagName('script')[0];
s.parentNode.insertBefore(ga, s);
})();

<p style="clear: both;padding: 8px 0 0 0;height: 2px;font-size: 1px;border: …read more

Source: FULL ARTICLE at DailyFinance

Identifying a missing primary key

By figaro

I have the following method to identify missing primary keys in a MySQL database schema:

Code:

USE information_schema;
SELECT xx.table_name
FROM (SELECT table_name, COUNT(*) FROM columns WHERE table_schema = @myDB GROUP BY table_name, column_key) xx
GROUP BY xx.table_name
HAVING COUNT(*) = 1;


I am not particularly enamored with the xx solution, because one can never be certain that a table with that name does not already exist. Does anyone know of a solution which is more elegant?

…read more

Source: FULL ARTICLE at The UNIX and Linux Forums

The Lancet Publishes SELECT Study Evaluating Efficacy and Safety of Daclizumab HYP in Multiple Scler

By Business Wirevia The Motley Fool

Filed under:

The Lancet Publishes SELECT Study Evaluating Efficacy and Safety of Daclizumab HYP in Multiple Sclerosis

-Data Demonstrated That DAC HYP Significantly Reduced Annualized Relapse Rate by up to 54 Percent at One Year-

-Phase 3 Study Underway to Further Assess Once-Monthly DAC HYP for Multiple Sclerosis-

WESTON, Mass.–(BUSINESS WIRE)– Today Biogen Idec (NAS: BIIB) announced that results from the daclizumab high-yield process (DAC HYP) SELECT clinical trial have been published as an online article in The Lancet. SELECT was a Phase 2b study designed to determine the efficacy and safety of DAC HYP in patients with relapsing-remitting multiple sclerosis (RRMS).

Published results demonstrate that both 150 mg and 300 mg subcutaneous injections of DAC HYP, administered once every four weeks, met the study’s primary endpoint by significantly reducing annualized relapse rate (ARR) by 54 percent (p<0.0001) and 50 percent (p=0.0002), respectively, compared to placebo at one year. In addition, results demonstrated that DAC HYP reduced multiple sclerosis (MS) brain lesions compared to placebo.

“DAC HYP represents the type of innovative research we are focused on cultivating as part of our MS pipeline because it potentially targets the disease in a new way,” said Gilmore O’Neill, M.D., vice president, Medical Research at Biogen Idec. “Based on these initial data from SELECT, we believe DAC HYP would complement our robust portfolio of four approved MS products by potentially offering people with MS a new treatment alternative. We look forward to continuing to work with our partners at AbbVie to progress the DAC HYP program further.”

Both doses of DAC HYP met key secondary endpoints in the study by significantly reducing the proportion of patients who relapsed at one year, as well as MS brain lesion activity, including the cumulative number of new gadolinium-enhancing (Gd+) lesions between weeks eight and 24 and the number of new or newly enlarging T2-hyperintense lesions at one year. Both doses of DAC HYP also demonstrated a trend in improvements in quality of life (QoL) compared to placebo as measured by the Multiple Sclerosis Impact Scale (MSIS-29) physical impact score.

“Because MS is unique to each person, we need a variety of treatment options to attack the disease in different ways,” said Ralf Gold, M.D., professor/chair of the Department of Neurology at St. Josef-Hospital/Ruhr-University in Bochum, Germany, and …read more

Source: FULL ARTICLE at DailyFinance

Mailx recipient from mysql database

By jazzyzha

Dear All,

Can I make Mailx to read recipient address from a mysql database?

I already tried emailing with bash script:

SUBJECT=”TEST”
export EMAIL_ADDRESS=`mysql -uroot -pabcde email -e “SELECT email FROM recipient”`
mysql -uroot -pabcde email -e “SELECT ID, SenderName, Body FROM inbox” | mailx -s “$SUBJECT” -a From:sales@acme.com “$EMAIL_ADDRESS”

but no luck. Any help would be very appriciated. Thanks

…read more

Source: FULL ARTICLE at The UNIX and Linux Forums

$ symbol in sql query in shell script

By indira_s

Hi Team,

Can you please help me to resolve this issue.

Am unable to use this $ symbol in sql query in the shell script.

For Example:

# !/bin/sh

export USER_NAME=XXX
export PASSWORD=YYY

export ORACLE_SID=xamdb
echo $ORACLE_SID

echo ” Session Details …”

DATE_TIME=`date +%Y%m%d_%H%M%S`
echo $DATE_TIME

#`$ORACLE_HOME/bin/
sqlplus $USER_NAME/$PASSWORD@xamdb << EOF
set serveroutput on
set feedback off

SELECT count(1) AS con_count, machine, username , osuser, status
FROM v$session
WHERE type ‘BACKGROUND’
GROUP BY username, machine ,osuser, status
ORDER BY con_count DESC;

END;
/
EOF

echo “Completed Session details…”

Output:
======
SQL> SQL> SQL> SQL> SQL> 2 3 4 5 FROM v
*
ERROR at line 2:
ORA-00942: table or view does not exist

SQL> SQL> SP2-0042: unknown command “END” – rest of line ignored.
SQL> FROM v
*
ERROR at line 2:
ORA-00942: table or view does not exist

———- Post updated at 05:22 PM ———- Previous update was at 05:11 PM ———-

I got the answer. we should use symbol.
SELECT count(1) AS con_count, machine, username , osuser, status
FROM v$session
WHERE type ‘BACKGROUND’
GROUP BY username, machine ,osuser, status
ORDER BY con_count DESC;

…read more
Source: FULL ARTICLE at The UNIX and Linux Forums

How to get the missing date and day in a table?

By Venkatesh1

Hi Am using unix Aix Ksh
Have Created table called vv
and i have inserted two date

Code:

Select * from vv;

Output :-

New_date
21/02/2013
24/02/2013


I have tried Using One query but Unsuccessful so far..

Code:


SELECT l.new_date + '1 day' as miss
from vv as l
left outer join vv as r
on l.new_date + '1 day' = r.new_date
where r.new_date is null


When i tried running the above query am getting the output as

Code:

miss

22/02/2013
25/02/2013


i need the results as

Code:

miss
22/02/2013 , fri
23/02/2013. sat
24/02/2013 , sun


Can anyone help me pls ….

…read more
Source: FULL ARTICLE at The UNIX and Linux Forums

How to grep the grant statement and output to the different files?

By jediwannabe

Hi

currently I have a list of *.sql files.

one of the file, terminal is

Code:

Prompt Table TERMINAL;
CREATE TABLE TERMINAL
(
TERMINAL_ID NUMBER(8),
EXCEL_TERMINAL_ID NUMBER(8),
MERCHANT_ID NUMBER(8),
SETTLE_TIME VARCHAR2(4 CHAR)
);

COMMENT ON TABLE TERMINAL IS 'This tables stores all the terminal details';

COMMENT ON COLUMN TERMINAL.TERMINAL_ID IS 'EXCEL internal unique terminal Identifier. PK for Terminal table';

COMMENT ON COLUMN TERMINAL.EXCEL_TERMINAL_ID IS 'This is ID within a merchant. This number is re-initialized for each merchant';

COMMENT ON COLUMN TERMINAL.MERCHANT_ID IS 'Merchant identifier (References Merchant.merchant_id)';

COMMENT ON COLUMN TERMINAL.SETTLE_TIME IS 'Settlement time of terminal';

Prompt Privs on TABLE TERMINAL TO BONUS_USER to BONUS_PORTAL;
GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE, ON COMMIT REFRESH, QUERY REWRITE, DEBUG, FLASHBACK ON TERMINAL TO BONUS_USER;

Prompt Privs on TABLE TERMINAL TO EXCEL_TXN to EXCEL_TXN;
GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE ON TERMINAL TO EXCEL_TXN WITH GRANT OPTION;


My output will be to 2 files for one 1 file, terminal.sql
output file 1, terminal_to_bonus_user,sql

Code:


Prompt Privs on TABLE TERMINAL TO BONUS_USER to BONUS_PORTAL;
GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE, ON COMMIT REFRESH, QUERY REWRITE, DEBUG, FLASHBACK ON TERMINAL TO BONUS_USER;


My output file 2, terminal_to_excel_txn.sql

Code:

Prompt Privs on TABLE TERMINAL TO EXCEL_TXN to EXCEL_TXN;
GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE ON TERMINAL TO EXCEL_TXN WITH GRANT OPTION;


My ksh code, grep_grant_tables.sh is as follow:

Code:

#!/bin/ksh

for i in *.sql
do
if [ -Z `grep EXCEL_TXN "$i"` ]
grep XLS_TXN "$i" > "${i%.*}_to_excel_txn.sql"
fi
grep BONUS_PORTAL "$i" > "${i%.*}_to_bonus_user.sql"
done

exit


when I run my code

Code:

grep_grant_tables.sh: syntax error at line 7: `fi' unexpected


May I know how to resolve the above?

thanks a lot!

…read more
Source: FULL ARTICLE at The UNIX and Linux Forums

Psql replace blanks with character

By wxornot

Well as the title describes, its a pretty straight forward problem. I have a series of psql tables where there are lots of blanks. However there is at least one column, called name, that will never be blank. I want to write a select statement to get all of the contents of the table and then turn blanks into a string like NULL. Here is my select statement idea, but I just don’t have a strong enough grasp on how the statement should look:



SELECT name,date,value
FROM table
WHERE value=’ ‘ print as “NULL“;

I don’t want to update the actual table, just print it without blanks. Obviously the above code doesn’t work, but hopefully you get the idea. I appreciate the help.

Dave

Source: FULL ARTICLE at The UNIX and Linux Forums

Why execution is different from orginal bash registry$database?

By jediwannabe

Hi all,

here’s my script

Code:


#!/bin/ksh
if [ -z "$DB_CREATE_PATH" ]
then
export DB_CREATE_PATH=`pwd`
fi

echo
echo "********************--Menu--*****************************"
echo "*** "
echo "*** 1. Pre-Upgrade Steps "
echo "*** 7. Exit "

read a

if [ $a == 1 ]
then

echo "1 Pre-Upgrade Steps"
${ORACLE_HOME}/bin/sqlplus /nolog <<!EOF
set termout on
set echo on
set time on

connect / as SYSDBA;

column timecol new_value timestamp
column spool_extension new_value suffix
SELECT to_char(sysdate,'dd_Mon_yyyy_hhmi') timecol,'.log' spool_extension FROM
sys.dual;
column output new_value dbname
SELECT value || '_' output FROM v$parameter WHERE name = 'db_name';

--step 6 step 7 start
spool logs/tz_version_nls_char_&&dbname&&timestamp&&suffix
select TZ_VERSION from registry$database;
select value from NLS_DATABASE_PARAMETERS where parameter = 'NLS_NCHAR_CHARACTERSET';
spool off
--step 6 step 7 end

exit;
!EOF
fi


when I examine the output code it is as follow

Code:

09:30:41 SQL> select TZ_VERSION from registry;
select TZ_VERSION from registry
*
ERROR at line 1:
ORA-00942: table or view does not exist

09:30:41 SQL> select value from NLS_DATABASE_PARAMETERS where parameter = 'NLS_NCHAR_CHARACTERSET';

VALUE
-----------------------------------------------------------------------------------------------------------------------------
-------------------------
AL16UTF16

1 row selected.

09:30:41 SQL> spool off


can someone tell me how do I make sure that registry$database is queried instead of registry?

thanks

Source: FULL ARTICLE at The UNIX and Linux Forums

Help with script to create users from database query

By bucketuk
Code:
#!/bin/bash
user=`mysql userList -uuserlist -puserlistpassword -s -N -e “SELECT userName FROM users WHERE activated=’n'”`
for i in $user; do
useradd “$i” -m
done
This is what I have done so far. But obviously it still does not work.
I’m trying to create users based on information stored in a database. At the moment I’m just concentrating on creating users. But would like to add passwords as well. Anybody able to point me in the right direction

EDIT: I’m very new to bash scripting as well. Hence why I’m struggling.

Many thanks

Bucket
Source: The UNIX and Linux Forums