Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 For all и Динамический SQL  [new]
Stepan_mk
Member

Откуда: Україна
Сообщений: 1299
Есть такой код
Procedure MyProc (... schema:Number) is 
cursor сr is select distinct one_number_filed
                from some_table_name;
begin
...
for scr in cr loop
execute immedate 'insert into other_table
select ... from sh_'||schema||'.table sl
START WITH sl.one_number_filed = '||scr.one_number_filed||'
connect ....'
...
end;
Вопрос можна ли такое переписать через forall? Если да то как?
Для меня вся проблема в том что селект идёт из таблички из неизвестной наперёд схеми.
31 авг 06, 12:33    [3076085]     Ответить | Цитировать Сообщить модератору
 Re: For all и Динамический SQL  [new]
dmidek
Member

Откуда: Киев - Дортмунд
Сообщений: 116077
Возможно.

declare
type typ_tab is table of scott.emp.empno%TYPE;
tab_tab typ_tab := typ_tab(7369, 7499);
begin
FORALL I IN 1..tab_tab.count
execute immediate
'Insert into scott.emp
 select * from scott.emp 
 connect by prior mgr = empno 
 start with empno = :empno' using tab_tab(I);
end;
/

(Не удивляйтесь, для теста отключил PK на scott.emp :-))
31 авг 06, 12:53    [3076256]     Ответить | Цитировать Сообщить модератору
 Re: For all и Динамический SQL  [new]
contr
Member

Откуда:
Сообщений: 1909
Stepan_mk
Есть такой код
Procedure MyProc (... schema:Number) is 
cursor сr is select distinct one_number_filed
                from some_table_name;
begin
...
for scr in cr loop
execute immedate 'insert into other_table
select ... from sh_'||schema||'.table sl
START WITH sl.one_number_filed = '||scr.one_number_filed||'
connect ....'
...
end;
Вопрос можна ли такое переписать через forall? Если да то как?
Для меня вся проблема в том что селект идёт из таблички из неизвестной наперёд схеми.

Можно но не нужно.
Вставьте перед началом
execute immediate 'alter session set current_schema= sh_'||schema;
И в forall - обычный статик с биндом без указания схемы.

Ваш вариант execute immediate тоже стоило бы переписать:
execute immedate 'insert into other_table
select ... from sh_'||schema||'.table sl
START WITH sl.one_number_filed = :F1 connect ....' 
using scr.one_number_filed;
31 авг 06, 12:58    [3076298]     Ответить | Цитировать Сообщить модератору
 Re: For all и Динамический SQL  [new]
вот так вот
Guest
во превых использовать execute immediate крайне нехорошо в этом случае.
используй dbms_sql : перед циклом разбери предложение, в цикле связывай переменные и выполняй.
после цикла закрыть курсор. так будет гораздо продуктивнее.

второе : сколоко всего вариантов схем из которых надо выбирать конкретную таблицу, не лучше ли их перечислить по условию статически ?
короче бельше инфы дайте.
31 авг 06, 12:58    [3076303]     Ответить | Цитировать Сообщить модератору
 Re: For all и Динамический SQL  [new]
Stepan_mk
Member

Откуда: Україна
Сообщений: 1299
dmidek

FORALL I IN 1..tab_tab.count
execute immediate
'Insert into scott.emp
select * from scott.emp
connect by prior mgr = empno
start with empno = :empno' using tab_tab(I);
end;
/[/src]

У меня в таком коде
     FORALL i IN l_site_loc_tab.FIRST .. l_site_loc_tab.LAST    --LOOP
       EXECUTE IMMEDIATE l_sql_stmt USING l_site_loc_tab(i), l_site_loc_tab(i);
На execute immediate ошиПка

(1): PLS-00103: Encountered the symbol "EXECUTE" when expecting one of the following:
(1):
(1): . ( * @ % & - + / at mod rem select update <an exponent (**)>
(1): delete insert ||

Oracle 8.1.7
31 авг 06, 13:28    [3076600]     Ответить | Цитировать Сообщить модератору
 Re: For all и Динамический SQL  [new]
contr
Member

Откуда:
Сообщений: 1909
Stepan_mk
Oracle 8.1.7

С этого надо было начинать :)
Пользуйтесь set current_schema и не мучайте животинку понапрасну.
31 авг 06, 13:31    [3076629]     Ответить | Цитировать Сообщить модератору
 Re: For all и Динамический SQL  [new]
dmidek
Member

Откуда: Киев - Дортмунд
Сообщений: 116077
Stepan_mk
dmidek

FORALL I IN 1..tab_tab.count
execute immediate
'Insert into scott.emp
select * from scott.emp
connect by prior mgr = empno
start with empno = :empno' using tab_tab(I);
end;
/[/src]

У меня в таком коде
     FORALL i IN l_site_loc_tab.FIRST .. l_site_loc_tab.LAST    --LOOP
       EXECUTE IMMEDIATE l_sql_stmt USING l_site_loc_tab(i), l_site_loc_tab(i);
На execute immediate ошиПка

(1): PLS-00103: Encountered the symbol "EXECUTE" when expecting one of the following:
(1):
(1): . ( * @ % & - + / at mod rem select update <an exponent (**)>
(1): delete insert ||

Oracle 8.1.7

Я на 9.2.0.5 пробовал, а восьмерки у меня нет ...
Значит, там нельзя ...
31 авг 06, 13:32    [3076641]     Ответить | Цитировать Сообщить модератору
 Re: For all и Динамический SQL  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 54376
Том Кайт пишет нечто подобное (для 8-ки):
execute immediate
'begin
   forall i in 1 .. :n
      insert into ' || p_name || '(x) values (:x(i));
end;'
using i, p_array
31 авг 06, 13:40    [3076719]     Ответить | Цитировать Сообщить модератору
 Re: For all и Динамический SQL  [new]
Stepan_mk
Member

Откуда: Україна
Сообщений: 1299
andreymx
Том Кайт пишет нечто подобное (для 8-ки):
execute immediate
'begin
   forall i in 1 .. :n
      insert into ' || p_name || '(x) values (:x(i));
end;'
using i, p_array

А какго там типа переменная p_array?
31 авг 06, 13:51    [3076806]     Ответить | Цитировать Сообщить модератору
 Re: For all и Динамический SQL  [new]
contr
Member

Откуда:
Сообщений: 1909
Степан, что же Вы так на immediate подсели-то?
create or replace procedure testP(i_sch varchar2) 
AUTHID CURRENT_USER
as
  l_schema varchar2(100);
begin
  select SCHEMANAME into l_schema from v$session where sid=(select sid from v$mystat where rownum=1);
  dbms_output.put_line('Current: '||l_schema||', target: '||i_sch);
  execute immediate 'alter session set current_schema='||i_sch;
    -- тут Ваша логика. Непосредственно. Без immediate.
  execute immediate 'alter session set current_schema='||l_schema;
exception
  when others then
    execute immediate 'alter session set current_schema='||l_schema;
    raise;
end;
/
31 авг 06, 13:58    [3076853]     Ответить | Цитировать Сообщить модератору
 Re: For all и Динамический SQL  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 54376
Stepan_mk
andreymx
Том Кайт пишет нечто подобное (для 8-ки):
execute immediate
'begin
   forall i in 1 .. :n
      insert into ' || p_name || '(x) values (:x(i));
end;'
using i, p_array

А какго там типа переменная p_array?
type vcArray as table of varchar2(5)
31 авг 06, 14:08    [3076943]     Ответить | Цитировать Сообщить модератору
 Re: For all и Динамический SQL  [new]
Stepan_mk
Member

Откуда: Україна
Сообщений: 1299
to contr
Прагму AUTHID CURRENT_USER мне нельзя использовать в том пакете
Прав на alter session юзер может и не иметь.
но решил немного переделать и запихнуть в тот динамический SQL и
обявление колекций и BULK collect и соответственно нет необходимости передавать параметри в него.
Но тут другая проблема появилась в исходно варианте било такое
execute immedate 'insert into other_table
select '||scr.one_number_filed||', sl.one_number_filed 
        from sh_'||schema||'.table sl
START WITH sl.one_number_filed = '||scr.one_number_filed||'
connect ....'
соответсвенно теперь пишу
     
FORALL i IN l_site_loc_tab.FIRST .. l_site_loc_tab.LAST   
       INSERT INTO ....
      SELECT l_site_loc_tab(i) slo, sl.one_number_filed  anc
                  from sh_'||schema||'.table sl
                 START WITH sl.one_number_filed = l_site_loc_tab(i)
               CONNECT BY ...
ошиПка что нету такого поля l_site_loc_tab
31 авг 06, 14:51    [3077210]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить