Версия для печати темы Нажмите сюда для просмотра этой темы в оригинальном формате

Форум программистов > Oracle > Хранимые процедуры и запрос

Автор: LSD 24.3.2004, 22:47

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

Автор: stron 24.3.2004, 23:27

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

Автор: LSD 25.3.2004, 21:43

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

Автор: maxi 10.11.2004, 09:26

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

Автор: LSD 10.11.2004, 20:25

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

Автор: AntonSaburov 11.11.2004, 19:07

А эта тема не поможет ? http://forum.vingrad.ru/index.php?showtopic=4483

Автор: LSD 11.11.2004, 20:42

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

Автор: Medved 11.11.2004, 22:56

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

Автор: AntonSaburov 12.11.2004, 18:42

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

Автор: LSD 12.11.2004, 18:53

Цитата(AntonSaburov @ 12.11.2004, 18:42) Читал, что Oracle8i и Oracle9i позволяют создавать "табличные функции". А как они называются на английском (что бы можно было поискать)?

Автор: AntonSaburov 15.11.2004, 15:41

Цитата(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        ));

Автор: igon 6.12.2004, 02:12

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

Автор: LSD 8.12.2004, 00:17

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

Автор: igon 8.12.2004, 04:04

Ну не хе-хе себе - >32К!!!! Это ж >= 15 страниц plain текста, причем плотного, без пустот и красоты!!! Я-то подумал, что ты вышел за лимит 4К. Слушай, это творение надо лицезреть - я как про легенду слышал про 4-страничные. Если это не коммерческая тайна - выложи куда-нить: очень любопытно посмотреть Может, и соображения появятся какие-нибудь. Так, если структура запроса постоянна и варьируют только параметры, можно отдельные подзапросы оформить как функции, например. Есть еще способ динамической генерации хранимой, ее компиляции и выполнения - вот это, чует мое сердце, тебе должно помочь. Правда, там используются прямые INSERT в SYS.SOURCE$ - не каждый DBA это одобрит. Цитата На данный момент работает такая схема: процедура на сервере генерирует запрос и в CLOB, возвращает его клиенту, тот выполняет его. Кстати, здесь все правильно? Что это за клиент, который выполняет то, что не по зубам серверу?

Автор: <Spawn> 8.12.2004, 21:24

А почему на поместить курсор в пакет и не использовать его? Что то наподобии этого: Код 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; /

Автор: igon 9.12.2004, 00:08

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

Автор: LSD 9.12.2004, 22:05

Цитата(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. А вот с этого места поподробнее

Автор: igon 10.12.2004, 00:49

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, входными строками приемлемой длины или из таблицы. Смысл, я полагаю, понятен.

Автор: LSD 16.12.2004, 23:44

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

Автор: Sleepy_PIP 20.12.2004, 20:22

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

Автор: LSD 20.12.2004, 20:41

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

Автор: Zloxa 31.12.2012, 12:34

А ответа на вопрос, как ни печально, так и не прозвучало  Цитата Support for SQL Statements Larger than 32KB The DBMS_SQL package supports SQL statements larger than 32KB; native dynamic SQL does not. http://docs.oracle.com/cd/A87860_01/doc/appdev.817/a76939/adg09dyn.htm#26790

Автор: LSD 3.1.2013, 13:05

Цитата(Zloxa @  31.12.2012,  13:34 ) А ответа на вопрос, как ни печально, так и не прозвучало Это не ответ на вопрос

Автор: Zloxa 3.1.2013, 20:41

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

Автор: LSD 4.1.2013, 10:03

Цитата(Zloxa @  3.1.2013,  21:41 ) Или ты просто придрался к форме ответа, которая, действительно с формальной точки зрения, ответом не является? Вопрос был "как сделать" а не "почему Оракл не может выполнить запрос over 32k".

Автор: Zloxa 4.1.2013, 19:40

Цитата(LSD @  4.1.2013,  11:03 ) Вопрос был "как сделать"  Цитата, мол DBMS_SQL умеет over 32к за ответ не засчитывается?

Автор: LSD 7.1.2013, 14:00

Цитата(Zloxa @  4.1.2013,  20:40 ) Цитата, мол DBMS_SQL умеет over 32к за ответ не засчитывается? Цитата(LSD @  8.12.2004,  01:17 ) Не поможет, т.к. dynamic SQL не может превышать 32к. А именно это ограничение и надо преодолеть. Единственный выход это пакет DBMS_SQL, но я не могу понять как из него вытащить курсор.

Автор: Zloxa 7.1.2013, 16:50

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

Автор: RockClimber 21.1.2013, 19:46

Цитата(Zloxa @ 31.12.2012,  12:34) А ответа на вопрос, как ни печально, так и не прозвучало  Цитата Support for SQL Statements Larger than 32KB The DBMS_SQL package supports SQL statements larger than 32KB; native dynamic SQL does not. http://docs.oracle.com/cd/A87860_01/doc/appdev.817/a76939/adg09dyn.htm#26790 А ответ сейчас, 8 лет спустя, все еще нужен? Как я понял из текста, автора спасла бы фича под названием "pivot table", появившаяся в 11-м оракле. Можно было бы отказаться от "динамики".

Автор: mbasil 1.2.2013, 12:59

Хоть вопрос и древний но актуальный например для Oracle 10 Обсуждался на http://habrahabr.ru/post/100798/ Я подумал, как бы я решал не в оперативной памяти при больших объемах: Код DECLARE   s1 VARCHAR2(10000) :=    'CREATE GLOBAL TEMPORARY TABLE T2 ( DATE1 DATE'; BEGIN   FOR i IN 1..99 LOOP     s1 := s1 || ',a'||i||' VARCHAR2(4)';   END LOOP;   s1 := s1||') ON COMMIT PRESERVE ROWS';   EXECUTE IMMEDIATE s1;   END; / CREATE FUNCTION fun1( p_d1 IN DATE, p_d2 IN DATE ) RETURN VARCHAR2 IS    da DATE;   dt DATE := p_d1;   delta NUMBER(3,0);    flag1 BOOLEAN := TRUE;   CURSOR c1 IS     SELECT DISTINCT FK_REF     FROM   T0     ORDER BY 1;   CURSOR c2 IS     SELECT *     FROM   T0     ORDER BY DATE1;   rec c2%ROWTYPE;   st1 VARCHAR2(25) := 'INSERT INTO T2 ( DATE1';   st2 VARCHAR2(10) := 'VALUES( ';   st3 VARCHAR2(100);   st4 VARCHAR2(100);   st5 VARCHAR2(100);   st  VARCHAR2(100);   TYPE type_tb_1 IS TABLE OF VARCHAR2(15)        INDEX BY VARCHAR2(15);   tb_1     type_tb_1;   n           BINARY_INTEGER := 0;   i           BINARY_INTEGER := 1;     FUNCTION f1( d DATE ) RETURN VARCHAR2 IS   BEGIN      RETURN 'to_date('''||to_char(d,'DD.MM.YYYY')             ||''',''DD.MM.YYYY'')';   END; BEGIN -----------------------   st3 := 'SELECT DATE1';   st5 := '';   flag1 := TRUE;   FOR rec1 IN c1 LOOP     tb_1( rec1.FK_REF ) := 'A'||i;     st3 := st3||','||'A'||i||' AS '||rec1.FK_REF;     IF flag1  THEN           flag1 := FALSE;     ELSE          st5 := st5||',';     END IF;     st5 := st5||rec1.FK_REF;     i := i + 1;   END LOOP;   st3 := st3||' FROM T2 ORDER BY DATE1'; -------------------------   flag1 := TRUE;   delta := p_d2-p_d1;   da := NULL;   OPEN c2;   FOR i IN 0..delta LOOP     dt := dt + 1;     IF flag1 = TRUE THEN        FETCH c2 INTO rec;        da := rec.DATE1;        flag1 := FALSE;     END IF;    IF da = dt THEN      st4 := tb_1( rec.FK_REF );      st := st1||','||st4||') VALUES('||f1(da)||','''||rec.VALUE||''')';      flag1 := TRUE;    ELSE        st := st1||') VALUES('|| f1(dt) ||')';    END IF;    EXECUTE IMMEDIATE st;   END LOOP;   CLOSE c2;    RETURN st5; END; И далее Java тест Код import java.sql.*; import java.io.*; import java.util.*; import java.text.SimpleDateFormat; public class ExApp {   private static Properties connInfo;   private static final String driverName = "oracle.jdbc.driver.OracleDriver";   private String dbUrl = "jdbc:oracle:thin:@localhost:1521:ORCL";   public void callEmp() {     java.sql.Date date1 = null;     java.sql.Date date2 = null;     SimpleDateFormat df = new SimpleDateFormat("dd.MM.yyyy");     try {          java.util.Date d1 = df.parse("29.12.2003");          date1 = new java.sql.Date( d1.getTime() );          d1 = df.parse("08.01.2004");          date2 = new java.sql.Date( d1.getTime() );     } catch( Exception e ) {          System.out.println( e );          System.exit( 0 );      }     System.out.println(date1+"  -  "+date2);     Connection conn = null;     try {          conn = DriverManager.getConnection( dbUrl, connInfo );     } catch ( SQLException eSQL ) {          ptintSQLExceptions( eSQL );     } catch ( Exception e ) {          System.out.println("Error: " + e);     }       if (conn == null) {          System.out.println("NULL Connection in callEmp() !" );          System.exit( 0 );      }     CallableStatement cst = null;     Statement stmt = null;     ResultSet rs = null;      String cTxt = "{?=call fun1(?,?)}";     try {       cst = conn.prepareCall( cTxt );       cst.registerOutParameter( 1, Types.VARCHAR );       cst.setDate( 2, date1 );       cst.setDate( 3, date2 );       cst.execute();       String ret = cst.getString(1);       String[] ar = ret.split(",");       System.out.println("========================================");         String query;         int i = 1;         StringBuilder sb = new StringBuilder();         sb.append("SELECT DATE1");         for( String s : ar ) {            sb.append(",A").append(i).append(" AS ").append( s );            i++;         }         sb.append(" FROM T2 ORDER BY 1");       String s = sb.toString();       System.out.println( s );       System.out.println("========================================");       stmt = conn.createStatement();       boolean result = stmt.execute( s );       if( result ) {         rs = stmt.getResultSet();         ResultSetMetaData rsmd = rs.getMetaData();         int numberOfColumns = rsmd.getColumnCount();         while (rs.next()) {           sb.delete( 0, sb.length() );           date1 = rs.getDate(1);           sb.append( df.format(date1) ).append("   ");           for( int j=2; j <= numberOfColumns; j++ ) {              sb.append( rs.getString( j )).append("   ");           }           System.out.println( sb.toString() );         }       }       result = stmt.execute( "TRUNCATE TABLE T2" );    } catch ( SQLException eSQL ) {              ptintSQLExceptions( eSQL );    } catch ( Exception e ) {              System.out.println("Error: " + e);    } finally {       try {             if (cst != null) cst.close();            if (conn != null) conn.close();       } catch (Exception e) {};    }   }   //-------------------------------------------------   public void ptintSQLExceptions( SQLException p_ex ) {    while( p_ex != null ) {      System.out.println( "Error code :" + p_ex.getErrorCode());      System.out.println( "Msg : " + p_ex.getMessage());       System.out.println( "SQL State : " + p_ex.getSQLState());         p_ex = p_ex.getNextException();    }   }   //======================================   public static void main (String args []) {     connInfo = new Properties();     connInfo.put( "user", "ora4" );     connInfo.put( "password", "oracle" );     try {           Class.forName( driverName ).newInstance();     } catch (Exception e) {          System.err.println (      "ERROR: Driver <" + driverName + "> not found");         System.exit(0);     }     ExApp a = new ExApp();     a.callEmp();   } } И последнее, что пришло в голову. Это ведь по сути матричный отчет. И как отчет его надо форматировать на клиенте, а не заставлять заниматься сервер  Pivot делами.

Автор: Zloxa 1.2.2013, 13:46

Цитата(RockClimber @  21.1.2013,  20:46 ) автора спасла бы фича под названием "pivot table", появившаяся в 11-м оракле. Можно было бы отказаться от "динамики". Не спасла бы. Применение и этой кляузы требует знание о структуре результата на этапе проектирования а не на этапе исполнения. Если мы на этапе проектирования не знаем структуру результата,  запрос все равно придется формировать динамически. Ну разве троху компактнее получается текст. В общем и целом pivot и unpivot это просто попсовая плюшка, не очень чтоб уж прям и мастхав. Цитата(mbasil @  1.2.2013,  13:59 ) Обсуждался на http://habrahabr.ru/post/100798/ И ведь ЧСХ ни одного коментария к статье, что тема заявленной над катом динамики pivot не раскрыта чуть менее чем полностью в виду ее отсутствия. mbasil, из вашего примера я не понял, вы ГТТ создаете одну на все случаи жизни? Если так, от чего так мало столбцов? Пивотинг over 99 значений по 4 символа в каждом столбце, полагаете вам с лихвой хватит?

Автор: mbasil 2.2.2013, 08:15

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

Автор: Zloxa 2.2.2013, 12:28

Цитата(mbasil @  2.2.2013,  09:15 ) Использовал я временную таблицу потому, что для выполнения операции в оперативной памяти при большом объеме может потребоваться слишком много памяти. Вы вот опубликовали некое решение. А от какой задачи это решение, наверное забыли упомянуть? И уж простите за прямоту, но ваши портянки кода - откровенный г0внокод. Я честно попытался их почитать, но так и не понял от какой задачи зто решение. Задача транспонирования решатеся одним запросом. В приведенно вами же статье есть ссыль на решение от Кайта для версий <11 В процессе выполнения запросов, при необходимости, используется временный сегмент, совершенно не понятно какие расходы памяти вас так тревожат.

Автор: mbasil 2.2.2013, 14:42

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

Автор: Zloxa 2.2.2013, 20:20

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

Автор: mbasil 7.2.2013, 15:55

Не удержусь, чтобы не ответить, хотя и не собирался, поскольку гсп.Zloxa опять вместо рассмотрения сути закопался в формалистику. Отвечаю не ему (так как в похвалах и порицаниях не нуждаюсь), а тем, кто хочет все-же разобраться в предложенной задаче. Есть три способа решения поставленной проблемы: 1. Выполнить "pivoting" в оперативной памяти. 2. Выполнить "pivoting" во временной таблице, примерно так, как я писал в "гОвнОкоде". 3. Выполнить форматирование отчета на клиенте. Вариант 3 предпочтительный, так как это правильное создание матричного отчета, когда клиенту по сети отправляется меньше информации, чем при "pivoting" в оперативной памяти и сервер не занят второстепенной для него задачей. Вариант 2 предлагается использовать, когда клиент хочет не только запросить несколько раз результаты "pivoting'а", вырезая, например, отдельные части, но и внести изменения во временную таблицу (по желанию) прежде чем напечатать окончательный отчет. Выполнять "pivoting" в оперативной памяти по варианту 1 стоит только лишь от лени. Что касается "гОвнОкода" то связан он отчасти с необходимостью обработки строк, которую трудно сделать "красивой". Кроме того, я и не пытался наводить красоту, возможно именно от лени. Я уже 30 лет пишу коды и, видимо обленился вконец до "гОвнОкода". Полагаю, тем не менее, что вариант 2 вследствие перечисленных выше причин также имеет право на существование. И, ежели кто-либо предпочитающий красоту в коде предложит красивое решение, полагаю все будут только рады. Говорить же, что вариант 2 не нужен никогда потому, что "гОвнОкод" - по меньшей мере, высокомерно.

Автор: Zloxa 7.2.2013, 20:39

Цитата(mbasil @  7.2.2013,  16:55 ) 1. Выполнить "pivoting" в оперативной памяти. Объясните пожалуйста что вы называете пивотингом в оперативной памяти. Традиционное решение задачи - динамическое формирование запроса, о котором идет речь в этой теме, и по той ссылке, которая приведена вами же, не есть суть "выполнение пивотинга в оперативной памяти". Выполнение этого запроса сервером не является "выполнением пивотинга в оперативной памяти". Наличие третьего пункта в вашем списке, как бы предполагает что речь идет не об оперативной памяти клиента. Применение своего оригинального подхода в пику ранее озвученных - традиционных вы обосновываете экономией памяти. Какой памяти? Альтернативой какому подходу вы предлагаете свое решение? В этой теме не было озвучено подхода, недостаток которого вы устранили своим решением.

Автор: LSD 8.2.2013, 13:23

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

Автор: Zloxa 8.2.2013, 14:23

Цитата(LSD @  8.2.2013,  14:23 ) Тема давно имеет только академический интерес Если имеет хотя бы академический интерес  , с 11й версии dbms_sql умеет возвращать и refcursor. Раньше, походу, действительно не умел. Удивился, что мне было позволено вернуть 1.5к, 5к столбцов, оказывается 1к столбцов это лимит для таблиц, сколько для вьюх-запросов надо буйт пошукать. Пригорюнился над текстом ошибки, когда попросил 50к столбцов Код SQL> var valcnt number SQL> var result refcursor SQL> exec :valcnt := 3; PL/SQL procedure successfully completed. SQL> declare    2    v_sql dbms_sql.varchar2a;   3    curid number;   4    ret integer;   5  begin   6    v_sql(1) := 'select dummy val1';   7    for i in 2..:valcnt loop   8      v_sql(i) := ',level val'||i;   9    end loop;  10    v_sql(:valcnt+1) := 'from dual connect by level <=3';  11    curid := dbms_sql.open_cursor;  12    dbms_sql.parse(c => curid  13                  ,statement => v_sql  14                  ,lb => 1  15                  ,ub => :valcnt+1  16                  ,lfflg =>true   17                  ,language_flag => dbms_sql.native);  18    ret := dbms_sql.execute(c => curid);  19    :result := dbms_sql.to_refcursor(curid);  20  end;  21  / PL/SQL procedure successfully completed. SQL> print result; V       VAL2       VAL3 - ---------- ---------- X          1          1 X          2          2 X          3          3 SQL> exec :valcnt := 500; PL/SQL procedure successfully completed. SQL> / PL/SQL procedure successfully completed. SQL> exec :valcnt := 1500; PL/SQL procedure successfully completed. SQL> / PL/SQL procedure successfully completed. SQL> exec :valcnt := 5000; PL/SQL procedure successfully completed. SQL> / PL/SQL procedure successfully completed. SQL> exec :valcnt := 50000; PL/SQL procedure successfully completed. SQL> / declare * ERROR at line 1: ORA-00600: internal error code, arguments: [mal0-size-too-large], [kksoff : opitca], [], [], [], [], [], [], [], [], [], [] ORA-06512: at "SYS.DBMS_SQL", line 1321 ORA-06512: at line 12 SQL> select * from v$version where rownum = 1; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production SQL>

Автор: LSD 8.2.2013, 15:32

Цитата(Zloxa @  8.2.2013,  15:23 ) с 11й версии dbms_sql умеет возвращать и refcursor Ура! Вот теперь Цитата(Zloxa @  31.12.2012,  13:34 ) А ответа на вопрос, как ни печально, так и не прозвучало ответа действительно прозвучала

Автор: Zloxa 8.2.2013, 17:13

Цитата(LSD @  8.12.2004,  01:17 ) igon Не поможет Кстати, поможет, но лишь отчасти. Конкатенировать три строки уже не получится, но две - да. Давешняя бага/особенность и по сей день смущает умы постигающие основы эксперементальным способом, а не аналитическим. В продуктивное решение нести такое конечно не след, но де факто получается ограничение длины натив динамик запроса в 64к, а не в 32к Код SQL> set serveroutput on SQL> var result refcursor SQL> declare    2    s1 varchar2(32767);   3    s2 varchar2(32767);   4  begin   5    s1 := rpad('select *',32767,' ');-- добавляем пробелы справа   6    s2 := lpad('from dual',32767,' '); -- добавляем пробелы слева   7    dbms_output.put_line('total length ='||(length(s1)+length(s2)));   8    open :result for s1||s2;   9  end;  10  / total length =65534 PL/SQL procedure successfully completed. SQL> print result; D - X а ручки то - вот они ©

Powered by Invision Power Board (http://www.invisionboard.com)
© Invision Power Services (http://www.invisionpower.com)