Pages

▼
Showing posts with label Oracle. Show all posts
Showing posts with label Oracle. Show all posts
26/05/2018

[Oracle] Verify if column has not null check

›
To test whether a column in an Oracle DB has been declared with a NOT NULL check, it is possible to query the dba_tab_columns table:   SE...
17/10/2017

[PL/SQL] Convert number to binary string

›
Here is a simple function to convert a number to a binary string in Oracle PLSQL. This can also easily be converted to an number array inste...
2 comments:
20/03/2017

[SQL] Excel COUNTIFS - count columns matching criteria in a row

›
Excel in his arsenal of useful functions has COUNTIFS , basically a count of how many elements in a one dimensional range match a specific c...
09/01/2017

[Oracle] Aurora module exception when compiling Java code on the DB

›
Sometimes it is possible to receive an exception when compiling Java code or running tools that generate Java code on the Oracle DB with thi...
10/12/2016

[Oracle] Initialise DB statistics to improve performance

›
A couple days ago we were analysing a customer issue concerning DB performance on simple and already optimised queries (indexes + hints). ...

[Oracle] List uploaded Java resources

›
After extending functionality on an Oracle DB with Java resources , it is possible to list the available ones and their status with a query ...
08/10/2016

[Oracle] SQL SELECT cast to table

›
In Oracle it is possible to cast one type to another simply by using the CAST keyword. This also applies to collections, meaning that the...
23/09/2016

[PL/SQL] existing state of package has been invalidated error

›
After compiling Oracle packages, it is possible to get a "existing state of packages has been discarded" or "existing state o...
21/07/2016

[TOAD] Oracle explain plan error looping chain of synonyms - fix

›
TOAD is a powerful client for Oracle DB, although I have to admit SQLDeveloper is not that bad either (and it's also free). Sometimes ...
2 comments:
31/03/2016

[Oracle] Search in sources

›
In Oracle it's possible to search for text in source files with a query on the all_source object. Of course the resulting data will onl...
19/03/2016

[PLSQL] Split string by delimiter and store it in VARRAY

›
Here is some sample Oracle PL/SQL code to split a string using a specific delimiter and storing the result pieces in a VARRAY. DECLARE ...

[SQL] Oracle count elements in nested table

›
When working with collections in Oracle, you might need to count the number of elements they contain. In the case of Nested Tables , you ...
03/08/2015

[Oracle] Purge schema

›
Purging a schema in Oracle isn't a straightforward procedure. Usually it's better to DROP the schema or the USER and recreate it. ...
14/07/2015

[Oracle] wget or curl binaries from OTN website

›
Usually when you try to download Oracle's JDK or Oracle's DB driver jars you need to manually accept a license agreement before yo...
04/07/2015

[Oracle] Remove tablespace with missing DBF file

›
Well, nobody's perfect. But if a software is good we can afford not to be flawless. Say instead of dropping a tablespace  the proper w...
26/06/2015

[SQL] Oracle subquery in join statement

›
In Oracle, it's possible to use sub queries in a join statement by giving an alias to the subquery and joining on that alias: SELECT...
29/11/2014

[Oracle DB] Create and schedule a job

›
To create and schedule a basic job on an Oracle DB, you'll need to create: A script performing the desired action A schedule A job ...
08/11/2014

[Oracle] Run commands in different schema

›
In Oracle, it is possible to change the current schema/user with the ALTER SESSION statement: ALTER SESSION SET CURRENT_SCHEMA = new_sche...
18/10/2014

[SQL] Escape special characters in LIKE statement

›
When using the LIKE statement in a SQL query, there are special characters which have a particular meaning such as _ (single character) and...
05/09/2014

[Oracle] Stop/start database and listener from command line

›
You can start or stop an Oracle database instance (you can dbca to configure one) from command line via the SQLPlus tool by connecting to...