从SOCI中使用匿名PL SQL块调用PLsql脚本 [英] Calling PLsql script with an anonymous PL SQL block from SOCI

问题描述

我正在寻找一种通过SOCI调用匿名PLsql块的方法.数据传输通过以前在脚本中作为变量创建的refcursor进行:

I'm searching for a way to call an anonymous PLsql block through SOCI. The data transfer takes place through a refcursor that was previously created as a variable in the script:

variable rc refcursor
declare
   v_obj_id number(4,0) := 1;
   v_obj_def varchar(30);
   v_obj_type number := 1;
begin
  open :rc for
     select v_obj_id, OBJ_DEF_ID
     from MY_OBJECT_DEFS
     where OBJECT_TYPE = v_obj_type;
end;

我需要从我的应用程序中读取refcursor才能检索数据.我试图通过soci::statement执行上述操作，但它给了我错误:ORA-24333: zero iteration count.在SqlPlus中执行时，PLsql脚本可以正常工作.

I need to read the refcursor from my application to retrieve the data. I tried to execute the above through a soci::statement but it gives me the error: ORA-24333: zero iteration count. The PLsql script works fine when executed in SqlPlus.

1. 我如何在语句和 refrcsor rc?我是否应该为此目的使用其他SOCI构造(语句以外的构造)?
2. 我了解上面有两条说明 脚本; (i.refcursor创建，ii.匿名PLsql块 本身).我不确定是否可以拨打多个电话 单个SOCI语句中的指令.可以确认吗?

1. How can I make the connection between the statement and the refcursor rc? Should I use some other SOCI construct (other than statement) for this purpose?
2. I understand there are two instructions in the above script; (i. the refcursor creation, ii. the anonymous PLsql block itself). I'm not sure whether its possible to call multiple instructions in a single SOCI statement. Can this be confirmed?

以下是我尝试过的. sSQL包含上面的PLsql脚本:

Following is the what I tried. The sSQL contains the above PLsql script:

dbConn.open("...");
int iObjId;
std::string iObjDefId;
soci::indicator ind_iObjId = soci::i_ok,
        ind_iObjDefId = soci::i_ok;

soci::statement stmt(dbConn);
stmt.alloc();
stmt.prepare(sSQL);
stmt.exchange(soci::into(iObjId, ind_iObjId));
stmt.exchange(soci::into(iObjDefId, ind_iObjDefId));
stmt.define_and_bind();
stmt.execute(false);

while (stmt.fetch())
{
    if (soci::i_ok == ind_iObjId)
        std::cout << "Obj ID: " << iObjId << std::endl;

    if (soci::i_ok == ind_iObjDefId)
        std::cout << "Obj Def ID: " << iObjDefId << std::endl;
}

我正在使用Oracle 11g

I'm using Oracle 11g

推荐答案

语句variable rc refcursor既不是SQL也不是PL/SQL，而是Oracle

The statement variable rc refcursor is neither SQL nor PL/SQL but part of Oracle's SQL*Plus command-line utility and compatible third party products. I don't know C++ but presumably you would need to define a ref cursor object in the host program.

如果这不可行，并且您使用的是Oracle 12.1或更高版本，则可能使用

If this is not feasible, and you are on Oracle 12.1 or later, it's possible that you could use an implicit result set construction, along the lines of

declare
    rc sys_refcursor;
begin
    open rc for select * from dual;
    dbms_sql.return_result(rc);
end;

如中所讨论的

是否可以从PL/SQL块中输出SELECT状态?

这篇关于从SOCI中使用匿名PL SQL块调用PLsql脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！