Hot answers tagged

5

You are mixing shell and sqlplus commands. sqlplus doesn't understand redirections, you have to use the spool keyword. For instance: SQL> spool /tmp/result.txt SQL> start requete.sql SQL> spool off From shell: $ sqlplus scott/tiger @requete.sql > /tmp/result.txt


4

setting of shell variable should be made before sqlplus call IFS=$'\n' for i in $(cat $LST_HOME/doc.lst) do P1=$(echo $i | awk '{print $1}') P2=$(echo $i | awk '{print $2}') P3=$(echo $i | awk '{print $3}') sqlplus64 $UserCoribudg/$PassCoribudg@$ORACLE_SID << EOF @$SQL_HOME/update_p.sql $P1 $P2 $P3 commit; exit EOF /data/java_1.6_XX/bin/java -cp ...


4

In ksh93 and zsh, there's a string replacement construct ${VARIABLE//PATTERN/REPLACEMENT}, used twice in the following snippet: once to replace ' by '' and once to replace newlines by '+char(10)+'. If there are no newlines in the input string, you can omit the second assignment command. quoted_string=\'${raw_string//\'/\'\'}\' ...


3

As indicated in Shell : while read line nested, you can just do something like (sed "s|^|@${SQL_HOME}/update_p.sql |" ${LST_HOME}/doc.lst; echo exit) | sqlplus64 ${UserCoribudg}/${PassCoribudg}@${ORACLE_SID} If you want to run a command after every SQL update, you could loop, although there are a number of caveats (globbing etc.). If the values in your ...


3

Dovecot wiki # Use "host= ... pass=foo#bar" if your password has '#' character so your /etc/dovecot/dovecot-sql.conf.ext should be driver = mysql connect = "host=127.0.0.1 dbname=froxlor user=froxlor password=test#test" default_pass_scheme = CRYPT


3

Here's a version of the script that works, and fixes some things you may not have considered: #!/bin/bash ( echo '-- some startup SQL*plus code' echo 'set echo off;' echo '-- etc' find /home/devuser -name 'BI*' -type f | awk -- '{ gsub("\047", "\047\047", $0); printf("INSERT INTO table1(file_name,status) " ...


3

for VARIABLE in 1 2 3 4 5 .. N do command1 command2 commandN done <<anything given here is taken as variable>> This is the basic syntax and after done, commit should be given in next line. So your code should be. #!/bin/bash names = find /home/devuser -name 'BI*' sqlplus -s schema_name/passwd << EOF for name in {names[@]} ...


2

This looks like an XY Problem - you say you want to add a break between every two words, but what you really want to do is pretty-print the data returned by an SQL query. Your problem is caused by the fact that your executeSQLQuery function (or script or program) returns formatted output rather than just the data....and it looks like it is doing that ...


2

output=executeSQLQuery "$QUERY" |sed 's/: [^ ]* /& \n/g' The above command yields user@user:~> z="Your Name: Samanta Your Phone: 111-111-1111 Email: testemail@test.com" user@user:~> echo $z | sed 's/: [^ ]* /& \n/g' Your Name: Samanta Your Phone: 111-111-1111 Email: testemail@test.com user@user:~> The command has used regex ...


2

If you only want to escape every double quotation mark and backslash you could use perl -wpe 's/([\\"])/\\$1/g' You could also use this with xclip: cat myfile | perl -wpe 's/([\\"])/\\$1/g' | xclip -selection clipboard


2

two things: while should be in this form while condition; do your code from while done also, make sure ENDOFSQL is from new line and there are no spaces between.


2

You can use sed: sed -e 's/[;,()'\'']/ /g;s/ */ /g' input.sql > output.txt or, if you rather want to specify what characters to keep: sed -e 's/[^a-zA-Z*0-9]/ /g;s/ */ /g' input.sql > output.txt


2

You probably want something like: #!/bin/ksh typeset -Au codes read -rA codes?"Enter codes: " IFS=, sql="select * from table where request_no in (${codes[*]})" echo "$sql" Running that looks like: Enter codes: foo bar baz select * from table where request_no in (FOO,BAR,BAZ) Proper quoting of values left as an exercise.


2

I've also come across this unwanted whitespace when working with a different database. Clearly, shell scripts and tools like SQLPlus are the wrong thing to use for extracting data from databases but there are occasions when it's convenient. My solution is to strip leading and trailing whitespace from the result: psnr() { sqlplus -s USR/PASS@PROD << ...


2

We can combine all of the commands from the original columns= shell pipeline into one sed script. This sed script modifies only the first line of the input and then exits. The following does exactly the same thing as the columns= in the original question: columns=$( sed ' 1 { # execute block on ...


1

When you use a hereis string <<END all $variable expansion is done. This is how your define lines can work. But you dont want the $lastMonth and so on in the WHERE statement to be expanded, so you need to quote them with backslash. The single quote has no particular effect here as we are inside a hereis. However, it seems that sqlplus uses ampersand ...


1

Using tr: $ tr -s "()',;" " " < data select * from Users insert into Users values UR01 Kim Director


1

If awk is not an substantial requirement, you could cut the file in pieces on fixed bytes (and grep away empty lines): cut -b 37-47 data | grep -v '^$'


1

I'm no awk guru, but if your output is always the same (I mean, second line is headers, and third line contains results), you can use something like this: cat /tmp/test.txt | awk 'NR==2 {headers=$0; start=0; len=0} NR==3 { start=index($0," "); len=length($2)+1; print substr(headers,start,len) ; print substr($0,start,len)} NR==4 {print substr($0,start,len)}' ...


1

IFS=$'\n' for i in $(cat doc.lst) do P1=$(echo $i|awk '{print $1}') P2=$(echo $i|awk '{print $2}') P3=$(echo $i|awk '{print $3}') #now your SQL set would look like **SET P.DATED='$P1', P.DATEF='$P1', P.COD='$P2', P.CODB='$P3'** and you can launch your sql done


1

You might have luck running every query through EXPLAIN ANALYZE and finding unique results in the query plans.


1

If the queries are predictable enough, maybe you could simply sed out the parameter values--e.g. if many queries contain equality comparison with numbers, sed 's/=[[:digit:]]+//g' would remove all the actual numbers, leaving only the column names. Otherwise, the only really general solutions I can think of are pattern recognition techniques like k-nearest ...


1

With xmllint using libxml version 20708: Name of root node "something": xmllint --xpath "name()" file.xml Text "lol": xmllint --xpath "//*/*/text()" file.xml Script sql.sh: #!/bin/bash file="$1" table=$(xmllint --xpath "name()" "$file") value=$(xmllint --xpath "//*/*/text()" "$file") cat << EOF INSERT INTO \`${table}\` VALUES (${value}) EOF ...


1

At minimum, you need to set ORACLE_HOME. You probably also want to add something to PATH. When you installed Oracle, you picked a home directory. For example, let's say you used /opt/oracle/oracle/product/11.2.0/dbhome_1. Then you'd run something like this in the shell to set the environment: export ORACLE_HOME=/opt/oracle/oracle/product/11.2.0/dbhome_1 ...


1

If your shell script is bash or sh you can try appending the -x switch to the shebang and then run the script. Typically used for debugging shell scripts it will print the next line/command before executing. So if you have the sample script below that logs to file logfile #!/bin/bash -x echo "Hello world!" >> logfile echo "Second command!" >> ...


1

First, you need to get SQL*Plus to error out if a SQL error occurs. You can do this by adding: WHENEVER SQLERROR EXIT FAILURE to your SQL script (probably up top). You can also give different codes (small non-negative integers; non-zero = failure) in place of the word FAILURE. These will come back to your shell script in $?. So you can then have your ...


1

The problem with the output is that you would like to group three words (though logically key-value pair) in the first line of output, another three in the next and final two in the third line. For this particular problem, the fast way would be: executeSQLQuery "$QUERY" | awk '{print $1 " " $2 " " $3 "\n" $4 " " $5 " " $6 "\n" $7 " "$8 }' But generally ...


1

While it is technically possible to do date arithmetic in sed, it is not at all the right tool for the job. Use a tool like awk or perl which has integer arithmetic built in. Your requirement is an unusual one for date manipulations, so you'll need a rich date manipulation library if you don't want to hard-code the date arithmetic. Perl's Date::Manip has ...


1

I doubt that sed is the right tool for the job, in this case. I think you probably want to use awk, if you're already familiar with awk, otherwise, write a program. I've known an engineer who used sed and awk to create MSC/NASTRAN input files, which had even stricter requirements than what you mention, but he was quite familiar with the tools, so cryptic ...



Only top voted, non community-wiki answers of a minimum length are eligible