请问如何取得dbms_output.put_line的结果？举个存储过程的例子？

[unm]

其实我是传入一个拼了的sql，然后想返回一个字符串，老是不通过啊

如果能一个sql执行完也可以，我查了一下好像不行，得用存储过程，是这样吗？
目的就是想把刚插入的id用一个字串取回，或者还有更好的方法吗？

declare rtndata nvarchar2(2000);mKeyID NUMBER;
begin
insert into t_vehicle_kind (id,code,name,org_id) values (S_vehicle_kind.NEXTVAL,'[e-mail]','singletab','0');
select S_vehicle_kind.currval into mKeyID from dual;
rtndata:=rtndata||',{comp_119:'||mKeyID||'}';
insert into t_vehicle_kind (id,code,name,org_id) values (S_vehicle_kind.NEXTVAL,'[e-mail]','singletab','0');
select S_vehicle_kind.currval into mKeyID from dual;
rtndata:=rtndata||',{comp_121:'||mKeyID||'}';
insert into t_vehicle_kind (id,code,name,org_id) values (S_vehicle_kind.NEXTVAL,'[e-mail]','singletab','0');
select S_vehicle_kind.currval into mKeyID from dual;
rtndata:=rtndata||',{comp_122:'||mKeyID||'}';
dbms_output.put_line(rtndata);
end;

[unm]

传入sql是这样的
------------------------------------------------------------------------------
declare rtndata nvarchar2(2000);mKeyID NUMBER;
begin
insert into t_vehicle_kind (id,code,name,org_id) values (S_vehicle_kind.NEXTVAL,'[e-mail]','singletab','0');
select S_vehicle_kind.currval into mKeyID from dual;
rtndata:=rtndata||',{comp_119:'||mKeyID||'}';
insert into t_vehicle_kind (id,code,name,org_id) values (S_vehicle_kind.NEXTVAL,'[e-mail]','singletab','0');
select S_vehicle_kind.currval into mKeyID from dual;
rtndata:=rtndata||',{comp_121:'||mKeyID||'}';
insert into t_vehicle_kind (id,code,name,org_id) values (S_vehicle_kind.NEXTVAL,'[e-mail]','singletab','0');
select S_vehicle_kind.currval into mKeyID from dual;
rtndata:=rtndata||',{comp_122:'||mKeyID||'}';
dbms_output.put_line(rtndata);
select rtndata into rtndata from dual;
end;
------------------------------------------------------------------------------
存储过程是这样的
create or replace procedure proc_getdata(exesql VARCHAR2,r_data out varchar2) AS
BEGIN
EXECUTE IMMEDIATE exesql into r_data;
END;
------------------------------------------------------------------------------
java代码是这样的
cs = conne.prepareCall("{call return_sql(?,?)}");
cs.setString(1, sql);
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
cs.executeQuery();
String return_data = cs.getString(1);
return return_data;
------------------------------------------------------------------------------
java提示错误是这样的
java.sql.SQLException: ORA-06550: 第 1 行, 第 7 列:
PLS-00905: 对象 RECODE_INIT.RETURN_SQL 无效
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1093)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2047)
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1940)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2709)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:589)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:527)

------------------------------------------------------------------------------
然后郁闷是我这样子的

：（

[xiaohu8855]

你怎么会有两个？号呢 ,把你的代码贴出来，给你看看 包括你的java代码？ 还有你的数据库连接打开了吗

[xiaohu8855]

我发给你的帖子是经过测试的

[Derek-Chen]

create or replace procedure test(v_sql in varchar2,v_out out 结构类型) is
begin
end;

declare
begin
//调用上面的存储过程
test( vsql,vout);
//这个vout里的值就是你需要的返回值了
end；

[unm]

哦，上面发的就是代码

一个传入sql语句
一个传回数据

两个参数啊

[Derek-Chen]

补充一点： 在存储过程中，把你需要的数据赋给v_out

[unm]

传入sql是这样的
------------------------------------------------------------------------------
declare rtndata nvarchar2(2000);mKeyID NUMBER;
begin
insert into t_vehicle_kind (id,code,name,org_id) values (S_vehicle_kind.NEXTVAL,'[e-mail]','singletab','0');
select S_vehicle_kind.currval into mKeyID from dual;
rtndata:=rtndata||',{comp_119:'||mKeyID||'}';
insert into t_vehicle_kind (id,code,name,org_id) values (S_vehicle_kind.NEXTVAL,'[e-mail]','singletab','0');
select S_vehicle_kind.currval into mKeyID from dual;
rtndata:=rtndata||',{comp_121:'||mKeyID||'}';
insert into t_vehicle_kind (id,code,name,org_id) values (S_vehicle_kind.NEXTVAL,'[e-mail]','singletab','0');
select S_vehicle_kind.currval into mKeyID from dual;
rtndata:=rtndata||',{comp_122:'||mKeyID||'}';
dbms_output.put_line(rtndata);
select rtndata into rtndata from dual;
end;
------------------------------------------------------------------------------
存储过程是这样的（参数一个in，一个out）
create or replace procedure proc_getdata(exesql in VARCHAR2,r_data out varchar2) AS
BEGIN
EXECUTE IMMEDIATE exesql into r_data;
END;
------------------------------------------------------------------------------
java代码是这样的
cs = conne.prepareCall("{call return_sql(?,?)}");
cs.setString(1, sql);
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
cs.executeQuery();
String return_data = cs.getString(1);
return return_data;
------------------------------------------------------------------------------
java提示错误是这样的
java.sql.SQLException: ORA-06550: 第 1 行, 第 7 列:
PLS-00905: 对象 RECODE_INIT.RETURN_SQL 无效
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1093)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2047)
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1940)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2709)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:589)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:527)


依然是这样的

[xiaohu8855]

java代码是这样的
cs = conne.prepareCall("{call proc_getdata(?,?)}");---此处已经改为正确的存储过程名
cs.setString(1, sql);
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
cs.executeQuery();
String return_data = cs.getString(1);
return return_data;

这句话不对
String return_data = cs.getString(1);
应该是
String return_data = cs.getString(2);

[unm]

[Quote=引用 19 楼 xiaohu8855 的回复:]

java代码是这样的
cs = conne.prepareCall("{call proc_getdata(?,?)}");---此处已经改为正确的存储过程名
cs.setString(1, sql);
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
cs.executeQuery();
String return_data = cs.getString(1);
return return_data;

这句话不对
String return_data = cs.getString(1);
应该是
String return_data = cs.getString(2);
[/Quote]

现在是执行到 cs.executeQuery(); 就出现这个错误了。。。。。
这个问题这么郁闷。。。。。

[xiaohu8855]

楼主，你这个肯定是错的嘛。
java提示错误是这样的
java.sql.SQLException: ORA-06550: 第 1 行, 第 7 列:
PLS-00905: 对象 RECODE_INIT.RETURN_SQL 无效
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored
从PLS-00905: 对象 RECODE_INIT.RETURN_SQL 无效可以看出过程名不存在或失效。
在看下java代码
cs = conne.prepareCall("{call return_sql(?,?)}");
cs.setString(1, sql);
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
cs.executeQuery();
String return_data = cs.getString(1);
return return_data;
而call 后面是过程名，而你的过程名是proc_getdata，所以错误了

[xiaohu8855]

楼主，你这个肯定是错的嘛。
java提示错误是这样的
java.sql.SQLException: ORA-06550: 第 1 行, 第 7 列:
PLS-00905: 对象 RECODE_INIT.RETURN_SQL 无效
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored
从PLS-00905: 对象 RECODE_INIT.RETURN_SQL 无效可以看出过程名不存在或失效。
在看下java代码
cs = conne.prepareCall("{call return_sql(?,?)}");
cs.setString(1, sql);
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
cs.executeQuery();
String return_data = cs.getString(1);
return return_data;
而call 后面是过程名，而你的过程名是proc_getdata，所以错误了

[inthirties]

用out 参数

[qinfei008]

存储过程的调用是cs.execute()而不是cs.executeQuery()

[unm]

哦，那是我写错的，就算正确的，也还是有问题的
在plsql中传入的sql都执行的正常，但是在java运行时就会错误

java里拼了sql，传入存储过程的sql是这样的
------------------------------------------------------------------------------
declare rtndata nvarchar2(2000);mKeyID NUMBER;
begin
insert into t_vehicle_kind (id,code,name,org_id) values (S_vehicle_kind.NEXTVAL,'[e-mail]','singletab','0');
select S_vehicle_kind.currval into mKeyID from dual;
rtndata:=rtndata||',{comp_119:'||mKeyID||'}';
insert into t_vehicle_kind (id,code,name,org_id) values (S_vehicle_kind.NEXTVAL,'[e-mail]','singletab','0');
select S_vehicle_kind.currval into mKeyID from dual;
rtndata:=rtndata||',{comp_121:'||mKeyID||'}';
insert into t_vehicle_kind (id,code,name,org_id) values (S_vehicle_kind.NEXTVAL,'[e-mail]','singletab','0');
select S_vehicle_kind.currval into mKeyID from dual;
rtndata:=rtndata||',{comp_122:'||mKeyID||'}';
dbms_output.put_line(rtndata);
select rtndata into rtndata from dual; -------不知这样写对不对？
end;
------------------------------------------------------------------------------
存储过程是这样的（参数一个in，一个out），不知这样返回是否正确？
create or replace procedure proc_getdata(exesql in VARCHAR2,r_data out varchar2) AS
BEGIN
EXECUTE IMMEDIATE exesql into r_data;
END;
------------------------------------------------------------------------------
java代码是这样的
cs = conne.prepareCall("{call proc_getdata(?,?)}");---此处已经改为正确的存储过程名
cs.setString(1, sql);
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
cs.executeQuery();
String return_data = cs.getString(1);
return return_data;
------------------------------------------------------------------------------
java提示错误是这样的
java.sql.SQLException: ORA-01007: 选择列表中没有变量
ORA-06512: 在"RECODE_INIT.PROC_GETDATA", line 4
ORA-06512: 在line 1


我查的有些回复是说列数不一致？什么意思？

[xiaohu8855]

那就是过程是错误的，你可以先在plsql下测试下，如果正常的情况下并且参数无误的话是不会有问题的，这是一个很简单的问题，楼主仔细检查检查就可以解决了

[unm]

[Quote=引用 1 楼 xiaohu8855 的回复:]

你这个没问题啊，预期结果是不是这样的？
,{comp_119:1},{comp_121:2},{comp_122:3}
[/Quote]

是啊，预期的结果是这样的，说明这个sql是没有问题的
------------------------------------------------------------------------------
declare rtndata nvarchar2(2000);mKeyID NUMBER;
begin
insert into t_vehicle_kind (id,code,name,org_id) values (S_vehicle_kind.NEXTVAL,'[e-mail]','singletab','0');
select S_vehicle_kind.currval into mKeyID from dual;
rtndata:=rtndata||',{comp_119:'||mKeyID||'}';
insert into t_vehicle_kind (id,code,name,org_id) values (S_vehicle_kind.NEXTVAL,'[e-mail]','singletab','0');
select S_vehicle_kind.currval into mKeyID from dual;
rtndata:=rtndata||',{comp_121:'||mKeyID||'}';
insert into t_vehicle_kind (id,code,name,org_id) values (S_vehicle_kind.NEXTVAL,'[e-mail]','singletab','0');
select S_vehicle_kind.currval into mKeyID from dual;
rtndata:=rtndata||',{comp_122:'||mKeyID||'}';
dbms_output.put_line(rtndata);
select rtndata into rtndata from dual;
end;
------------------------------------------------------------------------------
当我把这个sql代入存储过程的时候
create or replace procedure proc_getdata(exesql in VARCHAR2,r_data out varchar2) AS
BEGIN
EXECUTE IMMEDIATE exesql into r_data;
END;
想返回一个值，可是java代码就是提示
java.sql.SQLException: ORA-01007: 选择列表中没有变量
ORA-06512: 在"RECODE_INIT.PROC_GETDATA", line 4
ORA-06512: 在line 1

难道是哪里还是oracle有地雷？不容许这样的sql？plsql只执行了啊，没有报错
java执行就有问题了，怎么测试存储过程呢？
用cs.execute()也是这个错误提示的。非常非常想解决掉。。。

[forgetsam]

EXECUTE IMMEDIATE exesql into r_data 是把这个语句执行的结果存入r_data ，这个语句只能返回一行一列，你语句里写的什么?

另外你到底是想干啥啊?

如果你的表固定了，
insert into 表 values(....) returing 主键 into 你的输出参数 就完事了

[qldsrx]

显然你这个proc_getdata存储过程只能传递一个最简单的sql查询，也就是select语句，无法传递一个过程执行，因为过程是不能用into来输出的。

[悠忧虫]

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:45027262935845

测试用到的存储过程

create or replace procedure  emp_report
  as
  begin
      dbms_output.put_line
      ( rpad( 'Empno', ) ||
        rpad('Ename',) ||
        rpad('Job',) );

      dbms_output.put_line
      ( rpad( '-',, '-' ) ||
        rpad('  -',,'-') ||
        rpad('  -',,'-') );

      for x in ( select * from emp )
      loop
          dbms_output.put_line
          ( to_char( x.empno, '' ) || '  ' ||
            rpad( x.ename, ) ||
            rpad( x.job, ) );
      end loop;
  end;

import java.sql.*;

class DbmsOutput 
{
/*
 * our instance variables. It is always best to 
 * use callable or prepared statements and prepare (parse)
 * them once per program execution, rather then one per 
 * execution in the program.  The cost of reparsing is 
 * very high.  Also -- make sure to use BIND VARIABLES!
 *
 * we use three statments in this class. One to enable 
 * dbms_output - equivalent to SET SERVEROUTPUT on in SQL*PLUS.
 * another to disable it -- like SET SERVEROUTPUT OFF.
 * the last is to "dump" or display the results from dbms_output
 * using system.out
 *
 */
private CallableStatement enable_stmt;
private CallableStatement disable_stmt;
private CallableStatement show_stmt;


/* 
 * our constructor simply prepares the three
 * statements we plan on executing. 
 *
 * the statement we prepare for SHOW is a block of 
 * code to return a String of dbms_output output.  Normally, 
 * you might bind to a PLSQL table type but the jdbc drivers
 * don't support PLSQL table types -- hence we get the output
 * and concatenate it into a string.  We will retrieve at least
 * one line of output -- so we may exceed your MAXBYTES parameter
 * below. If you set MAXBYTES to 10 and the first line is 100 
 * bytes long, you will get the 100 bytes.  MAXBYTES will stop us
 * from getting yet another line but it will not chunk up a line.
 *
 */
public DbmsOutput( Connection conn ) throws SQLException
{
    enable_stmt  = conn.prepareCall( "begin dbms_output.enable(:1); end;" );
    disable_stmt = conn.prepareCall( "begin dbms_output.disable; end;" );

    show_stmt = conn.prepareCall( 
          "declare " +
          "    l_line varchar2(255); " +
          "    l_done number; " +
          "    l_buffer long; " +
          "begin " +
          "  loop " +
          "    exit when length(l_buffer)+255 > :maxbytes OR l_done = 1; " +
          "    dbms_output.get_line( l_line, l_done ); " +
          "    l_buffer := l_buffer || l_line || chr(10); " +
          "  end loop; " +
          " :done := l_done; " +
          " :buffer := l_buffer; " +
          "end;" );
}

/*
 * enable simply sets your size and executes
 * the dbms_output.enable call
 *
 */
public void enable( int size ) throws SQLException
{
    enable_stmt.setInt( 1, size );
    enable_stmt.executeUpdate();
}

/*
 * disable only has to execute the dbms_output.disable call
 */
public void disable() throws SQLException
{
    disable_stmt.executeUpdate();
}

/*
 * show does most of the work.  It loops over
 * all of the dbms_output data, fetching it in this
 * case 32,000 bytes at a time (give or take 255 bytes).
 * It will print this output on stdout by default (just
 * reset what System.out is to change or redirect this 
 * output).
 */

public void show() throws SQLException
{
int               done = 0;

    show_stmt.registerOutParameter( 2, java.sql.Types.INTEGER );
    show_stmt.registerOutParameter( 3, java.sql.Types.VARCHAR );

    for(;;)
    {    
        show_stmt.setInt( 1, 32000 );
        show_stmt.executeUpdate();
        System.out.print( show_stmt.getString(3) );
        if ( (done = show_stmt.getInt(2)) == 1 ) break;
    }
}

/* 
 * close closes the callable statements associated with
 * the DbmsOutput class. Call this if you allocate a DbmsOutput
 * statement on the stack and it is going to go out of scope -- 
 * just as you would with any callable statement, result set
 * and so on.
 */
public void close() throws SQLException
{
    enable_stmt.close();
    disable_stmt.close();
    show_stmt.close();
}
}

测试类：

import java.sql.*;
 
class test {

public static void main (String args []) 
   throws SQLException 
{
    DriverManager.registerDriver 
      (new oracle.jdbc.driver.OracleDriver());
 
    Connection conn = DriverManager.getConnection
         ("jdbc:oracle:thin:@dbserver:1521:ora8i", 
          "scott", "tiger");
    conn.setAutoCommit (false); 

    Statement stmt = conn.createStatement();

    DbmsOutput dbmsOutput = new DbmsOutput( conn );

    dbmsOutput.enable( 1000000 );

    stmt.execute
    ( "begin emp_report; end;" );
    stmt.close();

    dbmsOutput.show();

    dbmsOutput.close();
    conn.close();
}
 
}

[shakusi]

能不能不用存储过程呢？
像SQL SERVER 那样直接
select rtndata;