Jobs gets hanged when a call is made to PLSQL function in Data Services XI

[Former Member]

Hi,

I am facing the below issue after migration of BODI 11.7 to BODS XI 3.1.

The job is not proceeding after the below mentioned statements.

print('before call');

$is_job_enable=DS_TEST.TEST.MY_PKG.IS_JOB_ENABLED(job_name());

print($is_job_enable);

MY_PKG.IS_JOB_ENABLED plsql function will return Number.

$is_job_enable is a global variable declared as decimal (10, 0).

This Job works fine in Data Integrator 11.7.3 version and gets handed in Data Services XI 3.1.

I tried changing the global variable $is_job_enable to int and created new data sources before doesn't solve the problem. Can anyone tell me what is the issue?

Thanks & Regards

Maran MK

The trace file says

5260	3284	JOB	5/5/2009 4:43:17 AM	Job <TEST_JOB> is started.
5260	3284	PRINTFN	5/5/2009 4:43:17 AM	before call
5260	3284	SP	5/5/2009 4:43:18 AM	Stored procedure call <MY_PKG.IS_JOB_ENABLED> is started.
5260	3284	SP	5/5/2009 4:43:18 AM	SQL query submitted for stored procedure call <MY_PKG.IS_JOB_ENABLED> is: <BEGIN :AL_SP_RETURN :=
5260	3284	SP	5/5/2009 4:43:18 AM	"TEST"."MY_PKG"."IS_JOB_ENABLED"("P_JOB_NAME" => :P_JOB_NAME); END;
5260	3284	SP	5/5/2009 4:43:18 AM	>.
5260	3284	SP	5/5/2009 4:43:18 AM	Stored procedure call <E> input parameter <P> has value of <TEST_JOB>.
5260	3284	SP	5/5/2009 4:43:18 AM	Stored procedure call <E> return value is <1.0000000>.
5260	3284	SP	5/5/2009 4:43:18 AM	Stored procedure call <MY_PKG.IS_JOB_ENABLED> is done.

The below error occurs only in Windows and not in Linux environment.

5260	3284	SYS-170101	5/5/2009 4:43:21 AM	|Session TEST_JOB
5260	3284	SYS-170101	5/5/2009 4:43:21 AM	System Exception <ACCESS_VIOLATION> occurred. Process dump is written to <E:\Program Files\Business Objects\Data
5260	3284	SYS-170101	5/5/2009 4:43:21 AM	Services\log\BODI_MINI20090505044318_5260.DMP> and <E:\Program Files\Business Objects\Data
5260	3284	SYS-170101	5/5/2009 4:43:21 AM	Services\log\BODI_FULL20090505044318_5260.DMP>
5260	3284	SYS-170101	5/5/2009 4:43:21 AM	Process dump is written to <E:\Program Files\Business Objects\Data Services\log\BODI_MINI20090505044318_5260.DMP> and
5260	3284	SYS-170101	5/5/2009 4:43:21 AM	<E:\Program Files\Business Objects\Data Services\log\BODI_FULL20090505044318_5260.DMP>
5260	3284	SYS-170101	5/5/2009 4:43:21 AM	Call stack:
5260	3284	SYS-170101	5/5/2009 4:43:21 AM	001B:00CA9EAB, ActaDecimalImpl<RWFixedDecimal<RWMultiPrecisionInt<3> >,RWMultiPrecisionInt<3>,ActaDecimal28,char
5260	3284	SYS-170101	5/5/2009 4:43:21 AM	[29]>::operator=()0315 byte(s), x:\src\rww\actadecimalimpl.cpp, line 13140004 byte(s)
5260	3284	SYS-170101	5/5/2009 4:43:21 AM	001B:00D8A267, Convert()+0999 byte(s), x:\src\eval\calc.cpp, line 0303
5260	3284	SYS-170101	5/5/2009 4:43:21 AM	001B:00DBF9E0, XVal_cast::compute()+0272 byte(s), x:\src\core\compute.cpp, line 1664
5260	3284	SYS-170101	5/5/2009 4:43:21 AM	001B:00DBC239, XStep_assn::execute()+0057 byte(s), x:\src\core\step.cpp, line 0069
5260	3284	SYS-170101	5/5/2009 4:43:21 AM	001B:00DBB30D, XStep_sblock::execute()+0029 byte(s), x:\src\core\step.cpp, line 0707
5260	3284	SYS-170101	5/5/2009 4:43:21 AM	001B:00DBB30D, XStep_sblock::execute()+0029 byte(s), x:\src\core\step.cpp, line 0707
5260	3284	SYS-170101	5/5/2009 4:43:21 AM	001B:00DBE0BC, XPlan_spec::execute()+0348 byte(s), x:\src\core\plan.cpp, line 0082
5260	3284	SYS-170101	5/5/2009 4:43:21 AM	001B:00DC5EA0, XPlan_desc::execute()+0336 byte(s), x:\src\core\xplan.cpp, line 0153
5260	3284	SYS-170101	5/5/2009 4:43:21 AM	001B:00DBD68E, XPlan_spec::compute()0206 byte(s), x:\src\core\plan.cpp, line 01450011 byte(s)
5260	3284	SYS-170101	5/5/2009 4:43:21 AM	001B:00DBD891, XPlan_spec::compute()+0225 byte(s), x:\src\core\plan.cpp, line 0244
5260	3284	SYS-170101	5/5/2009 4:43:21 AM	001B:0074533A, AE_Main_Process_Options()+31498 byte(s), x:\src\xterniface\actamainexp.cpp, line 3485
5260	3284	SYS-170101	5/5/2009 4:43:21 AM	001B:00747EDA, AE_Main()1498 byte(s), x:\src\xterniface\actamainexp.cpp, line 07680030 byte(s)
5260	3284	SYS-170101	5/5/2009 4:43:21 AM	001B:004029F9
5260	3284	SYS-170101	5/5/2009 4:43:21 AM	Registers:
5260	3284	SYS-170101	5/5/2009 4:43:21 AM	EAX=0000000E  EBX=03E392E0  ECX=04B455A0  EDX=012346D8  ESI=02B75D88
5260	3284	SYS-170101	5/5/2009 4:43:21 AM	EDI=04B455A0  EBP=00212738  ESP=002124BC  EIP=00CA9EAB  FLG=00210206
5260	3284	SYS-170101	5/5/2009 4:43:21 AM	CS=001B   DS=0023  SS=0023  ES=0023   FS=003B  GS=0000
5260	3284	SYS-170101	5/5/2009 4:43:21 AM	Exception code: C0000005 ACCESS_VIOLATION
5260	3284	SYS-170101	5/5/2009 4:43:21 AM	Fault address:  00CA9EAB 01:00585EAB E:\Program Files\Business Objects\Data Services\bin\acta.dll

[Former Member]

what is getting printed for the following line in script in the trace file

print($is_job_enable);

there is bug in 12.1, in which for Oracle function, the return value is returned as first argument of the function, is the above line printing the name of the job or 1.0000000

try changing the type of the varaible ($is_job_enable) to VARCHAR

let me check if this is fixed in any of 12.1 Hot Fixes or its fixed in 12.2

from the stack trace looks like its failing while assigning a decimal value and its failing in conversion

[Former Member]

Hi Manoj & Tiji,

Thanks for your comments. Please find the below outcome.

print($is_job_enable); -- is not executed if PLSQL function is called.

I changed $is_job_enable to VARCHAR, still the same issue.

I created new project and executed the same in new job, still the same issue (all objects are new except Datastore).

The dmp happens only when we the PLSQL function is called. I commented the Function call, the execution proceeds further but got hanged in other PLSQL function call (different than the 1st one)

Is this bug in 12.1?

Can you tell any Hot fix available? If possible please give me the SAP Notes Number.

Is there any other way to execute the PLSQL functions/procedures in 12.1?

Thanks

Maran MK

[Former Member]

may be you are running into a different issue then, let me try to reproduce this with the version that you are using on Linux. Can you tell me the exact version of al_engine, run the following command from Linux

cd $LINK_DIR\bin

al_engine -v

yes, this looks like bug to me, there is nothing wrong in the way you are calling the function, moreover you mentioned that its working fine in 11.7.3

is it working on windows job server ? in that case what is the output for print($is_job_enable);

[Former Member]

Hi Manoj,

al_engine version is BusinessObjects Data Services Engine Version 12.1.0.0 in Linux Machine.

In windows Job server also the same issue. But in Linux no error is displayed. In windows i am getting that dump error.

At neither case the print($is_job_enable) is not printed..

Thanks

Maran

[Former Member]

this is a bug in 12.1 release and is not fixed in 12.1.1.1 either

this issue is fixed in DS XI 3.2 (12.2.0.0), net yet released

the stack trace on Linux will be generated in $LINK_DIR\log\stack_trace.txt file, check this file you will see the same stack trace that you see in windows, also you will see core file named as core.<number> in the $LINK_DIR\bin directory, you can delete those, else will take unnecessary space

please file a incident with support for 12.1

if you are not using lot of Oracle function then you can try following workaround,

wrap the oracle function call in a stored procedure and get the return value of function as OUTPUT param of the stored procedure and from DI call stored proc instead of function

[Former Member]

Thanks Manoj. I will raise with support if I get access.

[Former Member]

I don't see what the problem is but I would suggest you try the following.

- Reimport the function from the database

- Validate all objects in your job

It seems like the funciton is executed successfully but the next step is not initiated. What is the next step in your job after the function is executed and status is printed?

Thanks & Regards

Tiji

[Former Member]

Hi Tiji,

Thanks for your reply. I tried with the steps you have mentioned.

1. Reimport of the Function

2. Validate all objects in the job.

Still the same error.

My next statement after function call is print statement. Just to check I have added it. But it is not even executing the print statement itself. It gets hanged after the function call. But Function is done message is the last message in trace file.

Is anything on PageCache needs to be configured. I have created a folder in which the drive has around 7GB free space.

Thanks

Maran

[Former Member]

I understand this was working in your previous version. To test the source of your problem, can you comment the call to the function and run the job to see if you see the same dmp file in windows?

I doubt the function is causing the error because it returns the value after execution.

If commenting the function does not remove the error, then add a new script object in your job and paste the script into this new script object and delete the old script object. See if you are still facing the error.

Although, this is not the best solution, try creating a new a job and create a new script object with the same statements and see if you have the same problem.

If we still have the same problem, then I think it is time to send the DMP file to the customer support so that they can analyze it.

Hope this helps!

Thanks & Regards

Tiji