目录

标签: Oracle

.Net 操作 Oracle CLOB类型字段 INSERT 超长数据

如果仅仅在数据库中设置了类型为 CLOB 字段类型,使用普通的 INSERT 语句直接写入数据到数据库的话,它依然会将其视为 VARCHAR 类型数据,并最大长度为 4000 字符。超过该长度会报出字符串超长,写入数据失败的错误。

使用 Dapper 处理 CLOB 数据 INSERT

代码如下:

OracleConnection cnn = new OracleConnection("..数据库链接字符串..");
cnn.Open();

string content = "假定超过4000的超长字符串";
byte[] newValue = Encoding.Unicode.GetBytes(content); //这里一定要使用 Unicode 字符编码
OracleClob p_content = new OracleClob(cnn);
p_content.Write(newValue, 0, newValue.Length);

string sql = "INSERT INTO TABLE(CONTENT) VALUES(:content)";
DynamicParameters parameter = new DynamicParameters();
parameter.Add("content", p_content);
cnn.Execute(sql, parameter);
cnn.Close();

其实跟 Dapper 也没多大关系,在 .Net 中的 Oracle CLOB 需要以上特殊处理下,否则 Dapper 会将其视为普通字符串类型写入。

敲重点,如何 UPDATE CLOB 字段类型的数据

本来没有这一节文章内容的,我认为像 INSERT 同样的方式创建 SQL 参数传入就好了。实际上并不是,UPDATE 需要跟以往同样的方式,直接 string 传参就可以了,以下参考:

OracleConnection cnn = new OracleConnection("..数据库链接字符串..");
cnn.Open();

string content = "假定超过4000的超长字符串";
string sql = "UPDATE TABLE SET CONTENT = :content WHERE ID = :id";

DynamicParameters parameter = new DynamicParameters();
parameter.Add("id", 10001);
parameter.Add("content", content);
cnn.Execute(sql, parameter);
cnn.Close();

你没看错,它不需要再转换为 OracleClob 对象参数了,转换了反而会报错。

Oracle 解决错误:ORA-25153: Temporary Tablespace is Empty

出现问题的根源在于临时表空间文件没有挂载,我们将其挂载上去就可以了。

如何查看表空间临时文件是否挂载

SELECT TABLESPACE_NAME,FILE_NAME FROM DBA_TEMP_FILES;

执行以上代码,显示结果如下:

以上结果表示 表空间 是成功挂载的

Oracle 如何挂载临时表空间

ALTER TABLESPACE TEMP ADD TEMPFILE 'C:/app/Administrator/oradata/DOCARE/TEMP01.DBF';

执行以上代码即可,其中的 TEMP01.DBF 文件为你自己程序的 Oracle 数据库文件路径,如果不知道在哪里,Windows 可以用磁盘搜索工具进行相应的搜索, Linux 可以用 Find 命令来搜索一下,很快就能找到。

至此大功告成。

Oracle 行转列的两种方法

方法 1,使用 PIVOT 函数

表数据
WITH temp AS(
SELECT '四川省' nation ,'成都市' city,'第一' ranking FROM dual UNION ALL
SELECT '四川省' nation ,'绵阳市' city,'第二' ranking FROM dual UNION ALL
SELECT '四川省' nation ,'德阳市' city,'第三' ranking FROM dual UNION ALL
SELECT '四川省' nation ,'宜宾市' city,'第四' ranking FROM dual UNION ALL
SELECT '湖北省' nation ,'武汉市' city,'第一' ranking FROM dual UNION ALL
SELECT '湖北省' nation ,'宜昌市' city,'第二' ranking FROM dual UNION ALL
SELECT '湖北省' nation ,'襄阳市' city,'第三' ranking FROM dual
)
SELECT * FROM (SELECT nation,city,ranking FROM temp)PIVOT (MAX(city) FOR ranking IN ('第一' AS 第一,'第二' AS 第二,'第三' AS 第三,'第四' AS 第四));

直接运行以上代码即可实现以下效果:

查询结果

说明:(聚合函数 FOR 列名 IN(类型)),其中 IN(‘’) 中可以指定别名,IN 中还可以指定子查询,比如 SELECT distinct ranking FROM temp

SELECT * FROM [StudentScores] /*数据源*/
AS P
PIVOT 
(
    SUM(Score/*行转列后 列的值*/) FOR 
    p.Subject/*需要行转列的列*/ IN ([语文],[数学],[英语],[生物]/*列的值*/)
) AS T

方法 2,使用 MAX 结合DECODE 函数

表数据
WITH temp AS(
SELECT '四川省' nation ,'成都市' city,'第一' ranking FROM dual UNION ALL
SELECT '四川省' nation ,'绵阳市' city,'第二' ranking FROM dual UNION ALL
SELECT '四川省' nation ,'德阳市' city,'第三' ranking FROM dual UNION ALL
SELECT '四川省' nation ,'宜宾市' city,'第四' ranking FROM dual UNION ALL
SELECT '湖北省' nation ,'武汉市' city,'第一' ranking FROM dual UNION ALL
SELECT '湖北省' nation ,'宜昌市' city,'第二' ranking FROM dual UNION ALL
SELECT '湖北省' nation ,'襄阳市' city,'第三' ranking FROM dual
)

SELECT nation,
MAX(DECODE(ranking, '第一', city, '')) AS 第一,
MAX(DECODE(ranking, '第二', city, '')) AS 第二,
MAX(DECODE(ranking, '第三', city, '')) AS 第三,
MAX(DECODE(ranking, '第四', city, '')) AS 第四
FROM temp GROUP BY nation;

运行以上代码,显示结果如下:

运行结果

说明:DECODE 的用法:DECODE (条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值),该函数的含义如下:

IF 条件=值1 THEN
    RETURN(翻译值1)
ELSIF 条件=值2 THEN
    RETURN(翻译值2)
    ......
ELSIF 条件=值n THEN
    RETURN(翻译值n)
ELSE
    RETURN(缺省值)
END IF

解决 ORACLE 修改表字段NUMBER精度报错

错误:column to be modified must be empty to decrease precision or scale

出现这样的错误,是由于 ORACLE 数据引擎不允许存在拥有数据的情况下,进行精度修改。于是可以按照以下方法进行解决

解决方案

步骤 1 :备份原来的表

CREATE TABLE MY_BAK AS SELECT * FROM YOUR_TABLE;

步骤 2 :删除原来的表数据

DELETE FROM YOUR_TABLE;

步骤 3:

手动进行相应的字段精度修改,比如 Navicat / PL SQL 工具。

步骤 4:恢复数据

INSERT INTO YOUR_TABLE SELECT * FROM MY_BAK;

以上操作即可完美修改 Number 的精度,并且保留现有的数据。

.Net Core 访问Oracle的连接字符串

  1. Nuget 搜索安装: Oracle.ManagedDataAccess.Core
  2. 使用以下代码即可连接
// 连接字符串
string cnnStr = "User ID=ITGFS;Password=itsds#oracle;Data Source=\"(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 118.13.222.237)(PORT = 1521)))(CONNECT_DATA = (SERVICE_NAME = ORCL)))\";";

// 连接对象
OracleConnection cnn = new OracleConnection(cnnStr);

// 测试连接
cnn.Open();
cnn.Close();

通过以上代码就可以成功连接到 Oracle 服务器了,实际上 Nuget 搜索安装的包也就是 Oracle 为 .Net Core 提供的专用连接客户端。

运行电脑无需再安装其他的客户端即可运行。

oracle10G/11G官方下载地址集合 直接迅雷下载

Oracle Database 11g Release 2 (11.2.0.1.0) for Microsoft Windows (64-bit)

http://download.oracle.com/otn/nt/oracle11g/112010/win64_11gR2_database_1of2.zip

http://download.oracle.com/otn/nt/oracle11g/112010/win64_11gR2_database_2of2.zip

http://download.oracle.com/otn/nt/oracle11g/112010/win64_11gR2_client.zi

http://download.oracle.com/otn/nt/oracle11g/112010/win64_11gR2_grid.zip

Oracle Database 11g Release 2 (11.2.0.1.0) for Microsoft Windows (32-bit)

http://download.oracle.com/otn/nt/oracle11g/112010/win32_11gR2_database_1of2.zip

http://download.oracle.com/otn/nt/oracle11g/112010/win32_11gR2_database_2of2.zip

http://download.oracle.com/otn/nt/oracle11g/112010/win32_11gR2_client.zip

Oracle Database 11g Release 2 (11.2.0.1.0) for Microsoft Windows (64-bit)

http://download.oracle.com/otn/nt/oracle11g/112010/win64_11gR2_database_1of2.zip

http://download.oracle.com/otn/nt/oracle11g/112010/win64_11gR2_database_2of2.zip

http://download.oracle.com/otn/nt/oracle11g/112010/win64_11gR2_client.zip

http://download.oracle.com/otn/nt/oracle11g/112010/win64_11gR2_grid.zip

Oracle Database 11g Release 2 (11.2.0.1.0) for Microsoft Windows (32-bit)

http://download.oracle.com/otn/nt/oracle11g/112010/win32_11gR2_database_1of2.zip

http://download.oracle.com/otn/nt/oracle11g/112010/win32_11gR2_database_2of2.zip

http://download.oracle.com/otn/nt/oracle11g/112010/win32_11gR2_client.zip

Oracle Database 11g Release 2 (11.2.0.1.0) for Linux x86

http://download.oracle.com/otn/linux/oracle11g/R2/linux_11gR2_database_1of2.zip

http://download.oracle.com/otn/linux/oracle11g/R2/linux_11gR2_database_2of2.zip

http://download.oracle.com/otn/linux/oracle11g/R2/linux_11gR2_client.zip

http://download.oracle.com/otn/linux/oracle11g/R2/linux_11gR2_grid.zip

Oracle Database 11g Release 2 (11.2.0.1.0) for Linux x86-64

http://download.oracle.com/otn/linux/oracle11g/R2/linux.x64_11gR2_database_1of2.zip

http://download.oracle.com/otn/linux/oracle11g/R2/linux.x64_11gR2_database_2of2.zip

http://download.oracle.com/otn/linux/oracle11g/R2/linux.x64_11gR2_client.zip

http://download.oracle.com/otn/linux/oracle11g/R2/linux.x64_11gR2_grid.zip

Oracle Database 11g Release 2 (11.2.0.1.0) for Solaris Operating System (SPARC) (64-bit)

http://download.oracle.com/otn/solaris/oracle11g/R2/solaris.sparc64_11gR2_database_1of2.zip

http://download.oracle.com/otn/solaris/oracle11g/R2/solaris.sparc64_11gR2_database_2of2.zip

http://download.oracle.com/otn/solaris/oracle11g/R2/solaris.sparc64_11gR2_client.zip

http://download.oracle.com/otn/solaris/oracle11g/R2/solaris.sparc64_11gR2_client32.zip

http://download.oracle.com/otn/solaris/oracle11g/R2/solaris.sparc64_11gR2_grid.zip

Oracle Database 11g Release 2 (11.2.0.1.0) for Solaris Operating System (x86-64)

http://download.oracle.com/otn/solaris/oracle11g/R2/solaris.x64_11gR2_database_1of2.zip

http://download.oracle.com/otn/solaris/oracle11g/R2/solaris.x64_11gR2_database_2of2.zip

http://download.oracle.com/otn/solaris/oracle11g/R2/solaris.x64_11gR2_client.zip

http://download.oracle.com/otn/solaris/oracle11g/R2/solaris.x86_11gR2_client.zip

http://download.oracle.com/otn/solaris/oracle11g/R2/solaris.x64_11gR2_grid.zip

Oracle Database 11g Release 2 (11.2.0.1.0) for HP-UX Itanium

http://download.oracle.com/otn/hp/oracle11g/R2/hpia64_11gR2_database_1of2.zip

http://download.oracle.com/otn/hp/oracle11g/R2/hpia64_11gR2_database_2of2.zip

http://download.oracle.com/otn/hp/oracle11g/R2/hpia64_11gR2_client.zip

http://download.oracle.com/otn/hp/oracle11g/R2/hpia64_11gR2_client32.zip

http://download.oracle.com/otn/hp/oracle11g/R2/hpia64_11gR2_grid.zip

Oracle Database 11g Release 2 (11.2.0.1.0) for HP-UX PA-RISC (64-bit)

http://download.oracle.com/otn/hp/oracle11g/R2/hpux.parisc64_11gR2_database_1of2.zip

http://download.oracle.com/otn/hp/oracle11g/R2/hpux.parisc64_11gR2_database_2of2.zip

http://download.oracle.com/otn/hp/oracle11g/R2/hpux.parisc64_11gR2_client.zip

http://download.oracle.com/otn/hp/oracle11g/R2/hpux.parisc32_11gR2_client.zip

http://download.oracle.com/otn/hp/oracle11g/R2/hpux.parisc64_11gR2_grid.zip

Oracle Database 11g Release 2 (11.2.0.1.0) for AIX (PPC64)

http://download.oracle.com/otn/aix/oracle11g/R2/aix.ppc64_11gR2_database_1of2.zip

http://download.oracle.com/otn/aix/oracle11g/R2/aix.ppc64_11gR2_database_2of2.zip

http://download.oracle.com/otn/aix/oracle11g/R2/aix.ppc64_11gR2_client.zip

http://download.oracle.com/otn/aix/oracle11g/R2/aix.ppc32_11gR2_client.zip

http://download.oracle.com/otn/aix/oracle11g/R2/aix.ppc64_11gR2_grid.zip

ORACLE10GR2

Oracle Database 10g Release 2 (10.2.0.1.0) Enterprise/Standard Edition for Microsoft Windows (32-bit)

http://download.oracle.com/otn/nt/oracle10g/10201/10201_database_win32.zip

http://download.oracle.com/otn/nt/oracle10g/10201/10201_client_win32.zip

http://download.oracle.com/otn/nt/oracle10g/10201/10201_clusterware_win32.zip

http://download.oracle.com/otn/nt/oracle10g/10201/10201_gateways_win32.zip

Oracle Database 10g Release 2 (10.2.0.1.0) Enterprise/Standard Edition for Microsoft Windows (x64)

http://download.oracle.com/otn/nt/oracle10g/10201/102010_win64_x64_database.zip

http://download.oracle.com/otn/nt/oracle10g/10201/102010_win64_x64_client.zip

http://download.oracle.com/otn/nt/oracle10g/10201/102010_win64_x64_clusterware.zip

Oracle Database 10g Release 2 (10.2.0.1.0) Enterprise/Standard Edition for Linux x86

http://download.oracle.com/otn/linux/oracle10g/10201/10201_database_linux32.zip

http://download.oracle.com/otn/linux/oracle10g/10201/10201_client_linux32.zip

http://download.oracle.com/otn/linux/oracle10g/10201/10201_gateways_linux32.zip

Oracle Database 10g Release 2 (10.2.0.1.0) Enterprise/Standard Edition for Linux x86-64

http://download.oracle.com/otn/linux/oracle10g/10201/10201_database_linux_x86_64.cpio.gz

http://download.oracle.com/otn/linux/oracle10g/10201/10201_client_linux_x86_64.cpio.gz

http://download.oracle.com/otn/linux/oracle10g/10201/10201_clusterware_linux_x86_64.cpio.gz

http://download.oracle.com/otn/linux/oracle10g/10201/10201_gateways_linux_x86_64.cpio.gz

Oracle Database 10g Release 2 (10.2.0.1.0) Enterprise/Standard Edition for AIX5L

http://download.oracle.com/otn/aix/oracle10g/10201/10gr2_aix5l64_database.cpio.gz

http://download.oracle.com/otn/aix/oracle10g/10201/10gr2_aix5l64_client.cpio.gz

http://download.oracle.com/otn/aix/oracle10g/10201/10gr2_aix5l64_cluster.cpio.gz

http://download.oracle.com/otn/aix/oracle10g/10201/10gr2_aix5l64_gateways.cpio.gz

Oracle Database 10g Release 2 (10.2.0.2) Enterprise/Standard Edition for Solaris Operating System (x86)

http://download.oracle.com/otn/solaris/oracle10g/10202/10202_database_solx86.zip

http://download.oracle.com/otn/solaris/oracle10g/10202/10202_client_solx86.zip

http://download.oracle.com/otn/solaris/oracle10g/10202/10202_clusterware_solx86.zip

Oracle Database 10g Release 2 (10.2.0.1.0) Enterprise/Standard Edition for Solaris Operating System (x86-64)

http://download.oracle.com/otn/solaris/oracle10g/10201/x8664/10201_database_solx86_64.zip

http://download.oracle.com/otn/solaris/oracle10g/10201/x8664/10201_client_solx86_64.zip

http://download.oracle.com/otn/solaris/oracle10g/10201/x8664/10201_clusterware_solx86_64.zip