SQL Reviewer

Posted on Thursday, 14th May 2009 by captainmi2

Concat function in Oracle PL\SQL allows you to concatenate two strings together.

The syntax for the concat function is:

concat( string1, string2 )

string1 is the first string to concatenate.

string2 is the second string to concatenate.

Example:

SQL> select concat('abcd','efgh') as Col_1 from dual;

COL_1
--------
abcdefgh


Tags: Add new tag, Oracle PL\SQL Concat Function
Posted in Basic, Oracle PL\SQL Functions, Oracle PL\SQL String Functions | Comments (0)

Posted on Thursday, 14th May 2009 by captainmi2

Trim function removes all specified characters either in the beginning or ending of the a string.

The syntax for the Trim function is:
trim( [ leading | trailing | both [ trim_character ] ] string1 )

leading – remove trim_string from the front of string1.

trailing – remove trim_string from the end of string1.

both – remove trim_string from the front and end of string1.

If none of these are chosen (ie: leading, trailing, both), the trim function will remove trim_string from both the front and end of string1.

trim_character is the character that will be removed from string1. If this parameter is omitted, the trim function will remove all leading and trailing spaces from string1.

string1 is the string to trim.

Example:

Remove spaces in the beginning and ending of the string

SQL> select ' abcd ' as Col_1 from dual;

COL_1
-----
abcd


Removing leading zeros in the string

SQL> select trim(leading '0' from '000888' ) from dual;

TRIM(LEADING'0'FROM'000888')
----------------------------
888


Removing trailing zeros in the string

SQL> select trim(trailing '0' from '888000' ) from dual;

TRIM(TRAILING'0'FROM'888000')
-----------------------------
888

Removing both trailing and ending zeros in the string

SQL> select trim(both '0' from '00018881000' ) from dual;

Tags: Oracle PL\SQL Trim Function
Posted in Basic, Oracle, Oracle PL\SQL Functions, Oracle PL\SQL String Functions | Comments (0)

Posted on Thursday, 14th May 2009 by captainmi2

Add months statement

The syntax for the add months function is:

add_months( date1, n )

date1 is the starting date (before the n months have been added).

n is the number of months. If the value is negative, current month will be deducted else it will add months to the current month

Example :
select the current date

SQL> select sysdate from dual;

SYSDATE
-----------
14/05/2009

select the current date + 3 months

SQL> select add_months(sysdate,3) from dual;

ADD_MONTHS(SYSDATE,3)
---------------------
14/08/2009 2:32:09 PM

Select current date – minus 3 months

SQL> select add_months(sysdate,-3) from dual;

Tags: Oracle PL\SQL Add Months Function
Posted in Basic, Oracle, Oracle PL\SQL Functions | Comments (0)

Posted on Tuesday, 21st April 2009 by captainmi2

Replace Statement

The syntax for the replace function is:

replace( string1, string_to_replace, [ replacement_string ] )

string1 is the string to replace a sequence of characters with another set of characters.

string_to_replace is the string that will be searched for in string1.

replacement_string is optional. If replacement_string is omitted, it will just removed all occurrences of string_to_replace, and returns the resulting string

See example below


SQL> select '123sample456' Column1, replace('123sample456', '456', '789') Column2 from dual;

Tags: Oracle PL\SQL Replace Function
Posted in Basic, Oracle, Oracle PL\SQL Functions, Oracle PL\SQL String Functions | Comments (0)

Posted on Monday, 20th April 2009 by kwatog

In case you need to know what version of SQL Server you are currently using, you can use the queries below.

Starting at SQL Server 2000, you can use the following query.

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

The above query is applicable for SQL Server 2000, SQL Server 2005 and SQL Server 2008. For SQL Server 7 and SQL Server 6.5 series. The code below should be used.

SELECT @@VERSION

If you are not sure what version of SQL Server you have, use the second query. It still works as it isn’t deprecated yet. The new version is handy if you need to get the details programmatically.

Posted in Basic, SQL Server | Comments (0)

Posted on Thursday, 16th April 2009 by kwatog

If you can monitor SQL Server processes you can bet your dog that you can also do that with Oracle without using sophisticated but uberly bloated software. The query below will help you with that.

SELECT sess.sid, sess.serial#, sess.sql_child_number, sess.sql_exec_id,
sess.process, sess.status, sess.username, sess.osuser, sess.program,
sess.schemaname, sql.sql_text
FROM v$session sess,
v$sql sql
WHERE sql.sql_id(+) = sess.sql_id
AND sess.type = 'USER'
--AND sess.osuser = 'yourwindowslogin';

You can check the definition of v$session system view to see what are the other information you can use. But for me, those listed here is enough especially that the sql_text is already included. I commented the last line so that I’ll be able to see all processes but you can choose to uncomment it or replace it with something else. Like, peeping on what type of query your cute officemate is running.

As in the case of Sybase and SQL Server, monitoring your server is indespensable. It gives you an insight on what is going wrong or going right. In large stored procedures, you’ll be able to pinpoint which query is giving you problem. That’s just one of the sample advantage. Try it yourselft to find what the others are.

Posted in Intermediate, Oracle, Performance Tuning | Comments (0)

Posted on Sunday, 12th April 2009 by kwatog

TOAD for Oracle and PL/SQL Developer are two indespensable developer tools. They have built-in tools to check the query plan of your SQL scripts. But in case you only have SQL*Plus as your editor, you ultimately need to know how it is done manually.

First Step : create plan

EXPLAIN PLAN FOR
your_query_to_optimize

a sample of which is

EXPLAIN PLAN FOR
SELECT emp_no, emp_fname
FROM emp
WHERE emp_lname = 'CRUISE';


Second Step: show results
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

But before everything, you must check if you have the PLAN table on your schema. You can use the script that ships with oracle by executing the following statement on your SQL*Plus.

@?/rdbms/admin/utlxplan.sql

You only need to do it once for each schema. Take note that getting the query plan is one thing and analyzing it is another. Make sure you know what you are looking for.

Tags: Explain Plan, Oracle, Query Plan, SQL*Plus
Posted in Intermediate, Oracle, Performance Tuning | Comments (0)

Posted on Thursday, 9th April 2009 by kwatog

In my previous job, I have been too attached with DBArtisan that I sometimes no longer bother to know how to manually configure things in the database using plain SQL. With the lack of more advanced SQL editors and DBA tools in my current job, I had to rely on plain SQL to do all things from trivial to advanced tasks. One such task I failed to know is to manually monitor the processes running on the server.

To cut my introduction short, the script below will help you monitor processes on your database. Take note that you’ll need necessary rights to be able to run it.

select spid, status, blocked, open_tran, waitresource, waittype,
waittime, cmd, lastwaittype, cpu, physical_io,
hostname, hostprocess, loginame, program_name, net_address,
net_library, dbid, ecid, kpid, nt_domain, nt_username, uid, sid,
memusage, last_batch=convert(varchar(26), last_batch,121),
login_time=convert(varchar(26), login_time,121)
from master.dbo.sysprocesses
--where (blocked!=0 or waittype != 0x0000)

If you only want to see those that are blocking or locking, uncomment the last line. One thing that’s lacking here is the list of tables and database objects that each process id is using. That’s important if a user is running stored procedures and you need to know . I’ll provide that later as honestly, I don’t know yet which table holds that information.

Read the rest of this entry…

Tags: blocking, locking, monitor processes, SQL Server
Posted in Advanced, Performance Tuning, SQL Server | Comments (0)

Posted on Tuesday, 7th April 2009 by captainmi2


In this statement, when issuing drop statement, the table will be permanently be dropped or deleted.

Tags: Drop table, Oracle Drop Table
Posted in Basic, Oracle | Comments (0)

Posted on Tuesday, 7th April 2009 by captainmi2

To add column/s to an existing table

New columns AGE and NATIONALITY were added in the tab_customer table.


Tags: Adding columns, Oracle Adding Columns
Posted in Basic, Oracle | Comments (0)