Sublime Forum

Oracle PL/SQL

#1

Someone ask me how I work with PL/SQL, here it is…
It was created only for my own usage and tailored for it, so don’t ask too much…

Notes:
-It was only tested on Windows, so it will probably not work out of the box on other OS. But it could probably work.
-The syntax def. come from somewhere on internet and was slightly updated by myself.
-I work with scripts (.sql) that contains more than one ‘create or replace’ instance.
-It hijack the Build system to modify the output of it. This way standard error navigation (f4/shift+f4) should work.
-It use sqlplus.exe from Oracle (must be on path).
-The menu of Build come from Sublime Text 2\Packages\OracleSQL\oracle_functions.py and have to be updated for your need (a settings will be better, maybe next time ?):

class OracleExecuteListCommand(sublime_plugin.WindowCommand): instance_list = "MASTER COMPANY", "DEVELOP/DEVELOP@DEV1252A"], "MASTER PCS", "PCS/PCS@DEV1252A"], "10.02 COMPANY", "DEMO_MAS_F/DEMO_MAS_F@T1002U"], "10.01 COMPANY", "MAS_F/MAS_F@T4001U"]]

Good luck.
SublimeOracleSQL.zip (12.4 KB)

0 Likes
#2

Hi, I’m trying this script with Sublime Text 2 on Ubuntu 12.04 and it’s great!!!
thanks a lot.

0 Likes
#3

I am surprised that it works ‘out of the box’, the binary for sqlplus is called “sqlplus.exe” on Linux too ?
I think I changed a few things in the past months. If I have some times, I will push everything on github.

0 Likes
#4

Hey, is there a github or bitbucket for this yet?

I’m just mucking around on github and can’t find anything there.

If it stays like that, I want to start sharing any changes I make. Would that be okay? I’ll just have to link the repo back to this forum post, which is suboptimal.

0 Likes
#5

https://github.com/bizoo/Oracle

Feel free to change whatever you want and send Pull request, or Fork it.

As I already wrote in this topic, I only tested it on Windows.

0 Likes
#6

Hi,

When I’m using this, sometimes when i build, if it doesn’t compile or has warnings it does not give me the error messages and it doesn’t update the database. Instead, it just goes [Finished in 0.2s].

On good times, it will give me the error and the line it is on and f4 error navigation works.

When running PL/SQL anonymous blocks that doesn’t have an error it’ll return my query results and display.

Filename: C:\temp\test2.sql
AND TYPE || ’ ’ || NAME in ()
*
ERROR at line 4:
ORA-00936: missing expression

[Finished in 0.3s]

Are these known issues and is there a fix for them?

0 Likes
#7

This has been bugging me for a while as well so finally I took a look into the code. It happens when there isn’t a create or replace statement in the file. I was able to fix the problem by changing the following code in oracle_exec.py:

Before:

            sqlfilter = '"' + ",".join("'%s'" % entity for entity in self.entities.keys()) + '"'

After:

            if len(self.entities) == 0:
                sqlfilter = "\"''\""
            else:
                sqlfilter = '"' + ",".join("'%s'" % entity for entity in self.entities.keys()) + '"'

I’ll make a pull request with the change on github…

0 Likes
#8

Does anyone have this running with SublimeText 3? I used to have it running with SublimeText 2, but I can’t get it to work with SublimeText 3. In the console I get the error message:

Unable to find target command: oracle_exec

0 Likes
#9

[quote=“makebei”]Does anyone have this running with SublimeText 3? I used to have it running with SublimeText 2, but I can’t get it to work with SublimeText 3. In the console I get the error message:

Unable to find target command: oracle_exec[/quote]

I got this code from bizoo a while ago but he hasn’t updated the package with it (maybe he’s still using ST2). This gist linked to below also has some fixes for handling spaces in paths and preventing an error from being shown when executing a file that doesn’t have any DML in it.

gist.github.com/jbjornson/8704419

I’d happy to create a patch to bizoo with a version that works on both ST2 and ST3 but I’m not sure how to handle the different import between the two versions:

On ST2:

execmod = __import__("exec")

On ST3:

from Default import exec as execmod

Anyone know how to handle both those import cases?

0 Likes
#10

You’ll also need to update oracle_commands.py as well for ST3 compatibility.

oracle_exec.py
gist.github.com/jbjornson/8704419

oracle_commands.py
gist.github.com/jbjornson/8704545

0 Likes
#11

[quote=“jbjornson”]

[quote=“makebei”]Does anyone have this running with SublimeText 3? I used to have it running with SublimeText 2, but I can’t get it to work with SublimeText 3. In the console I get the error message:

Unable to find target command: oracle_exec[/quote]

I got this code from bizoo a while ago but he hasn’t updated the package with it (maybe he’s still using ST2). This gist linked to below also has some fixes for handling spaces in paths and preventing an error from being shown when executing a file that doesn’t have any DML in it.

gist.github.com/jbjornson/8704419

I’d happy to create a patch to bizoo with a version that works on both ST2 and ST3 but I’m not sure how to handle the different import between the two versions:

On ST2:

execmod = __import__("exec")

On ST3:

from Default import exec as execmod

Anyone know how to handle both those import cases?[/quote]

As exec is a statement in Python 2 (so ST2), I think the only way to manage it is with a conditional import (if) based on the sublime.version().
I asked jps to rename the exec.py file long time ago, but he refused.

0 Likes
#12

I am no longer interested in maintaining ST packages, if someone want to “carry the torch”…
I’ve merged the Pull requests but without testing anything.

In addition, it looks like some forks are better than the original, like:
https://github.com/mezdm/OracleSQL

Someone has already published a Oracle PL/SQL package:
https://sublime.wbond.net/packages/Oracle%20PL%20SQL
But it looks like a simple copy of tmBundle that only support syntax.

0 Likes