Hello Alexander,
I've removed almost everything in the procedure.
To make the procedure go, I've removed almost everything; as far as you can see in
the procedure following, the procedure is like a dummy one now and doesn't do anything
at all - only returns a 1.
CREATE FUNCTION ISEMPINUNITDIVISION (
UNITDIVISIONID FIXED(9,0),
ROLETYPEID FIXED(9,0),
EMPCOMPANYID FIXED(9,0),
EMPHUMANRESOURCEID FIXED(9,0),
EMPDSTARTCHR TIMESTAMP,
F_UD_LTD CHAR(1))
RETURNS NUMBER AS
VAR RES INT; N_PATH VARCHAR(4000); MSG VARCHAR(1000); L_EMPDSTARTCHR TIMESTAMP;
BEGIN
IF UNITDIVISIONID IS NULL OR EMPCOMPANYID IS NULL OR EMPHUMANRESOURCEID IS NULL OR
EMPDSTARTCHR IS NULL THEN
RETURN 0;
SET L_EMPDSTARTCHR = EMPDSTARTCHR;
TRY
RETURN 1;
CATCH
IF $RC <> 100 THEN
STOP($RC,$ERRMSG)
ELSE RETURN 0;
END;
//
If only I put a select statement without using any parameters, the procedure crash
(look the following example) .
CREATE FUNCTION ISEMPINUNITDIVISION (
UNITDIVISIONID FIXED(9,0),
ROLETYPEID FIXED(9,0),
EMPCOMPANYID FIXED(9,0),
EMPHUMANRESOURCEID FIXED(9,0),
EMPDSTARTCHR TIMESTAMP,
F_UD_LTD CHAR(1))
RETURNS NUMBER AS
VAR RES INT; N_PATH VARCHAR(4000); MSG VARCHAR(1000); L_EMPDSTARTCHR TIMESTAMP;
BEGIN
IF UNITDIVISIONID IS NULL OR EMPCOMPANYID IS NULL OR EMPHUMANRESOURCEID IS NULL OR
EMPDSTARTCHR IS NULL THEN
RETURN 0;
SET L_EMPDSTARTCHR = EMPDSTARTCHR;
TRY
SELECT 1 INTO :RES
FROM PSF.COMPANY_HR_ROLE A
INNER JOIN PSF.UNIT_DIVISION B
ON A.E_U_DIV_K_UNIT_DIVISION = B.K_UNIT_DIVISION
WHERE
ROWNO<=1;
RETURN 1;
CATCH
IF $RC <> 100 THEN
STOP($RC,$ERRMSG)
ELSE RETURN 0;
END;
//
The error appears in Sql Studio only once after function re-creation (every following
time everything goes fine); however, in Java it repeatedly occures.
It seems that something happens at session' starting.
If you need, I'll send you vTrace file.
Thanks,
Matteo
----- Original Message -----
From: Schroeder, Alexander
To: 'Matteo Gattoni'
Sent: Tuesday, August 03, 2004 4:35 PM
Subject: RE: Invalid Exponent (3)
Hello Matteo
sorry, I misunderstood slightly your previous mail, so you get the -3019 both with
execution in Java as also
with SQL Studio on the SELECT statement with the function.
Anyway, this does also not explain the -3019 to me. Is it possibly for you to strip
further down by removing
statements from the function/changing the SELECT what actually starts causing that
error (as I fear that you
did again run into some trouble with the current state DB function implementation)?
Sorry for any inconvenience caused
Alexander Schr�der
SAP DB, SAP Labs Berlin
-----Original Message-----
From: Matteo Gattoni [mailto:[EMAIL PROTECTED]
Sent: Tuesday, August 03, 2004 4:09 PM
To: Schroeder, Alexander
Subject: Re: Invalid Exponent (3)
Hello Alexander,
This is the application server's configuration
<local-tx-datasource>
<jndi-name>maxdb</jndi-name>
<connection-url>jdbc:sapdb://XXX/XXX?sqlmode=ORACLE</connection-url>
<driver-class>com.sap.dbtech.jdbc.DriverSapDB</driver-class>
<user-name>XXX</user-name>
<password>XXX</password>
</local-tx-datasource>
We are using PreparedStatement in our Java code and the mode is ORACLE both in
Java and in Sql studio.
Thanks,
Matteo
----- Original Message -----
From: "Schroeder, Alexander" <[EMAIL PROTECTED]>
To: "'Matteo Gattoni'" <[EMAIL PROTECTED]>
Sent: Tuesday, August 03, 2004 3:48 PM
Subject: RE: Invalid Exponent (3)
Hello Matteo,
can you try using a 'PreparedStatement' in Java (even if there are no parameters)?
Are you using the same SQL Mode in SQL Studio as in Java ?
Regards
Alexander Schr�der
SAP DB, SAP Labs Berlin
> -----Original Message-----
> From: Matteo Gattoni [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, August 03, 2004 3:41 PM
> To: [EMAIL PROTECTED]
> Subject: Invalid Exponent (3)
>
>
> I've changed the function in the following way
>
> CREATE FUNCTION ISEMPINUNITDIVISION (
> UNITDIVISIONID FIXED(9,0),
> ROLETYPEID FIXED(9,0),
> EMPCOMPANYID FIXED(9,0),
> EMPHUMANRESOURCEID FIXED(9,0),
> EMPDSTARTCHR TIMESTAMP,
> F_UD_LTD CHAR(1))
> RETURNS NUMBER AS
> VAR RES INT; N_PATH VARCHAR(4000); MSG VARCHAR(1000);
> L_EMPDSTARTCHR TIMESTAMP;
> BEGIN
> IF UNITDIVISIONID IS NULL OR EMPCOMPANYID IS NULL OR
> EMPHUMANRESOURCEID IS NULL OR
> EMPDSTARTCHR IS NULL THEN
> RETURN 0;
>
> SET L_EMPDSTARTCHR = EMPDSTARTCHR;
>
> TRY
> IF F_UD_LTD IS NOT NULL AND F_UD_LTD='T' THEN
> SELECT 1 INTO :RES
> FROM
> PSF.UNIT_DIVISION B
> ,PSF.COMPANY_HR_ROLE A
> WHERE
>
> DATE(VALUE(DATE(B.D_END),ADDDATE(TIMESTAMP,1)))>DATE(TIMESTAMP)
> AND DATE(B.D_START)<=DATE(TIMESTAMP)
> AND B.K_UNIT_DIVISION=A.E_U_DIV_K_UNIT_DIVISION
> AND
> DATE(VALUE(DATE(A.D_END),ADDDATE(TIMESTAMP,1)))>DATE(TIMESTAMP)
> AND DATE(A.D_START)<=DATE(TIMESTAMP)
> AND
> A.E_ROLE_TYP_K_ROLE_TYPE=VALUE(:ROLETYPEID,A.E_ROLE_TYP_K_ROLE_TYPE)
> AND A.E_U_DIV_K_UNIT_DIVISION=:UNITDIVISIONID
> AND A.E_COMP_HR_K_COMPANY=:EMPCOMPANYID
> AND A.E_COMP_HR_K_HUMAN_RESOURCE=:EMPHUMANRESOURCEID
> AND DATE(A.E_COMP_HR_D_START)=DATE(:L_EMPDSTARTCHR)
> AND ROWNO<=1
> ELSE
> BEGIN
> SELECT 1 INTO :RES
> FROM PSF.COMPANY_HR_ROLE A
> INNER JOIN PSF.UNIT_DIVISION B
> ON A.E_U_DIV_K_UNIT_DIVISION =
> B.K_UNIT_DIVISION
> WHERE
>
> DATE(VALUE(DATE(B.D_END),ADDDATE(TIMESTAMP,1)))>DATE(TIMESTAMP)
> AND DATE(B.D_START)<=DATE(TIMESTAMP)
> AND
> UNIT_DIVISION_ANCESTOR(B.K_UNIT_DIVISION) = :UNITDIVISIONID
> AND
> DATE(VALUE(DATE(A.D_END),ADDDATE(TIMESTAMP,1)))>DATE(TIMESTAMP)
> AND DATE(A.D_START)<=DATE(TIMESTAMP)
> AND
> A.E_ROLE_TYP_K_ROLE_TYPE=VALUE(:ROLETYPEID,A.E_ROLE_TYP_K_ROLE_TYPE)
> AND A.E_COMP_HR_K_COMPANY=:EMPCOMPANYID
> AND
> A.E_COMP_HR_K_HUMAN_RESOURCE=:EMPHUMANRESOURCEID
> AND DATE(A.E_COMP_HR_D_START)=DATE(:L_EMPDSTARTCHR)
> AND ROWNO<=1;
> END;
> RETURN 1;
> CATCH
> IF $RC <> 100 THEN
> STOP($RC,$ERRMSG)
> ELSE RETURN 0;
> END;
>
> where
>
> CREATE FUNCTION UNIT_DIVISION(C NUMBER) RETURNS NUMBER AS
> VAR FATHER FIXED(9,0); CHILD FIXED(9,0);
> TRY
>
> SET CHILD = C;
>
> WHILE 1 = 1 DO
> BEGIN
> SELECT E_U_DIV_K_UNIT_DIVISION
> INTO :FATHER
> FROM PSF.UNIT_DIVISION
> WHERE
> K_UNIT_DIVISION = :CHILD
> AND D_END IS NULL;
>
> IF FATHER IS NULL THEN
> RETURN CHILD;
>
> SET CHILD = FATHER;
> END;
>
> RETURN FATHER;
>
> CATCH
> IF $RC <> 100 THEN STOP($RC, $ERRMSG)
> ELSE RETURN CHILD;
> //
>
> Recreating all the procedure at once, the first time a submit
> the statement
>
> SELECT
> 1
> FROM
> dual
> WHERE
> 1=isEmpInAggregation(to_number('200004'),
> NULL,to_number('1'),
> to_number('200002'),to_date('21-05-2004','dd/mm/yyyy'))
>
> I receive the error 'Numeric value out of range;-3019 Invalid
> exponent.'
> From that moment on, everything goes ok.....but using Java I
> receive the same error
> 'exception [-3019]: Invalid exponent'.
>
> Why a SQL statement goes with SQL Studio and doesn't with Java?
> Why the first time the statement goes wrong and then everything is ok?
> What's my mistake?
>
> If you need trace or everything else, just ask me.
>
> Thanks,
> Matteo
>
>
