SQL_CL
Working with Vector Data and Oracle SQL Command Line SQLcl
Working with Vector data has been a hot topic over the past couple of years. There are lots and articles and blog posts showing some basic functionality and similarity search using Vectors. Most of these articles demonstrate the basics, but then you start working with Vector data and perhaps having to move this data between schemas or between databases you could face a little bit of a challenge. One of these challenges or perhaps compatibility issues is with the format of the Vector data in the output/input file. In the example below I’m going to use CSV file to illustrate the issue, and specifically with using Oracle SQLcl and the export/import to/from CSV file feature.
It should be noted that the issued described below might not be an issue in a future release of SQLcl. I’ve reported this issue and a bug report has been filled. So fingers cross it will be fixed soon and even back ported. So the workaround or fix illustrated below might not be necessary at some point in the future, but until such time the workaround below is required.
The basic secenario that I faced involved exporting a table, containing Vector data, and importing it into a different (empty) table in the same schema. The export feature seemed to work correctly but I get an error when importing the file. Something about the work data type etc. It was complaining about the Vector column format. After a little investigation, the export command exported the data but the Vector column did not have quotes around the data. The import command expected the Vector data to be in quotes. So there is a mismatch between the export and import commands for Vector data.
Here is a bit more detail on how I encountered this issue.
I created a table to store Wine Reviews, Imported the Wine Review data set. added a column to store the Vector Data, added the Vector data using a model I’d already imported. I then exported the Wine Reviews with the Vector data to CSV. I unloaded the table using the following SQL Command Line function UNLOAD
sql> unload wine_reviews
This created a file in my working directory called WINEREVIEWS_TABLE.csv
I then created a new version of the table with a different name, WINEREVIEWS_VEC, and used the LOAD function to load the CSV data into the the newly created table.
sql> load WINEREVIEWS_VEC WINEREVIEWS_TABLE.csv
This gives me the following error.
#ERROR Insert failed in batch rows 1 through 50#ERROR ORA-51804: Invalid syntax for VECTOR value. Must be a string of the formDENSE vector: '[<number> , <number> ..., <number>]'SPARSE vector: '[<total dimension count (optional)>, [<sparse index array>], [<sparse dimension value array>]]'.
What this error means is the Vector data is missing a quote at the beginning and end of the vector data.
So what can we do to fix this issue and move forward with import and being able to use the data.
In my case I used the ‘sed’ command, and here is the one line ‘sed’ command to add the quotes around the required field.
sed 's/,\(\[[^]]*\]\)/,"\1"/g' WINEREVIEWS_TABLE.csv > WINEREVIEWS_TABLE_vec.csv
No using he same LOAD function as previously used, the import/load works and is completed in a couple of seconds.
Before we end this post, lets have another look at the ‘sed’ command and what it does, as you can see it is a little complicated.
sed 's/,\(\[[^]]*\]\)/,"\1"/g' WINEREVIEWS_TABLE.csv > WINEREVIEWS_TABLE_vec.csv
The main focus of this command is to look for fields that are enclosed by square brackets [ ]. When you look at the CSV file, only the vector data is enclose in such brackets. what we need to do is to wrap this in quotes, as otherwise the comma in these brackets will be considered as field/column separators.
Breaking down the pattern ,\(\[[^]]*\]\)
So the whole thing matches: a comma, immediately followed by a bracketed block like [anything except ]], and captures that bracketed block (brackets included) as group 1.
The replacement ,"\1" puts back the comma, then wraps the captured bracket block in double quotes.
The g flag makes it do this for every match on the line, not just the first.
Oracle SQL Dev – VS Code – Recovering Deleted Connections
With the current early release, there is no way to organise your Database connections like you can in the full Oracle SQL Developer. We are told this will/might be possible in a future release but it might be later this year (or longer) before that feature will be available.
In a previous post, I showed how to import your connections from the full SQL Developer into SQL Dev VS Code. While this is a bit of a fudge, yet relatively straight forward to do, you may or may not want all those connections in your SQL Dev VS Code environment. Typically, you will use different tools, such as SQLcl, SQL*Plus, SQL Developer, etc to perform different tasks, and will only want those connections set up in one of those tools.
As shown in my previous post, SQL Dev VS Code and SQLcl share the same set of connections. These connections and their associated files are stored in the same folder on your computer. On my computer/laptop (which is currently a Mac) this connections folder can be found in the $HOME/.dbtools.
I kind of forgot this important little detail and started to clean up my connections in SQL Dev VS Code, but removing some of my old and less frequently used Connections. Only to discover, that these were no longer listed or available to use in SQLcl, using the connmgr command.
The question I had was, How can I recover these connections?
One option was to reimport the connections into SQLcl following the steps given in a previous blog post. When I do that, the connections are refreshed/overwritten in SQLcl, and because of the shared folder will automatically reappear in SQL Dev VS Code the next time I open it or by clicking on the refresh icon in the Connections pane in SQL Dev VS Code.
But was there a simpler solution? Yes there is, so let’s walk through a simple scenario to illustrate what you need to do.
In SQL Dev VS Code, you can delete a connection by right clicking the connection and selecting Delete from the popup menu. You’ll be asked to confirm the deletion of the connection.
Open a Terminal window and go to your $HOME/.dbtools/connections directory.
In this folder, you will see the deleted connections lists with ‘.removed’. These are your deleted connections. Some might have their original connection name with ‘.removed’ and others will have some weird name, for example, ‘zZqtNdeinniqNhofxqNI9Q.removed‘.
To make the deleted connection usable again just rename the directory removing the ‘.removed’ part. For example,
mv B01-Student2-Brendan.removed B01-Student2-BrendanIf you go back to SQL Dev VS Code, the connection will reappear in the list of Connections after about 5 seconds (on my laptop) but if it doesn’t then click on the refresh icon.
Oracle SQL Dev – VS Code – Import connections via SQLcl
I mentioned in my previous post about importing your SQL Developer connections into the VS Code Extension. The following are the steps you need to complete. A more direct method to import directly from SQL Developer will be available in an upcoming extension update.
To complete this migration of your database connects, you’ll need to have SQLcl (SQL Command Line) version 23.3 installed, as it comes with a new Connection Manager feature.
The first step involves exporting your SQL Developer connections. To do this right-click on ‘Oracle Connections’ in SQL Developer, and then select ‘Export Connections’ from the pop-up menu. Select all your connections and then go through the remaining steps of giving a file name, giving a password, and then Finish.
When the file has been created and the connection exported, go open SQLcl (needs to be a minimum of version 23.3), in /nolog mode.
Set the password used for the SQL Developers connections file. In this example ‘MyPassword’ is the password I entered when creating the file.
SQL> secret set mySecret MyPassword
Secret mySecret storedNow we can import the connections file.
SQL> connmgr import -key mySecret /Users/brendan.tierney/SQLDev-connections.json
Importing connection 23c-VM-STUDENT: Success
Importing connection B01-Student-Admin: Success
Importing connection B01-Student-Brendan: Success
Importing connection Oracle 23c - Docker - SYS: Success
Importing connection Oracle 23c - Docker - HR: Success
Importing connection 23-Free-VM-SYS: Success
Importing connection B01-Student2-Admin: Success
Importing connection 23-Free-VM-STUDENT: Success
Importing connection 23c-VM-SYSTEM: Success
Importing connection Oracle 23c - Docker - System: Success
Importing connection Oracle 23c - Docker - Benchmark: Success
Importing connection 23-Free-VM-SYSTEM: Success
Importing connection 23c-VM-SYS: Success
Importing connection Oracle 23c - Docker - SH: Success
Importing connection 23c-VM-STUDENT2: Success
Importing connection Oracle 23c - Docker - Brendan: Success
Importing connection B01-Student2-Brendan: Success
17 connection(s) processedIf you need to reimport the connections file, you’ll need to use the replace option. Here’s an example.
connmgr import -duplicates replace -key mySecret /Users/brendan.tierney/SQLDev-connections.jsonYou can see above the connections were imported. You can list these by running
SQL> connmgr list
23-Free-VM-STUDENT
23-Free-VM-SYS
23-Free-VM-SYSTEM
...To connect to one of these connections using its label by running the following, where the label for the connection is in quotes
SQL> connect -name "Oracle 23c - Docker - Brendan"
Connected.
SQL> show connection
COMMAND_PROPERTIES:
type: STORE
name: Oracle 23c - Docker - Brendan
user: brendan
CONNECTION:
BRENDAN@jdbc:oracle:thin:@//localhost:1521/FREEPDB1
CONNECTION_IDENTIFIER:
jdbc:oracle:thin:@//localhost:1521/FREEPDB1
CONNECTION_DB_VERSION:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0
NOLOG:
false
PRELIMAUTH:
falseWe’ve just imported and verified the import of our SQL Developer connections into SQLcl. The next step is to import them into VS Code. To do this is very easy. If you don’t have VS Code open, open it and the connections will be automatically imported from SQLcl. How easy is that! or if you have VS Code already open just click on the refresh button in the Connections section.
Note: The connections listed in VS code are shared with SQLcl. If you delete any of these connections in VS Code they will be deleted/removed from SQLcl. It’s the one set of connection details being shared by both applications. So some care is needed. I’m sure future releases of the VS Code extension will provide better options for managing your connections.
RIP SQL*Plus & hello SQL Command Line
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 you need to do is to download and un-zip the tile.
What I want to show in this blog post is some of new features that are available and that I have found particularly useful. But before we get onto those commands let us first have a look at how you can get setup and running with SQL Command Line.
Download & Setup
The current download of SQL Command Line can be found under the SQL Developer 4.1 EA Download page. I’m assuming when 4.1 is formally released the download for SQL Command line will be on the main SQL Developer Download web page.
After you have downloaded the file, all you need to do is to unzip the file and then copy the unzipped directory to where you want the software to be located on your client.
Now you are ready to get started with using SQL Command Line.
Connecting to your Oracle Schema
(That) Jeff Smith and Barry McGillin have a couple of good blog posts on the different connection methods and some setup or configuration you might need to consider. Check out these links for more details.
For me I did not have to do any additional setup or configuration. I was able to use the TNS Names and the EZConnect methods without any problems.
The following how to connect to my (DMUSER) schema using the EZConnect method. With this method we pass in the username, password, the host name, port number and the service name. Just like this
> sql dmuser/dmuser@localhost:1521/pdb12c
We can not have a look at the JDBC connection details.
SQL> show jdbc
— Database Info —
Database Product Name: Oracle
Database Product Version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Database Major Version: 12
Database Minor Version: 1
— Driver Info —
Driver Name: Oracle JDBC driver
Driver Version: 12.1.0.2.0
Driver Major Version: 12
Driver Minor Version: 1
Driver URL: jdbc:oracle:thin:@localhost:1521/pdb12c
SQL>
If we have a TNSNAMES.ORA file on our computer and the directory that it is in, is on the search PATH, then we can use the service names defined in the TNSNAMES.ORA file. The following example shows you how to use this in two ways. The first shows how to enter all the details when you are starting SQL CL and the other is when SQL CL prompts you for each parameter.
> sql dmuser/dmuser@pdb12c
and when we are prompted to enter the parameters, we get the following.
> sql
SQLcl: Release 4.1.0 Beta on Thu Mar 05 15:16:12 2015
Copyright (c) 1982, 2015, Oracle. All rights reserved.
SQLcl: Release 4.1.0 Beta on Thu Mar 05 15:16:14 2015
Copyright (c) 1982, 2015, Oracle. All rights reserved.
Username? (”?) dmuser
Password? (**********?) ******
Database? (”?) pdb12c
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
SQL>
As you can see these work in the same way as when we use SQL*Plus.
Now that you are connected to your schema, what else can you do? The following sections are some useful commands.
Commands & Help
The following list of commands is by no means a complete list of commands available in SQL Command Line. Theoretically everything you can currently do in SQL*Plus you can also do in SQL Command Line (theoretically) But the commands I give examples of below are some of my favourites (so far).
You can get the list of commands by typing help at the SQL prompt.
SQL> help
Then to get help on a specific command you can just add the command after the help.
SQL> help cd
CD
—
Changes path to look for script at after startup.
(show SQLPATH shows the full search path currently:
– CD current directory setting set by last cd command
– baseURL (url for subscripts)
– topURL (top most url when starting script)
– Last Node opened (i.e. file in worksheet)
– Where last script started
– Last opened on sqlplus path related file chooser
– SQLPATH setting
– “.” if in SQLDeveloper UI (included in SQLPATH in command line (sdsql))
).
SQL>
Some work is still needed on the help documentation and what is listed for each command, as the current version is missing some important details.
Alais
This is by far my favourite new feature. This allows us to take some of our most common SQL statements and to create a shortcut for it.
Very soon I will not be using Oracle SQL but I will be using My SQL, as I will have created my own personalised version of SQL.
To list what aliases you have defined in your schema you can type
SQL > alais
Oracle will have a few aliases already defined in SQL CL. By having a look at some of these you can see some of what you want they can do and get ideas for what you might want to do with them. To list the contents of an alias, you can use the following command.
alias list {alias name}
for example
SQL > alias list tables
This command lists the query that is used for the ‘tables’ alias that comes with SQL CL.
I use Oracle Data Miner a lot and when you use this tool it can create a number of tables with a variety of names in your schema. Most of these you will never need to look at. So what I do is create an alias that excludes these from the list of tables in my schema.
SQL> alias tables2=select table_name from user_tables where table_name not like ‘ODMR$%’ and table_name not like ‘DM$%’ and table_name not like ‘SYS_IOT%’;
So now all I need to do to list my important data only tables (and exclude all the Oracle Data Miner tables) I can run my alias ‘table2’.
SQL> tables2
You will quickly build up a suite of commands using aliases.
info and >info+
info and info+ are the new commands to replace the DESC command.
The difference between info and info+ is that info+ gives you some statistical information about the table and the attributes in the table. This is illustrated in the following examples.
Example using ‘info’
Example using ‘info+’
CTAS & DDL
If you want to get the DDL script to create a copy of a table you have two options open to you. The first of these is the DDL command. This creates a DDL statement based on the meta data for the table, just like in the following
An alternative to this is to use the CTAS command that will give a slightly different output to DDL command. With the CTAS we also get the CREATE TABLE .. AS SELECT …
History
In SQL*Plus we had a limited scroll through our previous commands. The same kind of scrolling is available in SQL CL, but we can get to see all our previous commands using the ‘history’ command. The following illustrates how you can list all you previous commands, I’m sure it is limited to a certain number or will be otherwise it will become a very long, long list.
SQL> history
To find out how often each command has been run you can run
SQL> history usage
and to find out how long the query took to run the last time it was run
SQL> history time
There are lots more that I could show, but this post is way, way to long as it is. What I suggest you do is go and download SQL CL (Command Line) and start using it today.
Ora-lytics 
You must be logged in to post a comment.