VPF::Хранимые процедуры и запрос - Форум программистов

igon

Дата 9.12.2004, 00:08 (ссылка)

(нет голосов)

Загрузка ...

Опытный

Профиль
Группа: Участник
Сообщений: 327
Регистрация: 30.10.2004

Репутация: 7
Всего: 21

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

Код

select * from ftp_files

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

.

LSD, динамическая генерация хранимой тебе точно поможет, зуб даю smile

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

) шла ну очень жаркая дискуссия о допустимости нарушать неприкосновенность. Так вот сторонников допустимости крыли во все тяжкие, в первую очередь сисадмины-DBA.
В любом случае, решение принимать тебе smile

--------------------

Хотите поговорить об этом?

LSD

Дата 9.12.2004, 22:05 (ссылка)

(голосов:1)

Загрузка ...

Leprechaun Software Developer

Профиль
Группа: Модератор
Сообщений: 15718
Регистрация: 24.3.2004
Где: Dublin

Репутация: 18
Всего: 538

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

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

--------------------

Disclaimer: this post contains explicit depictions of personal opinion. So, if it sounds sarcastic, don't take it seriously. If it sounds dangerous, do not try this at home or at all. And if it offends you, just don't read it.

igon

Дата 10.12.2004, 00:49 (ссылка)

(нет голосов)

Загрузка ...

Опытный

Профиль
Группа: Участник
Сообщений: 327
Регистрация: 30.10.2004

Репутация: 7
Всего: 21

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

Цитата

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

Место очерчено очень уж широко - smile

Если требуются подробности
а) технологии динамической генерации хранимой - приводятся ниже
б) определения допустимого размера хранимой в ТВОЕЙ БД - создать обычную хранимую процедуру, забить ее правдоподобным мусором >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 (ссылка)

(голосов:1)

Загрузка ...

Leprechaun Software Developer

Профиль
Группа: Модератор
Сообщений: 15718
Регистрация: 24.3.2004
Где: Dublin

Репутация: 18
Всего: 538

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

--------------------

Sleepy_PIP

Дата 20.12.2004, 20:22 (ссылка)

(нет голосов)

Загрузка ...

Опытный

Профиль
Группа: Участник
Сообщений: 512
Регистрация: 30.6.2004
Где: Moscow

Репутация: нет
Всего: 12

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

--------------------

--
Sleepy_PIP. Pavel Pryazhentsev (ex. 2:5020/141) "... Лучше быть нужным, чем
свободным ..."

LSD

Дата 20.12.2004, 20:41 (ссылка)

(голосов:1)

Загрузка ...

Leprechaun Software Developer

Профиль
Группа: Модератор
Сообщений: 15718
Регистрация: 24.3.2004
Где: Dublin

Репутация: 18
Всего: 538

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

--------------------

Zloxa

Дата 31.12.2012, 12:34 (ссылка)

(нет голосов)

Загрузка ...

Чо?

Профиль
Группа: Завсегдатай
Сообщений: 3473
Регистрация: 12.9.2008

Репутация: 37
Всего: 161

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

Цитата

Support for SQL Statements Larger than 32KB

The DBMS_SQL package supports SQL statements larger than 32KB; native dynamic SQL does not.

Oracle8i Application Developer's Guide

--------------------

Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile

LSD

Дата 3.1.2013, 13:05 (ссылка)

(голосов:1)

Загрузка ...

Leprechaun Software Developer

Профиль
Группа: Модератор
Сообщений: 15718
Регистрация: 24.3.2004
Где: Dublin

Репутация: 18
Всего: 538

Цитата(Zloxa @ 31.12.2012, 13:34

)

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

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

--------------------

Zloxa

Дата 3.1.2013, 20:41 (ссылка)

(нет голосов)

Загрузка ...

Чо?

Профиль
Группа: Завсегдатай
Сообщений: 3473
Регистрация: 12.9.2008

Репутация: 37
Всего: 161

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

--------------------

Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile

LSD

Дата 4.1.2013, 10:03 (ссылка)

(голосов:1)

Загрузка ...

Leprechaun Software Developer

Профиль
Группа: Модератор
Сообщений: 15718
Регистрация: 24.3.2004
Где: Dublin

Репутация: 18
Всего: 538

Цитата(Zloxa @ 3.1.2013, 21:41

)

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

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

--------------------

Zloxa

Дата 4.1.2013, 19:40 (ссылка)

(нет голосов)

Загрузка ...

Чо?

Профиль
Группа: Завсегдатай
Сообщений: 3473
Регистрация: 12.9.2008

Репутация: 37
Всего: 161

Цитата(LSD @ 4.1.2013, 11:03

)

Вопрос был "как сделать"

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

--------------------

Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile

LSD

Дата 7.1.2013, 14:00 (ссылка)

(голосов:1)

Загрузка ...

Leprechaun Software Developer

Профиль
Группа: Модератор
Сообщений: 15718
Регистрация: 24.3.2004
Где: Dublin

Репутация: 18
Всего: 538

Цитата(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 (ссылка)

(нет голосов)

Загрузка ...

Чо?

Профиль
Группа: Завсегдатай
Сообщений: 3473
Регистрация: 12.9.2008

Репутация: 37
Всего: 161

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

--------------------

Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile

RockClimber

Дата 21.1.2013, 19:46 (ссылка)

(нет голосов)

Загрузка ...

Опытный

Профиль
Группа: Участник
Сообщений: 848
Регистрация: 5.5.2006
Где: планета 013 в тен туре

Репутация: нет
Всего: 15

Цитата(Zloxa @ 31.12.2012, 12:34)

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

Цитата

Support for SQL Statements Larger than 32KB

The DBMS_SQL package supports SQL statements larger than 32KB; native dynamic SQL does not.

Oracle8i Application Developer's Guide

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

--------------------

Хорошо кинутый дятел далеко летит, крепко встревает, долго торчит.

mbasil

Дата 1.2.2013, 12:59 (ссылка)

(нет голосов)

Загрузка ...

Опытный

Профиль
Группа: Участник
Сообщений: 399
Регистрация: 4.5.2007
Где: Москва

Репутация: 1
Всего: 13

Хоть вопрос и древний но актуальный например для 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 делами.

1 Пользователей читают эту тему (1 Гостей и 0 Скрытых Пользователей)
0 Пользователей:
« Предыдущая тема \| Oracle \| Следующая тема »