分类文章

热门文章

oracle ‘转义

oracle中字符’的转义还是一个’,如’–>”,”–>”””

请给本文打分   
1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading ... Loading ...

oracle 实现字符串split

oracle中并不像java有对字符串分隔的split的方法,自己写了个方法实现字符串split

CREATE OR REPLACE TYPE type_str_split IS TABLE OF VARCHAR2 (4000);

CREATE OR REPLACE FUNCTION fn_split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2)

RETURN type_str_split
IS
j INT := 0;
i INT := 1;
len INT := 0;
len1 INT := 0;
str VARCHAR2 (4000);
str_split type_str_split := type_str_split ();
BEGIN
len := LENGTH (p_str);
len1 := LENGTH (p_delimiter);

WHILE j < len
LOOP
j := INSTR (p_str, p_delimiter, i);

IF j = [...]

请给本文打分   
1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading ... Loading ...

ORA-22992: cannot use LOB locators selected from remote tables

ORA-22992: cannot use LOB locators selected from remote tables 解决办法
今天通过dblike查询远程数据库带有blob字段的时候 报出了ORA-22992: cannot use LOB locators selected from remote tables,无法使用从远程表选择的 LOB 定位器

就是说如果远程表中有blob字段 用select blob_column_name from remote_table@linkname 是行不通的

解决方法有两种

1,creatd table table_name as (select blob_column_name from remote_table@linkname )

将远程表的数据先插入到一张新表中,然后从新表中取数据

2,建一个全局的临时表

create global temporary table temp_table_name(
   X BLOB
)
on commit delete rows;

insert into temp_table_name select blob_column_name from remote_table@linkname ;

请给本文打分   
1 Star2 Stars3 Stars4 Stars5 Stars (89 votes, average: 0.00 out of 5)
Loading ... Loading ...

thin和oci方式的连接oracle的区别

thin和oci方式的连接oracle的区别
10.1.0的驱动对8.1.7及更高版本的oracl都是支持的,所有的oracle JDBC驱动都很相似,OCI和Thin的驱动有一些特性是不同的。

使用oci驱动需要在服务端安装有oracle的客户端(最近遇到的一个问题,在用存储过程做blob操作的时候必须要使用oci方式连接,可是oracle版本是10g的服务器上装的oracle客户端是9i的,tomcat启动服务创建连接的时候由于不兼容报出JVM错误,导致服务器必须重启,可见oci对于oracle客户端的依赖性。最后不得已只有修改代码,用JDBC操作blob改用thin连接)
如果oracle数据库和客户端都安装在同一个服务器上,使用oci驱动的话会通过IPC (InterProcess Communication)方式连接db,而不是通过网络去连接oracle所在的服务器,在连接速度上会有所提高

thin方式连接的驱动完全是有java编写的,不依赖于oracle客户端

官方的说明

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
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
Oracle provides four different types of JDBC drivers, for use in different deployment scenarios. The 10.1.0 drivers can access
Oracle 8.1.7 and higher. While all Oracle JDBC drivers are similar, some features apply only to JDBC OCI drivers and some apply
only to the JDBC Thin driver.
 
JDBC OCI client-side driver: This [...]

请给本文打分   
1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)
Loading ... Loading ...

Non supported character set: oracle-character-set-852

Non supported character set: oracle-character-set-852 今天在做oracle插入blob的时候报出了这个异常

解决方法:添加10g_nls_charset12.jar到classpath

请给本文打分   
1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)
Loading ... Loading ...

ORA-22993 : specified input amount is greater than actual source amount

ORA-22993 : specified input amount is greater than actual source amount 今天在做blob插入的时候遇到了这个错误

查了下oracle官方的错误说明,导致该错误的原因是

(1) For LOB write, the amount of data received is different from the amount that was indicated would be sent. (2) For LOB copy and loadfromfile, the end of the source LOB/FILE value was reached before the specified input amount was copied/loaded.

哪些行为会导致这个错误呢

(1) will happen [...]

请给本文打分   
1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)
Loading ... Loading ...