ORACLE. Кое-что по DSQL

База знаний

Модераторы: m0p3e, edward_K, Модераторы

m0p3e
Местный житель
Сообщения: 1386
Зарегистрирован: 29 мар 2005, 17:49
Откуда: Москва

ORACLE. Кое-что по DSQL

Сообщение m0p3e »

Возникла необходимость вытянуть в интерфейс данные из таблицы не описанной в словаре. Скажем это таблица mData из схемы EXDATA.
В результате применения метода проб и ошибок родилось следующее решение:
1. Т.к. атлантис запросы посланные через DSQL обязательно пытается преобразовать по своему образу и подобию, то с помощью asql.exe создаем хранимую процедуру используемую как шлюз для пересылки as is.

Код: Выделить всё

SQL PROCEDURE GATE(S : STRING);
BEGIN
END
2. Средствами оракла заполняем процедуру логикой приводя ее к виду:

Код: Выделить всё

create or replace
PROCEDURE "S$GATE"( S IN VARCHAR2 ) AS 
BEGIN  
 EXECUTE IMMEDIATE S;
 COMMIT;
END; 
3. Проверяем в протекте права доступа к новенькой.
4. Использование в интерфейсе:
Т.к. select в Oracle требует обязательного указания приемника, то данные будем переливать во временную таблицу и уже оттуда вычерпывать.

Код: Выделить всё

 Interface.....
 cmInit :  {
  ....................
  var stmt : LongInt;
  stmt := sqlAllocStmt;
  //Создаем ВТ 
  sqlDropTmpTable('MyData');
  sqlCreateTmpTable('Table MyData (f : string);', ctmNormal);
  //Заполняем ВТ
  sqlExecStmt(stmt,'GATE('#39+ 'INSERT INTO ' + UpCase ( UserName ) + '.MyData (FF) SELECT MDATA.FIELD1 FROM EXDATA.MDATA'#39+')');
  //Читаем ВТ
  sqlExecStmt(stmt,'select F from MyData');
  var mField : string;
  sqlBindCol(stmt, 1, mField);
  While ( sqlFetch(stmt) = tsOk ) Do
   {
     LogStrToFile('C:\temp.log',mField);
   }  
  ...............
Есть еще побочный эффект в виде чтения данных с линкованных серверов.
Линки и синонимы можно создавать и напрямую из интерфейса если дать юзеру в оракле привилегии на CREATE PUBLIC DATABASE LINK и CREATE PUBLIC SYNONYM. В этом случае нижеидущие запросы пропускаем через S$GATE.

Код: Выделить всё

--create public database link <имя линка> connect to <имя удаленного пользователя> identified by <пароль удаленного пользователя> using '<путь>'; --создаем линк
create public database link REMOTEBASE connect to vasya_pupkin identified by qwerty using 'oraserver:1521/ORABASE';
--create public synonym <локальное имя синонима> for <схема удаленная>.<таблица удаленная>@<имя линка созданного выше>; --создаем синоним
create public synonym SynMyData for EXDATA.MDATA@REMOTEBASE; --создаем синоним
Соответственно данные в ВТ получаем из синонима:

Код: Выделить всё

sqlExecStmt(stmt,'GATE('#39+ 'INSERT INTO ' + UpCase ( UserName ) + '.MyData (FF) SELECT SynMyData.FIELD1 FROM SynMyData'#39+')');
Вот как-то так...
galover
Местный житель
Сообщения: 794
Зарегистрирован: 16 ноя 2007, 13:52

Re: ORACLE. Кое-что по DSQL

Сообщение galover »

Спасибо, полезно. И дяде Федору на заметку
m0p3e
Местный житель
Сообщения: 1386
Зарегистрирован: 29 мар 2005, 17:49
Откуда: Москва

Если необходимо получать отчеты по журналу обычному пользова

Сообщение m0p3e »

Как известно обычный пользователь прямого доступа к x$journal не имеет даже на чтение. Но если очень надо, то можно...
Реализовано как частный случай предыдущей задачи.
Процедуру для выгрузки затачиваем под x$journal (создаем через asql и правим напрямую средствами оракла):

Код: Выделить всё

PROCEDURE GAL."S$GETJOURNAL" ( DBEG IN VARCHAR2, DEND IN VARCHAR2 ) AS
S VARCHAR2(1000);
BEGIN 
 S := 'INSERT INTO ' || USER || '.VIEWJOURNAL (FLASTDATE,FLASTTIME,FUSERCODE,FTABLECODE,FTABLENREC,FOPERATION) '
   || 'SELECT x$journal.lastdate,x$journal.lasttime,x$journal.usercode,x$journal.tablecode,x$journal.tablenrec,x$journal.operation '
   || 'FROM GAL.X$JOURNAL '
   || 'WHERE X$JOURNAL.LASTDATE >= ' || TO_CHAR(TO_NUMBER(SUBSTR(DBEG,1,2))+TO_NUMBER(SUBSTR(DBEG,4,2))*256+TO_NUMBER(SUBSTR(DBEG,7,4))*65536) || ' '
   || 'AND X$JOURNAL.LASTDATE <= ' || TO_CHAR(TO_NUMBER(SUBSTR(DEND,1,2))+TO_NUMBER(SUBSTR(DEND,4,2))*256+TO_NUMBER(SUBSTR(DEND,7,4))*65536);
 EXECUTE IMMEDIATE S;
 COMMIT;
END;
Перед использованием необходимо создать времянку ViewJournal:

Код: Выделить всё

 sqlDropTmpTable ( 'ViewJournal' ); //На всякий случай дропнем
 sqlCreateTmpTable ( 'Table ViewJournal ( LastDate:date, LastTime:time, UserCode:comp, TableCode:word, TableNrec:comp, Operation:byte);', ctmNormal );
Идеологически правильно создавать ее в процедуре S$GETJOURNAL. Но по какой-то причине Галактика не видит таблицы созданные внешними средствами. (Серьезно не копался, может позже...)

В результате получаем копию журнала за требуемый период. С ним и работаем дальше...

Код: Выделить всё

 var stmt : LongInt;
 stmt := sqlAllocStmt;

 sqlExecStmt ( stmt, 'GETJOURNAL('
                   + ''#39 + DateToStr ( dBeg, 'DD.MM.YYYY' ) + ''#39 
                   + ','
                   + ''#39 + DateToStr ( dEnd, 'DD.MM.YYYY' ) + ''#39
                   + ')' )
 sqlPrepare ( stmt, 'SELECT LASTDATE, LASTTIME, USERCODE, TABLECODE, TABLENREC FROM VIEWJOURNAL WHERE OPERATION = 2' ); //Только операции Insert
 sqlBindCol ( stmt, 1, Jorn.LastDate );
 sqlBindCol ( stmt, 2, Jorn.LastTime );
 sqlBindCol ( stmt, 3, Jorn.UserCode );
 sqlBindCol ( stmt, 4, Jorn.TableCode );
 sqlBindCol ( stmt, 5, Jorn.TableNrec );
 sqlExecute ( stmt );
 While ( sqlFetch ( stmt ) = tsOk ) Do
  {
   Insert Current Jorn;
  };
 sqlFreeStmt ( stmt );

 sqlDropTmpTable ( 'ViewJournal' );
galover
Местный житель
Сообщения: 794
Зарегистрирован: 16 ноя 2007, 13:52

Re: ORACLE. Кое-что по DSQL

Сообщение galover »

1.
Как известно обычный пользователь прямого доступа к x$journal не имеет даже на чтение.
Если доступа нет, то как тогда отрабатывает хранимка, она же под этим пользователем и выполняется?
2. А если 2 пользователя одновременно начнут читать журнал что будет? Нельзя ли заполнение и чтение в одну операцию соединить?
3. Там в хранимке парсинг даты идет. Вроде как для DSQl для передачи дат, есть макрос #date (раздел chm Константы прямого SQL), может можно его заюзать?
m0p3e
Местный житель
Сообщения: 1386
Зарегистрирован: 29 мар 2005, 17:49
Откуда: Москва

Re: ORACLE. Кое-что по DSQL

Сообщение m0p3e »

galover писал(а):1.
Как известно обычный пользователь прямого доступа к x$journal не имеет даже на чтение.
Если доступа нет, то как тогда отрабатывает хранимка, она же под этим пользователем и выполняется?
2. А если 2 пользователя одновременно начнут читать журнал что будет? Нельзя ли заполнение и чтение в одну операцию соединить?
3. Там в хранимке парсинг даты идет. Вроде как для DSQl для передачи дат, есть макрос #date (раздел chm Константы прямого SQL), может можно его заюзать?
1. Видимо права на оракле есть, но режутся принудительно драйвером.
2. Ничего страшного не будет. Это уже БД будет разруливать. Хотя подозреваю, что неправильно понял вопрос. :)
3. Вся идея в отлучении драйвера Галактического от обработки передаваемого запроса. Не дотянется он до #date :)
galover
Местный житель
Сообщения: 794
Зарегистрирован: 16 ноя 2007, 13:52

Re: ORACLE. Кое-что по DSQL

Сообщение galover »

2. Ничего страшного не будет. Это уже БД будет разруливать. Хотя подозреваю, что неправильно понял вопрос.
Не, это я неправильно понял. Думал что временная таблица будет доступна всем, почитал, оказывается только для текущего сеанса пользователя. Так можно ли в итоге заполнение таблицы и select соединить в одну операцию. Или Галка не умеет работать с курсорами?
вся идея в отлучении драйвера Галактического от обработки передаваемого запроса. Не дотянется он до #date
я имел в виду попробовать так:

Код: Выделить всё

sqlExecStmt(stmt, 'GETJOURNAL('#date(' + DateToStr(dBeg, 'DD,MM,YYYY') + ')' + ',' + '#date(' + DateToStr(dEnd, 'DD,MM,YYYY') + '))');
ну и тип, принимаемый GETJOURNAL, поменять на INTEGER. Тогда ничего парсить не нужно будет.
m0p3e
Местный житель
Сообщения: 1386
Зарегистрирован: 29 мар 2005, 17:49
Откуда: Москва

Re: ORACLE. Кое-что по DSQL

Сообщение m0p3e »

Так можно ли в итоге заполнение таблицы и select соединить в одну операцию. Или Галка не умеет работать с курсорами?
В случае оракла select должен быть куда-то. (На сиквеле можно просто select from написать). Т.к. к x$journal доступ режет драйвер, то данные нужно перелить в таблицу доступную пользователю.
я имел в виду попробовать так:
Код:
sqlExecStmt(stmt, 'GETJOURNAL('#date(' + DateToStr(dBeg, 'DD,MM,YYYY') + ')' + ',' + '#date(' + DateToStr(dEnd, 'DD,MM,YYYY') + '))');
Попробую как будет время. Хотя смысла большого нет, т.к. стандартные функции вроде TO_ORADATE также парсят несчастные даты :)
galover
Местный житель
Сообщения: 794
Зарегистрирован: 16 ноя 2007, 13:52

Re: ORACLE. Кое-что по DSQL

Сообщение galover »

В случае оракла select должен быть куда-то. (На сиквеле можно просто select from написать). Т.к. к x$journal доступ режет драйвер, то данные нужно перелить в таблицу доступную пользователю.
Я имел в виду, чтобы хранимка сразу возвращала курсор http://download.oracle.com/docs/cd/E118 ... _plsql.htm. Т.е. заполнили времянку и открыли курсор, но не в курсе поддерживает ли такое механизм DSQL
m0p3e
Местный житель
Сообщения: 1386
Зарегистрирован: 29 мар 2005, 17:49
Откуда: Москва

Re: ORACLE. Кое-что по DSQL

Сообщение m0p3e »

Поэкспериментирую как нибудь на досуге... :)
spark
Местный житель
Сообщения: 478
Зарегистрирован: 19 окт 2005, 13:38
Контактная информация:

Re: ORACLE. Кое-что по DSQL

Сообщение spark »

m0p3e писал(а): Вот как-то так...

отличное решение! А как быть в случае когда запрос больше 255 символов? Как их передать в этот Gate?
m0p3e
Местный житель
Сообщения: 1386
Зарегистрирован: 29 мар 2005, 17:49
Откуда: Москва

Re: ORACLE. Кое-что по DSQL

Сообщение m0p3e »

spark писал(а):
m0p3e писал(а):отличное решение! А как быть в случае когда запрос больше 255 символов? Как их передать в этот Gate?
Не так давно сам столкнулся с тем, что составная строка >255 символов не шлюзом не воспринимается корректно. Т.к. задачу нужно было решить срочно, то создал процедуру оракловую и через шлюз вызываю ее.
m0p3e
Местный житель
Сообщения: 1386
Зарегистрирован: 29 мар 2005, 17:49
Откуда: Москва

Re: ORACLE. Кое-что по DSQL

Сообщение m0p3e »

Углубился в проблему ограничения в 255 символов.
При сборке запроса через sqlAddStr получаем ошибку:

Код: Выделить всё

Ошибка(1,12): Синтаксическая ошибка в выражении
Т.е. до оракла и шлюза запрос даже не проходит. Рубит его ora90drv. Пока попытки передать длинную строку не увенчались успехом.

Есть конечно другой способ. Три процедуры. Типа:
1. S$GATE_INIT - создаст временную пользовательскую таблицу (Pos, Str);
2. S$GATE_ADDSTR - добавит в таблицу запись с порядковым номером Pos и куском запроса в Str;
3. S$GATE_EXECUTE - соберет запрос и выполнит.
Но это уже на крайний случай...
m0p3e
Местный житель
Сообщения: 1386
Зарегистрирован: 29 мар 2005, 17:49
Откуда: Москва

Re: ORACLE. Кое-что по DSQL

Сообщение m0p3e »

Разобрался. Транслятор проверяет парность ковычек.
Работающий запрос с использованием sqlAddStr:

Код: Выделить всё

cmInit :
 {
  var stmt, stmt_str : LongInt;
  stmt := sqlAllocStmt;

  sqlAddStr ( stmt_str, 'GATE(' );
  sqlAddStr ( stmt_str, ''#39 + 'BEGIN ' + ''#39 + '||' );
  sqlAddStr ( stmt_str, ''#39 + 'COMMIT;' + ''#39 + '||' );
  sqlAddStr ( stmt_str, ''#39 + 'END;' + ''#39 );
  sqlAddStr ( stmt_str, ')' );

  sqlPrepare ( stmt, stmt_str );
  sqlExecute ( stmt );

  sqlFreeStr ( stmt_str );
  sqlFreeStmt ( stmt );
 }; //cmInit
Запрос более 255 символов не проверял, но уверен что проблем не будет.
m0p3e
Местный житель
Сообщения: 1386
Зарегистрирован: 29 мар 2005, 17:49
Откуда: Москва

Re: ORACLE. Кое-что по DSQL

Сообщение m0p3e »

Перевел ряд пользователей на трехзвенку и всплыл нюанс - временные таблицы создаются в схеме пользователя под которым авторизуется сервер приложений.
Соответственно при обращении к таблице "не из Галактики" это нужно учитывать. Добрый волшебник :) подсказал функцию для определения где исполняется код:

Код: Выделить всё

function IfDefServerApp : boolean; //false -двухзвенный клиент, true - трехзвенный.
Кроме того, необходимо учитывать возможность одновременный работы с таблицей нескольких пользователей. Проще всего путем добавления имени пользователя к имени таблицы.
Если есть трехзвенные клиенты и сервер приложений работает под пользователем APPSERVER, то в вышеуказанном коде меняем:

Код: Выделить всё

Interface.....
.............
Function sqlTableName ( n : string ) : string;
{
 If IfDefServerApp
   Result := SubStr ( n + '_' + UpCase ( UserName ), 1, 30 );
  else
   Result := UpCase ( n );
}; //Function SqlTableName

Function sqlSchemaName : string;
{
 If IfDefServerApp
   Result := 'APPSERVER';
  else
   Result := UpCase ( UserName );
}; //Function SqlSchemaName
............

 cmInit :  {
  ....................
  var stmt : LongInt;
  stmt := sqlAllocStmt;
  //Создаем ВТ 
  sqlDropTmpTable('MyData');
  sqlCreateTmpTable('Table ' + sqlTableName('MyData') + '(f : string);', ctmNormal);
  //Заполняем ВТ
  sqlExecStmt(stmt,'GATE('#39+ 'INSERT INTO ' + sqlSchemaName + '.' + sqlTableName('MyData') + (FF) SELECT MDATA.FIELD1 FROM EXDATA.MDATA'#39+')');
  //Читаем ВТ
  sqlExecStmt(stmt,'select F from ' + sqlTableName('MyData'));
  var mField : string;
  sqlBindCol(stmt, 1, mField);
  While ( sqlFetch(stmt) = tsOk ) Do
   {
     LogStrToFile('C:\temp.log',mField);
   }  
  ...............
К сожалению, функцию для определения имени пользователя сервера приложений розыскать не удалось.
Zmiter
Сообщения: 1
Зарегистрирован: 22 мар 2018, 11:13

Re: ORACLE. Кое-что по DSQL

Сообщение Zmiter »

А если стоит задача просто написать DSQL запрос с dblink? Можно ли обойтись без этих ухищрений? Какой будет синтаксис?

Код: Выделить всё

SELECT something FROM table_name@dblink_name
не получается.
Где вообще смотреть эти синтаксические конструкции, если справка viper очень ограничена?
Ответить