Calling an oracle stored procedure with SqlDB or Zeos components lazarus / 16-Databases ---------------------------------------------------------------------- flamer0n · 2015-02-24 14:38 Hi guys! I need some help. Straight to the point: calling a stored procedure from a TSqlQuery using a [CALL ProcName(params)]  statement always seems to fail with "not a valid function or stored procedure name". On the other hand, using TZQuery it always fails with "zero iteration count" . I had this issue long time ago but I kinda skipped it and continued with other things in lazsqlx. I also tried calling SPs this way [owner.package.procname(params)] but same errors. I don't know if I'm doing something wrong here, but I need hints. Thanks in advance. ---------------------------------------------------------------------- LacaK · 2015-02-25 05:31 I am not Oracle user, but try enclose CALL between BEGIN and END:   SQL.Text := 'BEGIN CALL ProcName(params); END'; (googling "calling Oracle stored procedure in OCI") ---------------------------------------------------------------------- flamer0n · 2015-02-25 08:35 Thank you for the answer. I think i tried that too, anyway I tried it again with both components Sqldb and Zeos. After trying that on many stored procedures (some created from the user, some from the system, some with some without parameters), basically in one line encapsulating proc call  with begin end, with sqldb a different error is raised now  ("ora-06550 line 1 column 12 pls-00103 encountered the symbol [ProcName] when excpecting one of the following: := . ( @ % ;") ; zeos still same ("zero iteration count" - btw I've asked this question in zeos forum too, and post is waiting for approval and I doubt anyone will respond); So my question is, has anyone in this forum managed to execute an oracle sp successfully from sqldb query component (or zeos)? If yes, could anyone provide me with an example? And if it's not possible then why it is so? ---------------------------------------------------------------------- miab3 · 2015-02-25 18:11 @flamer0n, Why do not you use TZStoredProc? Working example: Code: [Select]var i:integer; begin ZStoredProc1.Close; ZStoredProc1.StoredProcName:='ABTEST'; ZStoredProc1.Params[0].Value:=50; ZStoredProc1.Params[1].Value:=100; ZStoredProc1.Params[2].Value:='ab'; Edit1.Text:=''; for i := 0 to 9 do begin   ZStoredProc1.Params[1].Value:=i;   ZStoredProc1.ExecProc;   Edit1.Text:=Edit1.Text+ZStoredProc1.Params[3].AsString+' '+ZStoredProc1.Params[4].AsString; end; ZStoredProc1.Open; end; Michal ---------------------------------------------------------------------- flamer0n · 2015-02-25 21:06 @miab3 thanks for response. I'm basically trying to make it possible for users to be able to call stored procedures form LazSqlX (which optionally uses either sqldb or zeos to connect to servers) and view the results. But if using TZStoredProc is the only way for Oracle, I guess I'll have to parse the text from query editor and run it with TZStoredProc ---------------------------------------------------------------------- flamer0n · 2015-02-26 13:53 It doesn't work with TZStoredProc either. When in designtime, when trying to set ProcedureName with Lazarus 1.4 RC1 (fpc 3.0.1 ) it crashes the IDE , but with Lazarus 1.2.6 (fpc 2.6.4) Stable  there's an error [SQL Error: OCI_ERROR: ORA-00904: "OVERLOAD": invalid identifier]. Then I tried that with code adding parameters manually assigning values, but it didn't work. I tried many different things like different stored procedures on different oracle servers but no luck. This is tested with zeos 7.1.3a Stable, also with 7.1.4 Stable. With both same results. ---------------------------------------------------------------------- miab3 · 2015-02-26 15:15 @flamer0n, Works for me (the previous example) for: Windows7-64, CodeTyphon 5.30 Revision: 005221 (Beta1) Win32 and 64, ZEOS 7.2 svn 3619, http://svn.code.sf.net/p/zeoslib/code-0/branches/testing-7.2/ Oracle XE 11.2g. (In Delphi XE2-32 / 64 also work) And here's the procedure: Code: [Select]CREATE procedure SYSTEM.ABTEST   (P1 NUMBER, P2 NUMBER, P3 VARCHAR,   P4 OUT NUMBER, P5 OUT VARCHAR) IS BEGIN   p4 := p1 * 10 + p2;   p5 := p3 || p3; END; / Michal ---------------------------------------------------------------------- flamer0n · 2015-02-26 21:46 @miab thank you! I think I'll try that tomorrow with zeos from svn, but I prefer to stick with Lazarus; maybe I'll try it with CodeTyphon just to see if I can get this thing working ---------------------------------------------------------------------- miab3 · 2015-02-27 13:36 @flamer0n, The same works for me for: - Lazarus 1.2.6-Win64 (fpc 2.6.2) on Windows 8.1-64, - Lazarus 64(fpc 2.7.1) on Fedora-x86_64-20-1(CodeTyphon ver 4.80). Michał ---------------------------------------------------------------------- Zoran · 2015-02-28 15:23 I tried with Zeos ZQuery, using ExecSQL. I did not use ZStoredProc. Here is what I found out: ZQuery1.SQL can be either 1. Code: [Select]call MyProc()or 2. Code: [Select]begin MyProc(); end; Important: 1. If you use "call", you must NOT write semicolon to terminate the statement. 2. If you use begin-end, you must NOT write "call" and you must use two semicolons - to terminate the statement inside begin-end block and to terminate the final "end". Then in pascal code, execute the query with ZQuery1.ExecSQL; ---------------------------------------------------------------------- flamer0n · 2015-02-28 23:34 thank you very much for your help mijab3 and zoran ---------------------------------------------------------------------- flamer0n · 2015-03-03 11:32 unfortunately using lazarus I still can't make it work having done all what you suggested ---------------------------------------------------------------------- marcov · 2015-03-03 11:58 (afaik Oracleconnection saw quite some patches in the last year. 2.6.4 should contain some of those already though. Still, wouldn't hurt to try a SVN version) ---------------------------------------------------------------------- flamer0n · 2015-03-03 12:23 ok I'm getting fpc Revision 30083 and try to compile all with that ---------------------------------------------------------------------- Zoran · 2015-03-03 13:18 Sorry to here the it still doesn't work for you. I have only stable fpc 2.6.4 and here it works as I described above. It works here with fpc 2.6.4, Lazarus 1.4RC2, Zeos from trunk. ---------------------------------------------------------------------- flamer0n · 2015-03-03 13:35 Zoran do you mind doing that from LazSqlX and see if it works? I mean write the query to call oracle procedure from lazsqlx and execute it ? Optionally on connection dialog you can choose which components to use (sqldb or zeos) to execute queries and maybe spot the differences. compile sources yourself from here https://github.com/flakron- shkodra/LazSqlX or install latest windows setup http://sourceforge.net/projects/lazsqlx/ (which I built yesterday using Lazarus 1.4RC2 with fpc 2.6.4  and Zeos7.2 from svn) of course i understand if you don't have time but I just want to know where is the problem: is it my ignorance or database servers I'm running queries against or laz/fpc or zeos or sqldb? I still couldn't execute not even one simple parameterless, resultless procedure so I don't know how it's gonna be after it executes thanks ---------------------------------------------------------------------- Zoran · 2015-03-03 14:04 Okay, I'll try. I'm just about to finish my working time today and at home I do not have Oracle. Tomorrow I will try. ---------------------------------------------------------------------- flamer0n · 2015-03-03 15:28 thanks ---------------------------------------------------------------------- Zoran · 2015-03-04 14:50 I just couldn't connect to oracle server with LazSqlX (with Zeos). In your case, how do you connect with ZConnection? In past I have experienced a lot of trouble before managed to connect to oracle server. Eventually, I succeeded, but I still don't like and don't really understand the way I do it. If there is a better way, I would really like to know. In my application: I leave Server property empty LibraryLocation = path to oci.dll Protocol = oracle What is put in Database property I do not understand well, but my system admins gave me that and they tried to connect and succeded. See (the words in italic are of course replaced with actual addresses or names): Quote(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = server_ip)(PORT = port)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = instance_name_on_the_server))) So, I tried the same in LazSqlX, but it fails to connect. See attached picture.