一、在oracle项目开发中越到问题:

在利用ODP向oracle中插入数据时,如果这样写:
  insert into clobTable (id, story) values(1,'....'); 其中story为clob类型
  如果story的长度大于2000字节,直接插入将出现 ORA-01704:文字字符串过长 的错误。
解决方案:
      方案一、利用参数
  insert into clobTable (id, story) values(1,:story);
  OracleParameter param = new OracleParameter("story", OracleDbType.Clob);
  param.Direction = ParameterDirection.Input;
  param.Value = str;
  cmd.Parameters.Add(param);
      方案二、利用存储过程
  这个就不用说了,写个存储过程,把参数传入即可。

二、解决方法

oracle 中,如下操作:

insert into table values(a,3,'一个长文章');

ORA-01704: 文字字符串过长!

虽然在表中已经是clob字段,足够存储4G的内容,但是如果存的是特别长的字符串,超过4000就会报错。

解决方法:

方法一:就写个存储过程,然后用参数传过去就没问题了。

declare

v_clob clob :='一个长文章';

begin
  insert into table values(a,3,:clob);
 end;

 这样就可以插进去了,所以我觉得应该是隐式转换,oracle默认把字符串 转换成 varchar2 类型,
 而这个字符串的长度,又比4000大 所以会报ora-01704错误.

真实环境用的存储过程:

 CREATE OR REPLACE PROCEDURE "BAI"."LOGMNRTXT" (tab1 in varchar2,scns in number,timestamps in varchar2,seg_owner in varchar2,
table_name in varchar2,session_info in varchar2,sql_redo in clob,ssession in varchar2,serial in varchar2,operation in varchar2) is

 str varchar(1000);
 --注意tab1必须要更改,发现原来的logmnr_contents20140524中的sql_redo为varchar,需要改成clob
begin

str:= 'insert into '||tab1||' values(:1,:2,:3,:4,:5,:6,:7,:8,:9)';
   execute immediate str using scns,to_date(timestamps,'yyyy-MM-dd hh24:mi:ss'),seg_owner,table_name,session_info,sql_redo,ssession,serial,operation;
end logmnrtxt;
/

 

方法二:很复杂,其实没必要这么用,主要是为了学习高级的存储过程写法

创建存储过程:

CREATE OR REPLACE PROCEDURE p_In_Clob(pId IN NUMBER,outVar IN VARCHAR2)
 IS
   text_Var  CLOB;
   amount_Var  NUMBER;
   offset_Var  NUMBER;
 BEGIN
   INSERT INTO test VALUES(pId,empty_clob());
   SELECT text INTO text_var FROM test
     WHERE id=pId;
   amount_var:=LENGTH(outVar);
   offset_var:=1;
   DBMS_LOB.WRITE(text_Var,amount_Var,offset_Var,outVar);
   COMMIT;
 END p_In_Clob;



调用存储过程:

begin
   p_In_Clob(1,'...');
 end;

三、oracle 存储过程使用动态sql

Oracle存储过程使用动态SQL 有两种写法:用 DBMS_SQL 或 execute immediate,建议使用后者。试验步骤如下:

1. DDL和DML (注意DDL中可以用拼接字符串的方法用来create table或drop table,在DML中,类似于insert则不可以直接用execute immediate中直接拼接的方法,必须用using传递参数)



1     /*** DDL ***/  
 2     begin   
 3         EXECUTE IMMEDIATE 'drop table temp_1';   
 4         EXECUTE IMMEDIATE 'create table temp_1(name varchar2(8))';   
 5     end;  
 6      
 7     /*** DML ***/  
 8     declare   
 9         v_1 varchar2(8);   
10         v_2 varchar2(10);   
11         str varchar2(50);   
12     begin   
13         v_1:='测试人员';   --这里的v_1,v_2可以是直接存储过程中传过来的参数
14         v_2:='北京';   
15         str := 'INSERT INTO test (name ,address) VALUES (:1, :2)';   
16         EXECUTE IMMEDIATE str USING v_1, v_2;   
17         commit;   
18     end;



 

2. 返回单条结果

例1:



1     declare   
 2         str varchar2(500);   
 3         c_1 varchar2(10);   
 4         r_1 test%rowtype;   
 5     begin   
 6         c_1:='测试人员';   
 7         str:='select * from test where name=:c WHERE ROWNUM=1';   
 8         execute immediate str into r_1 using c_1;   
 9         DBMS_OUTPUT.PUT_LINE(R_1.NAME||R_1.ADDRESS);   
10     end ;



例2:



1     declare  
 2         v_col_name varchar2(30) := 'name'; --字段名 name 用变量来表示  
 3         v_user_name  varchar2(30); --用户名称  
 4         v_user_age  integer;       --用户年龄  
 5         v_sql_str  varchar2(500);  --动态 SQL 语句  
 6     begin  
 7         v_sql_str := 'select '||v_col_name||',age from users --字段名后面不能紧随 into 到变量了  
 8                        where age between :start_age and :end_age and rownum=1'; --两个命名参数  ,注意拼接的方法
 9           
10         --用 execute immediate 动态执行 SQL 语句  
11         --注意其后的 into 字段值到变量的写法,还有 using 来代入参数  
12         execute immediate v_sql_str into v_user_name,v_user_age using 18,25;   
13           
14         dbms_output.put_line('第一个符合条件的用户:'||v_user_name||',年龄:'||v_user_age);  
15     end;



 

3. 返回结果集



1     CREATE OR REPLACE package pkg_test as   
 2         /* 定义ref cursor类型   
 3         不加return类型,为弱类型,允许动态sql查询,   
 4         否则为强类型,无法使用动态sql查询;   
 5         */   
 6         type myrctype is ref cursor;  
 7      
 8         --函数申明   
 9         function get(intID number) return myrctype;   
10     end pkg_test;   
11     /  
12      
13     CREATE OR REPLACE package body pkg_test as   
14     --函数体   
15         function get(intID number) return myrctype is   
16             rc myrctype; --定义ref cursor变量   
17             sqlstr varchar2(500);   
18         begin   
19             if intID=0 then   
20                 --静态测试,直接用select语句直接返回结果   
21                 open rc for select id,name,sex,address,postcode,birthday from   
22     student;   
23             else   
24                 --动态sql赋值,用:w_id来申明该变量从外部获得   
25                 sqlstr := 'select id,name,sex,address,postcode,birthday from student   
26     where id=:w_id';   
27                 --动态测试,用sqlstr字符串返回结果,用using关键词传递参数   
28                 open rc for sqlstr using intid;   
29             end if;  
30      
31             return rc;   
32         end get;  
33      
34     end pkg_test;   
35     /



四、存储过程一个总结的非常全的博客