Recent Notes
Displaying keyword search results 21 - 30
Created by Dr. Xi on February 18, 2010 20:39:59
Last update: February 18, 2010 20:41:17
Oracle provides two functions for string replacement: REPLACE and TRANSLATE .
REPLACE replaces one substring with another:
-- Replace John with Bob
select REPLACE('Hello ...
TRANSLATE replaces one character by another, position for position:
-- rot13 in Oracle SQL
select TRANSLATE('Hello ...
Created by Dr. Xi on February 09, 2010 04:54:10
Last update: February 09, 2010 04:54:10
Use this to connect to oracle RAC (Real Application Cluster):
jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=on)
...
From Oracle Database JDBC Developer's Guide :
Specifier Supported Drivers Example
Oracle Net connection descriptor Thin, OCI
Thin, using an address list:
url="jdbc:oracle:thin:@(DESCRIPTION=
(LOAD_BALANCE=on)
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=host1) (PORT=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=host2)(PORT=1521)))
(CONNECT_DATA=(SERVICE_NAME=service_name)))"
OCI, using a cluster:
"jdbc:oracle:oci:@(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=cluster_alias)
(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=service_name)))"
Thin-style service name Thin
"jdbc:oracle:thin:scott/tiger@//myhost:1521/myservicename"
LDAP syntax Thin
"jdbc:oracle:thin:@ldap://ldap.example.com:7777/sales,cn=OracleContext,dc=com"
Bequeath connection OCI Empty. That is, nothing after @
"jdbc:oracle:oci:scott/tiger/@"
TNSNames alias Thin, OCI OracleDataSource ods = new OracleDataSource();
ods.setTNSEntryName("MyTNSAlias");
Created by Dr. Xi on January 07, 2010 23:40:28
Last update: February 09, 2010 03:24:35
This is a utility to generate SQL insert statements for Oracle for one table, or a set of tables. It doesn't cover all possibilities but should be good enough for most cases.
import java.io.*;
import java.sql.*;
import ...
To generate insert statements for multiple tables, simply put the table names in a file, one per line, and use the -f switch.
Created by Dr. Xi on January 07, 2010 23:56:00
Last update: January 07, 2010 23:56:00
This is a utility that generates a script to grant privileges for an existing schema for Oracle.
import java.io.*;
import java.sql.*;
pub...
Created by Dr. Xi on January 07, 2010 23:51:44
Last update: January 07, 2010 23:53:57
This is a utility to generate a "create synonym" script for Oracle for an existing schema.
import java.io.*;
import java.sql.*;
pub...
Created by Dr. Xi on January 07, 2010 23:47:36
Last update: January 07, 2010 23:47:36
This is a utility to generate a "create role" script for Oracle for an existing schema.
import java.io.*;
import java.sql.*;
pub...
Created by Dr. Xi on April 21, 2009 22:54:39
Last update: April 21, 2009 22:54:39
Update a field to empty string makes it NULL in Oracle:
update person set middle_initial = '' where id = 5...
Select with empty string returns nothing:
select * from person where id = 5 and middle_initi...
You have to use NULL for the field you previously set to empty string to get the original row:
select * from person where id = 5 and middle_initi...
Created by Dr. Xi on March 23, 2009 23:02:57
Last update: March 23, 2009 23:07:28
Oracle function instr searches the occurrence of a sub-string in a string. It returns the index of the first match. It is similar to the Java String.indexOf function, with the exception that the index is 1 based, not 0 based.
-- search for OR from the beginning, returns 2
...
Created by Dr. Xi on January 07, 2009 20:22:34
Last update: January 07, 2009 20:22:34
You can use the timestamp keyword to convert a string to Date :
select * from retail_transactions where posted...
Created by Dr. Xi on December 10, 2008 23:10:03
Last update: December 10, 2008 23:10:03
-- string to date
UPDATE my_table SET date_...
