Вопрос
Технические вопросы
Разработка

Возвращаемое значение хранимой процедуры Oracle.

SSV
10 августа 2010 10:37

ЕСть вот такое определение ХП в БД Oracle:

FUNCTION f_add(in_site_type_id IN INTEGER,
               in_name IN VARCHAR2,
               in_desc IN VARCHAR2 DEFAULT NULL,
               in_adress_id IN INTEGER DEFAULT NULL,
               in_developer_id IN INTEGER,
               in_owner_id IN INTEGER,
               in_management_id IN INTEGER,
               in_site_status_id IN INTEGER,
               in_comments IN VARCHAR2 DEFAULT NULL,
               in_id IN INTEGER DEFAULT NULL) RETURN INTEGER;

каким образом ее вызвать из скрипта TSCRM чтобы получить возвращаемое значение?

Нравится

53 комментария
Осауленко Александр
10 августа 2010 12:45

SSV, можно создать SelectQuery на таблицу с одной записью (tbl_DatabaseInfo) где создать CustomSQL колонку и там вызвать Вашу функцию. В итоге должно получиться что-то типа такого:

SELECT
f_add(:pin_site_type_id, ...) "Column1"
FROM
"tbl_DatabaseInfo"

Получить значение можно так:

...
var sq = Services.GetNewItemByUSI("f_addQuery");
sq.Parameters.ItemsByName("in_site_type_id").Value = 1;
...
var ds = sq.Open();
Log.Write(1, ds.ValAsInt("Column1"));
...

Писал по памяти, без проверки, так что если будут ошибки компиляции, думаю подправите :)

SSV
10 августа 2010 13:21

Спасибо, Александр, но после Вашего ответа я понял, что некорректно задал свой вопрос. Дело в том, что CRM работает с MS SQL сервером, а процедура должны вызываться из БД Oracle. Подозреваю, что в данном случае SelectQuery не сможет подключиться к серверу Oracle. Это нужно для синхронизации неких данных в двух БД.

Осауленко Александр
10 августа 2010 13:54

Можно попробовать через ADOQuery или создать linked-server на Oracle и попробовать вызвать функцию.

SSV
10 августа 2010 14:02

Именно ADO я и хотел использовать. Вот и возник основной вопрос - как с помощью ADO получить возвращаемое значение?

Осауленко Александр
10 августа 2010 14:05

Точно так же как я описал в первом посте. Только можно делать выборку не с tbl_DatabaseInfo, а из DUAL.

SSV
11 августа 2010 10:43

Что то ничего не получается. Не проходит в SelectQuery запрос вида:

SELECT
TestOut(1,0) as "Column1"
FROM
"tbl_DatabaseInfo"

создал тестовую процедуру на MS SQL и попытался выполнить, пишет "'TestOut' is not a recognized built-in function name"...

Осауленко Александр
11 августа 2010 11:07

Вы не указали owner для функции и не заменили кавычки на квадратные скобки. Попробуйте так:

SELECT
dbo.TestOut(1,0) AS [Column1]
FROM
[tbl_DatabaseInfo]

Только как это Вам поможет? У Вас же функция на Oracle :)

Осауленко Александр
11 августа 2010 11:09

"SSV" написал:создал тестовую процедуру на MS SQL

Процедуру или функцию?

SSV
11 августа 2010 11:09

"Cannot find either column "dbo" or the user-defined function or aggregate "dbo.TestOut", or the name is ambiguous"

SSV
11 августа 2010 11:19

Сначала сделал процедуру с OUTPUT параметром - не получилось. Переделал в функцию - получилось. Спасибо.

SSV
12 августа 2010 14:42
var SiteBalDataset = Services.GetNewItemByUSI('ADOC_Site');
SiteBalDataset.SQLText = 'select f_add("'+pNameSite+'"'+','+'"'+pAddressSite+'") as IDBalSys from DUAL';
SiteBalDataset.Open;
var IDBalSys = SiteBalDataset.ValAsFloat('IDBalSys');

На последней строке выдает ошибку - "Объект не поддерживает это свойство или метод."
В чем может быть дело?

Осауленко Александр
12 августа 2010 15:03

"SSV" написал:SiteBalDataset.Open;

А так?

var ds = SiteBalDataset.Open();
var IDBalSys = ds.ValAsFloat('IDBalSys');
Осауленко Александр
12 августа 2010 15:04

"SSV" написал:SiteBalDataset.SQLText = 'select f_add("'+pNameSite+'"'+','+'"'+pAddressSite+'") as IDBalSys

Никогда так не делайте, с этим много проблем. Используйте параметры.

SSV
13 августа 2010 08:21
var SiteBalDataset = Services.GetNewItemByUSI('ADOC_Site');
SiteBalDataset.Parameters.ItemsByName('pNameSite').ValAsStr = pNameSite;
SiteBalDataset.Parameters.ItemsByName('pAddressSite').ValAsStr = pAddressSite;
var ds = SiteBalDataset.Open();
var IDBalSys = ds.ValAsFloat('IDBalSys'); //SiteBalDataset.ValAsFloat('IDBalSys');

Та же самая ошибка на строке - var ds = SiteBalDataset.Open();

Осауленко Александр
13 августа 2010 09:20

SSV, а что такое ADOC_Site? ADODataSet? У него есть ADOCommand?
Если это ADODataSet, то тогда Ваш код подходит больше:

var SiteBalDataset = Services.GetNewItemByUSI('ADOC_Site');
SiteBalDataset.Parameters.ItemsByName('pNameSite').ValAsStr = pNameSite;
SiteBalDataset.Parameters.ItemsByName('pAddressSite').ValAsStr = pAddressSite;
SiteBalDataset.Open(); // Вызвать метод
var IDBalSys = SiteBalDataset.ValAsFloat('IDBalSys');
Осауленко Александр
13 августа 2010 09:27

SSV, а какой у Вас запрос сейчас? И какой провайдер Вы используете при подключении к Oracle?

SSV
13 августа 2010 09:46

Даже не знаю почему я использовал ADOCommand... ADOC_Site был типа ADOCommand, сейчас передела в ADODataSet, стало что-то получаться. Спасибо.

Запрос - select sites_pkg.f_add(:pNameSite,:pAddressSite) as IDBalSys from DUAL

Провайдер - MSDAORA.1

SSV
13 августа 2010 10:00

Что интересно - средствами Oracle выполнить этот запрос не получается - ругается на команду Insert в теле функции, а из скрипта CRM выполняется, но пишет, что "Object 'DataField IDBalSys' is not assigned"...

Осауленко Александр
13 августа 2010 10:03

"SSV" написал:

SELECT sites_pkg.f_add(:pNameSite,:pAddressSite) AS IDBalSys FROM DUAL


А Вы выполняли его в какой либо утилите по работе с Oracle? Он работает? Возвращает данные для Ваших параметров? Попробуйте еще указать имя колонки IDBalSys в кавычках - "IDBalSys"

Осауленко Александр
13 августа 2010 10:08

"SSV" написал:Что интересно - средствами Oracle выполнить этот запрос не получается - ругается на команду Insert в теле функции, а из скрипта CRM выполняется, но пишет, что "Object 'DataField IDBalSys' is not assigned"...

Все правильно, набор данных ничего не вернул, поэтому и ругается...

SSV
13 августа 2010 10:20

Мда... А есть еще способы выполнить из CRM функцию Oracle и вернуть результат ее работы?

Осауленко Александр
13 августа 2010 10:25

Есть :) Исправить ошибку в функции sites_pkg.f_add.

Осауленко Александр
13 августа 2010 10:26

Тут даже не в том проблема что ничего не вернул запрос, а в том, запрос некорректен.

SSV
13 августа 2010 10:29

Каким то образом добились выполнения запроса средствами Oracle. Из CRM так и не выполняется - "Object 'DataField IDBalSys' is not assigned".

Осауленко Александр
13 августа 2010 10:35

А в дебаггере проверяли, DataFields у SiteBalDataset содержит элементы?
Попробуйте еще так:

...
var IDBalSys = SiteBalDataset.DataFields.Items[0].Value;
...
SSV
13 августа 2010 10:41

"Осауленко Александр" написал:

var IDBalSys = SiteBalDataset.DataFields.Items[0].Value;

"Ошибка выполнения Microsoft JScript: Недопустимое число аргументов или присвоение значения свойства"

Осауленко Александр
13 августа 2010 10:49

Ну так у DataSet нет DataField. Вы их в дизайнере добавили?

SSV
13 августа 2010 11:00

"Осауленко Александр" написал:
Ну так у DataSet нет DataField. Вы их в дизайнере добавили?

:sad:
это что-то меня совсем переклинило. Теперь добавил, всё проходит без видимых ошибок, но такое впечатление, что ничего не происходит - функция запись не добавляет и возвращает 0...

Осауленко Александр
13 августа 2010 11:05

Наверное у Вас функция все-таки не корректна... Или параметры не те приходят. Тут только отлаживать функцию необходимо на стороне Oracle.

SSV
13 августа 2010 13:17

Попробовал помониторить с помощью Statement Tracer for Oracle, получил следующую ошибку:

Class: Error message
Time stamp: 13/08/2010 14:15:09
SQL statement: ORA-06503: PL/SQL: Возврат функции без значения
ORA-06512: на "NSI.SITES_PKG", line 63
ORA-06512: на line 1

Осауленко Александр
13 августа 2010 13:50

Опубликуйте код создания пакета.

SSV
13 августа 2010 14:07
FUNCTION f_add(in_site_type_id IN INTEGER DEFAULT NULL,
               in_name IN VARCHAR2,
               in_desc IN VARCHAR2 DEFAULT NULL,
               in_adress_id IN INTEGER DEFAULT NULL,
               in_developer_id IN INTEGER DEFAULT NULL,
               in_owner_id IN INTEGER DEFAULT NULL,
               in_management_id IN INTEGER DEFAULT NULL,
               in_site_status_id IN INTEGER DEFAULT NULL,
               in_comments IN VARCHAR2 DEFAULT NULL,
               in_id IN INTEGER DEFAULT NULL) RETURN INTEGER AS PRAGMA AUTONOMOUS_TRANSACTION;
v_id INTEGER;  
v_error VARCHAR2(4000);             
BEGIN  
  IF in_id IS NULL THEN 
     SELECT MAX(id)+1
       INTO v_id
       FROM sites;
  ELSE
     v_id:=in_id;
  END IF; 
  INSERT INTO sites
    VALUES
  (v_id,
   in_site_type_id,
   in_name,
   in_desc,
   in_adress_id,
   in_developer_id,
   in_owner_id,
   in_management_id,
   in_site_status_id,
   in_comments);
   COMMIT;
   RETURN v_id;
EXCEPTION
  WHEN OTHERS THEN 
  v_error:=SQLERRM(SQLCODE);
    INSERT INTO test_s VALUES (SYSDATE,'ERROR:'||v_error);  COMMIT; 
END;
Осауленко Александр
13 августа 2010 15:43

А что вернется в случае ошибки?

Осауленко Александр
13 августа 2010 15:45

"SSV" написал:SELECT MAX(id)+1
       INTO v_id
       FROM sites;

Это конечно Вы зря. А почему не последовательности?

SSV
16 августа 2010 06:37

Дело в том, что я не работаю с Oracle, функции пишет девочка. Меня тоже удивил этот кусочек кода. Я попросил упростить функцию - сократить количество параметров до двух самых необходимых - in_name и in_desc. В случае ошибки сейчас что-то пишется в таблицу test_s, что именно еще нужно выяснить у девочки.

FUNCTION add2(in_name IN VARCHAR2,
              in_desc IN VARCHAR2 DEFAULT NULL) RETURN INTEGER AS PRAGMA AUTONOMOUS_TRANSACTION;
v_id INTEGER;  
v_error VARCHAR2(4000);             
BEGIN  
   SELECT MAX(id)+1
     INTO v_id
     FROM sites; 
  INSERT INTO sites (id,name,description)
    VALUES
  (v_id,
   in_name,
   in_desc);
   COMMIT;
   RETURN v_id;
EXCEPTION
  WHEN OTHERS THEN 
  v_error:=SQLERRM(SQLCODE);
    INSERT INTO test_s VALUES (SYSDATE,'ERROR:'||v_error);  
COMMIT; 
END;

Теперь Statement Tracer for Oracle выдает такую ошибку:

Class: Error message
Time stamp: 16/08/2010 07:45:12
SQL statement: ORA-01722: неверное число

SSV
16 августа 2010 07:05

Только сейчас заметил, что изменилось имя функции...

var SiteBalDataset = Services.GetNewItemByUSI('ADOC_Site');
SiteBalDataset.Parameters.ItemsByName('pin_name').ValAsStr = pNameSite;
SiteBalDataset.Parameters.ItemsByName('pin_desc').ValAsStr = pAddressSite;
SiteBalDataset.Open();
var IDBalSys = SiteBalDataset.ValAsFloat('IDBalSys');

на строке SiteBalDataset.Open(); выдает ошибку:

"TSADOEngineLibrary.ADODataset: Не удается вновь задать положение в наборе строк"

Может в ADODataSet нужно что-то поменять в настройках курсора?

Осауленко Александр
16 августа 2010 07:18

SSV,
1. Вам необходимо обязательно уйти от SELECT MAX - это не подход к клиент-серверным базам
2. Вернуть какое-то число в случае ошибки

Какая структура таблиц sites и test_s?

Осауленко Александр
16 августа 2010 07:19

"SSV" написал:"TSADOEngineLibrary.ADODataset: Не удается вновь задать положение в наборе строк"

Ну так у Вас же функция еще не скомпилированная, как Вы можете делать выборку из нее?

SSV
16 августа 2010 07:30

У меня нет полного доступа к БД Oracle, но предполагаю, что структура sites идентична параметрам функции f_add. Каких либо реляционных связей с другими таблицами нет. Таблица test_s, опять же судя по коду в функции состоит из двух полей - дата и строка, она создавалась только для проверки данной функции.
Не совсем понял про не скомпилированную функцию. Почему, в таком случае, такая ошибка не всплывала с функцией f_add?

Осауленко Александр
16 августа 2010 07:35

А Вы тестировали add2 на Oracle? Она рабочая?

SSV
16 августа 2010 07:49

Девочка тестировала. Говорит - работает. Да там, на мой взгляд, и работать то особо нечему.

Осауленко Александр
16 августа 2010 07:54

У Вас есть какая-то утилита для работы с Oracle? TOAD, PL/SQL Developer? Можете там выполнить запрос, который Вы запускаете из Terrasoft?

Скажите разработчику функции:
1. MAX это не подход и это надо обязательно переписать
2. Вернуть значение в случае ошибки. Сейчас Функция "глушит" все ошибки и ничего не возвращает.

SSV
16 августа 2010 08:53

Запрос, который выполняет CRM, работает в Oracle. Более того - при выполнении метода SiteBalDataset.Open(); данные добавляются в таблицу, но всё равно в CRM вываливается ошибка - "TSADOEngineLibrary.ADODataset: Не удается вновь задать положение в наборе строк" и соответственно строка - var IDBalSys = SiteBalDataset.ValAsFloat('IDBalSys'); не выполняется и никакого возвращаемого значения я не получаю.

Осауленко Александр
16 августа 2010 08:59

А Вы как-то меняли ADODaatSet - настройки курсора и т.п.?

Осауленко Александр
16 августа 2010 09:02

Если у Вас серверный курсор, попробуйте клиентский.

SSV
16 августа 2010 09:02

Нет, настройки курсора пока не менял.

SSV
16 августа 2010 09:13

"Осауленко Александр" написал:Если у Вас серверный курсор, попробуйте клиентский.

Вот! Теперь получилось! Большое спасибо, Александр!

SSV
16 августа 2010 09:28

Если можно еще один вопрос:
как присвоить полю SiteBalDataset.Parameters.ItemsByName('pin_site_type_id').ValAsInt значение NULL?
По простому SiteBalDataset.Parameters.ItemsByName('pin_site_type_id').ValAsInt = null; не проходит, пишет о несоответствии типов.

SSV
16 августа 2010 10:01

Если параметры не типизировать, то значение вроде бы присваивается -

SiteBalDataset.Parameters.ItemsByName('pin_site_type_id').Value = null;

но при этом, при попытке выполнить метод Open(), вылезает ошибка - "TSADOEngineLibrary.ADODataset: Неправильно определен объект Parameter. Предоставлены несовместимые или неполные сведения".

Осауленко Александр
16 августа 2010 10:03

"SSV" написал:как присвоить полю SiteBalDataset.Parameters.ItemsByName('pin_site_type_id').ValAsInt значение NULL?

Посмотрите это свойство Value

Показать все комментарии