• Find us:
    +1-669-900-5138   |   +44-203-372-5553
  • Free Newsletter

    Get Latest Updates

  • Make Training Enquiry


    Company

  • Categories

  • Archive

  • Embedding SQL in Shell

    Posted by "" in "database, scripts, Unix" on 2008-10-16

    Share on FacebookTweet about this on TwitterShare on Google+Share on LinkedInEmail this to someone

    Embedding a SQL inside Shell Script:-

    Here is a tip on how to run sqlplus scripts within a shell script.

    This is an example of how to pass database values into shell variables, and to make shell scripts more dynamic. This will help DBA to automate certain DBA tasks, also to setup some automation using cron.

    #!/bin/sh
    VAR1=`sqlplus -s username/password  <<end
    set pagesize 0 feedback off ver off heading off echo off
    select  sysdate from dual;
    exit;
    end`
    echo “system date is ” $VAR1
    #end of shell script

    This will retrieve the system date from Oracle but you get the idea that you can expand the select script to get whatever you want from the database and place it in a shell variable where you can make decisions on it.

    2 Responses to “Embedding SQL in Shell”

    1. Gary says:

      Yipes.
      Firstly, DON’T put username/password on the command line where any monkey who can type ‘ps -ef’ can see it.
      Secondly, sqlplus is supposed to be interactive. If you are scripting SQL, look at perl (or python or php). Error/exception handling is much easier and you can keep the connection open for the entire script, avoiding any repeated sqlplus startup/logon/logoff.

    2. Rupesh says:

      Appreciate your hint, I second you that username/password cannot be disclosed, but here this was a simple narration to show how we can embed SQL inside shell, well in advance you can use external files to store username/password.

      This objective was not to make any interactive scripts, instead to automate some DBA tasks by placing embedded sql in shell.

    Leave a Reply



  • K21 Technologies is among the most experienced Oracle Gold Partner for Identity Access Management service providers. We work with application development companies and in-house technology division to help achieve significant returns on their IT security investment. Our clientele includes some of the globally renowned corporate, which speaks of our expertise in our field.

    We have the most talented and experienced team that can swiftly deploy security solutions even in complex IT ecosystem. Our clients highly appreciate our timely implementation, interactive training, on-demand support and community resources.
  • CONTACTS

    K21 Technologies
    8 Magnolia Place, Harrow,
    London, HA2 6DS

    UK: +44(0)7476444481
    USA: +1-888-414-1821

  • 2014, K21 Technologies. All rights reserved DMCA.com
  • TOP