Все технические форумы на одном сайте Удобный поиск информации с популярных форумов в одном месте
Вопрос: Что делать с alter function with execute as owner?

Помогите неграмотному сделать первые шаги!!!

Есть функция:
alter function [rprt].[KlntTrn2]( 	
)
returns table
as
return
(SELECT [CONT] FROM [Table1])

Пытаюсь добавить "with execute as owner"
alter function [rprt].[KlntTrn2]( 	
)
returns table
with execute as owner
as
return
(SELECT [CONT] FROM [Table1])

Выдает ошибку:
Сообщение 487, уровень 16, состояние 1, процедура KlntTrn2, строка 24
An invalid option was specified for the statement "CREATE/ALTER FUNCTION".
Сообщение 178, уровень 15, состояние 1, процедура KlntTrn2, строка 26
A RETURN statement with a return value cannot be used in this context.

Как это обойти? Очень не хочу писать конструкцию:
RETURNS @return_variable TABLE <table_type_definition>
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN 
        function_body 
        RETURN
    END

Заранее спасибо!
Ответ:
b2w
К сожалению на схему dbo права давать нельзя.

никто и не дает. схеме rprt меняют овнера.
а ссылку тоже к сожалению прочесть не удалось?
Вопрос: FB2.5 - ошибка при ALTER PROCEDURE, содержащей в параметрах ссылки на системные домены

Добрый день всем.

При автоматической генерации скрипта создания хранимых процедур наткнулся на ошибку, при которой невозможен DROP или ALTER PROCEDURE.

Ниже приведен текст скрипта, воспроизводящий данную ошибку.
Вроде бы из текста скрипта понятна суть ошибки.

P.S. Скрипт выполнялся в ISQL.EXE.

С уважением, Polesov.

/* ---------------------------------------
The script parts executed in ISQL.EXE
*/

CREATE DATABASE 'localhost:c:\db\sp_error.fdb'
USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE 4096;

CREATE TABLE ITEMS (
ID INTEGER NOT NULL, /* system domain RDB$1 */
NAME VARCHAR(10) NOT NULL, /* system domain RDB$2 */
COST NUMERIC(15,2) NOT NULL /* system domain RDB$3 */
);

ALTER TABLE ITEMS ADD CONSTRAINT PK_ITEMS PRIMARY KEY (ID);
ALTER TABLE ITEMS ADD CONSTRAINT UNQ_ITEMS UNIQUE (NAME);

SET TERM ^ ;

CREATE PROCEDURE GET_ITEMS
RETURNS (
ID RDB$1, -- specify the same system domains,
NAME RDB$2, -- which were created automatically
COST RDB$3 ) -- when creating a table ITEMS
AS
BEGIN
FOR
SELECT ID, NAME, COST
FROM ITEMS
INTO :ID, :NAME, :COST
DO
SUSPEND;
END ^

SET TERM ; ^


/*
when you try to change the procedure an error occurs
*/
SET TERM ^ ;

ALTER PROCEDURE GET_ITEMS
RETURNS (
ID RDB$1,
NAME RDB$2,
COST RDB$3 )
AS
BEGIN
FOR
SELECT ID, NAME, COST
FROM ITEMS
ORDER BY NAME
INTO :ID, :NAME, :COST
DO
SUSPEND;
END ^

SET TERM ; ^
/* ERROR:
Statement failed, SQLSTATE = 42000
unsuccessful metadata update
-cannot delete
-DOMAIN RDB$1
-there are 1 dependencies
*/
Ответ:
Dimitry Sibiryakov
Значит надо всего-то их использование технически запретить.


Или при ALTER PROCEDURE оставить домен в покое, если на него еще ссылается какой-либо объект.
Вопрос: Как отследить alter всех таблиц в схеме?

Народ,
подскажите как можно отследить изменения всех таблиц в схеме.
Я нашел подобное решение здесь:
Немного его переделал, на все объекты триггер повесился, но не реагирует не изменения колонок таблиц,
когда делаешь alter table явно(кодом) и неявно(через pl/sql developer).
Кто-нибудь реализовывал что-нибудь подобное?
Спасибо

CREATE OR REPLACE TRIGGER alter_trigger
BEFORE ALTER
ON SCHEMA

DECLARE
 oper ddl_log.operation%TYPE;
 sql_text ora_name_list_t;
 i PLS_INTEGER;
BEGIN
  SELECT ora_sysevent
  INTO oper
  FROM DUAL;

  i := sql_txt(sql_text);
  
 IF oper = 'ALTER' THEN
    INSERT INTO ddl_log
    SELECT ora_sysevent, 
           ora_dict_obj_owner,
           ora_dict_obj_name, 
           ora_dict_obj_type, 
           --sql_text(1), разблокировать, если нужно посмотреть текст объекта 
           SYS_CONTEXT ( 'userenv', 'OS_USER' ), 
           SYSDATE
      FROM sys.gv_$sqltext
     WHERE UPPER(sql_text) LIKE 'ALTER%'
       AND UPPER(sql_text) LIKE '%NEW_TABLE%';
  END IF;
END alter_trigger;
Ответ: oracle_adm,
FROM sys.gv_$sqltext
     WHERE UPPER(sql_text) LIKE 'ALTER%'
       AND UPPER(sql_text) LIKE '%NEW_TABLE%'

Выкинь и взамен пропиши просто
FROM dual

Тут ты перемудрил.
Вопрос: ALTER TABLE table_name SET MAC TO '{0, 0}';

pg_dump'ом создаю дамп базы данных:
pg_dump -U user_name -C db_name > /base/db_name
Получается текстовый файл, смотрю в него, после создания каждой таблицы строка:
ALTER TABLE table_name SET MAC TO '{0, 0}';
Что это такое?
Версия сервера 7.4
Ответ:
Valentine_vaia
Перевожу проект с версии 7.4 на версию 9.2
БД развертывается из скриптов.

Последовательно выполняется создание таблицы и сразу после этого обнуление мандатных меток.
...
ALTER TABLE table_name SET MAC TO '{0, 0}';

Под 9.2 ругается
ОШИБКА: нет доступа к отношению "название таблицы"

Подскажите, как получить доступ для данного ALTER или чем заменить его в версии 9.2?



Для выполнения данной операции в 9.2 нужно для созданного супер пользователя установить параметры MACCAPS:

ALTER ROLE my_super_user MACCAPS 'INGLVL,INGCAP,CHMAC'


или правильно настроить конфигурацию сервера. Эти параметры должны присутствовать в поле rolmaccaps таблицы pg_settings по данному пользователю. Тогда метка мандатного доступа будет ставиться.
Вопрос: select из вьюхи, которую только что alter'нули в другом аттаче

hi all.

Народ, проверьте, плз, у себя на пустой базе вот это:

isql-1.

C:\MIX\firebird\QA\fbt-repo\tmp>isql /3255:e25
Database: /3255:e25, User: SYSDBA
SQL> create table t(a integer, b integer, c integer);
SQL> commit;
SQL> insert into t values(1,2,3);
SQL> commit;
SQL> create view v(a, b) as
CON> select a, b from t;
SQL> commit;
SQL> select * from v;

A B
============ ============
1 2

isql-2.

SQL> select * from v;

A B
============ ============
1 2

SQL> commit;

isql-1.

SQL> alter view v(a, b, c) as
CON> select a, b, c from t;
SQL> commit;

isql-2.

SQL> select * from v;

ВОПРОС. Что у вас будет выведено в результате последнего селекта в isql-2 ?
Ответ: Странно как-то. Есть , и к нему был сбацан тест, который уже несколько дней почему-то проваливается.

И судя по итогам, которые показаны на этих страницах:



-- транзакция коннекта-2 не видит обновленных метаданных.
Но и приведенный выше isql-сценарий, и вот этот питонский скрипт (а тесты прокручиваются питоном):
+
import fdb

print fdb.__version__

att1=fdb.connect(dsn='/3255:e25',user='SYSDBA',password='masterkey')
att2=fdb.connect(dsn='/3255:e25',user='SYSDBA',password='masterkey')

trn1=att1.trans()
cur1=trn1.cursor()

cur1.execute("create table t(a int, b int, c int)")   # att_12, tra_4
cur1.execute("create view v as select a,b from t")
trn1.commit()

cur1.execute("insert into t values(1,2,3)")           # att_12, tra_5
cur1.execute("select * from v")

for c in cur1.fetchall():
    print(c)

trn1.commit()

trn2=att2.trans()
cur2=trn2.cursor()
cur2.execute("select * from v")                       # att_13, tra_7
trn2.commit()

cur1.execute("alter view v as select a, b, c from t") # att-12, tra_8
trn1.commit()

cur2.execute("select * from v")                       # att_13, tra_9

for c in cur2.fetchall():
    print(c)
-- стопудово говорят, что "всё гут".

Я смог воспроизвести "слепоту" второго коннекта только извратным способом:
+
set term ^;
execute block as
begin
execute statement 'drop view v';
when any do begin end
end
^
set term ;^
commit;

recreate table t(a integer, b integer, c integer);
commit;
insert into t values(1,2,3);
commit;

recreate view v(a, b) as
select a, b from t;
commit;

set transaction read committed; -- no wait;

select current_connection as this_att, current_transaction as this_trn, v.* from v;

set term ^;
execute block returns(this_att int, this_trn int, a int, b int, c int) as
begin
for
execute statement ('select current_connection, current_transaction, v.* from v')
on external 'localhost:'||rdb$get_context('SYSTEM', 'DB_NAME')
as user 'SYSDBA' password 'masterke'
into this_att, this_trn, a, b
do suspend;

execute statement 'alter view v as select a, b, c from t' with autonomous transaction;

for
execute statement ('select current_connection, current_transaction, v.* from v')
on external 'localhost:'||rdb$get_context('SYSTEM', 'DB_NAME')
as user 'SYSDBA' password 'masterke'
-- role 'r2'
with autonomous transaction
into this_att, this_trn, a, b
do suspend;

end
^
set term ;^
Этот код покажет следующее:
 THIS_ATT     THIS_TRN            A            B
========= ============ ============ ============
2 14 1 2


THIS_ATT THIS_TRN A B C
========= ============ ============ ============ ============
3 16 1 2 <null>
3 18 1 2 <null>

Но терзают смутные сомнения, отчего тест, прогоняемый питоном, тоже "вдруг" слепой. Причём, только в одном месте - "ТАМ", на тестопрогонной тачке. У себя я проверял на двух машинах, 32 и 64 разр., на всех трёх арх-рах. И везде всё работает.

Загадко какое-то...
Вопрос: Почему нет привилегии ALTER VIEW?

Конструкция alter view есть как и alter table.
Ваши мысли, господа.
Ответ:
SY
Все равно будет recompile и потребуются все привилегии как и при create view.
Иными словами говнодизайн - причина отсутсвия, поскольку импакт от alter тот же что от create.
Тему можно закрывать.
Вопрос: ALTER SEQUENCE и репликация


Поделитесь здравым смыслом: если в одном запросе ALTER SEQUENCE есть кляузы RESTART WITH и
ENABLE REPLICATION (а перед этим репликация данной последовательности была запрещена), то
должно ли реплицироваться данное изменение её значения?

И наоборот: если репликация была разрешена, а этот запрос меняет значение и запрещает её,
надо ли реплицировать новое значение?

Posted via ActualForum NNTP Server 1.5

Ответ:
Dimitry Sibiryakov
Поделитесь здравым смыслом: если в одном запросе ALTER SEQUENCE есть кляузы RESTART WITH и
ENABLE REPLICATION (а перед этим репликация данной последовательности была запрещена), то
должно ли реплицироваться данное изменение её значения?


Должно реплицироваться.

Dimitry Sibiryakov
И наоборот: если репликация была разрешена, а этот запрос меняет значение и запрещает её,
надо ли реплицировать новое значение?

Спорно. Если генератор реплицировался,а потом решили её отключить, то наверно стоит записать текущее значение.
Вопрос: alter system flush shared_pool ...

команда alter system flush shared_pool - очищает кэш от запросов, хранящихся в нем
Прошу высказаться знающих в каких случаях ее нужно применять
Ответ:
AlexFF__|
Круто, одни делает alter system flush shared_pool на нагруженных системах, другие пакеты меняют.
И ведь верю, что так и есть )))

тебя в последнее время что-то еще удивляет после чтения тем на этом форуме? :)
Вопрос: upgrade: function digest does not exist

Всем привет.
Делаю upgrade c 9.4 на 9.5, но при апдейте вылазит ошибка в теме. Странно следующее: на старой базе extension pgcrypto не включён. Ладно, включаю и апгрейджу так:
1) на старой базе добавляю необходимый extension:
create extension pgcrypto;

проверяю работу:
postgres=# SELECT digest('blah', 'sha1');
                   digest
--------------------------------------------
 \x5bf1fd927dfb8679496a2e6cf00cbe50c1c87145
(1 row)

2) инициализирую пустую базу для 9.5:
/usr/postgres/9.5/bin/amd64/initdb /var/postgres/9.5/data_64 -E utf8

3) на новой базе добавляю необходимый extension:
create extension pgcrypto;

проверяю работу:
postgres=# SELECT digest('blah', 'sha1');
                   digest
--------------------------------------------
 \x5bf1fd927dfb8679496a2e6cf00cbe50c1c87145
(1 row)

4) запускаю upgrade:
/usr/postgres/9.5/bin/amd64/pg_upgrade -d /var/postgres/9.4/data_64 -D /var/postgres/9.5/data_64 -b /usr/postgres/9.4/bin/amd64 -B /usr/postgres/9.5/bin/amd64 -v


Вот полный текст ошибки:

pg_restore: [archiver (db)] could not execute query: ERROR:  function digest(bytea, unknown) does not exist
LINE 2:     SELECT encode(digest(convert_to($1, 'UTF8')::bytea, 'sha...
                          ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:
    SELECT encode(digest(convert_to($1, 'UTF8')::bytea, 'sha1'), 'hex')

CONTEXT:  SQL function "sha1" during inlining
    Command was:
-- For binary upgrade, must preserve pg_class oids
SELECT pg_catalog.binary_upgrade_set_next_index_pg_class_oid('553607281'...


То ли что-то забыл, то ли какой-то баг.
Заранее спасибо.
Ответ:
skeletor
function digest does not exist

function digest does not exist
Вопрос: IN vs. EXISTS + user function

Есть 2 таблицы и функция

drop table t1;
drop table t2;

create table t1 as select trunc(dbms_random.value(1,100)) as n from dual connect by level<=100000;
create table t2 as select trunc(dbms_random.value(1,100)) as n from dual connect by level<=10000;

create or replace function f(n number)
return number
is 
begin
  return 1;
end;
/


Пишем запрос:

select * from t2 where exists (select null from t1 where t2.n = f(t1.n))


План получается не ахти

Plan hash value: 2132554994
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   101 |   303 |   206   (1)| 00:00:03 |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T2   | 10000 | 30000 |     7   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T1   |     1 |     3 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter( EXISTS (SELECT 0 FROM "T1" "T1" WHERE "F"("T1"."N")=:B1))
   3 - filter("F"("T1"."N")=:B1)


При этом, если переписать на IN, то всё OK

select * from t2 where t2.n in (select f(t1.n) from t1)


Plan hash value: 3077929639
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   101 |   606 |    54   (2)| 00:00:01 |
|*  1 |  HASH JOIN SEMI    |      |   101 |   606 |    54   (2)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T2   | 10000 | 30000 |     7   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T1   |   100K|   292K|    46   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("T2"."N"="F"("T1"."N"))


Хинты USE_HASH, HASH_SJ не помогли.

Глюк оптимизатора?

P.S. 11.2.0.4 64-bit windows и linux
Ответ: В общем, проблема в функции.

Если заменить в 1м запросе user-defined функцию F на что-нибудь типа LENGTH, MOD, SIN и т.д., то запрос прекрасно UNNEST-ится.

трассировка 10053 говорит о том, что

*****************************
Cost-Based Subquery Unnesting
*****************************
SU: Unnesting query blocks in query block SEL$1 (#1) that are valid to unnest.
Subquery Unnesting on query block SEL$1 (#1)SU: Performing unnesting that does not require costing.
SU: Considering subquery unnest on query block SEL$1 (#1).
SU:   Checking validity of unnesting subquery SEL$2 (#2)
SU:     SU bypassed: Invalid correlated predicates.
SU:   Validity checks failed.


Кроме того, там же многократно встречается следующее сообщение:

PL/SQL function (F) is not secure.


Беглый поиск по гуглу ничё полезного не дал.
Буду копать дальше.