Page 1 of 1

Execute Oracle Procedure

Posted: Tue Aug 31, 2010 11:43 pm
by ack1970
hi, i have a problem with SQL und oracle, connection via OLE DB / ODBC. i can start all sql-commands, (select, update, insert, delete), because i don't can start a procedure like plsql.... for example:
EXECUTE schema.procedure(value1, value2)
or
begin         & nbsp;         & nbsp;        
EXECUTE schema.procedure(value1, value2)
end
when i commit whit OK then comes the error: E: 01.09.2010 09:26:13: Error has occured: ORA-00900: Ungültige SQL-Anweisung
E: 01.09.2010 09:26:13: An error occured. Step #12 (...EXECUTE Procedure.....)
has anybody a solution for my problem???
many thanks, ack

 

Execute Oracle Procedure

Posted: Wed Sep 01, 2010 12:03 am
by Oleg
I hope that you speak about SQL Query action?

I think you have to write the SQL statement such:

begin
   schema.procedure(value1, value2);
end;

The keyword EXECUTE is valid only for SQL-plus

Execute Oracle Procedure

Posted: Wed Sep 01, 2010 5:55 am
by ack1970
yes thanks, that is what i mean, because this here ";" is not permitted  and this error code is coming on start:
E: 01.09.2010 15:52:50: Error has occured: ORA-06550: Zeile 3, Spalte 1:<LF>PLS-00103: Fand das Symbol "END" als eines der folgenden erwartet wurde:<LF><LF>   := . ( % ;

Execute Oracle Procedure

Posted: Thu Sep 02, 2010 5:04 am
by Oleg
I have tested this on our test Oracle server (10g x32)

The stored procedure on SCOTT schema is:

Code: Select all

CREATE OR REPLACE PROCEDURE "SCOTT"."TEST_PROC" ( 
num in integer 
) 
as 
begin 
update emp set comm = num where comm is null; 
end;
I wrote exactly this (see in attachment) as SQL statement in SQL Query action :

Code: Select all

begin
scott.test_proc(10);
end;
It's working.

if you need to run some procedure you must write the SQL statement such:

begin
   ....
   some statements on PL/SQL syntax
   ....
end;

Also see the working example below:

Code: Select all

;*****************************
;* RoboTask Task file
;* Do not edit in text editor!
;*****************************
 
[Root]
ActionAfterRun=INTEGER|0
Actions=FOLDER
Automat=INTEGER|-1
CatID=INTEGER|856389240
ContinueOnError=INTEGER|0
ExternalName=STRING|"Task5"
Hide=INTEGER|0
ID=INTEGER|1575368261
LogOnAsUser=INTEGER|1
Name=STRING|"Test execute ORA procedure"
OnErrorTaskID=INTEGER|0
Priority=INTEGER|3
RunOnClose=INTEGER|0
RunOnStartup=INTEGER|0
ToLog=INTEGER|3
WriteGeneralLog=INTEGER|0

[Actions]
Action1=FOLDER

[Actions\Action1]
ActionID=STRING|"A_EXECSQL"
Enabled=INTEGER|-1
Name=STRING|"SQL Query"
Params=FOLDER

[Actions\Action1\Params]
assignvar=STRING|"0"
connectionstring=STRING|"Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=scott;Password=tiger;Data Source=orcl"
override=STRING|"0"
password=STRING|"2005620106198561984823363"
sql=STRING|"begin,scott.test_proc(10);,end;"
timeout=STRING|"60"