Showing entries 11 to 20 of 32
« 10 Newer Entries | 10 Older Entries »
Displaying posts with tag: Oracle 11g (reset)
Excel PowerPivot & DAX

I’ve worked with every release of Microsoft Excel, and I know it takes effort to keep up to date with certain releases. Clearly, the Data Analysis eXpression (DAX) Language introduced in Excel 2010 went unnoticed by many, which was sad. DAX is truly a powerful extension to the analytical and modeling approaches in Microsoft Excel.

I’d like to recommend Microsoft Excel 2013 Building Data Models with PowerPivot to those who haven’t learned how to use DAX in Excel 2010, 2011, or 2013. DAX works with tables but if you don’t use tables, I guess you can skip DAX …

[Read more]
Zend 6 & Timezones

Just went through all my PHP testing against a fresh instance of Oracle with Zend Server Community Edition 6, and found these warnings, guess that’s pretty clean for the Oracle part of the installation. I didn’t notice it before because generally I do most of my PHP development against a MySQL database. I should have been configuring the php.ini file routinely, as qualified in this PHP forum discussion.

Warning: oci_set_client_info(): It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected the timezone 'UTC' for now, but please set date.timezone to select your timezone. in C:\Program Files …
[Read more]
Verifying a Socket w/Perl

Using a lowercase hostname is typical but I got sloppy on a Windows 7 installation, after all Windows is case insensitive, and I used a mixed case hostname. It raised an interesting error when installing Oracle Database 11g Release 2.

Enterprise manager configuration failed due to the following error -

Failed to allocate port(s) in the specified range(s) for the following process(es): JMS
[5540-5559], RMI [5520-5539], Database Control [5500-5519], EM Agent [3938] | [1830-1849]

Refer to the log file at C:\app\McLaughlinM\cfgtoollogs\dbca\orcl\emConfig.log for more details.

You can retry configuring this database with Enterprise Manager later by manually running C:\app\McLaughlinM\product\11.2.0\dbhome_1\bin\emca script.

After verifying the ports …

[Read more]
Oracle and Java Tutorial

I’m posting this because of a question raised against this older post on how to configure the %CLASSPATH% to find the ojdbc6.jar file. This is the lab file I use in my Database 1 class to expose students to the moving parts of writing Java programs against the Oracle database. That’s why I choose to use a CLOB data type, which requires Oracle’s DBMS_LOB package and wrapping stored procedures.

If you want the same content for MySQL, here’s the link. The full program in either blog entry is available by clicking on the fold/unfold Java Source Code Program widget at the bottom of the respective posts.

This demonstrates how to create an Java infrastructure for reading …

[Read more]
Free Oracle PHP Book

Six years ago, I wrote Oracle Database 10g Express Edition PHP Web Programming for the release of the express edition. It was a lot of fun to write because I enjoy the PHP programming language, but unfortunately sales didn’t measure up too well. That’s probably because the population of PHP developers working with Oracle was small.

Today it seems there are more PHP developers working with Oracle 11g. While the population of PHP community for Oracle 11g is still smaller than for MySQL, it continues to grow year-over-year.

The FREE

[Read more]
Derived Table Aliases

In my database class, students write solutions as group exercises against the Oracle 11g XE database and then they port the solution individually to the MySQL 5.5 database. One of the students copied over a query like the one below to MySQL (a query used to track the expected number of row returns).

SELECT   COUNT(*)
FROM    (SELECT   DISTINCT
                  k.kingdom_id
         ,        kki.kingdom_name
         ,        kki.population
         FROM     kingdom_knight_import kki LEFT JOIN kingdom k
         ON       kki.kingdom_name = k.kingdom_name
         AND      kki.population = k.population);

It got an error they didn’t understand:

ERROR 1248 (42000): Every derived TABLE must have its own alias

Providing a dt query alias fixes the problem in MySQL for the following query. The fact that it was just an alias was a revelation to the student. That’s because Oracle databases don’t require …

[Read more]
Single Wildcard Operator

Somebody wanted to understand why you can backquote a single wildcard operator (that’s the underscore _ character) in MySQL, but can’t in Oracle. The answer is you can in Oracle when you know that you required an additional clause.

While I prefer using regular expression resolution, the LIKE operator is convenient. Here’s an example of backquoting an underscore in MySQL, where it looks for any string with an underscore anywhere in the string:

SELECT   common_lookup_type
FROM     common_lookup
WHERE    common_lookup_type LIKE '%\_%';

You can gain the same behavior in Oracle by appending the ESCAPE '\' clause, like this:

SELECT   common_lookup_type
FROM     common_lookup
WHERE    common_lookup_type LIKE '%\_%' ESCAPE '\';

The ESCAPE '\' clause is one of those Oracle details that often gets lost. It only works when the SQL*Plus ESCAPE

[Read more]
Result Cache Functions

I finally got around to cleaning up old contact me messages. One of the messages raises a question about RESULT_CACHE functions. The writer wanted an example implementing both a standalone schema and package RESULT_CACHE function.

The question references a note from the Oracle Database 11g PL/SQL Programming book (on page 322). More or less, that note points out that at the time of writing a RESULT_CACHE function worked as a standalone function but failed inside a package. When you tried it, you raised the following error message:

PLS-00999: Implementation Restriction (may be temporary)

It’s no longer true in Oracle 11gR2, but it was true in Oracle 11gR1. I actually mentioned in a …

[Read more]
Oracle Within Group

Somebody asked me for a useful example of Oracle 11gR2′s new analytical LISTAGG function that uses a WITHIN GROUP syntax. They’d noticed an update to the askTom that showed how to use it. This post shows how to list values without a displayed aggregation column and how to use a JOIN and GROUP BY clause with the new analytical feature.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
COLUMN list      FORMAT A10
COLUMN last_name FORMAT A10
COLUMN names     FORMAT A42
COLUMN members   FORMAT 9,990
 
 
SELECT   m.account_number AS account
,        c.last_name AS last_name
,        LISTAGG(c.first_name||DECODE(c.middle_name,NULL,NULL,' '||SUBSTR(c.middle_name,1,1)||'.'),', ')
           WITHIN GROUP (ORDER BY 2) AS names
,        COUNT(*) AS members
FROM     contact c INNER JOIN member …
[Read more]
Oracle CSV Imports

The first step in creating an effective import plan for comma-separated value (CSV) files is recognizing your options in a database. There are several options in an Oracle database. You can read the file with Java, C/C++, C#, PL/SQL (through the UTL_FILE package), PHP, Perl, or any other C-callable programming language; or you can use SQL*Loader as a standalone utility or through externally managed tables (known as external tables). The most convenient and non-programming solution is using external tables.

Adopting external tables as your import solution should drive you to consider how to manage the security surrounding this type of methodology. Host hardening is a critical security step because it shuts down most, hopefully all, unauthorized use of the operating system where the database and external files reside. Next, you need to manage the access to the external tables and ensure that exposure of business sensitive information …

[Read more]
Showing entries 11 to 20 of 32
« 10 Newer Entries | 10 Older Entries »