OraNA :: Oracle News Aggregator

While I tried to deflect how you perform SQL Injection attacks against a MySQL procedure, my students requested that I post examples of what to do to avoid SQL injection, and what not to do to invite attacks. The best practice to avoid SQL injection attacks is too always bind (more...)

Sometimes interesting problems lead to shock or dismay at the suppositions of why they occur. Why an ORA-22979 is raised is one of those, and the error is typically:

ERROR at line 1:
ORA-22979: cannot INSERT object VIEW REF OR user-defined REF

This error (more...)

Somebody was trying to create a striped view based on a table’s start_date and end_date temporal columns. They asked for some help, so here are the steps.

Basically, you create a user-defined data type, or structure:

1
2
3
4

CREATE OR REPLACE TYPE item_structure IS OBJECT
( id      NUMBER
 (more...)

It’s funny but Oracle doesn’t want you to enter a trivial password, and about every week I get asked what the standards are for Oracle Database passwords. That’s funny too because it’s in the documentation, the one most experienced and new users never read – Oracle Database Installation Guide (available by platform, the link is for the Windows platform).

Anyway, let me quote the rules:

Oracle recommends that the password you specify:

• Contains at least one lowercase letter.
• Contains at least one uppercase letter.
• Contains at least one digit.
• Is at least 8 characters in length.
• Uses the database character (more...)

I’m off to speak at the Utah Oracle User’s Group Training Days 2013 tomorrow and Thursday. I’m presenting on Oracle Database Triggers and on techniques leveraging PHP and MySQL Striped Views. If you’re not there, you can check this older (but recently updated) post on PHP and MySQL Striped Views.

Hope to see a few folks who use the blog. BTW, I won’t be at Collaborate 2013 this year. Travel budgets are always tight, and this year they’re more than tight.

My take on the good, the bad, and the ugly of our Winter season, beside all the snow I’ve shoveled and blown to get in and out of my driveway, is:

The Good?

February brought us MySQL 5.6 GA (General Availability) and Ruby 2.0.0-p0. Mega thanks to the MySQL and Ruby teams! By the way, if you’ve always wanted to know a little about Ruby without a great deal of effort, you should check this awesome little 20 minute Ruby tutorial. Naturally, the best Ruby Programming book is a bit dated, and maybe there will be a (more...)

I had an interesting conversation about table functions in Oracle’s PL/SQL; and the fact that they’re not available in MySQL. When I explained they’re available in Microsoft T-SQL User-Defined Functions (UDFs), my students wanted a small example. One of them said they’d tried to do it but couldn’t get it to work because they found the Microsoft web pages difficult to read and use. Specifically, they didn’t like the sparseness of this one on how to create a function.

Here’s a quick definition of a UDF table function that runs in the studentdb schema (created in this post for migrating (more...)

Several years ago, I decided hosting my own blogs made sense because WordPress didn’t allow me to leverage plug-ins to format code blocks with GeSHi formatting. That was fine to a point. Although, the GeSHi formatting plug-in had some limits that I didn’t like and didn’t want to take the time to fix. The future was moving reusable code artifacts to a GitHub Gist repository.

I created this https://gist.github.com/maclochlainn Gist repository for the blog and implemented in this Common Lookup post from yesterday. As time allows, I’ll start shifting the reusable artifacts to the Gist repository from my (more...)

Last October I posted an example and description of a common_lookup table. It was intended to show how common_lookup tables support drop down selections in web forms. However, it wasn’t adequate to show how they work with existing data, and the function only supported fresh queries.

This post goes to the next level, and shows how to use foreign keys to preselect values for display in web forms. It also rewrites the prior function so that it supports querying existing data and inserting new data.

Let’s start with data stored in join between two tables – the member and contact (more...)

Somebody ran into a problem after reading about the MySQL CREATE statement and the AUTO_INCREMENT option. They couldn’t get a CREATE statement to work with an AUTO_INCREMENT value other than the default of 1. The problem was they were using this incorrect syntax:

CREATE TABLE elvira
( elvira_id    int unsigned PRIMARY KEY AUTO_INCREMENT=1001
, movie_title  varchar(60))
  ENGINE=InnoDB
  CHARSET=utf8;

It raises this error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the  (more...)