如何能够从动态SQL中返回新记录ROWID的值呢？

Question

因为插入语句是在客户端生成的，想插入一条记录后，可以方便再次的数据处理时用，另外要求插入的SQL语句是动态生成的，如何可以实现呢？我做的实验：
sql:='insert into table(field1, field2) values(value1, value2) returning rowid into 某变量'
运行后出现错误，如何解决这个问题呢？

Answer 1

chinaghf朋友，你的建议是不错啊，你没有做过实验吧？我终于解决这个问题了，不过费尽了九牛二虎之力。下面是我解决问题的过程，请大家看一下，希望有益于刚开始学习如何调试程序并尝试自己解决问题的朋友。1.初步的实验，考虑到insert 语句可以通过returing返回结果，建立一个实验SQL块如下
set serveroutput on
declare
  Rrowid varchar2(18);
begin
  insert into table (field1, field2) values (value1, value2) returning RowidToChar(rowid) into Rrowid;
  dbms_output.put_line(Rrowid);
end;初始化调用过程成功，非常高兴。2.考虑到动态SQL可以从using out变量中返回值，或从into子句中返回值，修改调用过程如下
set serveroutput on
DECLARE
sSQL varchar2(2000);
execSQL varchar2(2000);
Rrowid varchar2(18);
BEGIN
--实验一：直接从execSQL中返回值
sSQL:='    insert into table (field1, field2) '
    ||'      values (value1, value2) ';
execSQL:=sSQL||' returning rowidtochar(rowid) into :1;';
execute immediate execSQL using out Rrowid;
dbms_output.put_line(execSQL);
END;
运行后，返回
DECLARE
*
ERROR 位于第 1 行:
ORA-00911: 无效字符
ORA-06512: 在line 10这样就以为是声明语句的错，查找了半天，找不出原因，以为是set serveroutput on之后DECLARE之前没有
加入标点，在此处加上分号，
set serveroutput on;
DECLARE
sSQL varchar2(2000);
execSQL varchar2(2000);
Rrowid varchar2(18);
BEGIN
......
运行结果还是一样这时就想了个办法，不如把SQL打印出来，看是哪里的错
set serveroutput on;
DECLARE
sSQL varchar2(2000);
execSQL varchar2(2000);
Rrowid varchar2(18);
BEGIN
--实验一：直接从execSQL中返回值
sSQL:='    insert into table (field1, field2) '
    ||'      values (value1, value2) ';
execSQL:=sSQL||' returning rowidtochar(rowid) into :1;';
--execute immediate execSQL using out Rrowid; --把这行注释掉
dbms_output.put_line(execSQL); --加上这一行
END;结果返回：
    insert into table (field1, field2)
      values (value1, value2)
 returning rowidtochar(rowid) into :1;PL/SQL 过程已成功完成。这回想到了，肯定不是声明语句的错，而是execute immediate execSQL using out Rrowid这条语句的错，那么既然
是变量错，那能不能用execute immediate execSQL into Rrowid来解决呢？可是分析过所有网上的例子，
execute immediate ... into ...实际上只能针对返回的数据集进行处理，实验的结果也证明这种调用方法是不可行的后来发现在Rapid SQL生成的插入语句中，居然有一种调用方法可以返回，那就是：
    insert into table (field1, field2)
      values (value1, value2)
    returning rowidtochar(rowid) into :retRowid;
不知道Rapid SQL这个工具是如何从这样的语句中返回变量的，但是我用了九牛二虎之力仍然未能解决这个问题。想知道结果的朋友可以待续......

Answer 2

后来看到可以用VARIABLE来建立变量，试着做了一段程序
VARIABLE retRowid varchar2(18);
    insert into table (field1, field2)
      values (value1, value2)
    returning rowidtochar(rowid) into :retRowid;
commit;
select :retRowid from dual;
嘿嘿，实验成功，以为这已经是解决之道，马上做了一个实验程序
declare
  sSQL varchar2(2000);
begin
  sSQL:='VARIABLE retRowid varchar2(18);
         insert into table (field1, field2)
            values (value1, value2)
         returning rowidtochar(rowid) into :retRowid;
         commit;
         select :retRowid from dual;';
  execute immediate sSQL;
end;
返回：
declare
*
ERROR 位于第 1 行:
ORA-00900: 无效 SQL 语句
ORA-06512: 在line 10
想到可能是sSQL串不正确的可能，改程序为
declare
  sSQL varchar2(2000);
begin
  sSQL:='VARIABLE retRowid varchar2(18);
         insert into table (field1, field2)
            values (value1, value2)
         returning rowidtochar(rowid) into :retRowid;
         commit;
         select :retRowid from dual;';
  --execute immediate sSQL; --把这行注释掉
  dbms_output.put_line(sSQL); --加上这一行
end;
返回结果：
VARIABLE retRowid varchar2(18);
         insert into table (field1, field2)values (value1, value2)
         returning rowidtochar(rowid) into :retRowid;commit;
         select :retRowid from dual;PL/SQL 过程已成功完成。看来还是sSQL中的SQL串语法不正确，引起execute immediate sSQL错误的问题，可以问题在哪里呢？如何解决呢？后来实验了一个小程序
declare
  sSQL varchar2(2000);
begin
  sSQL:='VARIABLE retRowid varchar2(18);
         :retRowid:=''kkkk'' ;dbms_output.put_line(sSQL);';
  execute immediate sSQL; --把这行注释掉
end;
从这里可以看，在动态SQL语句中，不可能进行VARIABLE声明操作。既然这样，问题又得怎么解决呢？待续......

Answer 3

后来发现一个动态SQL语句的调用方法：DBMS_SQL来进行调用，不过在机械工业出版社的
《Oracle8 PL/SQL 程序设计》一文中有详细的介绍，其实返回变量可以通过DBMS_SQL.BIND_VARIABLE来建立，
不过我注意到书中的例子中，仍然是通过返回数据集才可以建立返回的数据集才可以取到返回值，并通过
DBMS_SQL.VARIABLE_VALUE来获得返回值，虽然我只是想返回变量，但能否可以返回呢？我只好硬着头皮进行了测试：
DECLARE 
  retRowid varchar2(2000);
execSQL varchar2(2000);
cur integer;
rc  integer;
BEGIN
execSQL:='insert into table (field1, field2)
            values (value1, value2)
            returning rowidtochar(rowid) into :retRowid;';
--实验：通过调用更复杂的语句进行动态SQL调用
cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cur, execSQL, DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE(cur, ':retRowid', retRowid, 2000);
rc := DBMS_SQL.EXECUTE(cur);
DBMS_SQL.VARIABLE_VALUE(cur, ':retRowid', retRowid);
DBMS_SQL.CLOSE_CURSOR(cur);
END;
返回结果：
DECLARE
*
ERROR 位于第 1 行:
ORA-00911: 无效字符
ORA-06512: 在"SYS.DBMS_SYS_SQL", line 824
ORA-06512: 在"SYS.DBMS_SQL", line 32
ORA-06512: 在line 12个日的，仍然是错，到底有没有办法解决的啊？这时我陷入深深的绝望中。兄弟们，如果你们在这个时候已经遇上了这么多问题，还会继续下去吗？待续......兄弟们，如果没有人应答，说明没有人感兴趣，我就写到这里了，挺累的。

Answer 4

没有测试过，只是有这个想法，请楼主试试
set serveroutput on
declare
sSQL varchar2(2000);
execSQL varchar2(2000);
Rrowid varchar2(18);
begin
sSQL:='    insert into table (field1, field2) '
    ||'      values (value1, value2) ';
execute immediate sSQL returning RowidToChar(rowid) into Rrowid; dbms_output.put_line(execSQL);end;