Просторы интернета
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 [...]
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 [...]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 [...]
The previous post on storing XML data in Oracle gave an overview of binary XML storage: compact, flexible, compatible with XML data with or without associated XML schema.
This post shows working examples of binary XML storage
1.1. without XML Schema
1.2 with XML Schema
The scripts below [...]
Oracle provides an abstract SQL data type called XMLType for storing XML data in the database. You can create an XMLType table, or an XMLType column in a relational table, to persist XML data.
Different storage models are available in Oracle to best fit the nature of the XML data [...]
Oracle has built-in functions to convert relational data into XML format easily. These functions comes under the umbrella of SQL/XML, a specification that supports the mapping and manipulation of XML from SQL.
This post shows you how to generate XML from relational data using Oracle SQL/XML functions as building blocks.
Let's [...]
Oracle's UTL_MATCH package contains functions to perform fuzzy matching between two strings based on one of these algorithms:
Levenshtein Distance Jaro-Winkler DistanceLet's understand the algorithms and see UTL_MATCH subprograms in action.
Note: It is not necessary to understand the nitty-gritties of the algorithms to use UTL_MATCH. The deep dive is for [...]
In a recent project, a "shadow" table had to be compared periodically with its main source table to identify the differences between the two tables.
The nature of differences fell into one of these buckets:
Insert Required [INS]: If the source had rows not present in the shadow table Update Required [UPD]: [...]It is well-acknowledged that attributes in the database should use the correct datatypes (numbers should go into NUMBER columns, dates into DATE columns, etc). Storing date values in VARCHAR2 columns is an open invitation for bugs and issues due to date format such as the one I'm about to [...]
Oracle 11G brought with it a beautiful new twist to range partitioning – the ability to create partitions on-the-fly rather than having to pre-create all partitions.
Hello interval partitioning.
Before Oracle 11G, DBAs would have to define all the partitions on a partitoned table and ensure there were no "cracks" between [...]
For those new to SQL, terms like INNER JOIN and OUTER JOIN can seem like fearsome foes. As the wise say, understanding conquers fear. Behind those geeky terms lie concepts rooted in simple real-world knowledge.
Here's a quickstart guide to these two basic joins in SQL: INNER JOIN and OUTER JOIN.
Case [...]When querying v$instance or v$session[1] as a non-admin user, you might come across this error:
SQL> select version 2 from v$instance; from v$instance * ERROR at line 2: ORA-00942: table or view does not existThe error suggests that the non-admin user does not have the SELECT privilege [...]
Just as you sometimes need to find out your database name when connected to Oracle, you may also need to find out your database version.
Here's a rundown of ways to identify the Oracle database version using SQL and PL/SQL.
V$VERSION displays version numbers of core library components [...]
I recently upgraded Oracle XE from 10G to 11G, and found that none of the PL/SQL code using UTL_HTTP was working after upgrade. The code failed with the error:
declare * ERROR at line 1: ORA-29273: HTTP request failed ORA-06512: at "SYS.UTL_HTTP", line 1130 ORA-24247: network access denied by access control list (ACL) ORA-06512: at [...]Sometimes we need to transpose columns as rows in SQL. Oracle 11G onwards, this requirement can be easily implemented using the UNPIVOT clause.
A typical scenario:
Customer data is validated against a set of audit checks and the validation results are stored against the Customer Id as Y/N flags. The checks [...]
Внутри потоки мусора, которые можно и не смотреть.
Данная заметка специально создается для нищебродов, которые воруют чужие материалы.
όΔΥΑ�Δ μΙΝΟΞΟΧ. όΤΟ Ρ — όΔΙήΛΑ
1. οτεμψ "χιξσμου ι εηο οβιτατεμι
π�ΟΘΟΔΡ ΝΕΦΔΥ ήΑΣΟΝ ΔΞΡ Ι Τ�ΕΝΡ ΠΟ νάΔΙΣΟΞ-ΑΧΕΞΐ, ΤΑΝ ΗΔΕ ΕΕ ΠΕ�ΕΣΕΛΑΕΤ
55-Ρ ΥΜΙΓΑ, ΞΕ ΠΟΜΕΞΙΤΕΣΨ, ΪΑΔΕ�ΞΙΤΕ ΗΟΜΟΧΥ Ι ΧΪΗΜΡΞΙΤΕ ΧΧΕ�Θ — ΞΑ ΞΕΝΩΤΩΕ
ΟΛΞΑ [...]
Let's say a table contains multiple rows for an id. The requirement is to select only one of those rows, based on the value in a "type" column which determines the row's priority.
A typical example is selecting one contact number for a customer, based on contact types.
The possible contact [...]
Sometimes you try to login to your Oracle XE on your home computer, and get this curt blocking response:
Here's the top reason why it might happen and how to fix it.
The first thing to check: is the Oracle service running? If you are seeing ORA-12560:TNS protocol [...]