Recent

Author Topic: Calling an oracle stored procedure with SqlDB or Zeos components  (Read 11517 times)

flamer0n

  • Guest
Calling an oracle stored procedure with SqlDB or Zeos components
« on: February 24, 2015, 03:38:16 pm »
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

  • Hero Member
  • *****
  • Posts: 691
Re: Calling an oracle stored procedure with SqlDB or Zeos components
« Reply #1 on: February 25, 2015, 06:31:52 am »
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

  • Guest
Re: Calling an oracle stored procedure with SqlDB or Zeos components
« Reply #2 on: February 25, 2015, 09:35:14 am »
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

  • Full Member
  • ***
  • Posts: 145
Re: Calling an oracle stored procedure with SqlDB or Zeos components
« Reply #3 on: February 25, 2015, 07:11:20 pm »
@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
« Last Edit: February 25, 2015, 07:13:56 pm by miab3 »

flamer0n

  • Guest
Re: Calling an oracle stored procedure with SqlDB or Zeos components
« Reply #4 on: February 25, 2015, 10:06:57 pm »
@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

  • Guest
Re: Calling an oracle stored procedure with SqlDB or Zeos components
« Reply #5 on: February 26, 2015, 02:53:23 pm »
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

  • Full Member
  • ***
  • Posts: 145
Re: Calling an oracle stored procedure with SqlDB or Zeos components
« Reply #6 on: February 26, 2015, 04:15:22 pm »
@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
« Last Edit: February 26, 2015, 04:19:14 pm by miab3 »

flamer0n

  • Guest
Re: Calling an oracle stored procedure with SqlDB or Zeos components
« Reply #7 on: February 26, 2015, 10:46:19 pm »
@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

  • Full Member
  • ***
  • Posts: 145
Re: Calling an oracle stored procedure with SqlDB or Zeos components
« Reply #8 on: February 27, 2015, 02:36:24 pm »
@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

  • Hero Member
  • *****
  • Posts: 1829
    • http://wiki.lazarus.freepascal.org/User:Zoran
Re: Calling an oracle stored procedure with SqlDB or Zeos components
« Reply #9 on: February 28, 2015, 04:23:01 pm »
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

  • Guest
Re: Calling an oracle stored procedure with SqlDB or Zeos components
« Reply #10 on: March 01, 2015, 12:34:31 am »
thank you very much for your help mijab3 and zoran

flamer0n

  • Guest
Re: Calling an oracle stored procedure with SqlDB or Zeos components
« Reply #11 on: March 03, 2015, 12:32:49 pm »
unfortunately using lazarus I still can't make it work having done all what you suggested

marcov

  • Administrator
  • Hero Member
  • *
  • Posts: 11383
  • FPC developer.
Re: Calling an oracle stored procedure with SqlDB or Zeos components
« Reply #12 on: March 03, 2015, 12:58:05 pm »
(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

  • Guest
Re: Calling an oracle stored procedure with SqlDB or Zeos components
« Reply #13 on: March 03, 2015, 01:23:32 pm »
ok I'm getting fpc Revision 30083 and try to compile all with that

Zoran

  • Hero Member
  • *****
  • Posts: 1829
    • http://wiki.lazarus.freepascal.org/User:Zoran
Re: Calling an oracle stored procedure with SqlDB or Zeos components
« Reply #14 on: March 03, 2015, 02:18:54 pm »
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.

 

TinyPortal © 2005-2018