Нужно написать процедуру которая бы возвращала таблицу. Проблема в том что SQL запрос получается очень большим и превышает максимальную длинну varchar, и просто так его не выполнишь. Что делать?

Извиняюсь, но я что-то не понял.
Какой SQL запрос получается очень большим?(чего к чему)

В поцедуре нужно выполнить select и вернуть результат пользователю. Так вот строка для этого select-а превышает (вернеее может превышать в зависимость от базы и переданных параметров) размерность varchar и помещается только в CLOB, как выполнить такой запрос?

Мне кажется, что надо тебе запрос упростить. Наклевать каких - нить VIEWS, подзапросы, хостпеременные и т.д. Вообще я впервые встречаюсь с запросом, который бы превышал такое офигитительное ограничение.

В данном случае надо один столбес таблицы развернуть в несколько, по условию содержащемуся в других столбцах.
На данный момент работает такая схема: процедура на сервере генерирует запрос и в CLOB, возвращает его клиенту, тот выполняет его.

А эта тема не поможет ?

http://forum.vingrad.ru/index.php?showtopic=4483

К сожалению нет , возвращать курсор из процедуры я уже умею. Не получается его получить, из-за его большого размера самого текста запроса.
Я не совсем четко сформулировал вначале задачу, из-за чего возникло недопонимание.

Если честно, я тоже никогда не сталкивался с запросами такого размера. Даже не знаю, что посоветывать. Ищите какой-то обходной путь, чтобы уменьшить размер запроса. Или обратитесь в корпорацию Oracle. Они внимательно относятся к своим клиентам.

Читал, что Oracle8i и Oracle9i позволяют создавать "табличные функции". Эти функции возвращают результирующий набор данных и из него можно делать выборки.

Но я их не пробовал, потому только даю направление.

Цитата(AntonSaburov @ 12.11.2004, 18:42)

Читал, что Oracle8i и Oracle9i позволяют создавать "табличные функции".

А как они называются на английском (что бы можно было поискать)?

Цитата(LSD @ 12.11.2004, 19:53)

А как они называются на английском (что бы можно было поискать)?

Прямо с доки перекидаю пример - разбирайся . Решаемая задача - в зависимости от типа животного создать детей - количество зависит от типа животного.

1. Определить вложенный табличный тип:

Код

CREATE TYPE pet_t IS OBJECT (    NAME VARCHAR2(60),    breed VARCHAR2(100),    dob DATE); ) CREATE TYPE pet_nt IS TABLE OF pet_t;

2. Создаем функцию, которая возвращает табличный тип.

Код

CREATE OR REPLACE FUNCTION pet_family(dad_in IN pet_t, mom_in IN pet_t)    RETURN pet_nt IS    l_count PLS_INTEGER;    retval pet_nt := pet_nt();    PROCEDURE extend_assign(pet_in IN pet_t) IS    BEGIN        retval.EXTEND;        retval(retval.LAST) := pet_in;    END; BEGIN    extend_assign(dad_in);    extend_assign(mom_in);    IF mom_in.breed = 'RABBIT' THEN l_count := 12;    ELSEIF mom_in.bread = 'DOG' THEN l_count := 4;    ELSEIF mom_in.bread = 'KANGAROO' THEN l_count := 1;    END IF;    FOR indx IN 1 .. l_count    LOOP        extend_assign(pet_t('BABY' || indx, mom_in.bread, SYSDATE));    END LOOP;    RETURN retval; END;

3. Делаем запрос. Если внимательно по коду посмотреть, то по идее все ясно. Функция получает на входе папу и маму кроликов и возвращает список детей.
Но одно НО - я не запускал этот пример реально

Код

SELECT * FROM TABLE (CAST (            pet_family (                pet_t ('Hoppy', 'RABBIT', SYSDATE),                pet_t ('Hippy', 'RABBIT' SYSDATE)            ) AS pet_nt        ));

Текст селекта разрезать на части приемлемой длины и передать их a1, a2...aN параметрам хранимой процедуры.

В хранимой
execute immediate (a1|| a2||...||aN)

igon
Не поможет, т.к. dynamic SQL не может превышать 32к. А именно это ограничение и надо преодолеть. Единственный выход это пакет DBMS_SQL, но я не могу понять как из него вытащить курсор.

Ну не хе-хе себе - >32К!!!!
Это ж >= 15 страниц plain текста, причем плотного, без пустот и красоты!!!
Я-то подумал, что ты вышел за лимит 4К.
Слушай, это творение надо лицезреть - я как про легенду слышал про 4-страничные. Если это не коммерческая тайна - выложи куда-нить: очень любопытно посмотреть

Может, и соображения появятся какие-нибудь.
Так, если структура запроса постоянна и варьируют только параметры, можно отдельные подзапросы оформить как функции, например.
Есть еще способ динамической генерации хранимой, ее компиляции и выполнения - вот это, чует мое сердце, тебе должно помочь. Правда, там используются прямые INSERT в SYS.SOURCE$ - не каждый DBA это одобрит.

Цитата

На данный момент работает такая схема: процедура на сервере генерирует запрос и в CLOB, возвращает его клиенту, тот выполняет его.

Кстати, здесь все правильно?
Что это за клиент, который выполняет то, что не по зубам серверу?

Это сообщение отредактировал(а) igon - 8.12.2004, 05:54

А почему на поместить курсор в пакет и не использовать его? Что то наподобии этого:

Код

create or replace package pck_open_cursor is  cursor c_open_cur is (select * from ftp_files);  procedure open_cur;  procedure close_cur; end; / create or replace package body pck_open_cursor is  procedure open_cur  is  begin    open c_open_cur;  end;  procedure close_cur  is  begin    close c_open_cur;  end; end; / set serveroutput on; declare  table_fields ftp_files%rowtype; begin  pck_open_cursor.open_cur;  --Тут можно организовать цикл извлечения данных  fetch pck_open_cursor.c_open_cur into table_fields;  dbms_output.put_line(table_fields.file_id);  pck_open_cursor.close_cur; end; /

Это сообщение отредактировал(а) <Spawn> - 8.12.2004, 21:25

<Spawn>, проблема не в ВЫХОДНЫХ, а во ВХОДНЫХ данных.
Твой запрос

Код

select * from ftp_files

занимает от силы 30 байтов. А представь, что он >32000 и к тому же не стационарный .

LSD, динамическая генерация хранимой тебе точно поможет, зуб даю (специально посмотрел), при условии:
1. Размер самой хранимой в твоей базе МОЖЕТ быть >32K
2. Ты готов поступиться неприкосновенностью словаря данных, который Oracle НЕ РЕКОМЕНДУЕТ изменять <b>напрямую</b> (но и НЕ ЗАПРЕЩАЕТ!!!). Тут в одном форуме (блин, забыл в каком, давно это было ) шла ну очень жаркая дискуссия о допустимости нарушать неприкосновенность. Так вот сторонников допустимости крыли во все тяжкие, в первую очередь сисадмины-DBA.
В любом случае, решение принимать тебе

Цитата(igon @ 8.12.2004, 04:04)

Слушай, это творение надо лицезреть - я как про легенду слышал про 4-страничные.

Запрос генерируется динамически в зависимости от данных содержащихся в таблице и некоторых параметров, а конкретно такую таблицу:

Код

DATE    | FK_REF | VALUE -----------+--------+------- 01.01.2004 |  COL1  | VAL1 05.01.2004 |  COL2  | VAL2

где FK_REF, ссылка на другую таблицу. Надо представить в виде:

Код

DATE    | COL1 | COL2 -----------+------+------ 01.01.2004 | VAL1 |     02.01.2004 |      |     03.01.2004 |      |     04.01.2004 |      |     05.01.2004 |      | VAL2

соответственно количество столбцов будет переменным и запрос при большом количестве данных во второй таблице будет превышать 32к.

Цитата(igon @ 9.12.2004, 00:08)

LSD, динамическая генерация хранимой тебе точно поможет, зуб даю (специально посмотрел), при условии: 1. Размер самой хранимой в твоей базе МОЖЕТ быть >32K 2. Ты готов поступиться неприкосновенностью словаря данных, который Oracle НЕ РЕКОМЕНДУЕТ изменять <b>напрямую</b> (но и НЕ ЗАПРЕЩАЕТ!!!). Тут в одном форуме (блин, забыл в каком, давно это было ) шла ну очень жаркая дискуссия о допустимости нарушать неприкосновенность. Так вот сторонников допустимости крыли во все тяжкие, в первую очередь сисадмины-DBA.

А вот с этого места поподробнее

1. То, что запрос генерируется динамически - это понятно. Мы, собственно, это и обсуждаем. Но, коль скоро запрос так или иначе сохраняется в CLOB и куда-то передается, то его достаточно просто перехватить и извлечь, записать в файл, а файл - выложить.
2.

Цитата

А вот с этого места поподробнее

Место очерчено очень уж широко -
Если требуются подробности
а) технологии динамической генерации хранимой - приводятся ниже
б) определения допустимого размера хранимой в ТВОЕЙ БД - создать обычную хранимую процедуру, забить ее правдоподобным мусором >32K и попытаться компилировать. Не получится - будем копать дальше.
Например,

Код

CREATE OR REPLACE PROCEDURE Dummy()      is      vDummy VARCHAR2(100);      Begin        vDummy := "Любой текст длиной не менее 100 символов";        vDummy := "Любой текст длиной не менее 100 символов";        vDummy := "Любой текст длиной не менее 100 символов";        vDummy := "Любой текст длиной не менее 100 символов"; ... -- Повторить строку 320 раз или больше        vDummy := "Любой текст длиной не менее 100 символов";        vDummy := "Любой текст длиной не менее 100 символов";      End;

в) о допустимости прямого доступа к системным таблицам словаря данных - здесь можно разливаться мыслью по древу сколько угодно, и "за", и "против". Ясно одно - Oracle не запрещает, но делать это нужно предельно корректно: при ошибке программиста может полететь база.

Итак, по пункту а)
Схема такая:
Пусть VNAME - имя нашей хранимой, создаем ее в некоторой пользовательской схеме

Код

BEGIN    DELETE FROM SYS.OBJ$ Q WHERE Q.NAME = VNAME AND OWNER# = USERENV('SCHEMAID'); -- Удаляем процедуру (на всякий случай)    execute immediate('CREATE OR REPLACE PROCEDURE ' || VNAME || ' IS' || CHR(10) || 'BEGIN' ||            CHR(10) || 'NULL;' || CHR(10) || 'END ' || VNAME || ';' || CHR(10)); -- Создаем пустую процедуру -- Глушим, обрабатываем, пропускам --   EXCEPTION --    WHEN OTHERS THEN --Null;   --   END;   SELECT OBJ#    INTO VOBJECTID -- получаем идентификатор объекта (нашей процедуры)    FROM SYS.OBJ$ Q    WHERE Q.NAME = VNAME AND Q.OWNER# = USERENV('SCHEMAID');   DELETE FROM SYS.SOURCE$ WHERE OBJ# = VOBJECTID; -- Удаляем записи о нашей процедуре (пустой!) в таблице исходных текстов   LOOP -- Исходный текст запроса разбиваем на строчки -- и построчно заносим в таблице исходных текстов (для сравнения можешь посмотреть, как в SYS.SOURCE$ хранятся другие твои хранимые)     INSERT INTO SYS.SOURCE$ (OBJ#, LINE, SOURCE) VALUES (VOBJECTID, VLINE, TMPSTR);     VLINE  := VLINE + 1;     TMPSTR := NULL;   END LOOP;   COMMIT;   BEGIN    execute immediate('ALTER PROCEDURE ' || VNAME || ' COMPILE'); -- компилируем нашу теперь полновесную процедуру, которую уже можно вызвать -- Глушим, обрабатываем, пропускам   EXCEPTION    WHEN OTHERS THEN     NULL;   END;   COMMIT;  END;

Естественно, User, запускающий процедуру генерации, должен иметь соответствующие права на системные таблицы.
В цикле разбиения на строки исходного запроса я опустил детали, которые будут зависеть от того, как будет он передаваться в процедуру генерации - из CLOB, входными строками приемлемой длины или из таблицы. Смысл, я полагаю, понятен.

После нового года обязательно попробую. Так я конечно делать не буду, но знать подобный финт ушами пригодится.

чего-то мне сдается что проблемма ползет от не правильной структуры БД для данной задачи ... а? не, я никого не хочу обидить! просто как-то все не так ...

Данная структура наиболее удобна для работы системы, на наш взгляд. Но она не очень наглядна для человека. Вот и извращаемся как можем, в принципе эту задачу мы уже решили на клиенте. Сейчас это уже скорее спортивный интерес: "А можно так сделать?".

А ответа на вопрос, как ни печально, так и не прозвучало 



Oracle8i Application Developer's Guide

Это не ответ на вопрос  

Я действительно не правильно понял и у тебя был статический, не динамический, запрос over 32к?
Или ты просто придрался к форме ответа, которая, действительно с формальной точки зрения, ответом не является?

Вопрос был "как сделать" а не "почему Оракл не может выполнить запрос over 32k".

Цитата, мол DBMS_SQL умеет over 32к за ответ не засчитывается?

Понял, я это пропустил.

А ответ сейчас, 8 лет спустя, все еще нужен? Как я понял из текста, автора спасла бы фича под названием "pivot table", появившаяся в 11-м оракле. Можно было бы отказаться от "динамики".

Хоть вопрос и древний но актуальный например для Oracle 10
Обсуждался на http://habrahabr.ru/post/100798/
Я подумал, как бы я решал не в оперативной памяти при больших объемах:

И далее Java тест


И последнее, что пришло в голову. Это ведь по сути матричный отчет. И как отчет его надо форматировать на клиенте,
а не заставлять заниматься сервер  Pivot делами.

Не спасла бы.
Применение и этой кляузы требует знание о структуре результата на этапе проектирования а не на этапе исполнения. Если мы на этапе проектирования не знаем структуру результата,  запрос все равно придется формировать динамически. Ну разве троху компактнее получается текст. В общем и целом pivot и unpivot это просто попсовая плюшка, не очень чтоб уж прям и мастхав.


И ведь ЧСХ ни одного коментария к статье, что тема заявленной над катом динамики pivot не раскрыта чуть менее чем полностью в виду ее отсутствия.

mbasil, из вашего примера я не понял, вы ГТТ создаете одну на все случаи жизни? Если так, от чего так мало столбцов? Пивотинг over 99 значений по 4 символа в каждом столбце, полагаете вам с лихвой хватит?

Это сообщение отредактировал(а) Zloxa - 19.1.2016, 02:09

Да, на все случаи жизни и для всех пользователей (GLOBAL TEMPORARY TABLE). Я просто отлаживал текст на маленьком примере, приведеном автором. В Oracle заявлено, что можно создавать таблицы с 1000 столбцами. Правда я задействовал временную таблицу, которая на самом деле "не настоящая" и сколько там можно создавать столбцов не знаю - выяснять лень. 

Некоторык комментарии:
1. Использовал я временную таблицу потому, что для выполнения операции в оперативной памяти при большом объеме может потребоваться слишком много памяти. Конечно, это требует времени на вставки, но DML операции в такой таблице не журналируются и в сегиенты отката не пишутся, так что вместо TRUNCATE можно смело использовать DELETE.
2. Поскольку это матричный отчет, трудно представить себе начальника с красным карандашом в руках и огнем в глазах анализируещего таблицу из 1000 столбцов - "замучается он". Так что по длительноиу размышлению в тртий раз намекаю, что отчеты форматировать надо на клиенте, передавая реляционные данные по сети компактно. Последние тенденции убеждают, что разделение труда между узлами системы не пустой звук, так как объемы сохраняемых данных растут и "напрягать" сервер задачами, которые ему не присущи фцнкционально, по меньшей мере, опрометчиво, даже если у вас супер серевер. Oracle же, например, в 9 версии предлагал форматировать HTML на сервере (ха). А с другой стороны Oacle Reports (хоть и кривоват), но позволяет уже много лет ваять из запросов многостраничные матричные отчеты "супер пупер" сложности.

Это сообщение отредактировал(а) mbasil - 2.2.2013, 08:21

Вы вот опубликовали некое решение. А от какой задачи это решение, наверное забыли упомянуть?
И уж простите за прямоту, но ваши портянки кода - откровенный г0внокод. Я честно попытался их почитать, но так и не понял от какой задачи зто решение.

Задача транспонирования решатеся одним запросом.
В приведенно вами же статье есть ссыль на решение от Кайта для версий <11
В процессе выполнения запросов, при необходимости, используется временный сегмент, совершенно не понятно какие расходы памяти вас так тревожат.


Это сообщение отредактировал(а) Zloxa - 2.2.2013, 12:41

to Zloxa

1. Код пишу, какой умею, во всяком случае он работает. Ясно, что я не пытаюсь предствить данное решение идеальным, как возможно не идеален и запрос, который не влезает в 32К. 
2. Вы читаете только то, что  можете критиковать хамским образом?  То что матричные отчеты надо форматировать на клиенте, а не гонять по сети ненужную метаинформацию, и то что такие инструменты как Oracle Reports это делают автоматически, вы пропустили, видмо, случайно.
3. Второй раз захожу на это ветку и второй же раз получаю от вас мягко говоря неприятные замечания. Видимо вам мои сообщения,  как завсегдатаю не нравятся вообще, а не по сути, а посему позвольте откланяться - вы ведь здесь хозяин

mbasil, простите меня за резкость столь ранившую Вас, но, поверьте, я не скуп на похвалу, и, если бы в вашем посте действительно было бы что-то достойное похвалы, за мной не заржавело бы. 

А резкая критика гoвнорешений, с моей точки зрения, на публичных ресурсах крайне необходима. И необходима она не столько для тех кто их публикует или тех, кто эту резкую критику преподносит, а для тех, кто молча почитывает и мотает на ус, возможно, сам еще будучи не в состоянии адекватно оценить суть решения.

Это сообщение отредактировал(а) Zloxa - 2.2.2013, 21:11

Не удержусь, чтобы не ответить, хотя и не собирался, поскольку гсп.Zloxa опять вместо рассмотрения сути закопался в формалистику. Отвечаю не ему (так как в похвалах и порицаниях не нуждаюсь), а тем, кто хочет все-же разобраться в предложенной задаче. Есть три способа решения поставленной проблемы:

1. Выполнить "pivoting" в оперативной памяти.
2. Выполнить "pivoting" во временной таблице, примерно так, как я писал в "гОвнОкоде".
3. Выполнить форматирование отчета на клиенте.

Вариант 3 предпочтительный, так как это правильное создание матричного отчета, когда клиенту по сети отправляется меньше информации, чем при "pivoting" в оперативной памяти и сервер не занят второстепенной для него задачей.

Вариант 2 предлагается использовать, когда клиент хочет не только запросить несколько раз результаты "pivoting'а", вырезая, например, отдельные части, но и внести изменения во временную таблицу (по желанию) прежде чем напечатать окончательный отчет.

Выполнять "pivoting" в оперативной памяти по варианту 1 стоит только лишь от лени.

Что касается "гОвнОкода" то связан он отчасти с необходимостью обработки строк, которую трудно сделать "красивой". Кроме того, я и не пытался наводить красоту, возможно именно от лени. Я уже 30 лет пишу коды и, видимо обленился вконец до "гОвнОкода". Полагаю, тем не менее, что вариант 2 вследствие перечисленных выше причин также имеет право на существование. И, ежели кто-либо предпочитающий красоту в коде предложит красивое решение, полагаю все будут только рады. Говорить же, что вариант 2 не нужен никогда потому, что "гОвнОкод" - по меньшей мере, высокомерно.

Это сообщение отредактировал(а) mbasil - 7.2.2013, 15:59

Объясните пожалуйста что вы называете пивотингом в оперативной памяти.

Традиционное решение задачи - динамическое формирование запроса, о котором идет речь в этой теме, и по той ссылке, которая приведена вами же, не есть суть "выполнение пивотинга в оперативной памяти". Выполнение этого запроса сервером не является "выполнением пивотинга в оперативной памяти". Наличие третьего пункта в вашем списке, как бы предполагает что речь идет не об оперативной памяти клиента. Применение своего оригинального подхода в пику ранее озвученных - традиционных вы обосновываете экономией памяти. Какой памяти? Альтернативой какому подходу вы предлагаете свое решение? В этой теме не было озвучено подхода, недостаток которого вы устранили своим решением.

Это сообщение отредактировал(а) Zloxa - 7.2.2013, 20:45

Думаю надо пояснить некоторые моменты:
1. Тема давно имеет только академический интерес. Тот прототип который тогда разрабатывался уже давно почил в бозе.
2. Табличное представление формируется на клиенте, а не на сервере потому что на тот момент вся логика была на сервере.
3. 200-300 столбцов человеку вполне можно обозреть. Смысл как раз и был в том, чтобы наглядно видеть где что.

Если имеет хотя бы академический интерес  , с 11й версии dbms_sql умеет возвращать и refcursor.
Раньше, походу, действительно не умел.
Удивился, что мне было позволено вернуть 1.5к, 5к столбцов, оказывается 1к столбцов это лимит для таблиц, сколько для вьюх-запросов надо буйт пошукать.
Пригорюнился над текстом ошибки, когда попросил 50к столбцов



Это сообщение отредактировал(а) Zloxa - 8.2.2013, 14:58

Ура! Вот теперь

ответа действительно прозвучала 

Кстати, поможет, но лишь отчасти. Конкатенировать три строки уже не получится, но две - да.
Давешняя бага/особенность и по сей день смущает умы постигающие основы эксперементальным способом, а не аналитическим. В продуктивное решение нести такое конечно не след, но де факто получается ограничение длины натив динамик запроса в 64к, а не в 32к

а ручки то - вот они ©   

Это сообщение отредактировал(а) Zloxa - 8.2.2013, 17:36