How to find tables and columns in Oracle Databases

Very often is necessary to find tables in a database, that corresponds to a specific pattern and there are specific Oracle Database tables which describe that information:

ALL_TABLES – list of all tables in the current database that are accessible to the current user;
ALL_TAB_COLUMNS – list of all columns in the database that are accessible to the current user;

• Find all Tables that have PATTERN in the table name:

SELECT TABLE_NAME FROM  ALL_TABLES
WHERE TABLE_NAME LIKE '%PATTERN%';

• Find all tables that have at least one column that matches a specific PATTERN in the column name:

SELECT TABLE_NAME, COLUMN_NAME FROM ALL_TAB_COLUMNS
WHERE COLUMN_NAME LIKE '%PATTERN%';

Oracle Minus

The sql minus operator is used between two SQL statements and returns only rows returned by the first query but not by the second:

SELECT column_name FROM table_name1
MINUS
SELECT column_name FROM table_name2;

Very useful, for instance to make a comparison between two tables and insert the values on the second table that do not exist in the 1st one. For e.g.:

declare

cursor c1 is
SELECT emp_name FROM employers
MINUS
SELECT emp_name FROM employers_bck;

begin
    for val in c1 loop
        insert into employers_backup(emp_name, inserted_date)
        values (val.emp_name, sysdate);
    end loop;
    commit;
end;
/

It could also be done by using cursors with parameters, passing the emp_name from employers table as a parameter, and then inserting the non-existent employers from employers table, in the employers_bck table.

But this approach is very bad regarding performance. So in these situations is very recommended to use SQL Minus as shown above.

Unix: Pipes

A pipe is a temporary storage place where the output of one command is stored and then it’s passed as input to the 2nd command.

So, with pipes is possible to connect the output of one program to the input of another, without any temporary file.
It is used to run two or more commands at the same time, from the same command line.

For e.g.: $ ls / -R | wc –l

The command above give us the number of files in our system.

Unix: Output redirection

Following up on Unix stuff for begginers…

Usually, unix commands take input from keyboard and show outputs on the standard output, the screen.
For e.g.: $ ls

But it is possible to send the output to a file, using the following syntax:
linux-command > filename

For e.g.: $ cal > test

If the file already exist, it will be overwritten, else a new file will be created.
To fix this problem and append the output to the end of the file, it’s used the following syntax: linux-command >> filename

For e.g.: $ w >> appendTest

If file doesn’t exist, it’s created a new file.

Is also possible to get input from file.
Syntax: linux-command < filename

For e.g.:

$ cat > test
this is some text.
this is more text.

Ctrl+D to save

 $ tr "a-z" "A-Z" < test > uppercaseTest

 $ cat uppercaseTest
 THIS IS SOME TEXT.
 THIS IS MORE TEXT.

This last example, get a text file as input and transforms all lowercase chars within it, to uppercase. Then its output is redirected and saved to a new file (“uppercaseTest”).

Creating a backup table from another… (ORACLE)

Imagine that you need to do a risky operation on your production Database (DB).
Since you can loose all your data, a good thing to do is to create a backup table with all columns and important records.

You can do it, with the following sql statement:

CREATE TABLE my_NEW_Backup_Table AS
( SELECT * FROM OLD_TABLE WHERE CLAUSE);

Simple and very useful!

MySQL #1

MySQL is the world’s most popular open source Relational Database Management System (RDBMS).
It is a popular choice for use in Web Applications because it is secure, robust, fast, Multi-Platform and very important these days: free!
Perfect for small to medium applications.

Some useful commands to start:

• mysqladmin -u root -ppassword create databasename # Creates a new database
• mysql -u root -p # Command used to connect to MySQL (The password request will be prompted)
• mysql -u root -ppassword_name # Command used to connect to MySQL (no space between the -p and the password)
• mysql -u root -ppassword database_name # Command used to connect with MySQL and start using a database
• mysql -u root –password=password_name database_name # Command used to connect with MySQL and start using a database
• CREATE DATABASE database_name; # Allows to create a database
• SHOW DATABASES; # Show all Databases
• USE database_name; # Allows us to change database
• SELECT DATABASE(); # Tells which database is currently being used
• \q; # This command allows us to exit from the Database;

Validations with Regexps (Ruby)

For those who don’t know what a regexp (regular expression) is: A regexp is a pattern or a set of rules, that can be used to match against a String.

One way to create a regular expression in Ruby, is to put the pattern between 2 slashes, like this: /pattern/
There are many tutorials on internet where you can learn more about Regular Expressions, and its power: the regexp wild cards!
For instance, here.
Just Google it!
Google is your friend!

Imagine that you need to make a validation of an email field, to make sure the user inserts a valid email, or you need to verify if a specific field is numeric?

Regular Expressions can help you doing that:

• To validate a numeric field (it accept negative and decimal values): ^[+|-]?(\d)+((\.)(\d)+)?$
• To get a valid email: ^[\w\.-]+@[\w\.-]+\.(\w){2,6}$

You can play with Regular Expressions on this fantastic website: Rubular.com
Or use this Ruby program to test the above patterns and make your validations:

def isNumeric?(str)
  pattern = /^[+|-]?(\d)+((\.)(\d)+)?$/
  matches = pattern.match(str)
  !matches.nil?
end

def isValidEmail?(str)
  pattern = /^[\w\.-]+@[\w\.-]+\.(\w){2,6}$/
  matches = pattern.match(str)
  !matches.nil?
end

def numericProg
  repeat = true
  while repeat
    puts
    print 'Digit something: '
    STDOUT.flush
    myStr = gets.chomp
    if isNumeric?(myStr)
      puts "My String is numeric."
      puts
    else
      puts "Not numeric."
      puts
    end
    begin
      print 'Try again(s/n): '
      STDOUT.flush
      ans = gets.chomp
      if ans.downcase == "n"
        repeat = false
      end
    end while (ans.downcase != "n" && ans.downcase != "s")
  end
end

def emailProg
  repeat = true
  while repeat
    puts
    print 'Digit an email: '
    STDOUT.flush
    myStr = gets.chomp
    if isValidEmail?(myStr)
      puts "It is a valid email!  "
      puts
    else
      puts "It isn\'t a valid email!  "
      puts
    end
    begin
      print 'Try again(s/n): '
      STDOUT.flush
      ans = gets.chomp
      if ans.downcase == "n"
        repeat = false
      end
    end while (ans.downcase != "n" && ans.downcase != "s")
  end
end

def menu
  begin
    puts
    puts '(1) Validate if it is numeric'
    puts '(2) Validate Email'
    puts '(3) Exit'
    puts

    print 'Choose an option: '
    STDOUT.flush
    ans = gets.chomp
    if ans == "1"
      numericProg
      menu
    elsif ans == "2"
      emailProg
      menu
    elsif ans == "3"
      puts 'i4mk.wordpress.com is cool!'
      exit
    end
  end while (ans != "1" && ans != "2" && ans != "3")
end

menu

Have fun with Regexp!

IT Private Jokes #8

PHP CLI example

PHP CLI is a short for PHP Command Line Interface and is intended for anybody who wants to write PHP Scripts, from the command line, that don’t require a web server, such as Apache.

• PHP CLI example:

#!/usr/bin/php
<<?php
echo "\nNumber of Arguments: " . $_SERVER["argc"]."\n\n";

#Getting arguments from the command line
echo "Getting Arguments (the 1st argument is the script itself):
1st arg: " . $_SERVER["argv"][0].
"\n2nd Arg: " . $_SERVER["argv"][1];

#Reading arguments from the command line
echo "\n\nReading from the standard input:";
echo "What's your name?\n";
$name = fread(STDIN, 50); # read up to 50 characters or a newline
echo "Hello " . $name ."\n";

#Simple countdown to execute something
echo "Countdown (10..1):\n";
	for($i=10; $i>0; $i--)
	{
		echo $i . "\n";
		sleep(1);
	}
	echo "BUM!!!\n\n";
?>

• Output:

Microsoft Voted ‘Best Place To Work’ in the World

Microsoft Voted ‘Best Place To Work’ in the World!
See the 25 best companies list, here.

“A great place to work is one in which you trust the people you work for, have pride in what you do, and enjoy the people you work with.”

by Robert Levering, Co-Founder, Great Place to Work®

I totally agree with Mr. Robert Levering!