Print Thread
IN VARCHAR2 params creating errors in cursor
#49531 09/10/14 05:11 PM
Joined: Sep 2014
Posts: 6
A
Member
OP Offline
Member
A
Joined: Sep 2014
Posts: 6
Hi All,

I am using oracle package function having IN, IN OUT and OUT params in functions which is taking few params and giving cursor as output.
TOracle Query is the component used. Please let me know what is wrong I am doing here.
Error when code executes QCursor.Execute; in Delphi code.

Oracle Function:
FUNCTION GETGA_LISTEX(P_CURSOR IN OUT T_CURSOR,
P_PREFIXKEY IN VARCHAR2,
P_STATEKEY IN VARCHAR2,
P_ASLINEKEY IN NUMBER,
P_COMMWILDCHAR IN VARCHAR2,
P_ACTIVE_FLAG IN NUMBER,
P_EFFDATE_FROM IN DATE,
P_EFFDATE_TO IN DATE,
P_TERMDATE_FROM IN DATE,
P_TERMDATE_TO IN DATE,
P_COMPANYID IN NUMBER,
P_PROGMANAGERID IN NUMBER,
P_GA_STATE IN VARCHAR2,
P_GA_PHONE IN VARCHAR2,
P_GA_WEBSITE_WC IN VARCHAR2,
P_MSG OUT VARCHAR2) RETURN NUMBER;

Delphi Code:

function TDMOracle.GetGA_ListEx(var GAIDsList, GANamesList: TStringList;
pPrefixKey, pStateKey: String;
pASLineKey: Integer;
pCommWildChar: String;
pActiveFlag: Integer;
pEffDateFrom: Variant;
pEffDateTo: Variant;
pTermDateFrom: Variant;
pTermDateTo: Variant;
pCompanyID: Integer;
pProgManagerID: Integer;
pAddressState: String;
pPhone: String;
pWebsite: String;
var pMsg: String): Integer;
begin
GAIDsList.Clear;
GANamesList.Clear;

QOracle.Clear;
QCursor.Clear;

with QOracle do begin
SQL.Add('begin');
SQL.Add(':P0 := '+ PACKAGE_NAME +'.GetGA_ListEx(:P1, :P2, :P3, :P4, :P5, '
+ ':P6, :P7, :P8, :P9, :P10, :P11, :P12, :P13, :P14, :P15, :P16);');
SQL.Add('end;');
DeclareVariable('P1', otCursor);
DeclareVariable('P2', otString);
DeclareVariable('P3', otString);
DeclareVariable('P4', otInteger);
DeclareVariable('P5', otString);
DeclareVariable('P6', otInteger);
DeclareVariable('P7', otDate);
DeclareVariable('P8', otDate);
DeclareVariable('P9', otDate);
DeclareVariable('P10', otDate);
DeclareVariable('P11', otInteger);
DeclareVariable('P12', otInteger);
DeclareVariable('P13', otString);
DeclareVariable('P14', otString);
DeclareVariable('P15', otString);
DeclareVariable('P16', otString);
DeclareVariable('P0', otInteger);

SetComplexVariable('P1', QCursor);
SetVariable('P2', pPrefixKey);
SetVariable('P3', pStateKey);
SetVariable('P4', pASLineKey);
SetVariable('P5', pCommWildChar);
SetVariable('P6', pActiveFlag);
SetVariable('P7', pEffDateFrom);
SetVariable('P8', pEffDateTo);
SetVariable('P9', pTermDateFrom);
SetVariable('P10', pTermDateTo);
SetVariable('P11', pCompanyID);
SetVariable('P12', pProgManagerID);
SetVariable('P13', pAddressState);
SetVariable('P14', pPhone);
SetVariable('P15', pWebsite);
SetVariable('P16', pMsg);

Execute;
Result := GetVariable('P0');
pMsg := GetVariable('P15');

QCursor.Execute;
with QCursor do begin
while not EOF do begin
GAIDsList.Add( FieldAsString('GENERAL_AGENT_ID') );
GANamesList.Add( FieldAsString('NAME') );
Next;
end;
end;
end;
end;

Re: IN VARCHAR2 params creating errors in cursor
abdul haq #49554 09/12/14 08:31 PM
Joined: Sep 2014
Posts: 6
A
Member
OP Offline
Member
A
Joined: Sep 2014
Posts: 6
@MARCO

Hello Marco,

Could you please give your valuable suggestions here for my problem.


Moderated by  support 

Link Copied to Clipboard
Powered by UBB.threads™ PHP Forum Software 7.7.4
(Release build 20200307)
Responsive Width:

PHP: 7.1.33 Page Time: 0.026s Queries: 15 (0.006s) Memory: 2.5106 MB (Peak: 3.0397 MB) Data Comp: Off Server Time: 2024-03-29 15:07:22 UTC
Valid HTML 5 and Valid CSS