Monday, September 19, 2016

Split comma separated string and pass to IN clause of SQL SELECT statement

This was a query from reporting team. Question looked too simple , but the solution was not obvious. 


Problem

We get a colon separated string (1:3)  that we would need to pass to the IN clause of a select statement to get the required rows. 
SQL>select * from test;ID Text1   A2   B3   CSQL>select * from test where ID in :P_INPUT  #P_INPUT=1:3No rows selected
Here we expected 2 rows instead we got no rows. 

Solution

  • Split the input string based on delimiter
  • Get the individual strings as rows

For the above, we use a combination of  regexp_substr function and connect by features in oracle. 

SQL>select * from test where ID in (     select regexp_substr(:P_INPUT,'[^:]+', 1, level) from dual  connect by regexp_substr(:P_INPUT, '[^:]+', 1, level) is not null;)ID Text1   A3   C


Tuesday, July 16, 2013

Quick Dirty cscope starter

find . -name "*.c" -o -name "*.cpp" -o -name "*.h" -o -name "*.hpp" > cscope.files
cscope -q -R -b -i cscope.files
cscope -d

Saturday, December 01, 2012

Installing Sun JDK 7 on Ubuntu

Given that Oracle does not have a release for Ubuntu platform, one way to seamlessly install the latest version is

$ sudo add-apt-repository ppa:webupd8team/java

$ sudo apt-get update

$sudo apt-get install oracle-java7-installer

Added after inputs from Kiran for Centos

wget --no-cookies --no-check-certificate --header "Cookie: gpw_e24=http%3A%2F%2Fwww.oracle.com%2F" "http://download.oracle.com/otn-pub/java/jdk/7u51-b13/jre-7u51-linux-x64.rpm"

Thursday, November 01, 2012

Screen way of terminal management

What is screen?

It is  a terminal multiplexer, essentially giving the ability . man screen is the best option still.

Getting started video can be useful.

Why screen?

Its original reason for existence was allowing you to switch between sub-sessions on a video-display terminal, but it grew to allow sessions that could be detached and reattached (if you went home for the day, or say you were connecting via a glitchy network) and eventually to allow the same session to be simultaneously accessed from multiple places.

Real power of screen


  1. Persistence of the terminal based work. 
  2. Start your builds -- go home and see all the results intact. Resistant to laptop or network snags.
  3. Unlimited windows (unlike the hardcoded number of Linux virtual consoles)
  4. Scroll-back buffer (not limited to video memory like Linux virtual consoles) & Logging.
  5. Copy/paste between windows.
  6. Notification of either activity or inactivity in a window
  7. Split terminal (horizontally and vertically) into multiple regions
  8. Locking other users out of terminal.



Advanced users of screen

Buffering

Ctrl +a+ [  --------- Page or arrow keys work best
Ctrl +a+ ]  --------- To close the buffer read session

Helpful on remote machines where the logs are being tail-ed.

Terminal Scrolling

Though the above buffering solution of (Ctrl+a+[ ) helps, screen uses alternate screen. So one way is to tell screen not to use this. Scroll-back can also be achieved on the terminal using
‘termcapinfo xterm ti@:te@’ to your .screenrc file.

Starting and Naming sessions


screen -S  

Reconnecting to screen session


screen -x

Split Screen


You can split your terminal windows horizontally and vertically (for vertical split, you need to patch your screen).
To Split the screen horizontally: Ctrl+a & S
To split the screen vertically: Ctrl+a & | or Ctrl+a & V ( the screen has to be patched for this)
To switch between windows: Ctrl+a & Tab
To kill your current window: Ctrl+a & X

Logging


The best thing what I like about screen is that you can have logs of anything you have done on screen, which can be used in lot of things in future. To enable logging, use “Ctrl+a” followed by “H“. This will create a file with name “screenlog.0” in your home directory, which will contain anything or everything you have done on the screen. If you want to stop logging, use the same, “Ctrl+a” & “H“.
You can also enable logging while starting your screen session, which can be done by using “-L” switch:
screen -L -S

Copying and Pasting across terminals 

To enter copy mode in screen, hit Ctrl+A, then [. You can now use the arrow keys, or vim-like keybindings, to move around the screen. 0 gets you to the start of a line, and $ to the end of a line.

  • When you've reached the point where you want to start the copy, hit Enter.
  • Move the cursor to the end point of your selection (you'll see what you're copying highlighted), and then hit Enter again.
  • Now move the cursor to wherever you want to paste the selection (you can move to another screen within your session), and hit Ctrl+A ] to paste the selection.
  • You can also use Ctrl+A > filename to paste the selection to a filename and Ctrl+A < filename to read a file into the selection buffer so you can then paste it out again using Ctrl+A ].

When in copy mode, you can also use screen's scrollback feature (i.e., you can scroll back upward past the top of the currently displayed text) and the search function.

Command line Ctrl+a does not work in screen?

It works in a different way where ctrl+a+a will get the effect.

Searching in the buffer (Like VIM )

When in Buffer mode (Ctrl + [) use / for backword search and ? for forward search. 

Getting ..... lines on the screen terminal

You see this when the user is connected in a multi-user mode and 2 users are on the same terminal. To overcome this, first issue screen -d and then connect using screen -x  

Increasing the scroll back limit

defscrollback 1000
This increases the scrollback to 1,000 lines; you can edit this value as you prefer.

Tuesday, October 16, 2012

Password-less ssh login




ssh from machine1 to machine2 should be password-less (say for some automation tasks)

user1@machine1>pwd
/home/user1/.ssh/
user1@machine1> ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/home/user1/.ssh/id_rsa):
Created directory '/home/user1/.ssh'.
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/user1/.ssh/id_rsa.
Your public key has been saved in /home/user1/.ssh/id_rsa.pub.
The key fingerprint is:
3e:4f:05:79:3a:9f:96:7c:3b:ad:e9:58:37:bc:37:e4 user1@machine1
user1@machine1>cat ~/.ssh/id_rsa.pub | ssh user1@machine2 'cat >> ~/.ssh/authorized_keys && chmod 640 ~/.ssh/authorized_keys'
user1@machine2's password:
user1@machine1>ssh user1@machine2
user1@machine2>hostname
machine2

Friday, October 21, 2011

Mail thru javascript

you can automate sending bulk emails. In case you dont want to send BCCs or even CCs, (some rare cases, i agree)..

if your content is all text or html, (the example given is for text), and the mail-ids you need to send individual mails are one per line in a google spreadsheet (customisable to add other data),
you can open the spreadsheet, go to tools->ScriptEditor
and start typing your javascript! heres an example that sends each mail to one individual at a time, in clear text

//This thing just adds a convenient menu/submenu in your spreadsheet window!


function onOpen() {
  var subMenus = [];
  subMenus.push({name: "Email File to selected Range", functionName: "mysendmail"});
  SpreadsheetApp.getActiveSpreadsheet().addMenu("Email", subMenus);
}

function mysendmail() {
  var numAddrs=SpreadsheetApp.getActiveRange().getValues().length;

  for (var i=0; i
    var toAddr=SpreadsheetApp.getActiveRange().getValues()[i].toString();
    //Browser.msgBox("what is in toAddr is: " + SpreadsheetApp.getActiveRange().getValues()[i].toString());

// this is the line that sends the mail
    GmailApp.sendEmail(toAddr, "sendmail", "testing");
  }
}

if you want to attach a file, you need to use MailApp's sendEmail interface that accepts the attachment.

MailApp.sendEmail(recipients, emailSubject, emailMessage, {attachments: fileName});

check the documentation for other cool scripting interfaces with google apps.

Monday, February 28, 2011

RSS feed for gmail ...

I like this feature, where gmail is now accessible over rss feed.

try this in firefox ( is rss feed support there in chrome by now? )
https://USERNAME:PASSWORD@gmail.google.com/gmail/feed/atom

Though this feature is nice , i wonder if there are any uses of this feature in-spite of PoP3 or SMTP clients