An interesting question popped up on the forum, and I thought I’d share my ‘answer’ with you folks. I figure this is something a lot of folks do several hundred times a day – pulling up a frequently used database objects in the browser.
Is there a way (more...)
Software development never really stops. The development cycle is truly a circle. Once a release is ready, the developers don’t go on break awaiting orders. There’s always the next sprocket to build or widget to refactor.
And so comes one of the best parts of my job: helping figure out (more...)
Oracle’s free data modeling solution, Oracle SQL Developer Data Modeler, has been updated with the release of version 3.3. I’ve already previewed a few of the new features here, and I’ll continue to talk more about the latest and greatest features. You can see the full list of (more...)
When I’m doing demonstrations of Oracle SQL Developer, I’m frequently asked about our support for datatypes like XML, BLOBS, Timestamp, etc. When I’m put on the spot like that, I have a tendency to forget that I already have tables setup for this. So I end up writing a query (more...)
The Oracle SQL Developer family currently has two ‘SQL Developer’ branded products:
- Oracle SQL Developer
- Oracle SQL Developer Data Modeler
Oracle SQL Developer is the database IDE and Oracle SQL Developer Data Modeler is our dedicated data modeling solution. Where it gets interesting is that the entire Data Modeler product (more...)
An interesting question came up on the ORACLE-L mailing list this morning regarding table comments:
Just wanted to get a general opinion on using comments in the database. For example COMMENT ON COLUMN table.column “No comment”.
Does anyone do this as a regular best practice? I would prefer to (more...)
In an earlier blog post I listed my ‘Top 10 Preferences to Tweak…’ Instead of amending that to Top 14, I wanted to give a fresh and updated take on some features that directly impact productivity and data quality.
After doing three customer calls in three consecutive days, (more...)
Because you don’t have any tables.
I hate to be the bearer of bad news, but you don’t actually ‘OWN’ any tables. What you mostly likely DO have are SYNONYMS that point to tables in a application schema. When you log on to Oracle, you are seeing this:
Your Table List is Empty!
The solution is simple!
Enable Synonym Support in the Tables Filter Dialog
Mouse-right click on your tables tree node, and select ‘Apply Filter…’
Treat Local Synonyms for Tables as Tables
With this checked, bam!
Oh my sweet, sweet darlings, how I missed you.
Note: This is also (more...)
decided to save a script that cleans out a couple of tables for me.
Now I have a script, how do I run it in SQL Dev? In SQL*Plus, I would run it like @clean_tables
. Two things to note there, 1, I didn't have to put the extension on the file and b, I assumed SQL*Plus was running from the directory where my file was located. If I was running the script from a different directory, I would have to use either a relative path...or something, but I digress.
I wanted to be able to run my (more...)
(First off, sorry Mike, I'm hoping this will break my writer's block...)
On Friday I was asked to look at a report that wasn't returning all of the data. Sample:
Year/Month Total Sales Total Sales (YAGO)
01/31/2013 $1,000,000 $900,000
For reference, YAGO is "Year Ago."
Notice anything funny there?
Yeah, February is missing. The (OBIEE) report has a filter on Jan, Feb and Mar of 2013. But it wasn't showing up. I confirmed via manual SQL (hah!) that there was (YAGO) data in there for February. Any ideas?
I immediately suspected one of two (more...)
This one is a quick hint for SQL Developer and the COLLECT function.
In case you're not familiar with it, COLLECT
is an aggregate function that was introduced in 10gR2 and simply gathers all the items up into a VARRAY/TABLE style collection type. If you use it in PL/SQL programs, you can handle the collection programmatically.
But sometimes it can be handy to see the elements in a quick ad-hoc query.
In SQL*Plus, you'll get an output that is ugly, but usable:SQL> l 1 select country_id, collect(city) 2 from hr.locations 3* group by country_idSQL> (more...)
Ok, Finally, we have got to part 3 of Application Migration. In Part 1
, we outlined a program which runs in Sybase through iSQL. We then followed this, in part 2
with 2 important pieces.
- Recognizers to identify the file types of the source we post
- Rules to identify items within the files and report on the them
In this part, We will take the rules we used for the previous part, and add some replacement rules. So, lets recap. Our recogniser is set for shell files as below.
<?xml version="1.0" encoding="UTF-8"?>
<rulesfile version="1.0" name="Shell (more...)
One of things I don't like about Oracle SQL Developer is that if you are browsing and clicking on tables, it can automatically open the table on the right side of the IDE.There is a simple way to switch that off, just heard how to switch this feature off. Navigate to the Preferences settings:
Goto Database >> Objectviewer, and uncheck the tickbox labelled "Open Object on Single Click"
And that's it.
I was working through a bug the other day and using SQL*Plus, which for the most part doesn't annoy me too much. However, one of the things that does, is having to retype lots of stuff. (We dont have that problem in SQL Developer).
Having hunted around for a few minutes, I found rlwrap which is a GNU readline wrapper. All this means is that when we use it on SQL*Plus, it give us keyboard history and user defined completion. I've found a few posts about it too, which are referred to below, but I wanted to do this for our (more...)
For those of you who end up using the Oracle Developers Day VM for more that just demo's but playing with other things too, will find that at some point, you'll need more space. (Like I did) Today's post is about just that. We're going to add a new VMDK drive to our virtual machine and configure it so its available to you in the machine.
First thing we want to do is to have a list of the devices in your linux box. This will save you searching for it once you add it later.
[oracle@localhost ~]$ cd (more...)
[This post was originally published on 2012/02/29 and was hidden shortly thereafter. I'm un-hiding it as of 2012/05/30 with some minor edits.]
Many Oracle Database users like tools with GUI interfaces because they add features and functionality that are not easily available from the command line interfaces like SQL*Plus. One of the more popular tools from my experiences is Oracle SQL Developer in part because it’s a free tool from Oracle. Given SQL Developer’s current design (as of version 3.1.07.42), some issues frequently show up when using it with Oracle Databases with Parallel Execution. SQL Developer (more...)
I'm speaking next week at the UK Oracle User Group at the ICC in Birmingham. The topic will be one I've posted several times about which is Tuning, Refactoring and Instrumentation. Have a look at the agenda, and if you are in town, come along. You can click on the image to go to the conference site and check out the agenda.
Working through security issues uncovers some interesting things. Anyone who has developed scripts for building out schemas for an application will have had the issues of passing variables to subscripts or managing password visibility when creating users, building objects or granting permissions
SQLDeveloper and SQL*Plus have substitution variables to solve this problem. Basically, there are two types of substitution variables, & and &&. &foo is used to refer to the variable foo. &&foo is also used to refer to the variable foo. The main difference between the two variables is that first time SQL*Plus comes across a variable defined with (more...)
@thatjeffsmith asked me today about running SQL*Plus from SQLDeveloper for his current sql file. This has been shown before, but there is a simple way to add it and to get it to run your file under SQL*Plus.
The only caveate on this example, is that we are using the bequeath adapter to connect to a local XE database. You can amend this to add a service after the username
The main steps are to point the program executable to your SQLPlus, which will populate the executable and the Run directory. Next you need to populate the arguments, which for (more...)
I encountered this error “I/O Error: SSO Failed: Native SSPI library not loaded” when using Oracle SQL Developer connecting to a MS SQL database using Windows Authentication. Fortunately, couple web sites (here and here) already mentioned about this.
Basically, from the jTDS distribution downloaded files, I have to copy a DLL file named ntlmauth.dll (which is for NT authentication) under the jtds-x.x.x-dist\x86\SSO\ or jtds-x.x.x-dist\x64\SSO\, to any directories in the PATH environment. I copied it to the bin directory of the JDK (which I already have it in the (more...)