EXECUTE IMMEDIATE

[sefafo]

Hello..
someone could help me... I am trying to do a dinamic query byt always fails when I call the function

create or replace function f_consulta_val_dominio(par_dominio varchar2,par_valor varchar2,par_Abreviatura varchar2)
return varchar2
is
Descripcion varchar2(255);
sQuery varchar2(5000);
Begin
Begin

if par_Abreviatura='N' then
sQuery:='Select initcap(substr(rv_meaning,1,255))';
elsif par_Abreviatura='S' then
sQuery:='Select initcap(substr(RV_HIGH_VALUE,1,255))' ;
end if;
sQuery:=sQuery || ' From com_ref_codes Where rv_domain=:' || par_dominio || ' And rv_low_value=:' || par_valor;
EXECUTE IMMEDIATE sQuery into Descripcion;
Exception when no_data_found Then
Descripcion:=null;
When others then
raise_application_error(30501,'Error en la funcion F_CONSULTA_VAL_DOMINIO '||SQLERRM);
End;
return Descripcion;
End;



SELECT f_consulta_val_dominio ('UNIDADES MEDIDA','1','S')AS Medida FROM DUAL

 

[sem]

Could YOU help US to help you? What kind of error do you get?

Regards, Dima

http://seminihin.narod.ru

 

[sefafo]

These are the messages....
ORA-21000: error number argument to raise_application_error of 30501 is out of range
ORA-06512: at "OPS$COMERCIAL.F_CONSULTA_VAL_DOMINIO",line 31
ORA-06512: at line 1

 

[carp]

I believe the allowable range of error numbers for RAISE_APPLICATION_ERRORS is -20000 to -21000 (I'm sure others will correct this quickly if it's wrong). But 30501 is DEFINITELY out of range.

 

[sefafo]

I change the range of error number to 20500 and don't work...

 

[carp]

Try -20500. The range requires negative numbers.

Elbert, CO
0902 MST

 

[sem]

To be more precise from -20000 to -20999. And this error doesn't relate to the thread topic

Regards, Dima

http://seminihin.narod.ru

 

[sefafo]

The function compiles, but when I call it fails again..

 

[sem]

Are you going to send all you problems in such manner: first complaint and only then error description? Note, that each your message may cause email notifications.

Regards, Dima

http://seminihin.narod.ru

 

[sefafo]

ok Sorry.
The problem is the following:
There is a table called COM_REF_CODES which has several fields. Two of them are called rv_meaning and RV_HIGH_VALUE. Some times the users wants to know the value of RV_HIGH_VALUE and some times the value of rv_meaning. The first solution was to craete the following function:

create or replace function f_consulta_val_dominio(par_dominio varchar2,par_valor varchar2,par_Abreviatura varchar2)
return varchar2
is
Descripcion varchar2(255);
Begin
Begin

if par_Abreviatura='N' then
Select initcap(substr(rv_meaning,1,20))
Into Descripcion
From com_ref_codes
Where rv_domain=par_dominio
And rv_low_value=par_valor;
end if;
if par_Abreviatura='S' then
Select initcap(substr(RV_HIGH_VALUE,1,20))
Into Descripcion
From com_ref_codes
Where rv_domain=par_dominio
And rv_low_value=par_valor;
end if;

Exception when no_data_found Then
Descripcion:=null;
When others then
raise_application_error(20500,'Error en la funcion F_CONSULTA_VAL_DOMINIO '||SQLERRM);
End;
return Descripcion;
End;
This Works fine,but I wondered if I could create a Dinamic Query, so I tried this:

create or replace function f_consulta_val_dominio(par_dominio varchar2,par_valor varchar2,par_Abreviatura varchar2)
return varchar2
is
Descripcion varchar2(255);
sQuery varchar2(5000);
Begin
Begin

if par_Abreviatura='N' then
sQuery:='Select initcap(substr(rv_meaning,1,255))';
elsif par_Abreviatura='S' then
sQuery:='Select initcap(substr(RV_HIGH_VALUE,1,255))' ;
end if;
sQuery:=sQuery || ' From com_ref_codes Where rv_domain=:' || par_dominio || ' And rv_low_value=:' || par_valor;
EXECUTE IMMEDIATE sQuery into Descripcion;
Exception when no_data_found Then
Descripcion:=null;
When others then
raise_application_error(30501,'Error en la funcion F_CONSULTA_VAL_DOMINIO '||SQLERRM);
End;
return Descripcion;
End;


But I always got the error I already wrote.. if I erase the Exception Block, also fails, and points to the EXECUTE IMMEDIATE line.
Do you understand?? Sorry my english is poor.

 

[sem]

Are you joking? Do you get ORA-21000 without exception block? I can not beleive it.

As I see you problem is in using colons before variable names. If you plan to BIND variables, add USING clause and replace variable VALUES with variable NAMES. Otherwise get rid of that colons and add quotes if necessary (depending on par_dominio and rv_low_value data types).

Regards, Dima

http://seminihin.narod.ru

 

[carp]

Sefafa -

Remove the colons from your character string.
Instead of rv_domain=: try rv_domain=

I think there are two occurrences of this problem in your code.

Elbert, CO
0925 MST

 

[sefafo]

do you have MSN to avoid mail's?

 

[sem]

Another idea is to use DECODE function:

Select decode( par_Abreviatura,
'N', initcap(substr(rv_meaning,1,20)),
'S', initcap(substr(RV_HIGH_VALUE,1,20)))
From com_ref_codes
Where rv_domain=par_dominio
And rv_low_value=par_valor;

Regards, Dima

http://seminihin.narod.ru

 

[sefafo]

Perfect!! Thanks a lot. Anymay a Want to know how to use dinamic querys...