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 :
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"