Выполнение Dynamic Native SQL с типом таблицы Oracle с использованием оператора IN дает недопустимую ошибку идентификатора

В процедуре окончательный оператор sql, который печатается в моей консоли, верен, и когда вы запускаете его как статический оператор sql внутри процедуры, он выполняется без каких-либо ошибок. Но внутри динамического sql это не удается.

Я попытался выполнить с использованием пакета DYNAMIC_SQL, но это приводит к той же ошибке. Кроме того, я попытался указать его как переменную привязки для «таблицы (testCodes)». Это тоже не удалось. Теги oracleplsqldynamicquerystored-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;

oracle stored-procedures plsql dynamicquery
person Ashish    schedule 15.08.2015    source источник
comment
Пожалуйста, ответьте на мои вопросы   -  person Ashish    schedule 15.08.2015
comment
Попробуйте предложение USING и переменную связывания: var_sqlStmt := var_sqlStmt || ', table(:x) tc'; и open p_recordset for var_sqlStmt USING testCodes;   -  person krokodilko    schedule 15.08.2015
comment
Это потому, что локальная переменная невидима внутри динамического запроса.   -  person Egor Skriptunoff    schedule 16.08.2015

Ответы (2)


arrow_upward
0
arrow_downward

Как предложил Кордирко, вам нужно связать переменную; не как оператор всей таблицы, а как 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
person Jon Heller    schedule 15.08.2015

arrow_upward
-1
arrow_downward 

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';

Это должно работать.

person Biswanath Mishra    schedule 15.08.2015