Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

EXECUTE IMMEDIATE

Status
Not open for further replies.

sefafo

Programmer
Aug 28, 2003
36
CO
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
 
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
 
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.
 
I change the range of error number to 20500 and don't work...
 
Try -20500. The range requires negative numbers.

Elbert, CO
0902 MST
 
The function compiles, but when I call it fails again..
 
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
 
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.



 
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
 
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
 
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
 
Perfect!! Thanks a lot. Anymay a Want to know how to use dinamic querys...
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top