使用IN操作符执行Oracle表类型的动态本地SQL会导致无效标识符错误

Question

在过程中，在我的控制台中打印的最终sql语句是正确的，当您将其作为过程内的静态sql语句运行时，它运行时没有任何错误。但是在动态sql中，它失败了。

我尝试使用DYNAMIC_SQL包执行，但它会导致相同的错误。此外，我试着给它作为绑定变量"表(testCodes)"。这也失败了。标签oracleplsqldynamicquersstored -procedures建议标签:oracle你的问题无法提交。

create or replace TYPE "INPUTCODE" as object (pc varchar2(100) )
create or replace TYPE "INPUTCODEARR" IS TABLE OF inputcode;
create or replace PROCEDURE "TEST_PROC" (
            testCodes IN inputcodeArr, 
            timeHorizon IN NUMBER, 
            p_recordset OUT SYS_REFCURSOR) 
AS var_sqlStmt VARCHAR2(4096); 
BEGIN 
   var_sqlStmt := 'select t.a,t.b, t.c'; 
   var_sqlStmt := var_sqlStmt || 'from test t';
   if testCodes is not null then 
      var_sqlStmt := var_sqlStmt || ', table(testCodes) tc'; 
      var_sqlStmt := var_sqlStmt || 'where tc.pc = t.name'; 
   end if; 
   dbms_output.put_line('Final SQL Statement::' || var_sqlStmt); 
   open p_recordset for var_sqlStmt; 
END TEST_PROC;

Answer 1

就像kordirko建议的那样，你需要绑定变量;不是作为整个表操作符，而是作为table(:x)。下面是SQL*Plus的完整示例:

SQL> create or replace type inputcode as object (pc varchar2(100));
  2  /
Type created.
SQL> create or replace type inputcodearr is table of inputcode;
  2  /
Type created.
SQL> create table test(a number, b number, c number, name varchar2(100));
Table created.
SQL> insert into test values (1,2,3,'A');
1 row created.
SQL> create or replace procedure test_proc(
  2     testcodes in inputcodearr,
  3     p_recordset in out sys_refcursor
  4  ) is
  5  begin
  6     open p_recordset for
  7     '
  8             select t.a, t.b, t.c
  9             from test t, table(:input) tc
 10             where tc.pc = t.name
 11     '
 12     using testcodes;
 13  end;
 14  /
Procedure created.
SQL> variable my_refcursor refcursor;
SQL> exec test_proc(inputcodeArr(inputcode('A'), inputcode('B')), :my_refcursor);
PL/SQL procedure successfully completed.
SQL> print my_refcursor;
         A          B          C
---------- ---------- ----------
         1          2          3

Answer 2

var_sqlStmt := 'select t.a,t.b, t.c'; 
var_sqlStmt := var_sqlStmt || ' from test t';
if testCodes is not null then 
    var_sqlStmt := var_sqlStmt || ', table('||testCodes||') tc';
var_sqlStmt := var_sqlStmt || ' where tc.pc = t.name';