[Squirrel-sql-users] Executing stored procedures for Oracle

[Stephen Hull]

Lots of good functionality to Squirrel-sql. However, is there a way to
execute a stored procedure?



I can view the procedure source, I can do "create and replace
procedure", but I can not execute the procedure. For example:



execute asc_version ('3-2-1-030519', 'HP 9Z','Cashier Session Report
fix', NULL);



Any suggestions?



Thanks,



Steve



Stephen Hull

ASIX, Inc.

[Colin Bell]

Try

I believe the syntax for Oracle is something like:

begin
asc_version ('3-2-1-030519', 'HP 9Z','Cashier Session Report fix', NULL);
end;

Just remember to change the Statement Separator setting in your session from ; to something else before you try running the procedure.

You could also try the JDBC escape syntax:

{call asc_version ('3-2-1-030519', 'HP 9Z','Cashier Session Report fix', NULL)}

--
Colin Bell
http://squirrel-sql.sf.net

----- Original Message -----
From: Stephen Hull <***@asix.com>
Date: Tuesday, November 18, 2003 4:53 am
Subject: [Squirrel-sql-users] Executing stored procedures for Oracle

Post by Stephen Hull
Lots of good functionality to Squirrel-sql. However, is there a
way to
execute a stored procedure?
I can view the procedure source, I can do "create and replace
execute asc_version ('3-2-1-030519', 'HP 9Z','Cashier Session Report
fix', NULL);
Any suggestions?
Thanks,
Steve
Stephen Hull
ASIX, Inc.

[Maury Hammel]

Post by Colin Bell
begin
asc_version ('3-2-1-030519', 'HP 9Z','Cashier Session Report fix', NULL);
end;

Yes, that is the correct syntax (My understanding is that 'execute' is a
SQL*Plus-specific thing. SQL*Plus drops the execute and wraps a
begin/end around the line of code in the background). You can also do:

declare
...
begin
...
end;

if you need to define variables that are used in the PL/SQL block.

Post by Colin Bell
Just remember to change the Statement Separator setting in your session from ;
to something else before you try running the procedure.

Ah, that's how you do it. I was wondering why I could never get a
begin/end block to work. I thought that block was getting split up into
multiple statements, but for whatever reason changing the Statement
Separator didn't occur to me. Thanks Colin. (this may be something for
a FAQ?)

Colin, just out of curiosity, does the SQuirreL Plug-In API have
anything that would allow a plug-in to define code-block
initiators/terminators (i.e. begin/end or declare/end) to tell SQuirreL
not to split those blocks up into multiple statements, irregardless of
the current Statement Separator?

Post by Colin Bell
{call asc_version ('3-2-1-030519', 'HP 9Z','Cashier Session Report fix', NULL)}

That doesn't appear to work with Oracle. When I try this, I get a
error: "Error: java.sql.SQLException: ORA-00928: missing SELECT keyword".


Maury...

[Colin Bell]

Post by Maury Hammel
Ah, that's how you do it. I was wondering why I could never get a
begin/end block to work. I thought that block was getting split up into
multiple statements, but for whatever reason changing the Statement
Separator didn't occur to me. Thanks Colin. (this may be something for
a FAQ?)

Certainly is. I've added it to the list.

Post by Maury Hammel
Colin, just out of curiosity, does the SQuirreL Plug-In API have
anything that would allow a plug-in to define code-block
initiators/terminators (i.e. begin/end or declare/end) to tell SQuirreL
not to split those blocks up into multiple statements, irregardless of
the current Statement Separator?

Not yet. If you want to send me a patch it will have :-)


Colin Bell
http://squirrel-sql.sf.net

[Stephen Hull]

To execute the procedure, this is what I did:

call asc_version('3-2-1-030519', 'HP 9Z','Cashier Session Report
fix',NULL);

That's all there was to it. Did not have to use begin/end block, just a
simple call.

Thanks for the help.

Steve

Stephen Hull
ASIX, Inc.

-----Original Message-----
From: squirrel-sql-users-***@lists.sourceforge.net
[mailto:squirrel-sql-users-***@lists.sourceforge.net] On Behalf Of
Maury Hammel
Sent: Tuesday, November 18, 2003 9:40 AM
Cc: squirrel-sql-***@lists.sourceforge.net
Subject: Re: [Squirrel-sql-users] Executing stored procedures for Oracle

Post by Colin Bell
begin
asc_version ('3-2-1-030519', 'HP 9Z','Cashier Session Report fix',

NULL);

Post by Colin Bell
end;

Yes, that is the correct syntax (My understanding is that 'execute' is a

SQL*Plus-specific thing. SQL*Plus drops the execute and wraps a
begin/end around the line of code in the background). You can also do:

declare
...
begin
...
end;

if you need to define variables that are used in the PL/SQL block.

Post by Colin Bell
Just remember to change the Statement Separator setting in your

session from ;

Post by Colin Bell
to something else before you try running the procedure.

Ah, that's how you do it. I was wondering why I could never get a
begin/end block to work. I thought that block was getting split up into

multiple statements, but for whatever reason changing the Statement
Separator didn't occur to me. Thanks Colin. (this may be something for

a FAQ?)

Colin, just out of curiosity, does the SQuirreL Plug-In API have
anything that would allow a plug-in to define code-block
initiators/terminators (i.e. begin/end or declare/end) to tell SQuirreL
not to split those blocks up into multiple statements, irregardless of
the current Statement Separator?

Post by Colin Bell
{call asc_version ('3-2-1-030519', 'HP 9Z','Cashier Session Report

fix', NULL)}

That doesn't appear to work with Oracle. When I try this, I get a
error: "Error: java.sql.SQLException: ORA-00928: missing SELECT
keyword".


Maury...
--
Maury Hammel
Cronus Technologies Inc.

http://www.cronustech.com



-------------------------------------------------------
This SF.net email is sponsored by: SF.net Giveback Program.
Does SourceForge.net help you be more productive? Does it
help you create better code? SHARE THE LOVE, and help us help
YOU! Click Here: http://sourceforge.net/donate/