calling stored procedures via oracle odbc

dotyet · *nix forums addict Joined: 06 Jul 2005 Posts: 55

wrong group!!

post your query here

http://groups-beta.google.com/group/comp.databases.oracle.server

regards,
dotyet

Harp · *nix forums beginner Joined: 01 Mar 2005 Posts: 2

I created a stored procedure in oracle with the help of sql +plus:

Set serveroutput on
create or replace procedure spComp(company IN CHAR,oFname OUT CHAR,
oFamname OUT CHAR, oAdr OUT CHAR, oTelno OUT CHAR)

AS

Cursor SelWorkers IS
SELECT
firstname, familyname, address, telno
FROM customers
WHERE company = 'Compname';

cnt NUMBER DEFAULT 1;

BEGIN
DBMS_OUTPUT.PUT_LINE('Employees of Companyname');
DBMS_OUTPUT.PUT_LINE('firstname familyname address telno');
FOR customers IN SelWorkers LOOP
oFname := customers.firstname;
oFamname := customers.familyname;
oAdr := customers.address;
oTelno := customers.telno;
cnt := cnt + 1;
DBMS_OUTPUT.PUT_LINE(customers.firstname||' :
'||customers.familyname||' : '||customers.address||' :
'||customers.telno);
END LOOP;
END spComp;
/

DECLARE
oFname CHAR(15);
oFamname CHAR(15);
oAdr CHAR(20);
oTelno CHAR(7);
BEGIN
spComp('Companyname',oFname,oFamname,oAdr,oTelno);
END;
/
The results were okay.
Now I am trying to call it via odbc.
All about connections were okay.

AnsiString stmt = "CALL
spComp('Companyname',firstname,familyname,address,telno)";
ret = SQLAllocStmt(hDBconn, &hstmt);//locate the statement ok
ret = SQLExecDirect(hstmt, stmt.c_str(), SQL_NTS);

immediately I step in here trying to execute, I get ret delivers -1
is there any tricks I am missing? Also bei Prepare, same problem. If I
just use normal sql-stament, it works fine.
How can I call this procedure well?

I am using C++Builder6 as my developing environment, Oracle9i.

Thanks in advance,
Harp

Google