Все технические форумы на одном сайте Удобный поиск информации с популярных форумов в одном месте
Вопрос: ALTER EXTERNAL FUNCTION (параметры функции) в 3.0

Все перечитал - ничего не нашел.
Слабая надежда осталась на форум - может все-таки возможно как-то.

Надо обновить параметры функций UDF без DROP/DECLARE (это очень сложно - перелапачивать все зависимости, их десятки-сотни). А именно - мне нужно только установить флаг FREE_IT у результата у нескольких ф-ций одной удфки.

Для < 3.0 работал скрипт (это обновление):
UPDATE RDB$FUNCTION_ARGUMENTS R
SET R.RDB$MECHANISM = -1
WHERE R.RDB$ARGUMENT_POSITION = 0
  AND R.RDB$FIELD_TYPE IN ( 35, 40 )
  AND...


Как теперь быть, не знаю. Пока не решился воротить огромный скрипт удаления/создания процедур, триггеров и вьюх - на это уйдет не один день кропотливого труда. Тем более, что, у меня всех этих данных и нет - наверно даже прогу придется писать.

Может, как-то можно все-таки?
Ответ:
miwaonline
Симонов Денис
что-то мне кажется что это можно сделать и внутри PSQL функций, при небольшом оверхеде или даже вовсе без оного

Судя по и реализуется все тривиально и оверхед найдет разве что Таблоид


ну если реально использовать затабулированные данные, то не уверен, что только Таблоид. Я вообще-то предполагал, что там просто формула какая-то.
Вопрос: Internal и external function с одинаковым названием

FB 3.0. В базе есть UDF с именем FORMAT_DATE. И я создал FUNCTION с именем FORMAT_DATE. Теперь я не могу сделать вызов UDF, так как она перекрывается обычной функцией. Надо ли идти с этим в трекер?
Ответ:
Симонов Денис
В UDF ты не можешь нормальными способами:


в скольких процентах случаев это реально нужно?

Arioch
простота [UDF] - сама по себе достоинство.
Вопрос: External table - optionally enclosed by сдвигает столбцы

oracle 10gR2

есть файл с разделителями "знак TAB"

Alvin	Tolliver	1976
Baer		1234
Mary	Dube	1973

(пример отсюда)


1) External table без optionally enclosed by - все хорошо:

CREATE TABLE SCOTT.emp_load (first_name CHAR(15), last_name CHAR(20), year_of_birth INT)
  ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY "FILE_DIR"
                         access parameters (
                          RECORDS DELIMITED by X'0D0A' 
    BADFILE 'test.bad' DISCARDFILE 'test.dsc' logfile 'test.log'
                         FIELDS TERMINATED by X'09' 
                         MISSING FIELD values ARE null)
                         LOCATION ('test.txt'));

FIRST_NAME      LAST_NAME            YEAR_OF_BIRTH          
--------------- -------------------- ---------------------- 
Alvin           Tolliver             1976                   
Baer                                 1234                   
Mary            Dube                 1973    


2) добавим optionally enclosed by - год съезжает на колонку влево - на место пропущенного LAST_NAME!!!

CREATE TABLE SCOTT.emp_load (first_name CHAR(15), last_name CHAR(20), year_of_birth INT)
  ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY "FILE_DIR"
                         access parameters (
                          RECORDS DELIMITED by X'0D0A' 
    BADFILE 'test.bad' DISCARDFILE 'test.dsc' logfile 'test.log'
                         FIELDS TERMINATED by X'09' optionally enclosed by '"'
                         MISSING FIELD values ARE null)
                         LOCATION ('test.txt'));


FIRST_NAME      LAST_NAME            YEAR_OF_BIRTH          
--------------- -------------------- ---------------------- 
Alvin           Tolliver             1976                   
Baer            1234                                        
Mary            Dube                 1973  


как бороться?
спасибо!
Ответ:
alex-ls
MISSING FIELD VALUES ARE NULL
и
OPTIONALLY ENCLOSED BY
вызывает сдвиг данных в другой столбец... к сожалению в данных есть и пустые значения и значения с кавычками.

Ну я бы для начала попробовал поиграть с режимами trim - возможно, явное указание "не резать бланки" позволит сохранить утерянный разделитель-табуляцию.
Вторым заходом убрал бы правило missing filed values - оно касается неполных строк, т.е. тех, в которых не хватает, собственно, полей (справа, в хвосте строки).
Третий хоп - убрать "optionally enclosed" и резать кавычки trim-ом в sql expression
Четвертый вариант - безотказный, но хлопотный - препроцессинг любой подходящей утилитой:
- замена разделителя
- приведение enclosing к единому виду
- добивание недостающих полей справа, if any
Вопрос: 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.


Беглый поиск по гуглу ничё полезного не дал.
Буду копать дальше.
Вопрос: как собрать статистику на external table c ORACLE_DATAPUMP ?

подскажите как собрать статистику
begin DBMS_STATS.gather_table_stats (ownname => 'OWNER',tabname => 'TABLE_EXT'); end;

[Error] Execution (3: 1): ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04084: The ORACLE_DATAPUMP access driver does not support the ROWID column.
ORA-06512: at "SYS.DBMS_STATS", line 23829
ORA-06512: at "SYS.DBMS_STATS", line 23880
ORA-06512: at line 1


таблица создана так
CREATE TABLE TABLE_EXT
(
...
)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_DATAPUMP
     DEFAULT DIRECTORY PATH1
     ACCESS PARAMETERS 
       (  )
     LOCATION (PATH1:'file.dmp')
  )
REJECT LIMIT 0
NOPARALLEL
NOMONITORING;
Ответ: joiner_plus,

KUP-04084: The ORACLE_DATAPUMP access driver does not support the ROWID column.
Cause: A query of an external table of type ORACLE_DATAPUMP tried to reference the ROWID column. The ORACLE_DATAPUMP access driver does not support ROWIDs.
Action: Remove the ROWID from the query. If that is not possible, create a table in the database with the same columns as the external table, copy the data from the external table into the newly created table, and use that new table for the query.
Вопрос: external table

Всем привет !
Обнаружил проблему в тестах с не читаемой external table, вычислил её и по ошибке дропнул в продакшине, как следствие один из пакеджей стал unusble ... Сегодня после лоада всё нормально, пакедж опять нормальный.
В связи с этим вопросы :
Нужно ли вообще восстанавливать таблицы external в такой ситуации ?
Если да, то как сделать это быстро ? Flashback ?
Можно ли с LogMiner ?
Ответ: P.S.
Кто-то или что-то восстановило за вас метаданные external table в базе...
Вопрос: Использование функции Trim

Всем привет. Помогите салаге. Не пойму чего от меня Access хочет. Как ему еще Тrim записать? Пишет ошибка компиляции.

SELECT authors.au_fname, TRIM([authors.au_lname]) AS Выражение1, authors.city, authors.state
FROM authors
ORDER BY authors.state, authors.city DESC;
Ответ:
ИгорьST
Скрин в студию, где это написано. Чтобы все знали....
Сегодня мучал на работе, так и не нашел Trim. Зато RTrim работает без проблем.Хрень какая-то.


См. ответ на вопрос Q1
Вопрос: 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
Вопрос: external tables и csv-файлы

Добрый день! Попытался организовать загрузку данных из csv файла в таблицу оракла. Но столкнулся с трудностями.

Таблица:

create table import_ext_tbl
(text varchar2(2000))
organization external
(type oracle_loader
default directory FINCOM
access parameters
(records delimited by newline
badfile 'bd.bad'
logfile 'lg.log'
skip 1
fields terminated by ''
missing field values are null
)
location('demo1.csv')
);


при выполнении запроса , приходит пустой результат... а в лог файле сохраняется такая пометка
============================
LOG file opened at 07/07/15 17:15:07

Field Definitions for table IMPORT_EXT_TBL
Record format DELIMITED BY NEWLINE
Data in file has same endianness as the platform
Rows with all null fields are accepted

Fields in Data Source:

TEXT CHAR (2000)
Terminated by ""
Trim whitespace same as SQL Loader
============================
понимаю что это связано с fields terminated by '' , но какой должен быть разделитель если у меня в csv файле всего один текстовый столбец? Подскажите пожалуйста, уже неделю с этим вожусь. раньше не доводилось работать с внешними таблицами...


структура csv файла следующая: один столбец, много строк. каждая строка это значения одной ячейки столбца.
пример:
hghhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhllllllllllllllll
2222222222222222222222222gfggggggggggggggggggggggggggggggggggggggggggggggggg
gggghjjhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh
Ответ: а чем просто fields (поле char(100500)) не устраивает без всяких делимитеров и терминаторов?
Вопрос: Ветвление в табличной функции (Inline Table-Valued Function)

Здравствуйте.
Помогите, пожалуйста, новичку.
Как в создаваемую табличную функцию (Inline Table-Valued Function) добавить ветвление в зависимости от введенного параметра. Ниже в коже прилагаю пример создания функции MonthList, которая выводит список месяцев в зависимости от введенного параметра - номера квартала (@QNum). Значение по умолчанию 0. Необходимо сделать так, чтобы при использовании параметра по умолчанию, функция возвращала полный список месяцев.

Заранее благодарен за помощь.

IF OBJECT_ID(N'Months', N'U') IS NOT NULL
	DROP TABLE Months;
GO
CREATE TABLE Months (MonthNum tinyint, QuarterNum tinyint);
GO
DECLARE @month as tinyint
SET @month = 1
WHILE @month <=12
	BEGIN
		INSERT INTO Months(MonthNum, QuarterNum)
		VALUES (@month
				,CASE
					WHEN @month <=3 THEN 1
					WHEN @month <=6 THEN 2
					WHEN @month <=9 THEN 3
					WHEN @month <=12 THEN 4
				END);
		SET @month = @month+1
	END;
GO

IF OBJECT_ID(N'MonthList', N'IF') IS NOT NULL
	DROP FUNCTION MonthList;
GO

CREATE FUNCTION MonthList(@QNum tinyint = 0)
RETURNS TABLE
AS
RETURN
-- ВЕТВЛЕНИЕ: ЕСЛИ @QNum = 0, то в запросе отсутствует оператор WHERE
-- и функиця возвращает все месяца
	(
	SELECT M.MonthNum
	FROM Months M
	WHERE QuarterNum = @QNum
	);
GO

SELECT * FROM MonthList(DEFAULT);
GO
Ответ: aleks2,

some people never learn.

Для тех, кто не писатель: в приведенном примере по плану видно, что дополнительное условие очень даже убрано оптимизатором внутрь функции. Для активных членов лиги лени - вот запрос, переделанный под использование функции (чтоб уж наверняка)

create table ttt
(
	id int, n int, primary key(n,id)
)

insert into ttt
select top(12) ROW_NUMBER() over(order by 1/0), 1
from master..spt_values
	union all
select top(12) ROW_NUMBER() over(order by 1/0), 2
from master..spt_values

go

create function f(@x int) returns table as
return (
	select * from ttt
	where @x = 0
		union all
	select * from ttt
	where @x <> 0 and id = @x
)
go


declare @x int = 5

select * from dbo.f(@x)
where n = 1

drop function dbo.f
drop table ttt