Home
Posts Tagged "oracle sql"
Posted
on Mar 30, 2011 in Technical Tips | 0 comments
Most people, including myself, don’t memorize the syntax and associated options for every programming language. So, I was interested in determining what the issue was with the following create table command that one of my developers was having. He continued to receive the following error, regardless of what he did with parentheses:
ORA-00907: missing right parenthesis
CREATE TABLE AppItem (id INTEGER PRIMARY KEY NOT NULL,
name VARCHAR(100),
url VARCHAR(100),
required CHAR(1) CHECK (required IN('N','Y')) DEFAULT 'N',
img VARCHAR(100),
title VARCHAR(100),
description VARCHAR(100)
);Being that the parentheses evened out, I first thought that the word required would be a reserved word in Oracle, so I tried changing that to is_required. No dice. After a little more fiddling — and only a few seconds before I would have turned to some documentation — I realized that the CHECK constraint needed to come after the DEFAULT value setting.
CREATE TABLE AppItem (id INTEGER PRIMARY KEY NOT NULL,
name VARCHAR(100),
url VARCHAR(100),
required CHAR(1) DEFAULT 'N' CHECK (required IN('N','Y')),
img VARCHAR(100),
title VARCHAR(100),
description VARCHAR(100)
);
Posted
on Sep 10, 2010 in Technical Tips | 0 comments
First Day of Current Month
1
| SELECT trunc(sysdate) - (to_number(to_char(sysdate,'DD')) - 1) FROM dual |
Last Day of Current Month
1
| SELECT add_months(trunc(sysdate) - (to_number(to_char(sysdate,'DD')) - 1), 1) -1 FROM dual |
Posted
on Jul 23, 2010 in Technical Tips | 0 comments
DBMS_SCHEDULER is a newer, more complex job scheduling engine released in 10g, intended to replace DBMS_JOB going forward. DBMS_JOB, the older job scheduler, as of now is still available for quick and easy DB based job scheduling.
Job scheduling comprises the core of the functionality of DBMS_SCHUDULER, however here are some additional benefits available to DBMS_SCHEDULER:
- Logging of job runs (job history)
- Simple but powerful scheduling syntax (similar to but more powerful than cron syntax)
- Running of jobs outside of the database on the operating system (see below)
- Resource management between different classes of jobs
- Use of job arguments including passing of objects into stored procedures
- Privilege-based security model for jobs
- Naming of jobs and comments in jobs
- Stored, reusable schedules
read more
Posted
on Feb 27, 2010 in Technical Tips | 63 comments
I recently downloaded the new 10.x version of TOAD and could not connect to my databases. Still running TOAD 9.x, no problem. This really threw me off for a minute, so I thought I would share the resolution.
You are likely running a 64-bit Windows OS and have installed TOAD into C:\Program Files (x86)\....
I simply copied over my entire Quest Software directory from C:\Program Files (x86)\... to C:\Program Files\..., and restarted the TOAD.exe. Everything is working great, without having to reinstall anything.
PS: Not sure who to blame here. Vendors (like Oracle and TOAD) should know Microsoft is capable of doing strange things with important directory names (using spaces and, in this case, “strange” characters like parentheses)…and of course, Microsoft should simply stop perpetuating the use of these characters in important directories.
UPDATE on 1/12/11: I had to install TOAD again today and noticed a message in the installer about directories with parentheses — glad to see Quest now added a note about it (see screenshot below).
Funny thing is, even though I installed to C:\Program Files\, there were still files created in C:\Program Files (x86)\ that I had to manually copy and paste over. This time, by the way, I cut and paste which also worked — so no files are actually needed in the C:\Program Files (x86)\ folders.
Posted
on Feb 24, 2010 in Technical Tips | 0 comments
Recursive queries are something I have used many, many times over the years to build dynamic, n-tiered hierarchies. Oracle’s CONNECT BY PRIOR is awesome.
Does anyone know if this is possible in SQL Server or MySQL?
From what I understand, stored procedures would need to be used in MySQL and SQL Server to accomplish what Oracle does in one SQL statement with CONNECT BY PRIOR. I need to dig into the other databases a bit further to verify, but I am interested in any elegant solutions people have come up with if you would like to share.
Posted
on Feb 23, 2010 in Technical Tips | 0 comments
Like me, some people may have gotten excited about using XMLFOREST in SQLX, only to find that NULL values result in altogether MISSING elements, as opposed to simply EMPTY elements. Many times avoiding printing elements with null values is desired functionality, but in some cases the consumer of the XML might require those elements to generated anyways, with a null value (i.e. [tagname][/tagname]).
Thus, I have found it is easiest to use XMLELEMENT instead of XMLFOREST for these cases. Note: IBM offers a directive to print empty elements for null values, but until Oracle implements such a feature, your best best is probably to leverage XMLELEMENT:
1
2
3
4
5
6
7
8
9
10
11
| SELECT xmlforest(a).getstringval() FROM (SELECT 1 a FROM dual);
--returns: <A>1</A>
SELECT xmlforest(a).getstringval() FROM (SELECT NULL a FROM dual);
--returns: null
SELECT xmlelement("A", a).getstringval() FROM (SELECT 1 a FROM dual);
--returns: <A>1</A>
SELECT xmlelement("A", a).getstringval() FROM (SELECT NULL a FROM dual);
--returns: <A></A> |
Posted
on Jan 7, 2010 in Technical Tips | 0 comments
Like many days, I had a need to query data stored in Oracle. But today was different. I needed to return the data in an XML format. I started with a very simple need and generated the XML brute-force while traversing my cursor. However, I realized the data would not be escaped properly and knew/assumed Oracle has created built-in functionality to accomplish what I was trying to do.
I then recreated my logic using DBMS_XMLGEN. This worked well, in that it created the desired result set, but I hate the idea of storing SQL in a string to be passed into a function, which is precisely what DBMS_XMLGEN was having me do. I had a hard time figuring out the precise technique recommended by Oracle, or even the “latest” technique. My client’s environment was 9i, so I knew I wasn’t going to get to play with anything interesting 11g might have to offer.
My brief research showed that SQL/XML (SQLX) appeared to be the best option. A few hours later, it really has been nice. I have been able to create both simple and complex XML results. Once you have the syntax down, you can generate just about any XML structure you desire.
Let me know if you come across anything more interesting/useful than SQLX for querying Oracle RDBMS tables (not XML data stored natively in the DB), and I’ll be interested in checking it out.
Posted
on Nov 24, 2009 in Technical Tips | 0 comments
I have seen multiple custom solutions and packaged applications store their SQL outside of stored procedures. I am focusing this article on custom enterprise solutions, which I feel is one of the fastest growing areas in development. Some people who choose to store SQL outside of their database have touted database independence/abstraction, others easier maintenance, and I’m sure many other “excuses” have been communicated. In my opinion, if you have 1) a custom enterprise application and 2) an Oracle database(s) as the backend, all of your SQL — and associated DB logic — should be written, stored, maintained, etc. within Oracle stored procedures. I can dream up very unique scenarios where a valid argument could possibly begin to be made to keep SQL outside — but I think 99% of places that have done this, or will be considering this, really should have kept SQL inside their DB.
I’d like to hear your reasons for maintaining your Oracle SQL outside of Stored Procedures.
Recent Comments