Test Data Manager

Hi All,

How can we call SQL Function through GTD / FDM from target instance?

scenario: We tried to generate our own sequence for customer ID using Next Val function but its failed out as we are using different sequence. And later we decided the to consume the Next_Customer_ID function in target instance.

But while calling those function we are facing an issue "Aggregate Function are not supported". And we are not able to find any SQL function in GTD to Get our own data base function to generate value to have sync with test environment.

Thanks in Advance!

Regards,

Shahul

Hi Shahul, 

Are you able to upgrade to TDM 3.8?

I think this issue will take a WebEx in order to investigate further. Please open a support case by going to https://support.ca.com/irj/portal/newhome or calling CA Customer Care using one of these numbers http://www3.ca.com/us/support/customer-assistance.aspx. 

We will post the resolution to the issue here once the case is closed. 

Best regards,

Taylor

Hi Gill,

Yes, I have updated GTD to 3.8 and the issue is.

If we try to call the function @execsql(PXXXX_TARGET_CRM_DB,Next_Customer_ID(Contact))@,  Its giving the "ORA-24333: zero iteration count" error.

From above error we come to know that, we can not execute DML statement in the above functions. Because its TDM only having the function to SQL not a PL/SQL code.

So We are trying now to create a Procedure and trying to call the function inside the procedure providing parameter value and store the return value of the function as output parameter using the TDM function

@execsqlproc(PJ2CRMNL_TARGET_CRM_DB,procname,paramname1,paramdirection1,paramvalue1,paramname2,paramdirection2,paramvalue2,...[paramnamen,paramdirectionn,paramvaluen],paramoutname)@

Please suggest is it a good idea or do we need to follow any other mechanism.

Regards,

Shahul

Hi All,

By using the @execsqlproc(PJ2CRMNL_TARGET_CRM_DB,procname,paramname1,paramdirection1,paramvalue1,paramname2,paramdirection2,paramvalue2,...[paramnamen,paramdirectionn,paramvaluen],paramoutname)@ TDM function, we want to user our own oracle store procedure as mentioned in your CA docops section and we noticed that you have given example to connect to SQL server not an Oracle server.

our connection string:

@execsqlproc(PJ2CRMNL_TARGET_CRM_DB,PROCNAME,INPARAM NAME,IN,PARAM DIRECTION'INPARAM VALUE',OUT PARAM NAME1,OUT PARAM DIRECTION)@  

ERROR:

Wrong number of parameters - execsqlproc(PJ2CRMNL_TARGET_CRM_DB,INTF_NEXTOBJID1,STABLENAME,IN,'CONTACT',O_RS,OUT)

Go back and edit the data.

@execsqlproc(PJ2CRMNL_TARGET_CRM_DB,PROCNAME,INPARAM NAME,IN,PARAM DIRECTION'INPARAM VALUE',OUT PARAM NAME1)@  

ERROR:

The parameter name that was entered to get the output data does not correspond to any parameter that is part of the procedure signature

Go back and edit the data.

@execsqlproc(PJ2CRMNL_TARGET_CRM_DB,PROCNAME,INPARAM NAME,IN,PARAM DIRECTION'INPARAM VALUE',OUT PARAM NAME1, OUT PARM DIRECTION1, OUT PARAM VALUE )@  

ERROR:

Wrong number of parameters - execsqlproc(PJ2CRMNL_TARGET_CRM_DB,INTF_NEXTOBJID1,STABLENAME,IN,'CONTACT',O_RS,OUT)

Go back and edit the data.

Please help us out here!

Regards,

Shahul