1. Luca Militello
  2. PowerBuilder
  3. Monday, 10 December 2018 15:15 PM UTC

Hi,

an error occurs while calling Oracle PLSQL functions using Powerbuilder 12.5.

This is one of the function call:

trans_crcc.cl_fun_esiste_sosp(al_id_anag_rem,ls_codct)

trans_crcc is an  transaction object connected to DB with no problems

al_id_anag is a long variable and ls_codct a string

This is the SQLCA error:

ORA-01741: illegal zero-length identifier

The function does not give problems using it from TOAD or SQL-NAVIGATOR.

Moreover the same PB application compiled with PB9  works fine. No error occurs.

The application has datawindows that extract data using the trans_crcc with no problems in PB12.5 or PB9.

Error happens only with PB 12.5 calling any PL SQL functions.

Any suggestions?

 

Thanks

 

Luca Militello

 

 

 

 

 

 

Luca Militello Accepted Answer Pending Moderation
  1. Tuesday, 11 December 2018 15:59 PM UTC
  2. PowerBuilder
  3. # 1

Hi guys,

the situation is more complex than i said before.

Function cl_fun_esiste_sosp(al_id_anag_rem,ls_codct) resides  in a DB located to another server, so trans_crcc can access at this function by a DBlink connection.

However the function works fine if i try to launch it from TOAD or SQL-Navigator, for example:

Select CL_FUN_ESISTE_SOSP ( 53635, '082345' ) from DUAL;

where CL_FUN_ESISTE_SOSP is the synonym to the CL_FUN_ESISTE_SOSP function

I had several tests from Powerbuilder and i found another strange thing.

If i create this test functions to the server linked by the trans_crcc, as is:

----------------------------------------------------- FUNCTION TEST 1

create or replace function cl_fun_prova_luca_1( an_id_anag IN number) return integer is
Result integer;
begin

return(10);
end cl_fun_prova_luca_1;

----------------------------------------------------- FUNCTION  TEST 2

create or replace function cl_fun_prova_luca_2( av_id_codct IN varchar2) return integer is
Result integer;
begin

return(10);
end cl_fun_prova_luca_3;

 

after that i create the 2 synonyms that uses Dblink (cl_fun_prova_luca_1 and cl_fun_prova_luca_2) in the schema of the trans_crcc, i notice that only if the function has varchar parameter (as in cl_fun_prova_luca_2) the error occurs; in all other cases (as  cl_fun_prova_luca_2 without any varchar paramter ) the function call works well.

This is the functions declaration in PB 12.5:

function double cl_fun_prova_luca_1(double AN_ID_ANAG) RPCFUNC

function double cl_fun_prova_luca_2(string AV_ID_CODCT) RPCFUNC

 

Any ideas?

Thank you very much

 

Luca

 

 

 

Comment
There are no comments made yet.
Luca Militello Accepted Answer Pending Moderation
  1. Monday, 10 December 2018 22:05 PM UTC
  2. PowerBuilder
  3. # 2

Hi Chris,

Here is the function declaration:

 

function double CL_FUN_ESISTE_SOSP(double AN_ID_ANAG,string AV_ID_CODCT) RPCFUNC

 

Luca

 

Comment
  1. Luca Militello
  2. Friday, 14 December 2018 10:27 AM UTC
Hi Chris,

i tried using a cursor.

First i have created a test procedure to make sure about the correct syntax.

After that, i tested my real SP. Here the declaration and call:



Declare CL_PROCIDONEITASTIME Procedure for CL_PROCIDONEITASTIME(double :ld_id_anag, double :ld_crd) using trans_crcc;



Execute CL_PROCIDONEITASTIME;



fetch CL_PROCIDONEITASTIME into :atd_descr_emc[40],:ate_emcdon1[40], :atd_datastimata1[40], :atw_warnings1[5], :atw_warnings2[100], :ate_erremc1[40], :avc_err1;



but always the same error occurs.... ORA-01741: illegal zero-length identifier

i think the problem is always about string parameter.



I also tried initializing the output variables, but without success.



IMPORTANT NOTICE: the same application in PB9 has no problem, the original call:



trans_crcc.cl_procidoneitastime(ld_id_anag, ld_crd, &

ls_descrizione, &

ld_emc_don,&

ls_data_stimata,&

ls_warnings_generali,&

ls_warnings_emc,&

ls_errore_emc,&

ls_errore)



works fine (also for functions).



Maybe the last way in Pb12.5 is using DW, but, as i said, i don't understand why i can't get it my procedure in the SP list of datasource.

Other suggestions?



Thanks



Luca



  1. Helpful
  1. Chris Pollach @Appeon
  2. Friday, 14 December 2018 17:15 PM UTC
I wonder if its some kind of ANSI to Unicode (and vise versa) issue?

Maybe a DB driver setting?
  1. Helpful
  1. Luca Militello
  2. Friday, 14 December 2018 18:26 PM UTC
Yes, it could be...

but at the moment i haven't found anything which seems to be the cause of the error.



Luca
  1. Helpful
There are no comments made yet.
Samir Mori Accepted Answer Pending Moderation
  1. Monday, 10 December 2018 21:05 PM UTC
  2. PowerBuilder
  3. # 3

Luca 

Prueba llamando a tu función pasando valores fijo, en reemplazo de al_id_anag  utiliza el 0 y en reemplazo de s_codct  

utiliza "  " . Adicionalmente revisa la declaración de tu función   

Saludos, Samir

Comment
  1. Luca Militello
  2. Tuesday, 11 December 2018 19:08 PM UTC
Hi Samir,

i tried with fixed value (no variables) but the same error occurs.



Thanks



Luca







  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Monday, 10 December 2018 20:31 PM UTC
  2. PowerBuilder
  3. # 4

Hi Luca;

   Can we see how is your external function ( cl_fun_esiste_sosp ) declared?

Regards ... Chris

Comment
There are no comments made yet.
Luca Militello Accepted Answer Pending Moderation
  1. Monday, 10 December 2018 20:07 PM UTC
  2. PowerBuilder
  3. # 5

Hi Samir,

the parameters are INPUT parameters and are always valued. 

 

Luca

Comment
There are no comments made yet.
Samir Mori Accepted Answer Pending Moderation
  1. Monday, 10 December 2018 17:54 PM UTC
  2. PowerBuilder
  3. # 6

Los parámetros al_id_anag y ls_codct son de  tipo salida (OUTPUT), de ser así, asigna valor inicial a las variables.

al_id_anag = 0

s_codct = Space(500)

 

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.