[Oracle] DBA 常用脚本二
9: 修改SQL提示符 Change SQL prompt
When you connect to sqlplus, you see the the following sql prompt. SQL>
By using this sql in the glogin.sql, you will see a prompt similar to the following:
SCOTT@DB-01>
Code:
The following code works on Oracle 8i (8.1.5, 8.1.6, 8.1.7).
You have to insert the following line of code in glogin.sql which is usually found in $ORACLE_HOME/sqlplus/admin
set termout off
set echo off
define X=NotConnected
define Y=DBNAME
Column Usr New_Value X
Column DBName New_Value Y
Select SYS_CONTEXT('USERENV','SESSION_USER') Usr from Dual;
--- The following does not work in 8.1.5 but works --- in 8.1.6 or above
Select SYS_CONTEXT('USERENV','DB_NAME') DBNAME from Dual;
--- If you are using 8.1.5, use this .
Select Global_Name DBNAME from Global_Name;
set termout on
set sqlprompt '&X@&Y> '
Please note that this method will work only when you make a new sql plus session because when you make a new sql plus session, then only glogin.sql is executed.
10: 位运算函数 bitwise functions for numbers
I got sick and tired of not having bitwise functions for numbers in Oracle so I went ahead and wrote my own.
Code:
CREATE OR REPLACE PACKAGE bitwise IS
FUNCTION hexconverter (pi_number IN NUMBER)
RETURN CHAR;
FUNCTION hexconverter (pi_hexstr IN CHAR)
RETURN NUMBER;
FUNCTION bitand (pi_num1 IN NUMBER, pi_num2 IN NUMBER)
RETURN NUMBER;
FUNCTION bitor (pi_num1 IN NUMBER, pi_num2 IN NUMBER)
RETURN NUMBER;
FUNCTION bitxor (pi_num1 IN NUMBER, pi_num2 IN NUMBER)
RETURN NUMBER;
END bitwise;
/
CREATE OR REPLACE PACKAGE BODY bitwise
IS
FUNCTION numtohexchar (pi_number IN NUMBER)
RETURN CHAR
IS
v_hextoreturn CHAR (1);
BEGIN
IF pi_number = 0 THEN
v_hextoreturn := '0';
ELSIF pi_number = 1 THEN
v_hextoreturn := '1';
ELSIF pi_number = 2 THEN
v_hextoreturn := '2';
ELSIF pi_number = 3 THEN
v_hextoreturn := '3';
ELSIF pi_number = 4 THEN
v_hextoreturn := '4';
ELSIF pi_number = 5 THEN
相关文档：
1、在oracle的sys-sysdba下登陆
写一个java source程序链接sqlserver2005：
create or replace and compile java source named test as
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class excutesql1
{
public static String entry ......
声明：
以下的例子不一定正确，只是为了演示大概的流程。
一：无返回值的存储过程
存储过程为：
CREATE OR REPLACE PROCEDURE TESTA(PARA1 IN VARCHAR2,PARA2 IN VARCHAR2) AS
BEGIN
INSERT INTO HYQ.B_ID (I_ID,I_NAME) VALUES (PARA1, PARA2);
END TESTA;
然后呢，在java里调用时就用下面的� ......
MySQL Data Type
Oracle Data Type
BIGINT
NUMBER(19, 0)
BIT
RAW
BLOB
BLOB, RAW
CHAR
CHAR
DATE
DATE
DATETIME
DATE
DECIMAL
FLOAT (24)
DOUBLE
FLOAT (24)
DOUBLE PRECISION
FLOAT (24)
ENUM
VARCHAR2
FLOAT
FLOAT
INT
NUMBER(10, 0)
INTEGER
NUMBER(10, 0)
LONGBLOB
BLOB, RAW
LONGTEX ......
