Archive for the ‘OracleDB’ Category
Posted by jpluimers on 2016/03/22
Even though as of 12c R1, Oracle supports a row limiting clause, NHibernate 4.2 with Oracle.DataAccess.dll 2.112.3.0 does not support that.
When you let it generate the SQL for a LINQ Take call to limit the number of results, you get an exception like this (full exception and stack trace are below):
System.NotSupportedException was unhandled by user code
HResult=-2146233067
Message=Specified method is not supported.
The place where you Take is important, as this does fail:
Read the rest of this entry »
Posted in .NET, .NET 2.0, .NET 3.0, .NET 3.5, .NET 4.0, .NET 4.5, .NET ORM, C#, C# 2.0, C# 3.0, C# 4.0, C# 5.0, C# 6 (Roslyn), Database Development, Development, NHibernate, OracleDB, Software Development | Leave a Comment »
Posted by jpluimers on 2016/03/17
When you have one app.config for your whole set of DTAP environments (develop/test/acceptance production), every once in a while you get this error:
ORA-12154: TNS:could not resolve the connect identifier specified
For this particular setup, it means the replacement of parameters in the app.config with actual values from the DTAP went wrong (or was missing). For that we’ve some steps in both the PreBuildEvent and PostBuildEvent of the .csproj file:
PreBuildEvent:
del "$(ProjectDir)$(TargetFileName).config"
copy "$(ProjectDir)app.config" "$(ProjectDir)$(TargetFileName).config"
PostBuildEvent:
powershell -noprofile -file Replace-Parameter.ps1 -Path "$(TargetDir)$(TargetFileName).config"
The PreBuildEvent looks like it is not needed, but sometimes Visual Studio forgets to perform the copy action.
–jeroen
Posted in .NET, .NET 2.0, .NET 3.0, .NET 3.5, .NET 4.0, .NET 4.5, .NET ORM, C#, C# 2.0, C# 3.0, C# 4.0, C# 5.0, C# 6 (Roslyn), Database Development, Development, NHibernate, OracleDB, Software Development | Leave a Comment »
Posted by jpluimers on 2016/02/18
An interesting tip at alter table drop column tips for Oracle: unused columns.
I knew about the multi-column syntax for drop column, but the unused columns:
You can also drop a table column by marking it unused and then dropping the column, thusly:
alter table
table_name
set unused column
column_name
;
They will appear in the DBA_UNUSED_COL_TABS view.
After that:
alter table
table_name
drop unused columns
;
You can perform the first statement for a few columns over time, then the last statement will delete them all.
Together with some other views like described in places like oracle – How to check if a column exists before adding it to an existing table in PL/SQL?, you can create nifty scripts for your meta-data maintenance.
–jeroen
via: alter table drop column tips.
Posted in Database Development, Development, OracleDB | Leave a Comment »
Posted by jpluimers on 2016/02/17
A wile ago, I had a this error when trying to get the TIME portion of a DATE column:
ORA-00904: "TIME": invalid identifier
This doesn’t work in Oracle, even though when you search for Oracle convert DATE to TIME you end up at this page listing TIME as a function: 12.7 Date and Time Functions. Alas, that page is for MySQL which is owned by Oracle for a while now.
Back to the query which was like this where date_column was of type DATE.
SELECT
id,
date_column,
TIME (date_column)
FROM some_table
That DATE type actually stores date+time, and since it was filled with Delphi TTime values, the date parts would always be “1899-12-30” (yes, I like ANSI DATE and TIMESTAMP formats). Oracle doesn’t get that, so I wanted to get the time portion.
Solutions:
Read the rest of this entry »
Posted in Database Development, Delphi, Delphi 10 Seattle, Delphi 2005, Delphi 2006, Delphi 2007, Delphi 2009, Delphi 2010, Delphi 7, Delphi XE, Delphi XE2, Delphi XE3, Delphi XE4, Delphi XE5, Delphi XE6, Delphi XE7, Delphi XE8, Development, OracleDB, Software Development | Leave a Comment »
Posted by jpluimers on 2015/10/27
Today it is about the curse of
ORA-12560: TNS:protocol adapter error
Don’t you love the overly generic error messages you often get, especially from Oracle.
We log the additional information which doesn’t bring much help either:
Errors:Oracle.DataAccess.Client.OracleErrorCollection; Number: 12560
There is so much that can cause the Oracle 12560 error (including spurious SSL things), that it is often like searching for a needle in a haystack.
What in fact happened is that in a few of our .NET config files got empty ConnectionString attributes for Data Source, User Id and Password as this fragment shows:
connectionString=”Data Source=; User Id=; Password=;”
The cause was a parameter substitution step in our build process where we generate each config file based on templates. It failed on some of them as this simple grep query can reveal:
grep -ind connectionstring\=.*\=; *.config
grep -indl connectionstring\=.*\=; *.config
The first one shows the files and lines, the second one only the files.
So we now have some guarding in place that will prevent these attributes to become empty.
–jeroen
Posted in .NET, .NET 4.0, .NET 4.5, C#, C# 3.0, C# 4.0, C# 5.0, C# 6 (Roslyn), Database Development, Development, OracleDB, Software Development | Leave a Comment »
Posted by jpluimers on 2014/04/08
Posted in Access, Database Development, DB2, Development, Firebird, InterBase, MySQL, OracleDB, PostgreSQL, SQL, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 7 | Leave a Comment »
Posted by jpluimers on 2014/03/19
Today, Version 4.4.5 of Database Workbench got released.
It mainly is a bugfix release (15 out of 18 issues were bugfixes, of the rest, 2 are feature requests, 1 enhancement): List of items new/enhanced/fixed in Product: Database Workbench v4 – version: 4.4.5.
Download from the trial/lite download page, or from the customer download page.
Enjoy (:
–jeroen
via: News @ Upscene Productions.
Posted in Database Development, Delphi, Development, Firebird, InterBase, MySQL, NexusDB, OracleDB, Software Development, SQL Server, Sybase SQL Anywhere | Leave a Comment »
Posted by jpluimers on 2014/01/22
A while ago, I came across an interesting post Bad Habits to Kick : Using AS instead of = for column aliases by Aaron Bertrand, a major contributor on SQLblog.com – The SQL Server blog spot on the web.
The last link indicates my problem with this “AS” versus “=”: it is SQL Server specific.
So, if you mainly use SQL Server, then it is OK (or even preferable) to use “=” for aliasing columns in human written SQL as it makes spotting the names used much easier. Read the rest of this entry »
Posted in Access, Database Development, DB2, Development, Firebird, InterBase, MySQL, OracleDB, Paradox, PostgreSQL, SQL, SQL Server, Sybase | Leave a Comment »
Posted by jpluimers on 2013/08/22
I like this Delphi program very much: it is one of the database tools with the widest support of back-ends, and friendliest user interface I know.
Oh, and it is by a great Dutch company too: UpScene (:
So this is their release information:
2013-08-19:
This new release of Database Workbench brings new features and fixes for issues reported by our users.
The free Lite Editions will be released later.
Multi-DBMS developer tool
Database Workbench works natively with:
- Oracle Database
- Microsoft SQL Server
- Sybase SQL Anywhere
- MySQL
- Firebird
- InterBase
- NexusDB
More information about Database Workbench is available at the Database Workbench page, download your copy today via our downloads page, pricing information is available.
This release includes fixes for the InterBase, Firebird, MySQL and Microsoft SQL Server modules, as well as general fixes and small new features.
The full details and list of changes in 4.4.1 is available here.
–jeroen
via: News @ Upscene Productions.
Posted in Database Development, Delphi, Development, Firebird, InterBase, MySQL, OracleDB, Software Development, SQL, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, Sybase | 2 Comments »
Posted by jpluimers on 2013/01/18
In most database index nodes are doubly linked to allow bi-directional scans. http://is.gd/8CMb7w, however not for InterBase and FireBird, there the reverse link isn’t used because it can be inconsistent due to write order of index pages.
The result is that in Firebird and InterBase, indexes are single-directional (either ascending or descending).
This is for your safety: it guarantees index consistency, even if because of EMP, your machine suddenly reboots after your tank fired a missile.
–jeroen
via Twitter / Avalanche1979: @SQLPerfTips For Firebird the ….
(Wow, did I really wrote 1200 blog posts?)
Posted in Database Development, DB2, Development, Firebird, InterBase, MySQL, OracleDB, PostgreSQL, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 7, Sybase | Leave a Comment »
The Wiert Corner - irregular stream of stuff 