The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My work

  • My badges

  • Twitter Updates

  • My Flickr Stream

    20140508-Delphi-2007--Project-Options--Cannot-Edit-Application-Title-HelpFile-Icon-Theming

    20140430-Fiddler-Filter-Actions-Button-Run-Filterset-now

    20140424-Windows-7-free-disk-space

    More Photos
  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,632 other followers

Archive for the ‘Database Development’ Category

Common Table Expressions: no nesting, but consecutively usage – via Stack Overflow

Posted by jpluimers on 2016/09/28

Common table expressions are awesome. They work in at least Oracle and SQL Server.

You cannot nest them, but you can use them consecutively. Thanks spender for explaining that:

WITH
x AS
(
SELECT * FROM MyTable
),
y AS
(
SELECT * FROM x
)
SELECT * FROM y

–jeroen

via: sql – Can you create nested WITH clauses for Common Table Expressions? – Stack Overflow.

Posted in Development, Database Development, SQL Server, SQL Server 2008, SQL Server 2005, SQL Server 2008 R2, SQL Server 2012, OracleDB, SQL Server 2014 | Leave a Comment »

Oracle Cross database queries: you need a “database link” for that

Posted by jpluimers on 2016/09/27

I recently learned that you can do cross database queries in Oracle using database links.

You need to prefix your objects with the right schema/owner (for instance dbo.) and suffix with an @ sign followed by the database link name.

This query finds all configured database links:

select * from all_db_links;

–jeroen

via: Ask Tom “database link”.

Posted in Database Development, Development, OracleDB | Leave a Comment »

Firebird gbak backing up a remote database that has spaces in the path and is on a remote Windows machine

Posted by jpluimers on 2016/09/13

I will likely need a batch file like this again:

setlocal
set gbakExe=C:\Program Files (x86)\Firebird\Firebird_2_5\bin\gbak.exe
set dbPart=MyDatabase
set dbSource=192.168.199.24:C:\Path with spaces\%dbPart%.fdb
set dbUser=sysdba
set dbPassword=masterkey
"%gbakExe%" -verify -transportable -user %dbUser% -password %dbPassword% "%dbSource%" %dbPart%.fbk
endlocal

You need the quotes around %gbakExe% and %dbSource% to ensure spaces in paths are preserved.

–jeroen

Sources:

Posted in Batch-Files, Database Development, Development, Firebird, Scripting, Software Development | Leave a Comment »

sql – Oracle to_date() to parse input to datetime values – via Stack Overflow

Posted by jpluimers on 2016/09/07

The Oracle to_date() function has saved me numerous times parsing date time strings into datetime values like this:

to_date('29/07/2015 13:18:00', 'DD/MM/YYYY HH24:MI:SS')

One gotcha is that when you query back the resulting date-time values, they might be formatted differently because of NLS_DATE_FORMAT settings

In addition to the query for ‘nls_date_format’ mentioned in sql – Oracle to_date() incorrect output – Stack Overflow, I usually use this one:

select v.*
from v$parameter v
where name like 'nls_%'
order by lower(v.name)
;

–jeroen

Posted in Database Development, Development, OracleDB, Software Development | Leave a Comment »

ORACLE-BASE – DDL With the WAIT Option (DDL_LOCK_TIMEOUT) in Oracle Database 11g Release 1

Posted by jpluimers on 2016/08/23

To work around this error:

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

ensure your DDL does wait a bit longer than the default of NOWAIT seconds:

alter session set ddl_lock_timeout=30
;
alter table TABLE_NAME
modify constraint CONSTRAINT_NAME enable
;
commit
;

via ORACLE-BASE – DDL With the WAIT Option (DDL_LOCK_TIMEOUT) in Oracle Database 11g Release 1.

Posted in Database Development, Development, OracleDB | Leave a Comment »

 
%d bloggers like this: