Devart Forums

I have the following function:
CREATE OR REPLACE FUNCTION company_select(IN v_companynr integer, INOUT swv_refcur refcursor, INOUT swv_refcur2 refcursor)
RETURNS record AS
$BODY$
DECLARE
v_Count SMALLINT;
BEGIN
select Count(*) INTO v_Count From Company Where Company.CompanyNr = v_CompanyNr;
If v_Count = 0 then
open SWV_RefCur for Select 1 AS Err, 'Company not found. (Company_Select)' AS Msg;
Return;
end if;

select Count(*) INTO v_Count From CompanyAccount Where CompanyNr = v_CompanyNr;
If v_Count = 0 then
Insert Into CompanyAccount(CompanyNr) Values(v_CompanyNr);
end if;

open SWV_RefCur2 for Select *,
(select ObjText from ObjDescrCoy
where CompanyNr = 0 and ObjName = 'BANK' and ObjCode = BankNr) AS BankName
From Company, CompanyAccount
Where Company.CompanyNr = v_CompanyNr
And CompanyAccount.CompanyNr = v_CompanyNr;

Insert Into ObjDescrCoy(CompanyNr, ObjName, ObjCode, ObjText)
Select v_CompanyNr, 'COMPUTER', Coalesce(Max(ObjCode),0)+1, SYS_CONTEXT('userenv','host')
From ObjDescrCoy
Where CompanyNr = v_CompanyNr
And ObjName = 'COMPUTER'
And Not Exists(Select * From ObjDescrCoy x
Where x.CompanyNr = v_CompanyNr
And x.ObjName = 'COMPUTER'
And x.ObjText = SYS_CONTEXT('userenv','host'));

Insert Into ObjDescrCoy(CompanyNr, ObjName, ObjCode, ObjText)
Select v_CompanyNr, 'USER', Coalesce(Max(ObjCode),0)+1, CURRENT_USER
From ObjDescrCoy
Where CompanyNr = v_CompanyNr
And ObjName = 'USER'
And Not Exists(Select * From ObjDescrCoy x
Where x.CompanyNr = v_CompanyNr
And x.ObjName = 'USER'
And x.ObjText = CURRENT_USER);
END; $BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
...................
Now, how in the world can I access swv_refcur, swv_refcur2 in Delphi?

Hello,


The code specified below shows how to work with refcursor through PgDAC:

Thank you!
PgQuery1.Cursor := PgStoredProc.ParamByName('swv_refcur').AsCursor;
raises an error:
function sys_context(unknown, unknown) does not exist.

And I thought T-SQL was difficult!

Hello,

There is no sys_context function in PostgreSQL (this is an Oracle function). You should use functions available in PostgreSQL.

That's my problem!
I have no idea where postgesql is getting this error message. As far as I know, I call the function, (using your method), and the answer I get back is the error "function sys_context...etc., etc.)"
Maybe better just to re-write the function and not use cursors at all would be the better idea?
By the way PostgreSQL version is version 9.
I do not have Oracle on my machine.

MY APOLOGIES!
I now see the sys_context line! I'm blind!
The system I'm working on was "translated" from MSSQL to PostgreSQL, and the translation has inserted this function.
I will carry on and build further work-arounds!
I have already migrated one system to posgres + pgDAC very successfully - AND IT'S VERY FAST! Only 6 million records, but going up each month........hope it still performs like it does now!

Hello,

PgDAC shows high performance even when working with very large amounts of data, so it is not likely that you encounter any problems even when the number of records increases. If you have any further questions, please contact us and we will try to help you.