Playing With SQL Quries

Do you want to play with SQL queries, what a disgusting question, no this is not disgusting, yes you can play, even create games using various codes. Lets create a query to create a triangle. Below SQL query is prepare in Oracle SQL, it can clear your logical skills and usage of SQL function at right place and at right time.

SQL> select lpad(' ',8-level, ' ') ||rpad('*',level-2, '*')
  2  || rpad('*',level-1, '*')
  3   as  a
  4  from dual connect by level < 8
  5  order by level
  6  /

A
---------------

      *
     ***
    *****
   *******
  *********
 ***********

7 rows selected.

Create Folder/Subfolder in transformation java script

This Article related to Pentaho Kettle ETL tool, where we have requirement to create folder, sub folder in physical file directory dynamically using java script. Please follow below steps, you will be definitely able to do this.

We have step in job, using which we can create folder ans sub-folders.

Magically we have "Modified Java Script Value" step in transformation, using which we can also create folder and Subfolders in filesystem.

Steps :

• Create new transformation and save it
• Add - "Table Input" - "Select Values" - "Modified Java Script Value" in transformation
• Table Input step contains - "select 'temp_folder' as path"
• Select Values Contains entry of field Path
• Modified Java Script Value contains - createFolder('e:/'+path);
• Now save and execute it

Get the list of all tables using JDBC driver using kettle

This is very very beautiful article for me, i bet you guys also enjoy this. The thing is i need information of all tables and columns and keys available in some database and i need all this information at one place and in some different server or some different database.

I searched on google and thanks god i am able to catch up a transformation, which will provide you list of tables, columns, data type of fields, available primary_keys , foreign keys and indexes available with in the database

We can easily get all these information using JDBC connection

Download the transformation from below link

http://wiki.pentaho.com/display/EAI/JDBC+Metadata

List and used function:

1. data types output - function used getTypeInfo()
2. tables output - function used getTables()
3. columns output - function used getTables()
4. primary key output - function used getPrimaryKeys()
5. imported key output - function used getImportedKeys()
6. index info output - function used getIndexInfo()

I have modified this transformation since i need information related to tables and columns only, and end result will be saved in tables of different database

Define Variable for Database Connection in kettle

Instead of using static Database connection name in spoon UI, We can define database connection variable using kettle.properties file, where we can assign dynamic connection which will be available in through out
the kettle JVM space. Once you need to update the ketlle.properties file to update the connection name or server name, in that case you definitely need to restart your spoon or server. If you do not perform this action then variable contains will contains old connection detail thought the project.

To define variables we need to make change in two files
1. kettle.properties
2. shared.xml

Content of shared.xml

<?xml version="1.0" encoding="UTF-8"?>
<sharedobjects>
  <connection>
    <name>TestConnection</name>
    <server>${DBHostname}</server>
    <type>MSSQL</type>
    <access>Native</access>
    <database>${DBName}</database>
    <port>1433</port>
    <username>${DBUsername}</username>
    <password>${DBPassword}</password>
    <servername/>
    <data_tablespace/>
    <index_tablespace/>
    <attributes>
      <attribute><code>FORCE_IDENTIFIERS_TO_LOWERCASE</code><attribute>N</attribute></attribute>
      <attribute><code>FORCE_IDENTIFIERS_TO_UPPERCASE</code><attribute>N</attribute></attribute>
      <attribute><code>IS_CLUSTERED</code><attribute>N</attribute></attribute>
      <attribute><code>MSSQL_DOUBLE_DECIMAL_SEPARATOR</code><attribute>N</attribute></attribute>
      <attribute><code>PORT_NUMBER</code><attribute>1433</attribute></attribute>
      <attribute><code>PRESERVE_RESERVED_WORD_CASE</code><attribute>N</attribute></attribute>
      <attribute><code>QUOTE_ALL_FIELDS</code><attribute>N</attribute></attribute>
      <attribute><code>SUPPORTS_BOOLEAN_DATA_TYPE</code><attribute>Y</attribute></attribute>
      <attribute><code>SUPPORTS_TIMESTAMP_DATA_TYPE</code><attribute>Y</attribute></attribute>
      <attribute><code>USE_POOLING</code><attribute>N</attribute></attribute>
    </attributes>
  </connection>

Content of kettle.properties
#TestConnection
DBHostname=localhost
DBName=XYZ0001
DBUsername=sa
DBPassword=******

Now Close your spoon UI if opened (this action is required to reflect your changes in spoon/UI of ETL tool)
Move to directory **/.kettle
delete any backup files if exists
start spoon.bat file

In above picture you can see the newly added variable in shared.xml file and the variable values available in kettle.properties file.

ORA-00119: invalid specification for system parameter LOCAL_LISTENER

Welcome back after a weekend off, every body is waiting for weekend, we too. Some times when return to office some of the stuffs not working properly. Do not know why, any alien?

Some thing happen, we returned and tried to startup services, it was not working, throws below error. Without database we can not start our work, so need to fix this on higher priority.

I followed below step to fix this and resolved and working fine, you guys can also follow below step if you have faced the same problem. Do not afraid just fire the commands after verifying the paths.

ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENER_ORCL'

Problem
E:\app\*****\admin\orcl\pfile
Open pfile in editor

local_listener=LISTENER_ORCL

Solution
Update the below entry (orcl is a local database sid)
local_listener=orcl



startup pfile='E:\app\*****\admin\orcl\pfile\init.ora'

create spfile from pfile='E:\app\*****\admin\orcl\pfile\init.ora' ;

shu immediate

startup