Oracle
文章平均质量分 64
假装80后
一个不念过去,不畏将来的程序员
展开
-
Oracle cursor in sqlplus, and optimizer hints
Example of How to use cursor in Oracle sqlplus, and optimizer hints is used also:SQL> CREATE TABLE TESTTAB( 2 A NUMBER, 3 B CHAR(4), 4 C VARCHAR2(20), 5 CONSTRAINT PK原创 2016-10-17 23:06:27 · 312 阅读 · 0 评论 -
Oracle sequence increased unexpected when using in merge statement
Following sample is going to merge from tab2 to tab1:CREATE TABLE tab1(id NUMBER, name varchar2(10), score NUMBER);CREATE TABLE tab2(id NUMBER, name varchar2(10), score NUMBER);CREATE SEQUEN原创 2016-10-18 21:15:03 · 225 阅读 · 0 评论 -
Oracle export and import data
Oracle support 2 types of data export/import1. Utility exp/imp -- But data format is binary, people cannot read and write them.2. Using sqlplus/sqlldr -- Export data as原创 2016-10-18 21:16:11 · 647 阅读 · 0 评论 -
Install procob for Oracle 11gR2
1. procob does not in the Oracle server installer, you must install Oracle client to get procob installed.2. procob does not installed by default, it must be selected explicit.Steps1. [原创 2016-10-18 21:37:52 · 766 阅读 · 0 评论 -
Oracle BLOB data type
Define BLOBSQL> CREATE TABLE TESTTAB(A BLOB)INSERT BLOBSQL> INSERT INTO TESTTAB VALUES(HEXTORAW('61626364'));SQL> INSERT INTO TESTTAB VALUES(UTL_RAW.CAST_TO_RAW('ABCD'));SQL> SELECT A FROM原创 2016-11-11 22:35:43 · 496 阅读 · 0 评论 -
Oracle/DB2 null key index
(Index key is null means all fields in an index are null.)In DB2 only one NULL key may exist in a unique index.When table and index are created asCREATE TABLE TAB ( A DECIMAL(6原创 2016-10-16 12:59:06 · 1123 阅读 · 0 评论 -
SQL Function for DB2 and ORA
SQL Function in DB2 9(UDF=user defined function)DB2 9 and earlier, your could only has RETURN statement in your UDF logic. (This is a too limited design)• RETURN can’t contain a SELECT sta原创 2016-11-12 09:15:07 · 375 阅读 · 0 评论 -
How to display CLOB column
This article will give samples how database client display CLOB column for DB2 and OracleDB2 clientDB2 client only show first 8K length, the rest are truncated with following WARNING message原创 2016-11-19 14:12:03 · 843 阅读 · 0 评论 -
JDBC insert BLOB column
Insert a BLOB column from a file.Suppose table is defined as: CREATE TABLE TESTTAB(A INT, B BLOB);Oracle JDBC insert BLOB dataimport java.io.File;import java.io.FileInputStream;import java原创 2016-11-19 14:18:18 · 1095 阅读 · 0 评论 -
Oracle how to reset sequence nextval
2 Solutions:1. drop and re-create itdrop sequence seqname;create sequence seqname start with NEWVALUE ...(Notice: don't forget about sequence's privilege)2. alter sequencea原创 2016-10-18 21:13:49 · 692 阅读 · 0 评论 -
ORACLE SQLNOTFOUND 100 vs. 1403
ORACLE SQLNOTFOUND 100 vs. 1403Problem statementWhen using COBOL read Oracle database, the SQLNOTFOUND sometimes return 100, and sometimes return 1403, what's the difference.What is原创 2016-10-18 21:12:49 · 2784 阅读 · 1 评论 -
Configure unixODBC connect to Oracle 11g
1. Check unixODBC is installed.$ odbcinst -junixODBC 2.2.14DRIVERS............: /etc/odbcinst.iniSYSTEM DATA SOURCES: /etc/odbc.iniFILE DATA SOURCES..: /etc/ODBCDataSourcesUSER DATA SOURCE原创 2016-10-17 23:08:50 · 599 阅读 · 0 评论 -
Oracle Execution Plan & Optimizer Hints
Describe how to show Oracle execution plan & optimizer hints.0. prerequisite$ sqlplus / as sysdbaSQL> @/YOUR_ORACLE_HOME/sqlplus/admin/plustrce.sqlSQL>GRANT PLUS原创 2016-10-17 23:11:29 · 403 阅读 · 0 评论 -
Oracle AWR(Automatic workload repository)
How to generate an AWR report.1. $ export ORACLE_SID=batch0052. $ echo $ORACLE_SID3. $ sqlplus / as sysdba4. SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sqlCurrent Instance~~~~~~~~~~~~~~原创 2016-10-18 21:04:50 · 444 阅读 · 0 评论 -
Oracle Performance: SDU, SEND_BUF_SIZE and RECV_BUF_SIZE
Refer from: http://docs.oracle.com/cd/B19306_01/network.102/b14212/performance.htmProblem Statement:The select operation is too slow compared with expected value.Session Data Unit(SDU):原创 2016-10-18 21:06:07 · 1442 阅读 · 0 评论 -
Oracle PL/SQLWarning: Function created with compilation errors.
How to handle "Warning: Function created with compilation errors." when create Oracle function.For example (MYFUNC.sql): 1 CREATE OR REPLACE FUNCTION MYFUNC 2 RETURN NUMBER AS 3 MYVAR原创 2016-10-18 21:07:30 · 2837 阅读 · 0 评论 -
Oracle Drop Table If Exists
Sometimes we want to clean up Oracle database, such as to drop a table if there exists, and do nothing if table does not exist; such as:- drop table table if existsUnfortunately, there is no s原创 2016-10-18 21:10:52 · 11646 阅读 · 1 评论 -
Oracle inner/outer/nature join
Oracle inner/outer/nature joinprerequisite:create table tab1(id smallint, name char(6), value varchar(10), primary key(id));create table tab2(id smallint, name char(6), score int, primary ke原创 2016-10-18 21:11:48 · 574 阅读 · 0 评论 -
Oracle SQL function overloading
Sample of implement Oracle SQL function overloading, and default parameter.To define your own to_char(...) function support different kinds of input data type.Please notice they must be defi原创 2016-11-19 14:22:16 · 412 阅读 · 0 评论