Просторы интернета

19 Февраль 2015
Ср, Дек 13, 2017

Alex R recently discovered interesting thing: in SQL pipelined functions work much faster than simple non-pipelined table functions, so if you already have simple non-pipelined table function and want to get its results in sql (select * from table(fff)), it's much better to create another pipelined function [...]

Source: orasql.org
Вт, Дек 12, 2017
Extract long string or CLOB from JSON

"Wheels within wheels", as Monty Bodkin would say. Extracting a very long string or CLOB from a JSON CLOB (very long => larger than max_string_size of 32767), in a pre-12.2 Oracle database, turned out to be more complex than it appeared at first sight.

This case study shows how to [...]

Source: www.oratable.com
Вс, Дек 10, 2017

When “serveroutput” is enabled, SQL*Plus executes “BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;” after each command.
That's why I don't like when it is always enabled: it adds extra calls and round-trips and it is inconvenient when I want to get a plan of the last executed query:

SQL> set serverout on; SQL> select * from [...]
Source: orasql.org
Сб, Дек 09, 2017

This interesting question was posted on our russian forum yesterday:

We have a huge PL/SQL package and this simple function returns wrong result when it's located at the end of package body:

create or replace package body PKGXXX as ... function ffff return number is nRes [...]
Source: orasql.org
Ср, Дек 06, 2017
Convert Relational Data to JSON

In the JSON series so far, we've talked about how to store JSON data in Oracle and apply JSON conditional checks, and how to query JSON data and convert it to relational form. What if you want the opposite i.e. to convert relational data to JSON form? [...]
Source: www.oratable.com
Пт, Ноя 24, 2017

Sometimes it's really hard even to create reproducible test case to send it to oracle support, especially in case of intermittent errors.
In such cases, I think it would be really great to have access to similar service requests or bugs of other oracle clients.
So while my poll [...]

Source: orasql.org
Ср, Ноя 22, 2017

In the last two articles, we saw the means and reasons for storing JSON data in Oracle and ways of retrieving JSON data from Oracle. In this article, we will explore ways of implementing true/false tests on JSON data using conditionals: JSON_EXISTS, JSON_TEXTCONTAINS.

JSON conditionals check for the existence [...]

Source: www.oratable.com
Вт, Ноя 21, 2017
Querying JSON Data in Oracle

The last article talked about how to store JSON in the database. This article shows you how to retrieve it meaningfully using various query approaches in Oracle 12c.

For the demo, we'll use the same old CUSTOMER table with JSON metadata.

The table:

-- Customer table DDL with JSON metadata CREATE [...]
Source: www.oratable.com
Вс, Ноя 19, 2017

In one of the previous posts I showed How even empty trigger increases redo generation, but running the test from that post, I have found that this behaviour a bit changed on 12.2:
In my old test case, values of column A were equal to values of B, [...]

Source: orasql.org
Вс, Ноя 19, 2017

On versions – Oracle uses objects' statistics to make direct path reads decision (of course, if “_direct_read_decision_statistics_driven” haven't changed it to “false”), and we can force serial direct reads on statement level using sql profiles with hints INDEX_STATS/TABLES_STATS, but since at least this decision ignores statistics.

Btw, thanks [...]

Source: orasql.org
Чт, Ноя 16, 2017
JSON in the database

JSON is a simple data interchange format, an alternative to XML that's gaining wider favor by the day especially for big data storage and REST web services. With release 12c, Oracle has introduced JSON support too – handy new features for storage and retrieval of JSON data.

Here's a [...]

Source: www.oratable.com
Чт, Ноя 16, 2017
select s.module ,ceil(max(s.rows_processed/s.fetches)) rows_per_fetch from v$sql s where s.rows_processed>100 and s.executions >1 and s.fetches >1 and s.module is not null and s.command_type = 3 -- SELECTs only and s.program_id = 0 [...]
Source: orasql.org
Сб, Ноя 11, 2017
Полное атомарное обновление матвью / Complete Atomic MV Refresh: большую часть времени тратит на непараллельное выполнение запроса на вставку: , который в свою очередь, в основном ожидает db file scattered read: — что нормально и непротиворечиво Однако, отдельное выполнение того же запроса из SQL*Plus происходит почти в 5 раз быстрее: [...]
Source: iusoltsev.wordpress.com
Пт, Ноя 10, 2017

Oracle 12.2 has introduced a number of new features that ease partitioning, the most-awaited perhaps is the ALTER TABLE MODIFY syntax to convert a non-partitioned table to partitioned.

What do you do if your database version is pre-12.2? Partitioning a non-partitioned table in pre-12.2 databases is trickier, not [...]

Source: www.oratable.com
Вс, Окт 22, 2017
При установленном параметре: параллельное построение индекса легко распеределяется между нодами RAC: , несмотря на исправленный Bug 16458105 — PARALLEL_FORCE_LOCAL=true not honoured for ALTER INDEX — superseded (Doc ID 16458105.8): — что отражено/соответствует документу Patch Set — List of Bug Fixes by Problem Type (Doc ID 1683802.1) Реклама Filed under: [...]
Source: iusoltsev.wordpress.com
Пн, Окт 02, 2017

Unfortunately associative arrays still require more “coding”:
we still can't use “indices of” or “values of” in simple FOR(though they are available for FORALL for a long time), don't have convinient iterators and even function to get all keys…
That's why I want to show my templates for such things like iterator [...]

Source: orasql.org
Пт, Сен 29, 2017

I see quite often when developers ask questions about connected components:

Table “MESSAGES” contains fields “SENDER” and “RECIPIENT”, which store clients id.
How to quickly get all groups of clients who are connected even through other clients if the table has X million rows?
So for this table, there should be 4 groups: (1, [...]
Source: orasql.org
Ср, Сен 27, 2017

I've troubleshooted one query today and I was very surprised that bind variables in this query were specified with &ersand instead of :colon! I have never seen this before and I couldn't find anything about this in documentation…
Unfortunately SQL*Plus doesn't support ampersand yet, even if you disable define (“set define [...]

Source: orasql.org
Пн, Сен 25, 2017
Stored Procedures in Packages

Typical scenario: in a project's design phase, procedureA is meant to be placed in packageX. During implementation, packages are refactored: packageX gets split into packageY and packageZ. All goes well – the application gets deployed and is running merrily – till a change request comes in.

A new developer refers [...]

Source: www.oratable.com
Чт, Сен 14, 2017
with tst as ( select 'qwe word1 asd ...............' s from dual union all select 'qwe word1 asd word2 .........' s from dual union all select 'qwe word1 asd word2 zxc word3' s from dual union all select 'qwe word2 asd word1 zxc [...]
Source: orasql.org
Пт, Сен 01, 2017
Proxy User Authentication in Oracle

Proxy user authentication is a powerful feature in Oracle that lets one database user (proxy user) connect to the database "on behalf of" another user (client user).

With proxy login, the proxy user can act as if it is the client user with all of the client user's access rights, without [...]

Source: www.oratable.com
Ср, Авг 16, 2017
XMLTABLE to Convert XML To Relational Data

Here's a prototype for using the SQL/XML function XMLTABLE to map XML data into relational rows and columns.

This solution uses the standard EMP table – the same can be extended to work with any XMLTYPE-relational mapping.

Problem Statement: Receive XML Payload, Parse and Store in Relational Table

An application receives employee data [...]

Source: www.oratable.com
Пт, Авг 11, 2017
Dynamic SQL

A comparison between static SQL vs dynamic SQL shows us pretty clearly that, if there exists a choice, we are better off choosing static SQL.

So, when should we use dynamic SQL?

Oracle documentation tells us that we need dynamic SQL to run:

SQL whose text is unknown at compile [...]
Source: www.oratable.com
Чт, Авг 10, 2017

I've reviewed this book recently, and I highly recommend it as it has almost all that needed to become strong Oracle developer. You can check at least the table of contents:

ORACLE SQL & PL/SQL Golden Diary: by Asim Chowdhury
New Book Demystifies Complex Cross-Version Oracle Problem Solving

Compiled [...]

Source: orasql.org
Ср, Июл 26, 2017

Some questions do not have definitive answers. "Is a full table scan bad? Should this design be denormalized? Will partitioning this table help?" The answers vary widely depending on the specifics on the problem.

Fortunately, "Should I use static SQL or dynamic SQL?" is not one of those questions.

You can come [...]

Source: www.oratable.com
Пт, Июл 14, 2017
В PDB snapshot copy окружении версии Артём Горбик показал странно отображаемую сессию-блокер: — формально работающую в CDB$ROOT: , и в то же время выполняющую Scheduler Job в контейнере-клоне: Модифицировал под это дело скрипт (с суффиксом _CDB), добавив к инстансу/сессии (INST#/SID#4) номер контейнера — CON#: — и в выводе скрипта [...]
Source: iusoltsev.wordpress.com
Чт, Июл 13, 2017
на physical standby / ADG instance в виде: , как нам с Максимом Филатовым удалось выяснить, вызывается установкой параметра: и, соответственно, лечится (по рекомендации техподдержки): В общедоступном описании бага: о параметре не упомянуто, в то время как в документах типа ORA-1555 Error With Query Duration as 0 Seconds (Doc ID [...]
Source: iusoltsev.wordpress.com
Ср, Июн 28, 2017

SQLCODE and SQLERRM are Oracle's built-in error reporting functions in PL/SQL.

When an error occurs in PL/SQL at runtime:

SQLCODE returns the number of the last encountered error.

SQLERRM returns the message associated with its error-number argument. The error-number argument is optional: if omitted, SQLERRM returns the message associated with the current [...]

Source: www.oratable.com
Пт, Июн 16, 2017
Check if JVM is Installed in Oracle

I recently thought of using Java's built-in BigInteger.gcd() method inside a PL/SQL wrapper to calculate GCD in SQLs.

And so I wrote the Java code and ran it on Oracle XE.

Things didn't go as planned.

SQL> -- Create java source for GCD calculation SQL> create or replace and resolve java [...]
Source: www.oratable.com
Вт, Июн 13, 2017
Oracle XML Object-Relational Storage

After the post on models for storing XML data in Oracle and examples of binary XML storage, here's a detailed look at the rigorous and performant structured XML storage or object-relational storage.

Object-relational storage of XML data is based on "shredding" the XML content into a set of SQL [...]

Source: www.oratable.com
Комментирование отключено.