Discussion of SQuirreL SQL Universal SQL Client

Matheus Garcia | 22 Dec 2011 13:53

Run Oracle scripts on Squirrel SQL

Hi all,

I'm used to create and run SQL scripts in Microsoft SQL Server with
Query Analyser tool, something simple just like declaring some
variables, setting their values and using them on sql queries. Like the
following example:

DECLARE
    <at> foo INTEGER
SET  <at> foo = 1
INSERT INTO mytable (foofield) value ( <at> foo)

Although I saw it's possible to do something like that in Oracle PL-SQL,
I couldn't run any samples I got on internet in SQuirrel SQL. My oracle
connection is correctly configured, I can run simple sql commands, but
when I try to somethibng like the  the following code:

DECLARE
v1  NUMBER(3);

BEGIN
   v1 := 3;
   select 1 from dual
END;

the DECLARE keywork appears in red, and when I try to run I got the
following error: "java.lang.IllegalArgumentException: No SQL selected
for execution."

Could any of you please give me some help on doing that? Or running

(Continue reading)

headers

Drav Sloan | 22 Dec 2011 14:26

Re: Run Oracle scripts on Squirrel SQL

Matheus Garcia wrote:
> Although I saw it's possible to do something like that in Oracle PL-SQL,
> I couldn't run any samples I got on internet in SQuirrel SQL. My oracle
> connection is correctly configured, I can run simple sql commands, but
> when I try to somethibng like the  the following code:
> 
> DECLARE
> v1  NUMBER(3);
> 
> BEGIN
>    v1 := 3;
>    select 1 from dual
> END;
> 
> the DECLARE keywork appears in red, and when I try to run I got the
> following error: "java.lang.IllegalArgumentException: No SQL selected
> for execution."

Make sure you leave no gaps in your SQL and make sure you follow
your procedural code with a /

DECLARE
v1  NUMBER(3);
BEGIN
   v1 := 3;
   select v1 from dual
END;
/

The above should now run :)

(Continue reading)

headers

Matheus Garcia | 22 Dec 2011 15:26

Re: Run Oracle scripts on Squirrel SQL

Thanks for replying, Drav.

First of all, forgot to say, but I was using 3.1, now I upgraded to 3.3.0. Running on Linux.

I tried
SELECT :var FROM dual
and squirrel asked me for the value of var, very good, thanks!

Then I tried the code:
DECLARE
v1 NUMBER(3);
BEGIN
v1 := 3;
select v1 from dual
END;
/

After adding the slash in the end, instead of "java.lang.IllegalArgumentException: No SQL selected for execution.", I'm getting the following error:

Error: ORA-06550: line 7, column 1:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

   begin case declare end exception exit for goto if loop mod
   null pragma raise return select update while with
   <an identifier> <a double-quoted delimited-identifier>
   <a bind variable> << close current delete fetch lock insert
   open rollback savepoint set sql execute commit forall merge
   pipe

SQLState: 65000
ErrorCode: 6550

Searching on internet for error PLS-00103 lead me to the following question in stack overflow: http://stackoverflow.com/questions/2289723/statement-execute-returns-error-with-slash-at-the-end-of-pl-sql

Where I can extract from the answers and comments:

"As the message indicates, the compiler doesn't want to encounter the symbol "/", so just remove it. That simple. Let me explain. When using sqlplus or an SQL worksheet in sqldev, you do well appending your PL/SQL blocks with the slash. However, when using the procedure editor (native to sqldev), you'll have to remove it. (...)"

"In SQL Developer you would need to click the "Run Script (F5)" button to create all of your triggers (not the main "Run Statement" button)"

So, if I understood it correctly, I think squirrel should have another option to run (oracle) scrips, is it? Or am I missing something?

Matheus

Em Qui, 2011-12-22 às 13:26 +0000, Drav Sloan escreveu:

Matheus Garcia wrote: > Although I saw it's possible to do something like that in Oracle PL-SQL, > I couldn't run any samples I got on internet in SQuirrel SQL. My oracle > connection is correctly configured, I can run simple sql commands, but > when I try to somethibng like the the following code: > > DECLARE > v1 NUMBER(3); > > BEGIN > v1 := 3; > select 1 from dual > END; > > the DECLARE keywork appears in red, and when I try to run I got the > following error: "java.lang.IllegalArgumentException: No SQL selected > for execution." Make sure you leave no gaps in your SQL and make sure you follow your procedural code with a / DECLARE v1 NUMBER(3); BEGIN v1 := 3; select v1 from dual END; / The above should now run :) Also you can do parameters like MSQL: First off make sure you have SQL Parametrisation turned on (found under Plugins -> Summary (and called sqlparam). Then you can do SELECT :var FROM dual and Squirrel should ask you for the value of :var. Regards D.

--
Matheus Garcia Barbosa de Figueiredo
Tel.: (31) 2108 - 7018
Analista de Sistemas
Gerência de Desenvolvimento de Sistemas - GDS
Gerência-Geral de Sistemas de Informação - GSI
Assembleia Legislativa de Minas Gerais - ALMG

------------------------------------------------------------------------------
Write once. Port to many.
Get the SDK and tools to simplify cross-platform app development. Create 
new or port existing apps to sell to consumers worldwide. Explore the 
Intel AppUpSM program developer opportunity. appdeveloper.intel.com/join
http://p.sf.net/sfu/intel-appdev

_______________________________________________
Squirrel-sql-users mailing list
Squirrel-sql-users <at> lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users

headers

Drav Sloan | 22 Dec 2011 15:47

Re: Run Oracle scripts on Squirrel SQL

Matheus Garcia wrote:
> First of all, forgot to say, but I was using 3.1, now I upgraded to
> 3.3.0. Running on Linux.

Well you are now on latest version with 3.3.0 :)

> I tried
> SELECT :var FROM dual
> and squirrel asked me for the value of var, very good, thanks!
>
> Then I tried the code:
> DECLARE
> v1  NUMBER(3);
> BEGIN
> v1 := 3;
> select v1 from dual
> END;
> /
>
> After adding the slash in the end, instead of
> "java.lang.IllegalArgumentException: No SQL selected for execution.",
> I'm getting the following error:
>
> Error: ORA-06550: line 7, column 1:
> PLS-00103: Encountered the symbol "end-of-file" when expecting one of
> the following:

You _may_ need to load the oracle plugin, which again is under
Plugins -> Summary and is simply called 'oracle' (but as far
as I know that just enables things like the database output window).

If I run the statement:

DECLARE
v1  NUMBER(3);
BEGIN
v1 := 3;
select v1 from dual
END;
/

I get:

Error: ORA-06550: line 5, column 2:
PLS-00428: an INTO clause is expected in this SELECT statement

SQLState:  65000
ErrorCode: 6550
Position: 991

.. all procedural oracle code uses SELECT INTO. However, if I do:

DECLARE
v1  NUMBER(3);
BEGIN
select 3 into v1 from dual;
dbms_output.put_line(v1||chr(13));
END;
/

I get '3' in the oracle output window ('View Oracle Database Output'
quick icon opens this up)

Regards

D.

------------------------------------------------------------------------------
Write once. Port to many.
Get the SDK and tools to simplify cross-platform app development. Create 
new or port existing apps to sell to consumers worldwide. Explore the 
Intel AppUpSM program developer opportunity. appdeveloper.intel.com/join
http://p.sf.net/sfu/intel-appdev

headers

Matheus Garcia | 22 Dec 2011 16:02

Re: Run Oracle scripts on Squirrel SQL

I think the fact your error message was more clear is because distinct oracle version or setup, here we use 10.2.0.4.0. Problem really was with the select, using SELECT INTO worked like a charm!

Special thanks, you saved me a lot of time.

Matheus

Em Qui, 2011-12-22 às 14:47 +0000, Drav Sloan escreveu:

Matheus Garcia wrote: > First of all, forgot to say, but I was using 3.1, now I upgraded to > 3.3.0. Running on Linux. Well you are now on latest version with 3.3.0 :) > I tried > SELECT :var FROM dual > and squirrel asked me for the value of var, very good, thanks! > > Then I tried the code: > DECLARE > v1 NUMBER(3); > BEGIN > v1 := 3; > select v1 from dual > END; > / > > After adding the slash in the end, instead of > "java.lang.IllegalArgumentException: No SQL selected for execution.", > I'm getting the following error: > > Error: ORA-06550: line 7, column 1: > PLS-00103: Encountered the symbol "end-of-file" when expecting one of > the following: You _may_ need to load the oracle plugin, which again is under Plugins -> Summary and is simply called 'oracle' (but as far as I know that just enables things like the database output window). If I run the statement: DECLARE v1 NUMBER(3); BEGIN v1 := 3; select v1 from dual END; / I get: Error: ORA-06550: line 5, column 2: PLS-00428: an INTO clause is expected in this SELECT statement SQLState: 65000 ErrorCode: 6550 Position: 991 .. all procedural oracle code uses SELECT INTO. However, if I do: DECLARE v1 NUMBER(3); BEGIN select 3 into v1 from dual; dbms_output.put_line(v1||chr(13)); END; / I get '3' in the oracle output window ('View Oracle Database Output' quick icon opens this up) Regards D.

------------------------------------------------------------------------------
Write once. Port to many.
Get the SDK and tools to simplify cross-platform app development. Create 
new or port existing apps to sell to consumers worldwide. Explore the 
Intel AppUpSM program developer opportunity. appdeveloper.intel.com/join
http://p.sf.net/sfu/intel-appdev

_______________________________________________
Squirrel-sql-users mailing list
Squirrel-sql-users <at> lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users

headers

John Hardin | 22 Dec 2011 15:43

Re: Run Oracle scripts on Squirrel SQL

On Thu, 22 Dec 2011, Drav Sloan wrote:

> Matheus Garcia wrote:
>> when I try to run I got the
>> following error: "java.lang.IllegalArgumentException: No SQL selected
>> for execution."
>
> Make sure you leave no gaps in your SQL

or make sure you select (highlight) the entire block you want executed 
before hitting Run.

--

-- 
  John Hardin KA7OHZ                    http://www.impsec.org/~jhardin/
  jhardin <at> impsec.org    FALaholic #11174     pgpk -a jhardin <at> impsec.org
  key: 0xB8732E79 -- 2D8C 34F4 6411 F507 136C  AF76 D822 E6E6 B873 2E79
-----------------------------------------------------------------------
   "Bother," said Pooh as he struggled with /etc/sendmail.cf, "it never
   does quite what I want. I wish Christopher Robin was here."
                                            -- Peter da Silva in a.s.r
-----------------------------------------------------------------------
  3 days until Christmas

------------------------------------------------------------------------------
Write once. Port to many.
Get the SDK and tools to simplify cross-platform app development. Create 
new or port existing apps to sell to consumers worldwide. Explore the 
Intel AppUpSM program developer opportunity. appdeveloper.intel.com/join
http://p.sf.net/sfu/intel-appdev

headers

Robert Manning | 22 Dec 2011 19:42

Re: Run Oracle scripts on Squirrel SQL

SQuirreL (just like Oracle's SQL-Plus) needs to know when you have finished you anonymous procedure. That is usually done by adding a line at the end of your procedure with a sinlge slash (/) character. For example:

DECLARE
v1 NUMBER(3);

BEGIN
v1 := 3;
select 1 from dual
END;
/

Note: you will need to highlight the entire procedure since there are 2 eol characters between the semi-colon of the number declaration and the BEGIN keyword.

Rob

On Thu, Dec 22, 2011 at 7:53 AM, Matheus Garcia <matheus.figueiredo <at> almg.gov.br> wrote:

Hi all,

I'm used to create and run SQL scripts in Microsoft SQL Server with
Query Analyser tool, something simple just like declaring some
variables, setting their values and using them on sql queries. Like the
following example:

DECLARE
<at> foo INTEGER
SET <at> foo = 1
INSERT INTO mytable (foofield) value ( <at> foo)

Although I saw it's possible to do something like that in Oracle PL-SQL,
I couldn't run any samples I got on internet in SQuirrel SQL. My oracle
connection is correctly configured, I can run simple sql commands, but
when I try to somethibng like the the following code:

DECLARE
v1 NUMBER(3);

BEGIN
v1 := 3;
select 1 from dual
END;

the DECLARE keywork appears in red, and when I try to run I got the
following error: "java.lang.IllegalArgumentException: No SQL selected
for execution."

Could any of you please give me some help on doing that? Or running
scripts these way is not supported on Squirrel?

Best regards,
Matheus

------------------------------------------------------------------------------
Write once. Port to many.
Get the SDK and tools to simplify cross-platform app development. Create
new or port existing apps to sell to consumers worldwide. Explore the
Intel AppUpSM program developer opportunity. appdeveloper.intel.com/join
http://p.sf.net/sfu/intel-appdev
_______________________________________________
Squirrel-sql-users mailing list
Squirrel-sql-users <at> lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users

------------------------------------------------------------------------------
Write once. Port to many.
Get the SDK and tools to simplify cross-platform app development. Create 
new or port existing apps to sell to consumers worldwide. Explore the 
Intel AppUpSM program developer opportunity. appdeveloper.intel.com/join
http://p.sf.net/sfu/intel-appdev

_______________________________________________
Squirrel-sql-users mailing list
Squirrel-sql-users <at> lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users