JOINS In SQL Developer, JOINS are used to relate information in different tables. A JOIN condition retrieves data from two or more tables. It is performed in the WHERE clause of the select statement. There are multiple types of SQL joins, but this post is going to be focusing on the OUTER JOIN. SYNTAX The (+) operator can be on either the right or left side of the join condition. The side the operator needs to be on depends on whether “Nulls” need to be included from that table. Using the first SQL JOIN, the results would return all the … Read More
Create Table – ORA-00907: missing right parenthesis — CHECK and DEFAULT Order Swapped
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) );CREATE TABLE AppItem (id INTEGER PRIMARY KEY NOT NULL, name VARCHAR(100), url VARCHAR(100), required CHAR(1) CHECK (required IN(‘N’,’Y’)) … Read More
First and Last Day of Month – Oracle SQL Syntax Examples
First Day of Current Month 1 SELECT trunc(sysdate) – (to_number(to_char(sysdate,’DD’)) – 1) FROM dualselect 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 dualselect add_months(trunc(sysdate) – (to_number(to_char(sysdate,’DD’)) – 1), 1) -1 from dual
Oracle DBMS_SCHEDULER vs DBMS_JOB (Create, Run, Monitor, Remove)
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
TOAD for Oracle 9 or TOAD 10 – 64-bit Windows – ORA-12154: could not resolve the connect identifier specified or ORA-6413: connection not open
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 … Read More
MySQL and SQL Server – Oracle CONNECT BY PRIOR for Recursive, Hierarchical Data
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.
XMLFOREST vs XMLELEMENT – Missing vs Empty in SQLX
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 … Read More
SQL to Query Oracle, Return XML – with SQLX
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 … Read More