Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 dynamic open for + ref cursor  [new]
KoTTT
Member

Откуда: Екб
Сообщений: 1511
В 8.1.7 это принципиально не работает?

DECLARE
   TYPE refcursor IS REF CURSOR;

   c     refcursor;
   l_c   DBMS_SQL.number_table;
BEGIN
   OPEN c FOR 'select rownum from all_objects where rownum < 1000';

   LOOP
      FETCH c
      BULK COLLECT INTO l_c LIMIT 100;

      FOR i IN 1 .. l_c.COUNT
      LOOP
         DBMS_OUTPUT.put_line (l_c (i));
      END LOOP;

      EXIT WHEN c%NOTFOUND;
   END LOOP;

   CLOSE c;
END;

В 9.2 - запросто...
2 мар 07, 13:57    [3853795]     Ответить | Цитировать Сообщить модератору
 Re: dynamic open for + ref cursor  [new]
KoTTT
Member

Откуда: Екб
Сообщений: 1511
Причем без bulk collect работает.
2 мар 07, 14:23    [3854048]     Ответить | Цитировать Сообщить модератору
 Re: dynamic open for + ref cursor  [new]
Jannny
Member

Откуда: Спб
Сообщений: 6424
Ну и чего Вам Оракл отвечает?
2 мар 07, 14:32    [3854118]     Ответить | Цитировать Сообщить модератору
 Re: dynamic open for + ref cursor  [new]
dmidek
Member

Откуда: Киев - Дортмунд
Сообщений: 116328
Что говорит ?

P.S. Зачем OPEN FOR динамически пишете ?
2 мар 07, 14:33    [3854126]     Ответить | Цитировать Сообщить модератору
 Re: dynamic open for + ref cursor  [new]
KoTTT
Member

Откуда: Екб
Сообщений: 1511
Говорит

ORA-01001: invalid cursor

Зачем динамически.

Есть некая процедура, в которой есть огроменный инсерт по большому объему данных.
Если открывать статически курсор, оптимизатор рассчитывает план как с переменными привязки, что делает план напрочь неработоспособным на таких объемах. С литералами план нормальный. Вот и хотел динамически открывать, пусть 1 раз разберет нормально и 1 раз выполнит.

Пример выше привел чисто для описания проблемы.
2 мар 07, 14:46    [3854257]     Ответить | Цитировать Сообщить модератору
 Re: dynamic open for + ref cursor  [new]
dmidek
Member

Откуда: Киев - Дортмунд
Сообщений: 116328
KoTTT
Говорит

ORA-01001: invalid cursor



Простите, а можно copy-paste сессии в sqlplus ?
2 мар 07, 14:51    [3854301]     Ответить | Цитировать Сообщить модератору
 Re: dynamic open for + ref cursor  [new]
KoTTT
Member

Откуда: Екб
Сообщений: 1511
SQL> select banner from v$version;

BANNER                                                                          
----------------------------------------------------------------                
Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production                      
PL/SQL Release 8.1.7.4.0 - Production                                           
CORE	8.1.7.2.1	Production                                                       
TNS for 32-bit Windows: Version 8.1.7.4.0 - Production                          
NLSRTL Version 3.4.1.0.0 - Production                                           

SQL> DECLARE
  2     TYPE refcursor IS REF CURSOR;
  3  
  4     c     refcursor;
  5     l_c   DBMS_SQL.number_table;
  6  BEGIN
  7     OPEN c FOR 'select rownum from all_objects where rownum < 100';
  8  
  9     LOOP
 10        FETCH c
 11        BULK COLLECT INTO l_c LIMIT 10;
 12  
 13        FOR i IN 1 .. l_c.COUNT
 14        LOOP
 15           DBMS_OUTPUT.put_line (l_c (i));
 16        END LOOP;
 17  
 18        EXIT WHEN c%NOTFOUND;
 19     END LOOP;
 20  
 21     CLOSE c;
 22  END;
 23  /
DECLARE
*
ERROR at line 1:
ORA-01001: invalid cursor 
ORA-06512: at line 10 


SQL> spool off
2 мар 07, 15:00    [3854374]     Ответить | Цитировать Сообщить модератору
 Re: dynamic open for + ref cursor  [new]
dmidek
Member

Откуда: Киев - Дортмунд
Сообщений: 116328
KoTTT
SQL> 


ОК. Спасибо.
Что произойдет
а. при декларировании OPEN FOR курсора статически ?
б. при явном статическом декларировании курсора
CURSOR cur_... IS ?
2 мар 07, 15:04    [3854416]     Ответить | Цитировать Сообщить модератору
 Re: dynamic open for + ref cursor  [new]
KoTTT
Member

Откуда: Екб
Сообщений: 1511
Если

OPEN c FOR select rownum from all_objects where rownum < 100;
или

CURSOR r IS SELECT ROWNUM FROM all_objects WHERE ROWNUM < 100;
...
OPEN c;

то все срабатывает.
2 мар 07, 15:08    [3854476]     Ответить | Цитировать Сообщить модератору
 Re: dynamic open for + ref cursor  [new]
KoTTT
Member

Откуда: Екб
Сообщений: 1511
Могу обрисовать проблему, может что и посоветуете.

Есть необходимость делать следующее

insert into table1
select pk, ... from ... table11
where table11.dat between d1 and d2
and ...

(Условию "table11.dat between d1 and d2" удовлетворяют около миллиона записей (~50% таблицы))

Затем, по всем только что вставленным pk (уникальным для table1)

loop
insert into table2
select ... from table22
where table22.col1 = pk
and ...;

insert into table3
select ... from table33
where table33.col1 = pk
and ...;

и т.д.
end loop;

Т.к. в insert into ... select ... нельзя использовать returning bulk collect into ..., хотелось сделать все это в виде

cursor c is
select pk, ... from ... table11
where table11.dat between d1 and d2
and ...

open c;
loop
fetch c bulk collect into ... limit N;
insert into table1 values (...) returning pk bulk collect into bc...

forall i in 1..bc.count
	insert into table2
	select ... from table22
	where table22.col1 = bc.pk
	and ...;

forall i in 1..bc.count
	insert into table3
	select ... from table33
	where table33.col1 = bc.pk
	and ...;

и т.д.

exit when c%notfound;
end loop;
Однако в любом варианте, план запроса в курсоре "c" будет неадекватным (доступ по индексам) из-за переменных привязки.
Поэтому решил открывать его динамически

open c for
'select pk, ... from ... table11
where table11.dat between ' || d1 || ' and ' || d2 ||
'and ...';

Но вот столкнулся с тем, что в 8.1.7 так нельзя в сочетании с "fetch c bulk collect into ...".

Подскажите, как можно проще и правильнее сделать? Чувствую, что можно, но найти способ сам пока не могу.
5 мар 07, 08:14    [3861164]     Ответить | Цитировать Сообщить модератору
 Re: dynamic open for + ref cursor  [new]
anvano
Member

Откуда: Москва
Сообщений: 986
KoTTT

Однако в любом варианте, план запроса в курсоре "c" будет неадекватным (доступ по индексам) из-за переменных привязки.
Поэтому решил открывать его динамически


А воспользоваться хинтами для закрепления нормального, с вашей точки зрения, плана никак?
5 мар 07, 08:38    [3861260]     Ответить | Цитировать Сообщить модератору
 Re: dynamic open for + ref cursor  [new]
KoTTT
Member

Откуда: Екб
Сообщений: 1511
Хинтами не хотелось бы. Данные меняются, объемы тоже. Да и пишется это все не для одной БД, а для одинаковых по структуре, но разных по объемам БД. Хотелось бы оставить оптимизатору свободу выбора, может на каких-то наборах или с новыми условиями он правильно решит использовать индексы. Смысл динамического курсора здесь как раз в этом - дать ему больше информации для правильного выбора. Повторюсь - курсор "одноразовый", раз открыли, раз выбрали и все.
5 мар 07, 08:52    [3861289]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить