September 2010
M T W T F S S
« Jul    
 12345
6789101112
13141516171819
20212223242526
27282930  

20090623…

Good news or bad news?
God knows!!!
……

First glance on oracle regular expression

Oracle 10g SQL/PLSQL引入了正则表达式语法。有以下几个内置函数:
REGEXP_LIKE
REGEXP_SUBSTR
REGEXP_INSTR
REGEXP_REPLACE
到了11g,oracle引入了一个新的函数:
REGEXP_COUNT
正则表达式的强大功能就不说了,在Oracle10g之前,很多复杂的字符串操作编程需要在客户端应用中实现。
下面简要介绍这几个函数:

SQL Element     Category    Description
-------------  ----------  -------------------------------------------------------------------------------------------------
REGEXP_LIKE     Condition   Searches a character column for a pattern. 
                            Use this function in the WHERE clause of a query to return rows matching a regular expression. 
                            The condition is also valid in a constraint or as a PL/SQL function returning a boolean.
                            The following WHERE clause filters employees with a first name of Steven or Stephen:
                            WHERE REGEXP_LIKE(first_name, '^Ste(v|ph)en$')
 
REGEXP_REPLACE  Function    Searches for a pattern in a character column and replaces each occurrence of that pattern with the specified string.
                            The following function call puts a space after each character in the country_name column:
                            REGEXP_REPLACE(country_name, '(.)', '\1 ')
 
REGEXP_INSTR    Function    Searches a string or substring for a given occurrence of a regular expression pattern (a substring) 
                            and returns an integer indicating the position in the string or substring where the match is found.
                            You specify which occurrence you want to find and the start position.
                            The following function call performs a boolean test for a valid email address in the email column:
                            REGEXP_INSTR(email, '\w+@\w+(\.\w+)+') > 0
 
REGEXP_SUBSTR   Function    Searches a string or substring for a given occurrence of a regular expression pattern (a substring) 
                            and returns the substring itself.You specify which occurrence you want to find and the start position.
                            The following function call uses the x flag to match the first string by ignoring spaces in the regular expression:
                            REGEXP_SUBSTR('oracle', 'o r a c l e', 1, 1, 'x')
 
REGEXP_COUNT    Function    Returns the number of times a pattern appears in a string. You specify the string and the pattern.
                            You can also specify the start position and matching options (for example, c for case sensitivity).
                            The following function call returns the number of times that e (but not E) appears in the string 'Albert Einstein', 
                            starting at character position 7 (that is, one):
                            REGEXP_COUNT('Albert Einstein', 'e', 7, 'c')

正则表达式功能强大,内容繁多,要想学好正则表达式需要花一定的力气,这是一个开始,有机会做个系列学习。
Refer to:
———
Oracle? Database Advanced Application Developer’s Guide 11g Release 1 (11.1)
Oracle? Database SQL Language Reference 11g Release 1 (11.1)

http://www.opengroup.org/onlinepubs/007908799/xbd/re.html

Oracle Regular Expressions Pocket Reference by Jonathan Gennick, O’Reilly & Associates
Mastering Regular Expressions by Jeffrey E. F. Friedl, O’Reilly & Associates

http://www.adp-gmbh.ch/blog/2005/december/22.html

The database link can not be dropped after changing the global_name

如果通过以下语句修改了global name,数据库链接将不能被删除,除非改回原来的global name.
ALTER DATABASE RENAME GLOBAL_NAME TO ;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
SQL> conn scott/scott
已连接。
SQL> create database link  link_test connect to strmadmin identified by oracle10g using 'db1.onlyring1.net';
 
数据库链接已创建。
 
SQL> drop database link link_test;
 
数据库链接已删除。
 
SQL> create database link  link_test connect to strmadmin identified by oracle10g using 'db1.onlyring1.net';
 
数据库链接已创建。
 
SQL> conn /as sysdba
已连接。
SQL> select global_name from global_name ;
 
GLOBAL_NAME
--------------------------------------------------------------------------------
ORA10GDB.MYCORP
 
SQL> alter database rename global_name to ORA10GDB.MYCORP.tests;
 
数据库已更改。
 
SQL>
SQL> conn scott/scott
已连接。
SQL> drop database link link_test;
drop database link link_test
                   *1 行出现错误:
ORA-02024: 未找到数据库链接
 
 
SQL> select global_name from global_name ;
 
GLOBAL_NAME
----------------------------
ORA10GDB.MYCORP.TESTS
 
SQL> sho user
USER 为 "SCOTT"
SQL> conn / as sysdba
已连接。
SQL> alter database rename global_name to ORA10GDB.MYCORP;
 
数据库已更改。
 
SQL> select global_name from global_name ;
 
GLOBAL_NAME
-------------------
ORA10GDB.MYCORP
 
SQL>  conn scott/scott
已连接。
SQL>  drop database link link_test;
 
数据库链接已删除。

如果过global name本来是不包含domain name的,那么利用:
ALETER 语句更改global name 将不能再使用该语句修改回原来的名字。

可以通过修改props$基本表的方法[Metalink DocDI 382994.1]:

1)Take a complete consistent backup of the database

2)Execute the following:

1
2
3
4
5
6
$sqlplus /nolog
connect / as sysdba
 
SQL> update props$ set value$ = '<globalname without domain>' where name ='GLOBAL_DB_NAME';
 
SQL>commit;

3)Then connect as the schema user that owns the DBLINK and try to drop it.

4)Once the database link is dropped, the global_name can be changed back to the
desired name containing domain part using the alter database rename global_name
statement

Small introduction to SQL*Net debugging [client side]

http://www.liberidu.com/blog/?page_id=230

Posted on September 29, 2005
Filed Under SQL*Net, Protocol Server, RDBMS, Oracle

Say it’s that day again. You try to connect to a (new) database and once again you are facing SQL*Net problems.
C:/oracle/product/10.1.0/client/BIN>sqlplus /nolog
SQL*Plus: Release 10.1.0.4.0 – Production ON Thu Sep 29 10:37:15 2005
Copyright (c) 1982, 2005, Oracle. ALL rights reserved.
SQL> conn scott/tiger@lab.amis.nl
ERROR: ORA-12154:
TNS:could NOT resolve the connect identifier specified
Blast!. What can you do? Once in a while i get a lot of these SQL*Net problems (network changes, etc, the wrong weather type). So
to help you (and me), here i will you give you my grey-matter checklist (it’s not complete but should help you to resolve 80% of
the problems)
Continue reading Small introduction to SQL*Net debugging [client side]

Some examples of stty usage

1.Typing invisiable password

1
2
3
4
5
echo "Enter your password: \c"
oldstty=`stty -g`
stty -echo intr ^-
read password
stty $oldstty

2.Backspace setting

1
stty erase ^h

3.Colunm setting

1
stty column 100

4.List current stty setting

1
2
3
stty -a (all setting)
stty    (undefault setting)
stty -g (RAW mode)

You can get the help files regarding stty usage in the folllowing ways:

1
2
3
info stty
man stty
stty --help