MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘SQL Developer’ Category

SQL Developer on Ubuntu

without comments

The following steps show how to install and configure SQL Developer on Ubuntu 22.0.4 to work with Oracle Database 23c Free in a Docker container. The first steps are installing the Java Runtime Environment and Java Development Kit, and then downloading, installing and configuring SQL Developer. These are the required steps:

  1. Install the Java Runtime Environment:

    sudo apt install default-jre

    The log file for this is:

  2. Install the Java Runtime Environment:

    sudo apt install -y default-idk

    The log file for this is:

  3. Download SQL Developer from here; and then install SQL Developer to the /opt directory on your Ubuntu local instance:

    Use the following command to unzip the SQL Developer files to the /opt directory:

    sudo unzip ~/Downloads/sqldeveloper-23.1.0.097.1607-no-jre.zip
  4. Create the following /usr/local/bin/sqldeveloper symbolic link:

    sudo ln -s /opt/sqldeveloper/sqldeveloper.sh /usr/local/bin/sqldeveloper
  5. Edit the /opt/sqldeveloper/sqldeveloper.sh file by replacing the following line:

    cd "`dirname $0`"/sqldeveloper/bin && bash sqldeveloper $*

    with this version:

    /opt/sqldeveloper/sqldeveloper/bin/sqldeveloper $*
  6. Now, you can launch SQL Developer from any location on your local Ubuntu operating system, like:

    sqldeveloper
  7. You can now connect as the system user through SQL Developer to the Oracle Database 23c Free Docker instance with the following connection information:

  8. You can also create a Desktop shortcut by creating the sqldeveloper.desktop file in the /usr/share/applications directory. The SQL Developer icon is provided in the sqldeveloper base directory.

    You should create the following sqldeveloper.desktop file to use a Desktop shortcut:

    [Desktop Entry]
    Name=Oracle SQL Developer
    Comment=SQL Developer from Oracle
    GenericName=SQL Tool
    Exec=/usr/local/bin/sqldeveloper
    Icon=/opt/sqldeveloper/icon.png
    Type=Application
    StartupNotify=true
    Categories=Utility;Oracle;Development;SQL;

You can create a sandboxed container c##student user with the instructions from this earlier post on Oracle Database 18c, which remains the correct syntax.

As always, I hope this helps those trying to accomplish this task.

Written by maclochlainn

December 3rd, 2023 at 12:11 am

SQL Developer & PostgreSQL

without comments

I had a request from one of the adjunct professors to connect SQL Developer to the PostgreSQL database. This is in support of our database programming class that teaches students how to write PL/SQL against the Oracle database and pgPL/SQL against the PostgreSQL database. We also demonstrate transactional management through Node.js, Python and Java.

Naturally, this is also a frequent step taken by those required to migrate PostgreSQL data models to an Oracle database. While my final solution requires mimicking Oracle’s database user to schema, it does work for migration purposes. I’ll update this post when I determine how to populate the database drop-down list.

The first step was figuring out where to put the PostgreSQL JDBC Java ARchive (.jar) file on a Linux distribution. You navigate to the end-user student account in a Terminal and change to the .sqldeveloper directory. Then, create a jdbc subdirectory as the student user with the following command:

mkdir /home/student/.sqldeveloper/jdbc

Then, download the most current PostgreSQL JDBC Java ARchive (.jar) file and copy it into the /home/student/.sqldeveloper/jdbc, which you can see afterward with the following command:

ll /home/student/.sqldeveloper/jdbc

It should display:

-rw-r--r--. 1 student student 1041081 Aug  9 13:46 postgresql-42.3.7.jar

The next series of steps are done within SQL Developer. Launch SQL Developer and navigate to Tools and Preferences, like this:

Inside the Preferences dialog, navigate to Database and Third Party JDBC Drivers like shown and click the Add Entry button to proceed:

Inside the Select Path Entry dialog, select the current PostgreSQL JDBC Java ARchive (.jar) file, which is postgresql-42-3.7.jar in this example. Then, click the Select button.

You are returned to the Preferences dialog as shown below. Click the OK button to continue.

After completing the 3rd Party Java Driver setup, you attempt to create a new connection to the PostgreSQL database. You should see that you now have two available Database Type values: Oracle and PostgreSQL, as shown below:

When you click on the PostgreSQL Database Type, the dialog updates to the following view. Unfortunately, I couldn’t discover how to set the values in the list for the Choose Database drop down. Naturally, a sandboxed user can’t connect to the PostgreSQL database without qualifying the database name.

Unless you qualify the PostgreSQL database or connect as the postgres user with a privileged password, SQL Developer translates the absence of a database selection to a database name equivalent to the user’s name. That’s the default behavior for the Oracle database but differs from the behavior for MySQL, PostgreSQL, and Microsoft SQL Server. It returns the following

Status: Failure - Test failed: FATAL: database "student" does not exist

As seen in the diaglog’s result when testing the connection:

Based on my hunch and not knowing how to populate the database field for the connection, I did the following:

  • Created a Linux OS videodb user.
  • Copied the .bashrc file with all the standard Oracle environment variables.
  • Created the /home/videodb/.sqldeveloper/jdbc directory.
  • Copied the postgresql-42.3.7.jar into the new jdbc directory.
  • Connected as the postgres super user and created the PostgreSQL videodb user with this syntax:

    CREATE USER videodb
      WITH ROLE dba
           ENCRYPTED PASSWORD 'cangetin';
  • As the postgres super user, granted the following privileges:

    -- Grant privileges on videodb database videodb user.
    GRANT ALL ON DATABASE "videodb" TO "videodb";
     
    -- Connect to the videodb database.
    \c
     
    -- Grant privileges.
    GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO videodb;
    GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO videodb;
  • Added the following line to the pg_hba.conf file in the /var/lib/pgsql/15/data directory as the postgres user:

    local   all             videodb                                 peer
  • Connected as the switched from the student to videodb Linux user, and launched SQL Developer. Then, I used the Tools menu to create the 3rd party PostgreSQL JDBC Java ARchive (.jar) file in context of the SQL Developer program. Everything completed correctly.
  • Created a new PostgreSQL connection in SQL Developer and tested it with success as shown:

  • Saving the new PostgreSQL connection, I opened the connection and could run SQL statements and display the catalog information, as shown:

    Connected as the videodb user to the videodb database I can display tables owned by student and videodb users:

    -- List tables.
    \d
     
                       List of relations
     Schema |           Name           |   Type   |  Owner
    --------+--------------------------+----------+---------
     public | new_hire                 | table    | student
     public | new_hire_new_hire_id_seq | sequence | student
     public | oracle_test              | table    | videodb
    (3 rows)

    In SQL Developer, you can also inspect the tables, as shown:

At this point, I’m working on trying to figure out how to populate the database drop-down table. However, I’ve either missed a key document or it’s unfortunate that SQL Developer isn’t as friendly as MySQL Workbench in working with 3rd Party drivers.

Written by maclochlainn

August 8th, 2023 at 11:29 pm

AWS EC2 TNS Listener

without comments

Having configured an AlmaLinux 8.6 with Oracle Database 11g XE, MySQL 8.0.30, and PostgreSQL 15, we migrated it to AWS EC2 and provisioned it. We used the older and de-supported Oracle Database 11g XE because it didn’t require any kernel modifications and had a much smaller footprint.

I had to address why attempting to connect with the sqlplus utility raised the following error after provisioning a copy with a new static IP address:

ERROR:
ORA-12514: TNS:listener does NOT currently know OF service requested IN CONNECT descriptor

A connection from SQL Developer raises a more addressable error, like:

ORA-17069

I immediately tried to check the connection with the tnsping utility and found that tnsping worked fine. However, when I tried to connect with the sqlplus utility it raised an ORA-12514 connection error.

There were no diagnostic steps beyond checking the tnsping utility. So, I had to experiment with what might block communication.

I changed the host name from ip-172-58-65-82.us-west-2.compute.internal to a localhost string in both the listener.ora and tnsnames.ora. The listener.ora file:

# listener.ora Network Configuration FILE:
 
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)
      (PROGRAM = extproc)
    )
  )
 
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )
 
DEFAULT_SERVICE_LISTENER = (XE)

The tnsnames.ora file:

# tnsnames.ora Network Configuration FILE:
 
XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )
 
EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

I suspected that it might be related to the localhost value. So, I checked the /etc/hostname and /etc/hosts files.

Then, I modified /etc/hostname file by removing the AWS EC2 damain address. I did it on a memory that Oracle’s TNS raises errors for dots or periods in some addresses.

The /etc/hostname file:

ip-172-58-65-82

The /etc/hosts file:

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4 ip-172-58-65-82
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6 ip-172-58-65-82

Now, we can connect to the Oracle Database 11g XE instance with the sqlplus utility. I believe this type of solution will work for other AWS EC2 provisioned Oracle databases.

Written by maclochlainn

March 22nd, 2023 at 10:09 pm

INSERT Statement

without comments

INSERT Statement

Learning Outcomes

  • Learn how to use positional- and named-notation in INSERT statements.
  • Learn how to use the VALUES clause in INSERT statements.
  • Learn how to use subqueries in INSERT statements.

The INSERT statement lets you enter data into tables and views in two ways: via an INSERT statement with a VALUES clause and via an INSERT statement with a query. The VALUES clause takes a list of literal values (strings, numbers, and dates represented as strings), expression values (return values from functions), or variable values.

Query values are results from SELECT statements that are subqueries (covered earlier in this appendix). INSERT statements work with scalar, single-row, and multiple-row subqueries. The list of columns in the VALUES clause or SELECT clause of a query (a SELECT list) must map to the positional list of columns that defines the table. That list is found in the data dictionary or catalog. Alternatively to the list of columns from the data catalog, you can provide a named list of those columns. The named list overrides the positional (or default) order from the data catalog and must provide at least all mandatory columns in the table definition. Mandatory columns are those that are not null constrained.

Oracle databases differ from other databases in how they implement the INSERT statement. Oracle doesn’t support multiple-row inserts with a VALUES clause. Oracle does support default and override signatures as qualified in the ANSI SQL standards. Oracle also provides a multiple- table INSERT statement. This section covers how you enter data with an INSERT statement that is based on a VALUES clause or a subquery result statement. It also covers multiple-table INSERT statements.

The INSERT statement has one significant limitation: its default signature. The default signature is the list of columns that defines the table in the data catalog. The list is defined by the position and data type of columns. The CREATE statement defines the initial default signature, and the ALTER statement can change the number, data types, or ordering of columns in the default signature.

The default prototype for an INSERT statement allows for an optional column list that overrides the default list of columns. When you provide the column list you choose to implement named-notation, which is the right way to do it. Relying on the insertion order of the columns is a bad idea. An INSERT statement without a list of column names is a position-notation statement. Position-notation is bad because somebody can alter that order and previously written INSERT statements will break or put data in the wrong columns.

Like methods in OOPLs, an INSERT statement without the optional column list constructs an instance (or row) of the table using the default constructor. The override constructor for a row is defined by any INSERT statement when you provide an optional column list. That’s because it overrides the default constructor.

The generic prototype for an INSERT statement is confusing when it tries to capture both the VALUES clause and the result set from a query. Therefore, I’ve opted to provide two generic prototypes.

Insert by value

The first uses the VALUES clause:

INSERT
INTO table_name
[( column1, column2, column3, ...)] VALUES
( value1, value2, value3, ...);

Notice that the prototype for an INSERT statement with the result set from a query doesn’t use the VALUES clause at all. A parsing error occurs when the VALUES clause and query both occur in an INSERT statement.

The second prototype uses a query and excludes the VALUES clause. The subquery may return one to many rows of data. The operative rule is that all columns in the query return the same number of rows of data, because query results should be rectangles—rectangles made up of one to many rows of columns.

Insert by subquery

Here’s the prototype for an INSERT statement that uses a subquery:

INSERT
INTO table_name
[( column1, column2, column3, ...)]
( SELECT value1, value2, value3, ... FROM table_name WHERE ...);

A query, or SELECT statement, returns a SELECT list. The SELECT list is the list of columns, and it’s evaluated by position and data type. The SELECT list must match the definition of the table or the override signature provided.

Default signatures present a risk of data corruption through insertion anomalies, which occur when you enter bad data in tables. Mistakes transposing or misplacing values can occur more frequently with a default signature, because the underlying table structure can change. As a best practice, always use named notation by providing the optional list of values; this should help you avoid putting the right data in the wrong place.

The following subsections provide examples that use the default and override syntax for INSERT statements in Oracle databases. The subsections also cover multiple-table INSERT statements and a RETURNING INTO clause, which is an extension of the ANSI SQL standard. Oracle uses the RETURNING INTO clause to manage large objects, to return autogenerated identity column values, and to support some of the features of Oracle’s dynamic SQL. Note that Oracle also supports a bulk INSERT statement, which requires knowledge of PL/SQL.

Written by maclochlainn

April 5th, 2022 at 1:23 pm

Tiny SQL Developer

without comments

The first time you launch SQL Developer, you may see a very small or tiny display on the screen. With some high resolution screens the text is unreadable. Unless you manually configure the sqldeveloper shortcut, you generally can’t use it.

On my virtualization on a 27″ screen it looks like:

As an Administrator user, you right click the SQLDeveloper icon and click the Compatibility tab, which should look like the following dialog. You need to check the Compatibility Mode, which by default is unchecked with Windows 8 displayed in the select list.

Check the Compatibility Mode box and the select list will no longer be gray scaled. Click on the select list box and choose Windows 7. After the change you should see the following:

After that change, you need to click on the Change high DPI settings gray scaled button, which will display the following dialog box.

Click the Override high DPI scaling behavior check box. It will change the gray highlighted Scaling Performed by select box to white. Then, you click the Scaling Performed by select box and choose the System option.

Click the OK button on the nested SQLDeveloper Properties dialog box. Then, click the Apply button on the SQLDeveloper Properties button and the OK button. You will see a workable SQL Developer interface when you launch the program through your modified shortcut.

Written by maclochlainn

January 9th, 2022 at 9:11 pm

SQL Developer JDK

without comments

In my classes, we use a VMware Linux install with SQL Developer. One of my students called me in a panic after an upgrade of packages when SQL Developer failed to launch. The student was astute enough to try running it from the command line where it generates an error like:

 Oracle SQL Developer
 Copyright (c) 2005, 2018, Oracle and/or its affiliates. All rights reserved.
 
/opt/sqldeveloper/sqldeveloper/bin/../../ide/bin/launcher.sh: line 954: [: : integer expression expected
The JDK (/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.222.b10-0.fc30.x86_64/) is not a valid JDK.
The JDK was specified by a SetJavaHome directive in a .conf file or by a --setjavahome option.
Type the full pathname of a JDK installation (or Ctrl-C to quit), the path will be stored in /home/student/.sqldeveloper/19.2.0/product.conf
 
Error:  Unable to get APP_JAVA_HOME input from stdin after 10 tries

The error is simple, the SQL Developer package update wipe clean the configuration of the SetJavaHome variable in the user’s ~/.sqldeveloper/19.2.0/product.conf file. The fix is three steps because its very likely that the Java packages were also updated. Here’s how to fix it:

  1. Navigate to the directory where you’ve installed the Java Virtual Machine (JVM) and find the current version of the JVM installed:

    cd /usr/lib/jvm
    ls java*

    It will return a set of files, like:

    java
    java-1.8.0
    java-1.8.0-openjdk
    java-1.8.0-openjdk-1.8.0.252.b09-0.fc30.x86_64
    java-openjdk
    jre
    jre-1.8.0
    jre-1.8.0-openjdk
    jre-1.8.0-openjdk-1.8.0.252.b09-0.fc30.x86_64
    jre-openjdk

  2. Navigate to your user’s product configuration file with this command:

    cd ~/.sqldeveloper/19.2.0
  3. Add the following line to the product.conf file:

    # SetJavaHome /path/jdk
    SetJavaHome /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.252.b09-0.fc30.x86_64/

Now, you should be able to run it from the command line. The shortcut icon should also work if one was installed. Also, don’t forget to update your $JAVA_HOME variable in the master Bash resource file, or your local user’s .bashrc files.

As always, I hope this helps those looking for a quick solution.

Written by maclochlainn

September 22nd, 2020 at 12:08 pm

SQL Developer Error

without comments

It’s been a couple releases trying to fix the following error thrown by SQL Developer on Fedora 30 and shown as the following dialog:

When you click the Detail button it shows the following error stack:

java.lang.NoClassDefFoundError: javafx/embed/swing/JFXPanel
	at oracle.dbtools.raptor.javafx.ui.JFXPanelFactory.createJFXPanelImpl(JFXPanelFactory.java:58)
	at oracle.dbtools.raptor.javafx.ui.JFXPanelFactory.createJFXPanel(JFXPanelFactory.java:34)
	at oracle.dbtools.raptor.startpage.StartPageViewer.createGUIComponent(StartPageViewer.java:179)
	at oracle.dbtools.raptor.startpage.StartPageViewer.getEditorContent(StartPageViewer.java:136)
	at oracle.ide.editor.AsynchronousEditor$2.run(AsynchronousEditor.java:345)
	at oracle.ide.editor.AsynchronousEditor$5.run(AsynchronousEditor.java:555)
	at org.openide.util.RequestProcessor$Task.run(RequestProcessor.java:1443)
	at org.netbeans.modules.openide.util.GlobalLookup.execute(GlobalLookup.java:68)
	at org.openide.util.lookup.Lookups.executeWith(Lookups.java:303)
	at org.openide.util.RequestProcessor$Processor.run(RequestProcessor.java:2058)
Caused by: java.lang.ClassNotFoundException: javafx.embed.swing.JFXPanel cannot be found by oracle.sqldeveloper_19.2.0
	at org.eclipse.osgi.internal.loader.BundleLoader.findClassInternal(BundleLoader.java:501)
	at org.eclipse.osgi.internal.loader.BundleLoader.findClass(BundleLoader.java:421)
	at org.eclipse.osgi.internal.loader.BundleLoader.findClass(BundleLoader.java:412)
	at org.eclipse.osgi.internal.baseadaptor.DefaultClassLoader.loadClass(DefaultClassLoader.java:107)
	at org.netbeans.modules.netbinox.NetbinoxLoader.loadClass(NetbinoxLoader.java:81)
	at java.lang.ClassLoader.loadClass(ClassLoader.java:352)
	... 10 more

I thought applying the Open Java FX package might fix the problem. I installed the package like the following:

yum install -y openjfx

The installation log:

Last metadata expiration check: 4:03:29 ago on Tue 21 Apr 2020 06:42:26 PM MDT.
Dependencies resolved.
=============================================================================================
 Package            Architecture      Version                       Repository          Size
=============================================================================================
Installing:
 openjfx            x86_64            8.0.202-8.b07.fc30            updates            8.8 M
 
Transaction Summary
=============================================================================================
Install  1 Package
 
Total download size: 8.8 M
Installed size: 11 M
 
Downloading Packages:
openjfx-8.0.202-8.b07.fc30.x86_64.rpm                        2.5 MB/s | 8.8 MB     00:03    
---------------------------------------------------------------------------------------------
Total                                                        2.1 MB/s | 8.8 MB     00:04     
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                     1/1 
  Installing       : openjfx-8.0.202-8.b07.fc30.x86_64                                   1/1 
  Running scriptlet: openjfx-8.0.202-8.b07.fc30.x86_64                                   1/1 
  Verifying        : openjfx-8.0.202-8.b07.fc30.x86_64                                   1/1 
 
Installed:
  openjfx-8.0.202-8.b07.fc30.x86_64                                                          
 
Complete!

After installing the software, I determined the new JAR files. Then, I added them to my $CLASSPATH environment variable, like:

export CLASSPATH=/usr/share/java/mysql-connector-java.jar:/usr/lib/jvm/openjfx/rt/lib/ext/fxrt.jar:/usr/lib/jvm/openjfx/rt/lib/jfxswt.jar:.

While it appears to load faster with these JAR files, it still raises the same Dialog error. I simply have to continue to look for a complete fix.

Written by maclochlainn

April 21st, 2020 at 11:49 pm

Fedora SQL*Developer

without comments

After you download SQL Developer 18 on Fedora 27, you can install it with the yum utility, like

yum install -y sqldeveloper-18.2.0.183.1748-1.noarch.rpm

The installation should generate the following log file:

Last metadata expiration check: 2:26:23 ago on Sat 25 Aug 2018 07:10:16 PM MDT.
Dependencies resolved.
================================================================================================
 Package               Arch            Version                      Repository             Size
================================================================================================
Installing:
 sqldeveloper          noarch          18.2.0.183.1748-1            @commandline          338 M
 
Transaction Summary
================================================================================================
Install  1 Package
 
Total size: 338 M
Installed size: 420 M
Downloading Packages:
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                        1/1 
  Installing       : sqldeveloper-18.2.0.183.1748-1.noarch                                  1/1 
  Running scriptlet: sqldeveloper-18.2.0.183.1748-1.noarch                                  1/1 
  Verifying        : sqldeveloper-18.2.0.183.1748-1.noarch                                  1/1 
 
Installed:
  sqldeveloper.noarch 18.2.0.183.1748-1                                                         
 
Complete!

After you install SQL Developer, you won’t be able to launch it. Attempts to launch it won’t raise an error message either. The problem is that there is a post-installation step, which requires you to configure the product.conf file.

You can see the error by navigating to the /opt/sqldeveloper directory. You will find the sqldeveloper.sh file in that directory. You will see the error when you run the command as the root user from the command-line interface (CLI), as follows:

/opt/sqldeveloper/sqldeveloper.sh

 Oracle SQL Developer
 Copyright (c) 2005, 2018, Oracle and/or its affiliates. All rights reserved.
 
Type the full pathname of a JDK installation (or Ctrl-C to quit), the path
 will be stored in /root/.sqldeveloper/18.2.0/product.conf

You can find the Oracle home by searching for the rt.jar file as the root user. You use the following find command syntax from the / topmost directory.

find . -name rt.jar

On Fedora 27, you should see the following absolute file name:

./usr/lib/jvm/java-1.8.0-openjdk-1.8.0.171-1.b10.fc27.x86_64/jre/lib/rt.jar

You discard the /jre/lib portion of the directory path and the rt.jar file name to get the Java home’s fully qualified path. This should update the product.conf file but if you have to change it manually you should edit the following file:

/root/.sqldeveloper/18.2.0/product.conf

You need to configure the SetJavaHome parameter value in the product.conf file. The SetJavaHome parameter needs to point to the Java home directory on your Fedora instance. It should look like this:

#
# By default, the product launcher will search for a JDK to use, and if none
# can be found, it will ask for the location of a JDK and store its location
# in this file. If a particular JDK should be used instead, uncomment the
# line below and set the path to your preferred JDK.
#
SetJavaHome /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.171-1.b10.fc27.x86_64

It’s possible that an attempt to launch SQL Developer by another user may have copied the product.conf file into a local directory. You should change those manually by editing their respective product.conf files. Assuming you attempted to launch SQL Developer by a student user before you changed the root user’s copy of the SQL Developer’s product.conf file.

Written by maclochlainn

August 25th, 2018 at 11:51 pm

SQL Developer & PL/SQL

without comments

While SQL Developer installs with a dbms_output view, some organizations close it before they distribute images or virtual machine (VM) instances. This post shows you how to re-enable the Dbms Output view for SQL Developer.

SQL Developer DBMS_OUTPUT Configuration

SQLDeveloper1

  1. You need to open SQL Developer, which may look like this when the DBMS_OUTPUT view isn’t visible.

SQLDeveloper1

  1. You need to click on the View menu option in SQL Developer and choose the Dbms Output dropdown menu element.

SQLDeveloper1

  1. You should see a grayed-out Dbms Output view.

SQLDeveloper1

  1. You should type a simply “Hello World!” anonymous block program in PL/SQL, like the one shown in the drawing.

SQLDeveloper1

  1. After writing the “Hello World!” anonymous block program in PL/SQL, click the green arrow to start the statement and you will see two things. There is now a Script Output view between your console and Dbms Output views, and it should say “anonymous block completed.” Unfortunately, none of your output is displayed in the Dbms Output view because you need to enable it.

SQLDeveloper1

  1. If you hover over the Dbms Output view’s green arrow, you see the help message that describes the behavior of the green arrow. The Dbms Output green arrow lets you enable the Dbms Output view for output.

SQLDeveloper1

  1. After you click the Dbms Output view’s green arrow, you receive a Select Connection prompt for the view. Make sure you have the right user, and click the OK button to continue.

SQLDeveloper1

  1. After you create the connection for the Dbms Output stream, the view area becomes white rather than gray.

SQLDeveloper1

  1. Click the green arrow to start the statement and you will see the “Hello World!” string in the Dbms Output view.”

As always, I hope this helps those looking for a solution.

Written by maclochlainn

May 13th, 2016 at 10:55 am

Mac SQL Developer Install

without comments

This how you install SQL Developer on Mac OS Yosemite. The first thing you need to do is download and install Java 8, not Java 7 on your Mac OS Yosemite as suggested on some web sites. You can determine whether or not Java is installed by running the following command:

Mac-Pro-3:~ username$ java -version
No Java runtime present, requesting install.

You must accept the Java license to install Java 8 on the Mac OS X operating system:

YosemiteInstallJava_01

You have the option of installing the Java SDK or JDK. I’ve opted to install Netbeans 8 with JDK 8u45, as you can tell from the screen capture after you launched the file:

YosemiteInstallJava_02

It is a standard Mac OS installation, which is why I didn’t bother showing any dialog messages. After installing the Java JDK or SDK, you should download SQL Developer 4.1 from Oracle’s web site. Below is a screen shot of the Oracle download web page where I’ve accepted the license agreement:

SQLDeveloperDownload

If you attempt to launch the installation and you’ve set your Mac Security to the “Mac App Store and identified developers” setting, you should raise the following exception:

SQLDeveloperInstall_01

If you reset the Mac Security to an “Anywhere” setting, you can install Oracle SQL Developer on Yosemite. Just make sure you reset it to the “Mac App Store and identified developers” setting after you install SQL Developer.

If you launch SQL Developer with the Security “Anywhere” setting, it displays the following dialog:

SQLDeveloperInstall_02

After you launch the program, you will see the following progress dialog:

SQLDeveloperInstall_03

The last step of the installation launches SQL Developer, as shown below:

SQLDeveloperInstall_04

Click the Connections icon to create an initial connection, like the following:

SQLDeveloperInstall_05

After connecting to the database, you can write and execute a query as shown in the next screen capture:

SQLDeveloperInstall_06

As always, I hope that this helps those who require an example to install SQL Server on a Mac OS.

Written by maclochlainn

June 12th, 2015 at 3:08 am