Just been tearing my hair out on some unexpected SQL behaviour in SQL Developer.
Eventually I managed to get to the root cause of the problem as illustrated below.
SQL> var ps number
SQL> exec :ps := 4001644945;
PL/SQL procedure successfully completed.
SQL> select :ps from dual;
SQL Developer v3.2 and v4.0 via “Run As Script(F5)”:
var ps number
exec :ps := 4001644945;
select :ps from dual;
I shouldn’t be using an old version of SQL Developer like v3 (3.2.10.09) anyway but… cut a long story short … I am.
I’ve also got v4 which doesn’t display the same problem
Just pointing out an oddity whilst investigating something for someone:
with x as
(select to_date('28-MAR-2015 01:30','DD-MON-YYYY HH24:MI') dt1
, to_date('29-MAR-2015 01:30','DD-MON-YYYY HH24:MI') dt2
, dt2 "date"
, cast(dt2 as timestamp) "timestamp"
, to_Char(cast(dt2 as timestamp),'DD-MON-YYYY (more...)
Over the past couple of months Oracle has been releasing some EA (Early Adopter) versions of a new tool that is currently called SQL Command Line.
The team behind this new tool is the SQL Developer development team and they have been working on creating a new command line SQL tool that is based on some of the technology that is included in SQL Developer.
SQL Command Line in an stand alone tool and all (more...)
Working with database performance troubleshooting means dealing with challenging and exciting problems. But it also means doing certain things over and over. And over. Like typing the same query or clicking the same button zillion times a day. And when you’re doing something very frequently, anything that can be done to simplify or automate such tasks becomes very important. So I decided to share some of the tricks I use to optimize my working experience in (more...)
Just a quick plug for looking at raw sql trace files via SQL Developer.
There is a nice Tree View:
Which can be expanded:
Also summary view of statistics, filterable:
And a list view, filterable and orderable:
Some sort of right click summary for binds/waits might be a nice addition.
Der Oracle SQL Developer hat eine kurze und bewegte Geschichte hinter sich. Aus meiner persönlichen Sicht ist die Software inzwischen wirklich gereift, läuft performant und wirkt immer noch nicht überfrachtet. Nichts desto trotz gibt es auch im SQL Developer eine Reihe versteckter sehr nützlicher Features. Um diese den Leuten näher zu bringen, hat das Oracle SQL Developer Team einen sehr eleganten Weg gefunden. Sie nutzen einen Twitter Account OracleSQLDev
. Unter diesem werden immer wieder die (more...)
Wussten Sie das der Oracle SQL Developer eine sehr gute APEX Integration bietet? Ich möchte dies Anhand der Import und Export Fähigkeit näher demonstrieren.
Im SQL Developer gibt es im Navigationsmenü neben den üblichen Verdächtigen (Tabellen, Funktionen, Triggern, ...) auch einen Punkt Namens: Application Express
Wenn Sie diesen öffnen, dann sehen Sie alle installierten APEX Anwendungen die auf das Schema referenziert sind.Export
Über die Rechte Maustaste > Schnell-DDL > In Datei schreiben... können Sie (more...)
A third-party supplier delivered some SQL today but it did not work in SQL*Plus. We asked the supplier about this and it turned that the code had been tested in SQL Developer. The reason for the failure was as follows. If you end a line of SQL with a semi-colon then add a comment afterwards, SQL*Plus rejects it with an ORA-00911:
SQL> set echo on
SQL> select 'Comment->' from dual; /*Andrew was (more...)
I've been having a bit of a play with the Oracle 12c database over the past few days and I thought I'd mention a gotcha
Of course, oracle-base
is a great place to start for clear & concise information on new features and I was trying out some of the WITH clause enhancements (a.k.a. subquery factoring clause
). As a developer I'm pretty excited about these in particular.
Creating inline functions (more...)
When Oracle Data Miner (ODMr) 4.0 (which is part of SQL Developer) came out back in late 2013 there was a number of new features added to the tool. One of these was a Graph node that allows us to create various graphs and charts that include Line, Scatter, Bar, Histogram and Box plot.
I've been using this node recently to produce graphs and particularly scatter plots. I've been using the scatter plots to (more...)
Unshared worksheets are created to have a private connection to the database. When that unshared worksheet is closed, the connection and session for it should disappear as well.
This graphic shows what should happen!
In this post I’m going to show to synchronize the remote and local repositories after an existing file in local gets modified. What I’ll do is modify the sp_test_git.pls file in our local repository and then push those changes to the remote repository (GitHub).
First, I proceed to open the sp_test_git.pls file using SQL Developer, add another dbms_output line to it and save it. The moment I save the file, the Pending Changes (more...)
In my previous post, I showed how to clone a GitHub repository using SQL Developer. In this post I’m going to show to synchronize the remote and local repositories after remote gets modified.
Here I use GitHub to commit a file called sp_test_git.pls. You can create files by clicking on the icon the red arrow is pointing to.
The content of the file is a PL/SQL procedure that prints a message.
At this (more...)
SQL Developer 4 provides an interface that allows us to interact with Git repositories. In this post, I’m going to show how to clone a GitHub (A web based hosting service for software development projects that uses the Git revision control system) repository.
First you need to sign up for a GitHub account. You can skip this step if you already have one.
Your account will give you access to public repositories that could be cloned (more...)
The PL/SQL Debugger works pretty much out of the box when used with a previous Oracle version. These are the things we needed in place before we could start debugging PL/SQL:
- A grant of the DEBUG CONNECT SESSION privilege.
- EXECUTE privilege on DBMS_DEBUG_JDWP.
- EXECUTE privilege on the stored procedure you want to debug.
- Make sure the stored procedure is “Compiled for Debug”.
Jeff Smith talks about it in this post.
But what happens when you (more...)
This, the last part of a four part tutorial goes over the movement of data using files generated by Oracle SQL Developer.
Looking at the data move scripts that we generated in an earlier part. We need to zip up the files and copy them to the SQL Server machine (more...)
In part 1
we generated the offline capture scripts to take to the SQL Server machine, unloaded the metadata, zipped it up and copied it back to out local machine. In part 2
we used SQL Developer to create a migration project and load the capture files into SQL Developer. (more...)
Ok, Now we have the files as generated and moved in part 1
, we can now start SQL Developer to load the files. Start up SQL Developer and create a connection with the following privileges: CONNECT, RESOURCE and CREATE VIEW.
When the connection is opened, right click on it (more...)
Apparently, being able to use Oracle’s SQL Developer to connect to SQL Server databases has been around for a while (via third party drivers), but I only started using it today for the first time, with SQL Developer 4 EA2 after my colleague mentioned it to me this afternoon First (more...)