Akdora’s Blog

Programming, Oracle, Life, Fun

How to remove spaces in a string in Shell March 8, 2011

Filed under: Shell — Akdora @ 9:45 am
Tags: , , ,

I experienced that when you connect to Oracle database from shell and get some data from the table, if the length of the column value is more than 80 letters. Shell puts space in every 80th letter automaticly. I do not know why, but it is really interesting. I counted the number 80 for my environment. It may differ in different systems.

Anyway, I need to remove that interesting spaces. So here is a script to do this.

RETVAL=`echo $RETVAL | sed 's/ //g'`

We use the SED command. Its use like ‘s/[seach_letter]/[replacement]/g’. In my example [seach_letter] is ” ” space and [replacement] is nothing. For example; If we want to replace ”  ” (space) characters with “_” (underscore) character. Here is how we do this:


RETVAL=`echo $RETVAL | sed 's/ /_/g'`

I wrote this quick entry, because if you do not remember how to do something like this and want to google it. It is not easy to find sometimes 🙂

 

Calling SQL and PL/SQL codes from Shell March 6, 2011

Filed under: Linux,Oracle — Akdora @ 11:24 am
Tags: , , , , , , , , ,

#################################################################################
#!/bin/ksh
##################################################################################
# Filename: test.sh
# Name    : Özay Akdora (https://akdora.wordpress.com)
# Date		: 04.03.2011
# Purpose : Oracle - Shell Integration (SQL Operations & PL/SQL Execution )
# Env			: This example is uses Oracle 11G with "/export/home/oracle/app/product/11.2c" path and korn shell script

# First of all we need to define some variable to access Oracle Database
export ORACLE_HOME=/export/home/oracle/app/product/11.2c
export LD_LIBRARY_PATH=/export/home/oracle/app/product/11.2c/lib
export PATH=/usr/local/bin:/usr/local/sbin:/usr/bin:/export/home/oracle/app/product/11.2c/bin:/export/home/ubtool/ubSQL_5.0.1:

# Then, we need to define username/password (@ optional another database TNS definition).
export db_connection_url=scott/tiger@MYDB
# I needed to connect to another database to query a table that has my configuration data.
# Your shell code may locate in an another machine that you want to connect.
# Anyway, You have to define TNS info of MYDB database to /export/home/oracle/app/product/11.2c/network/admin/tnsnames.ora

# You have to give permission to execute this file. For example : chmod 777 test.sh
# You also have to give permission, if you put this file to a folder that you created new.

# Run as : "./test.sh"

#######################################################################
# Let's see what we got here;
# Example 1: Access db and query SYSDATE:

echo "########## Example 1: SYSDATE ############"
RETVAL=`sqlplus -s $db_connection_url <<EOF
select to_char(sysdate,'dd.mm.yyyy hh24:mi:ss') my_date from dual;
exit;
EOF`

echo $RETVAL
# As you see, we connected to database with sqlplus command ( We defined it's path to PATH above.
# Then we set username and password with $db_connection_url variable
# After that, we tell the command line execute what you see between EOF words 🙂 (Note: You can use ENDASDKAJSDK instead of EOF ) 🙂
# Finally, we set the date to $RETVAL variable and prints it to screen.
# Let me explain that : Think like this way. We are using SQLPLUS (in any environment) and executing some commands and getting some result on the screen
# In this example; We set everything to $RETVAL that we see the results of sqlplus screen. Everything!

#######################################################################
# Example 2 : SQLCODE
echo "########## Example 1: SQLCODE ############"
RETVAL=`sqlplus -s $db_connection_url <<EOF
whenever sqlerror exit sql.sqlcode;
select to_char(sysdate,'dd.mm.yyyy hh24:mi:ss') my_date from dual;
exit;
EOF`

ERROR_CODE=$?

#Let me check the error code.
if [ $ERROR_CODE != 0 ]
then
  echo "There are some errors. ErrorCode: $ERROR_CODE";
else
  echo "Command executes successfully. ErrorCode: $ERROR_CODE";
fi

echo $RETVAL
#######################################################################
# Example 3 : SQL(Select) with WHERE clause
echo "########## Example 3: SQL with WHERE clause ############"

#Declare the variable
feed_id=1;

RETVAL=`sqlplus -s $db_connection_url <<EOF
set feed off echo off head off 
select * from extract_external_feeds where feed_id='$feed_id';
exit;
EOF`

#echo $RETVAL
v_result=`echo -n $RETVAL|awk '{printf $4}'`
echo $v_result

# Let's see what we done here. 
# We added "set feed off echo off head off " line to command. So, I do want to see column names as result.
# In (feed_id='$feed_id') section do not forget to use (').
# We writes (awk '{printf $3}') the data of third column in my table.
# Note: We assumes that the data in the columns do not have any space like " " space  🙂
#######################################################################
# Example 4 : SQL(Select) with WHERE clause & Multiple Variables
echo "########## Example 4: SQL with WHERE clause ############"

#Declare the variable
feed_id=1;

RETVAL=`sqlplus -s $db_connection_url <<EOF
set feed off echo off head off 
select ROW_LINE from EXTRACT_EXTERNAL_FEEDS_VW where feed_id='$feed_id';
exit;
EOF`

#echo $RETVAL
var1=`echo -n $RETVAL|awk -F\# '{printf $1}'`
var2=`echo -n $RETVAL|awk -F\# '{printf $2}'`
var3=`echo -n $RETVAL|awk -F\# '{printf $3}'`
echo $var1
echo $var2
echo $var3

# Since, I do want to make query for each column value, I return them with and deliminater like "#". The we parse it with awk command in shell.
# SELECT FEED_ID, FILE_PATH, MAIN_PATH, DB_VIEW_NAME FROM EXTRACT_EXTERNAL_FEEDS
#       
# CREATE OR REPLACE VIEW EXTRACT_EXTERNAL_FEEDS_VW AS
#  SELECT FILE_PATH || '#' || MAIN_PATH || '#' || DB_VIEW_NAME AS ROW_LINE, FEED_ID
#    FROM EXTRACT_EXTERNAL_FEEDS

#######################################################################
# Example 5 : SQL (Select) with dynamic column names
echo "########## Example 5 : SQL (Select) with dynamic column names ############"

feed_id=1;
column_name='DB_VIEW_NAME';

RETVAL=`sqlplus -s $db_connection_url <<EOF
set feed off echo off head off 
select $column_name from EXTRACT_EXTERNAL_FEEDS where feed_id='$feed_id';
exit;
EOF`

echo $RETVAL
# We can pass variables to SQLPLUS commands like ($column_name)
#######################################################################
# Example 6 : Calling PL/SQL 
echo "########## Example 6 : Calling PL/SQL ############"

RETVAL=`sqlplus -s $db_connection_url <<EOF
var x number ;
exec  pk_my_package.prc_my_proc(1,1,:x);
print x;
exit;
EOF`

echo $RETVAL
# We can call PL/SQL codes as we work in SQLPLUS and read the outputs
#######################################################################
# Example 7 : Calling Dynamic PL/SQL 
echo "########## Example 7 : Calling Dynamic PL/SQL ############"
dynamicCode1="declare v_date date; begin select sysdate into v_date from dual; dbms_output.put_line(to_date(v_date,'dd.mm.yyyy hh24:mi:ss') ); end; ";
dynamicCode2="
var x varchar2(2000); 
exec prc_my_proc;
print x;";

RETVAL=`sqlplus -s $db_connection_url <<EOF
set serveroutput on;
$dynamicCode2
exit;
EOF`

echo $RETVAL
# We can also set dynamic variable that contains PL/SQL commands, but you have to set the variable with new lines to execute them successfully.
# If you set the variable like $dynamicCode1. The code does not work. You have to do it as $dynamicCode2. You may read this code from an config file or oracle table.
#######################################################################

# Finally, if we want to do fancy. The following is a way one can describe this script as Oracle job. 
# 
#	BEGIN
#		DBMS_SCHEDULER.CREATE_PROGRAM(PROGRAM_NAME        => 'akdora_test',
#									  PROGRAM_TYPE        => 'EXECUTABLE',
#									  PROGRAM_ACTION      => '/export/home/bonjovi/ozay/test.sh',
#									  NUMBER_OF_ARGUMENTS => 0,
#									  ENABLED             => TRUE,
#									  COMMENTS            => 'Fantasy Program');
#	END;
#	/
#
#
#
# I hope this entry is helpful for you.
# Cheers