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

База знаний

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

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

Сообщение m0p3e » Пн дек 20, 2010 20:03

Возникла необходимость вытянуть в интерфейс данные из таблицы не описанной в словаре. Скажем это таблица 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+')');


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

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

Сообщение galover » Вт дек 21, 2010 13:34

Спасибо, полезно. И дяде Федору на заметку
galover
Местный житель
 
Сообщения: 794
Зарегистрирован: Пт ноя 16, 2007 14:52
Reputation point: 27

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

Сообщение m0p3e » Сб мар 05, 2011 12:56

Как известно обычный пользователь прямого доступа к 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' );
m0p3e
Местный житель
 
Сообщения: 1384
Зарегистрирован: Вт мар 29, 2005 17:49
Откуда: Москва
Reputation point: 97

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

Сообщение galover » Сб мар 05, 2011 14:28

1.
Как известно обычный пользователь прямого доступа к x$journal не имеет даже на чтение.

Если доступа нет, то как тогда отрабатывает хранимка, она же под этим пользователем и выполняется?
2. А если 2 пользователя одновременно начнут читать журнал что будет? Нельзя ли заполнение и чтение в одну операцию соединить?
3. Там в хранимке парсинг даты идет. Вроде как для DSQl для передачи дат, есть макрос #date (раздел chm Константы прямого SQL), может можно его заюзать?
galover
Местный житель
 
Сообщения: 794
Зарегистрирован: Пт ноя 16, 2007 14:52
Reputation point: 27

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

Сообщение m0p3e » Сб мар 05, 2011 14:56

galover писал(а):1.
Как известно обычный пользователь прямого доступа к x$journal не имеет даже на чтение.

Если доступа нет, то как тогда отрабатывает хранимка, она же под этим пользователем и выполняется?
2. А если 2 пользователя одновременно начнут читать журнал что будет? Нельзя ли заполнение и чтение в одну операцию соединить?
3. Там в хранимке парсинг даты идет. Вроде как для DSQl для передачи дат, есть макрос #date (раздел chm Константы прямого SQL), может можно его заюзать?

1. Видимо права на оракле есть, но режутся принудительно драйвером.
2. Ничего страшного не будет. Это уже БД будет разруливать. Хотя подозреваю, что неправильно понял вопрос. :)
3. Вся идея в отлучении драйвера Галактического от обработки передаваемого запроса. Не дотянется он до #date :)
m0p3e
Местный житель
 
Сообщения: 1384
Зарегистрирован: Вт мар 29, 2005 17:49
Откуда: Москва
Reputation point: 97

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

Сообщение galover » Сб мар 05, 2011 15:25

2. Ничего страшного не будет. Это уже БД будет разруливать. Хотя подозреваю, что неправильно понял вопрос.

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

я имел в виду попробовать так:
Код: Выделить всё
sqlExecStmt(stmt, 'GETJOURNAL('#date(' + DateToStr(dBeg, 'DD,MM,YYYY') + ')' + ',' + '#date(' + DateToStr(dEnd, 'DD,MM,YYYY') + '))');

ну и тип, принимаемый GETJOURNAL, поменять на INTEGER. Тогда ничего парсить не нужно будет.
galover
Местный житель
 
Сообщения: 794
Зарегистрирован: Пт ноя 16, 2007 14:52
Reputation point: 27

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

Сообщение m0p3e » Сб мар 05, 2011 15:54

Так можно ли в итоге заполнение таблицы и select соединить в одну операцию. Или Галка не умеет работать с курсорами?

В случае оракла select должен быть куда-то. (На сиквеле можно просто select from написать). Т.к. к x$journal доступ режет драйвер, то данные нужно перелить в таблицу доступную пользователю.

я имел в виду попробовать так:
Код:
sqlExecStmt(stmt, 'GETJOURNAL('#date(' + DateToStr(dBeg, 'DD,MM,YYYY') + ')' + ',' + '#date(' + DateToStr(dEnd, 'DD,MM,YYYY') + '))');

Попробую как будет время. Хотя смысла большого нет, т.к. стандартные функции вроде TO_ORADATE также парсят несчастные даты :)
m0p3e
Местный житель
 
Сообщения: 1384
Зарегистрирован: Вт мар 29, 2005 17:49
Откуда: Москва
Reputation point: 97

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

Сообщение galover » Сб мар 05, 2011 16:27

В случае оракла select должен быть куда-то. (На сиквеле можно просто select from написать). Т.к. к x$journal доступ режет драйвер, то данные нужно перелить в таблицу доступную пользователю.

Я имел в виду, чтобы хранимка сразу возвращала курсор http://download.oracle.com/docs/cd/E118 ... _plsql.htm. Т.е. заполнили времянку и открыли курсор, но не в курсе поддерживает ли такое механизм DSQL
galover
Местный житель
 
Сообщения: 794
Зарегистрирован: Пт ноя 16, 2007 14:52
Reputation point: 27

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

Сообщение m0p3e » Сб мар 05, 2011 17:12

Поэкспериментирую как нибудь на досуге... :)
m0p3e
Местный житель
 
Сообщения: 1384
Зарегистрирован: Вт мар 29, 2005 17:49
Откуда: Москва
Reputation point: 97

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

Сообщение spark » Ср мар 21, 2012 13:08

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



отличное решение! А как быть в случае когда запрос больше 255 символов? Как их передать в этот Gate?
Аватара пользователя
spark
Местный житель
 
Сообщения: 476
Зарегистрирован: Ср окт 19, 2005 13:38
Reputation point: 41

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

Сообщение m0p3e » Пн мар 26, 2012 10:52

spark писал(а):
m0p3e писал(а):отличное решение! А как быть в случае когда запрос больше 255 символов? Как их передать в этот Gate?

Не так давно сам столкнулся с тем, что составная строка >255 символов не шлюзом не воспринимается корректно. Т.к. задачу нужно было решить срочно, то создал процедуру оракловую и через шлюз вызываю ее.
m0p3e
Местный житель
 
Сообщения: 1384
Зарегистрирован: Вт мар 29, 2005 17:49
Откуда: Москва
Reputation point: 97

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

Сообщение m0p3e » Пт май 18, 2012 11:16

Углубился в проблему ограничения в 255 символов.
При сборке запроса через sqlAddStr получаем ошибку:
Код: Выделить всё
Ошибка(1,12): Синтаксическая ошибка в выражении

Т.е. до оракла и шлюза запрос даже не проходит. Рубит его ora90drv. Пока попытки передать длинную строку не увенчались успехом.

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

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

Сообщение m0p3e » Пт май 18, 2012 17:12

Разобрался. Транслятор проверяет парность ковычек.
Работающий запрос с использованием 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
Местный житель
 
Сообщения: 1384
Зарегистрирован: Вт мар 29, 2005 17:49
Откуда: Москва
Reputation point: 97

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

Сообщение m0p3e » Ср мар 26, 2014 13:02

Перевел ряд пользователей на трехзвенку и всплыл нюанс - временные таблицы создаются в схеме пользователя под которым авторизуется сервер приложений.
Соответственно при обращении к таблице "не из Галактики" это нужно учитывать. Добрый волшебник :) подсказал функцию для определения где исполняется код:
Код: Выделить всё
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);
   } 
  ...............

К сожалению, функцию для определения имени пользователя сервера приложений розыскать не удалось.
m0p3e
Местный житель
 
Сообщения: 1384
Зарегистрирован: Вт мар 29, 2005 17:49
Откуда: Москва
Reputation point: 97

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

Сообщение Zmiter » Чт фев 25, 2021 14:44

А если стоит задача просто написать DSQL запрос с dblink? Можно ли обойтись без этих ухищрений? Какой будет синтаксис?
Код: Выделить всё
SELECT something FROM table_name@dblink_name
не получается.
Где вообще смотреть эти синтаксические конструкции, если справка viper очень ограничена?
Zmiter
 
Сообщения: 1
Зарегистрирован: Чт мар 22, 2018 12:13
Reputation point: 0

След.

Вернуться в Опыт

Кто сейчас на конференции

Сейчас этот форум просматривают: нет зарегистрированных пользователей и гости: 1


cron