Oracle-club: FAQ - PL/SQL


Новости | FAQ | Ссылки | Темы | Утилиты | Documentation | Семинары
O создании Udf под Oracle

Есть такой метод.
Идея и универсальная реализация (включая все исходники) на
http://www.oracle.com/st/products/features/plsqlplex.html
http://www.oracle.com/st/products/features/moreplex_downloads.html
Вкратце идея такова - пишется прога, реализующая необходимый интерфейс с (чем
угодно, в данном случае с программами ОС), которая цепляется как обычный
оракловый процесс, с этой программой общаются через оракловые пайпы хранимые
процедуры, которые в свою очередь, используются счастливыми пользователями (или
администраторами ;)
Bye, Dmitri Ovechkin.

======================================================================
> Выскочила ошибка ORA-06571: Function MY_FUNCTION does not guarantee not
             > to update database. Сабж. А произошло это  в функции пакета. В ней
             > происходит вызов тр?х других ф-ций из других пакетов. Каждая из них в
             > отдельности работает нормально и возвращает то, что нужно.
             >

Ответ:

1. Если у тебя MY_FUNCTION и вызываемые из нее функциидействительно не меняют данные в базе, то тебе поможет указание прагмы: function MY_FUNCTION ( ... ) return .... begin . ...тело функции . pragma restrictt_references('WNDS',MY_FUNCTION); end или если функция включена в пакет: package Имя_пакета is ... function MY_FUNCTION( ... ) return ... ; pragma restrictt_references(MY_FUNCTION,WNDS); ... end Имя_пакета; 2. Как правило, это происходит при вызове функций в SQL выражениях. Даже если функция написана правильно, Oracle иногда "перестраховывается" и не позволяет использовать функцию по причине, указанной в тексте сообщения об ошибке. Выйти из этой ситуации можно указав явно ограничения на поведение функции при ее объявлении. Например, FUNCTION get_statement_site (p_customer_id IN ra_customers.customer_id%type ) RETURN NUMBER; -- pragma restrict_references(get_statement_site, WNDS, WNPS); ================================================================ Error: program too large : >у него на этапе компайла строится дерево лексем и листьев там до 32 K : >(если не ошибаюсь) : >А больше нельзя : >(Т е размер исходника не показатель) : >Все что можгно сделать это заремить ненужные переменные и куски кода : Мужики да ВЫ что? Напрягитесь! Ведь ограничение совершенно дурацкое. : Наверняка какой-нибудь буфер в настройках можно подкрутить. Try to use SYS.DBMS_SHARED_POOL.ABORTED_REQUEST_THRESHOLD procedure. For example: connect by sys or system begin sys.dbms_shared_pool.aborted_request_threshold(2000000); end; / and after it try to recompile your package. But on Windows environment it can make some troubles. From S.Feuerstein's "ORACLE PL/SQL Programming" book (p. 833): "In the Windows environment, you run into severe memory problems when your program size (for a standalone procedure or function, or an entire package) approaches and exceeds 32k. Now, thirty-two thousand-odd bytes is a very large size for a program. If you are writing a program this large, you should probably break it up into smaller pieces." On my system (Digital Unix, Oracle 8.0.4) I have a packages with source_size above 300000 bytes and code_size above 70000 bytes. Regards, ------------------------------ Alexey Reshetov real@kpbank.ru ============================================================== Re: И опять триггер... 24-Nov-98 22:08 VKM Sayenko wrote: VS>> В триггере AFTER DELETE (not for each row!) 3й таблицы читаешь VS>> этот список, делаешь свой select и грохаешь все, что хочешь VS>> из первой и второй таблицы ( если надо то и всю базу, кроме VS>> 3й таблицы :) ). VS> VS>Увы... VS> VS>ORA-04091: table ... is mutating, trigger/function may not see it VS>ORA-06512: at "...", line 18 VS>ORA-04088: error during execution of trigger '...' VS> VS>> Счастливого удаления, VS>> Eugene VS> А у меня работает BR, Eugene ==================== Create table table_1(a1 number(1) ); Create table Table_2(a1 number(1),a2 number(1)); Create or replace trigger tr_a1 after delete on table_2 begin declare aa1 number; aa2 number; nn number; begin nn:=dbms_pipe.receive_message('mypipe'); If nn=0 then dbms_pipe.unpack_message(aa2); Select count(*) into aa1 from table_2 where a1=aa2; If aa1=0 then delete from table_1 where a1=aa2; end if; end if; end; end; / CREATE OR REPLACE TRIGGER TR_A2 BEFORE DELETE ON table_2 FOR EACH ROW BEGIN Declare nn integer; begin dbms_pipe.pack_message(:old.a1); nn:=dbms_pipe.send_message('mypipe'); end; END; / insert into table_1 Values (1); insert into table_1 Values (2); insert into table_1 Values (3); insert into table_2 Values (1,1); insert into table_2 Values (1,2); insert into table_2 Values (1,3); insert into table_2 Values (2,1); insert into table_2 Values (2,2); insert into table_2 Values (2,3); insert into table_2 Values (3,1); insert into table_2 Values (3,2); insert into table_2 Values (3,3); commit; REM ====================================== SQL> select * from table_1; A1 ---------- 1 2 3 SQL> delete from table_2 where a1=1; 3 строк удалено. SQL> commit; Фиксация обновлений завершена. SQL> select * from table_1; A1 ---------- 2 3 ---------------- А какой собственно говоря Oracle используешь? Если 8.xx, то все ОК. 1. Создаешь представление 2. Пишешь триггер INSTEAD OF ..... который в свою очередь редактирует базовую таблицу, как побочный эффект - можно использовать рекурсию. :)
> Как запустить программы, которые храняться в таблицах Oracle? > > Я это делаю след. образом: > 1 - создаю package, > 2 - записываю вызов в таблицу > 3 - достаю запись из таблицы и запускаю через DBMS_JOB > Недостаток данного метода заключается в горбатой передаче параметров. > JOB не понимает переменные PL/SQL и приходится передавать параметры > через таблицы. Это приведет большим временным задержкам при > распеределенных вычислениях.

Ответ:

В Вашем случае можно использовть пакет DBMS_SQL.
>С чьими правами выполняется хранимая процедура PL/SQL, если еe запускает не > владелец, а другой юзер, которому даны права на ее выполнение?

Ответ:

Выполнение идет с правами создателя процедуры. В версии 8.1 (8i) можно будет создавать хран. процедуры, которые будут исполняться с правами вызывающего.
> Вот возникла у меня трабла. Я сделал хранимую функцию, которая внутри > себя вызывает пакет dbms_sql. Мне всего то нужно > было на лету собрать запрос на SELECT и получить _один_ результат. Что я > и сделал. Все нормально, но полученную функцию не могу использовать в > запросе типа select MyFinc(Par1, Par2) from dual; Ругается что "Не > гарантируется, что функция MyFunc не обновляет данные". Ну не обновляет > она у меня! Но как Ораклу об этом сказать? И вообще это как-то лечится? > Потому как я хотел сделать View, которое среди прочих полей возвращает и > расчитанные моей функцией. > Если кто знает как общаться с динамическим сиквелом не оставьте меня в > моей просьбе plz!

Ответ:

Вообще-то выход есть: 1. сгенерить пакет, содержащий все необходимые селекты. 2. сгенерить(перегенерить) свитчер, который обращется к нужной сгенеренной функции. 3. в view использовать вызов свитчера
>Mожно ли управлять как-нибудь количеством обработанных строк ? >Нельзя ли из процедуры Oracle как-то это удовольствие возвращать ? > SQL%ROWCOUNT спасет отца русской демократии ... После INSERT/UPDATE/DELETE можно прочитать и сделать выводы.Если же нужно ограничить I/U/D кол-вом n записей, то решение очевидно - пишется соотв. курсор SELECT (FOR UPDATE для UPDATE и DELETE), далее цикл FETCH ... I/U/D (для U/D - WHERE CURRENT OF).
Вопроc: надо посчитать разницу в секундах (минутах,часах) между двумя переменными PL/SQL типа DATE.

Ответ:

дата3 := дата1 - дата2 - разница в днях дата3*24 - разница в часах дата3*24*60 - разница в минутах дата3*24*60*60 - разница в секундах. data1, data2 типа DATE, data3 должна быть типа NUMBER.
как заставить PL/SQL Developer забыть на > очередные 30 дней о своей триальности путем удаления нескольких ключей в > реестре. Никто не напомнит, каких?

Ответ:

"Перед запуском нужно начисто грохнуть в регистри два раздела: [HKEY_USERS\.Default\Software\Microsoft\PSystem] [HKEY_USERS\.Default\Software\PL/SQL Developer]" или в HKEY_CURRENT_USER\Software\Microsoft\PSystem грузить перед запуском ПЛ/Сиквел девелопера в рестр один ключик....и он снова думает что ему осталось жить целых 30 дней просто в HKEY_CURRENT_USER\Software\Microsoft\PSystem надо создать строковый пустой ключик Set

Тема : Идентификатор исключительной ситуации ORA-04091

Вопрос : >> После WHEN надо написать идентификатор исключительной ситуации, >> а как его узнать для ORA-04091 ?

Ответ:

DECLARE mutating_table EXCEPTION; PRAGMA EXCEPTION_INIT(mutating_table, -4091); ... BEGIN ... EXCEPTION WHEN mutating_table THEN ... END;

Тема : localtime && Oracle

Вопрос : Есть в libc функция localtime && asctime. Есть переменная ENV TZ. > Все это реализует непростой алгоритм вычисления локального времени > со всякими смешениями, летними и зимними временами. А есть-ли что-то > похожее в Оракле или как ?

Ответ:

Есть функция NEW_TIME (some_date, timezone1, timezone2) Она переводит some_date из timezone1 в timezone2.
Есть ли PL/SQL-пакет для запуска внешних программ?

Ответ:

Нет такого пакета. Есть другой. DBMS_PIPE Пишется отдельная программулина, которая висит и слушает пайп. А потом запускает чего нибудь. Рекомендую посмотреть главу 10 в PL/SQL User's Guide and Reference о внехних процедурах. Это только для оракла8. Можно запускать процедуры из dll для NT или so для Unix. Для семерки можно использовать пакет Томаса Кайта PLEX, он работает через dbms_pipe. Лежит на http://www.oracle.com/st/products/features/plsqlplex.html
>При переносе базы с InterBase на Oracle возникла следующая трудность, помогите >plz. >Вот процедура TestProc, которая возвращает курсор RetCur : > > PROCEDURE TestProc ( ..., RetCur IN OUT TrfCur ) > IS > BEGIN > OPEN RetCur AS SELECT FLD_1, FLD_2, ..., TrfFunc( FLD_X) FROM TABLE > WHERE > CheckFunc(...) > 0; > END > > Хочется, чтобы фукция ( конкретно TrfFunc ) за один вызов возвращала не одну >колонку а несколько: > OPEN > RetCur AS > SELECT FLD_1, FLD_2, ..., > TrfFunc( FLD_X) <- вот здесь хотелось бы получить несколько значений >(на пример : Сумма, Тип валюты и.т.п.) > FROM ... >Вообще возможно ли это? Если нет - как обойти? Подставлять OUT параметры? >Попытался создать новый тип RECORD но при попытке вернуть его в SELECT - ошибка >(wrong type...). > >В глубокой тоске ожидаю....

Ответ:

Я тоже тосковал, но сейчас похоже смирился с этой ограниченностью Оракла в связке с BDE. Мне видится только такой путь: в пакадже создать PL/SQL TABLE и отдельные функции вида function ExtractField1(i number) return <нужный тип> is begin return PLSQLTABLE(i).Field1; end; Общее число функций = числу полей в твоей TrfFunc(...). Надобно сначала заполнить PLSQLTABLE, а потом делать OPEN RetCur AS SELECT FLD_1, FLD_2, ..., ExtractField1(rownum), ExtractField2(rownum), ... FROM TABLE WHERE CheckFunc(...) > 0; В принципе этот подход позволяет полностью воссоздать возможности интербейзовской for select do ... suspend (а также возможности работы с временными таблицами в MSSQL и Sybase). Т.е. можно возвращать на BDE-шного клиента не просто какой-то SELECT (хоть и через REF CURSOR), а любую PL/SQL TABLE.
Вопрос: нужно вызывать хранимые процедуры и функции и получать от них ответ по ходу дела они пока еще не собраны в пакеты соответственно прагмы не написаны через SQL Plus v3.2 это проблематично Или подскажите чем можно воспользоваться для этой цели

Ответ:

Использовать SQL Plus v3.3 Установить опцию ServerOutput=On exec dbms_output.put_line(function_name(param_list)); Пример : SQL> exec dbms_output.put_line(ADDCOUNTRY('Армения')); 3 PL/SQL procedure successfully completed.

Вопрос:

помогите написать триггер вроде все правильно а выдает ошибку CREATE OR REPLACE TRIGGER VESTOCK.AU_ORG AFTER UPDATE OF WAREHOUSE ON VESTOCK.ORG FOR EACH ROW WHEN(NEW.WAREHOUSE=1) BEGIN INSERT INTO STOCK SELECT DISTINCT ARTICLE.CODE,:NEW.ORG,0,0,0 FROM ARTICLE ; END; ORA-04091: таблица VESTOCK.ORG изменяется функция триггер может не заметить этого ORA-06512: на "VESTOCK.AU_ORG" Oracle 7.3 for NT поле WAREHOUSE определят является ли данная организация складом ARTICLE справочник товаров STOCK справочник о наличии товара на различных складах Они обе связаны (ARTICLE и ORG) связана ссылочной целостностью с таблицей STOCK вот их описание CREATE TABLE VESTOCK.ARTICLE ( CODE VARCHAR2(20) NOT NULL, NAMESMALL VARCHAR2(40) DEFAULT 'NoInformation' NOT NULL, TYPEOFARTICLE NUMBER(*, 0) DEFAULT 0 NOT NULL, NAMEFULL1 VARCHAR2(80) DEFAULT 'NoInformation' NOT NULL, пропущено REMARK VARCHAR2(2000) ) CREATE TABLE VESTOCK.ORG ( ORG VARCHAR2(40) DEFAULT 'NoInformation' NOT NULL, ORGFULLNAME1 VARCHAR2(80) DEFAULT 'NoInformation' NOT NULL, ORGFULLNAME2 VARCHAR2(80) DEFAULT 'NoInformation' NOT NULL, пропущено CONTRACT LONG RAW ) CREATE TABLE VESTOCK.STOCK ( CODE VARCHAR2(20) NOT NULL, ORG VARCHAR2(40) DEFAULT 'NoInformation' NOT NULL, QUANTITYPHISICAL NUMBER(*, 0) DEFAULT 0 NOT NULL, QUANTITYREZERV NUMBER(*, 0) DEFAULT 0 NOT NULL, QUANTITYDOCUMENT NUMBER(*, 0) DEFAULT 0 NOT NULL

Ответ 1:

Похоже на то, что STOCK имеет внешний ключ на ORG. Для того чтобы вставить записи в STOCK, нужно проверить ORG на наличие там соответствующих значений первичного ключа, а сделать это Oracle не может, т.к. таблица ORG в этот момент мутирующая. Классическое решение проблемы мутирующих таблиц заключается в создании трех триггеров и plsql-пакета: 1. Пакет, что-то типа: CREATE OR REPLACE PACKAGE org_pack IS TYPE orgpk_tabtype IS TABLE OF org.org%TYPE INDEX BY BINARY_INTEGER; orgpk_tab orgpk_tabtype; END org_pack; 2. Триггер AFTER UPDATE FOR EACH ROW - заполняет plsql-таблицу, объявленную в пакете, значениями первичного ключа измененных записей таблицы ORG. CREATE OR REPLACE TRIGGER VESTOCK.AU_ORG AFTER UPDATE OF WAREHOUSE ON VESTOCK.ORG FOR EACH ROW WHEN(NEW.WAREHOUSE=1) BEGIN org_pack.orgpk_tab (NVL (org_pack.orgpk_tab.COUNT, 0) + 1) := :NEW.org; END; 3. Триггер AFTER UPDATE - для каждой записи в plsql-таблице выполняет вставку в STOCK. CREATE OR REPLACE TRIGGER VESTOCK.AU_ORG AFTER UPDATE ON VESTOCK.ORG BEGIN FOR i IN 1 .. org_pack.orgpk_tab.COUNT LOOP INSERT INTO STOCK SELECT DISTINCT ARTICLE.CODE,org_pack.orgpk_tab (i),0,0,0 FROM ARTICLE ; END LOOP; END; 4. Триггер BEFORE UPDATE - чистит plsql-таблицу CREATE OR REPLACE TRIGGER VESTOCK.AU_ORG BEFORE UPDATE ON VESTOCK.ORG BEGIN org_pack.orgpk_tab.DELETE; END; -- Павел Лузанов

Ответ 2:

Вероятно (точно) либо таблица ARTICLE либо STOCK связана ссылочной целостностью с таблицей ORG. Если бы ты написал структуру всех трех таблиц и констрэйнов ссылочной целостности, то можно было-бы написать тебе триггер(ы). А так... :( Попробуй сохранять в этом триггере значение :NEW.ORG в пакетную глобальную переменную. Либо, если их, значений, будет много, разные для каждой строки, можно сохранить в пайп (dbms_pipe) или еще куда, например в PL/SQL таблицу. Потом в другом after-триггере, который НЕ FOR EACH ROWS, а просто для уровня всего оператора выполняешь свой заброс, извлекая значения из пакетной переменной или пайпа.

Вопрос:

> Может кто сталкивался со следующей проблемой > когда используя пакет dbms_sql патаюсь создать таблицу. Мне сообщают, > что у меня нехватает привилегий. Если я удаляю существующую таблицу, > создаю индекс или что то еще все проходит нормально. Я работаю под DBA.

Ответ :

Этого мало. Надобно прямо дать привелегии юзеру - внутри PL/SQL-процедур роли отключены. А права dbms_sql - вообще одна головная боль. Практика показывает, что для создания таблицы требуется привелегия create any table, даже если пытаешься создать таблицу в своей схеме.

Вопрос:

при работе с PL/SQL таблицами не освобождается память

Ответ :

при активной работе с PL/SQL таблицами хорошо помогает DBMS_SESSION.FREE_UNUSED_USER_MEMORY особенно в случае удаления/изменения таблиц.

Вопрос:

Как в PL/SQL процедуре сделать sleep, например, на 15 секунд?

Ответ :

dbms_lock.sleep(XXX); Где ХХХ - время в секундах.

Вопрос:

никак не могу по-человечески преобразовать число в строку функцией to_char в PL/SQL. Нужно-то всего ничего: чтобы числа с дробной частью выводились в виде 0,25 12,34 (а не в виде .25 и 12.34), числа же без дробной части нужно без нее и выводить ( целое 12 как 12 а не как 12,00). Можно ли все это получить, задав всего одну строку формата?

Ответ :

select RTRIM(to_char( .34,'FM990.99' ),'.' ) from dual '0.34' select RTRIM(to_char( 12.34,'FM990.99' ),'.' ) from dual '12.34' select RTRIM(to_char( 12,'FM990.99' ),'.' ) from dual '12' Можно еще задать NLS_FORMAT если хочешь вместо точки выводить запятые. :)

Новости | FAQ | Ссылки | Темы | Утилиты | Documentation | Семинары