Package body

[jamoji]

I´ve got this package_body and I want to return "Cadena2" and "Cadena3".Can somebody tell me the best way to do it?:

CREATE OR REPLACE PACKAGE BODY "PARQUE"."PRUEBA"
as

Procedure MP_InformeSociosActividad
(FechaInicio IN varchar2 default null,
FechaFin IN varchar2 default null,
C OUT tipo_cursor)
as

Cadena1 varchar2(250);
Cadena2 varchar2(2000);
Cadena3 varchar2(2500);
Cadena4 varchar2(4000);

BEGIN

If FechaInicio is not null and FechaFin is not null then
Cadena1:=' and FechaAlta Between to_date('''|| FechaInicio ||''',''DD/MM/YYYY'') and to_date('''|| FechaFin ||''',''DD/MM/YYYY'')';
Else
Cadena1:='';
End if;


--Retocar la select
Cadena2:='select * from(Select actividad.descripcion as Actividad,count(empresas.cnae) as Numero
from empresas,actividad,cnae2
where empresas.cnae=cnae2.cnae
and actividad.codigoactividad=cnae2.codigoactividad'|| Cadena1 ||'
group by actividad.descripcion order by count(empresas.cnae) desc)
where rownum<=10';

Cadena3:='(select(Select count(em.cnae) from empresas em,cnae2 cn2,actividad ac2
where cn2.cnae=em.cnae
and ac2.codigoactividad=cn2.codigoactividad
and ac2.codigoactividad not in
(select * from(Select ac3.codigoactividad from empresas em3,cnae2 cn3,actividad ac3
where em3.cnae=cn3.cnae
and cn3.codigoactividad=ac3.codigoactividad
group by ac3.codigoactividad
order by count(em3.cnae) desc)
where rownum <=10))+
(Select count(empresas.cnae) from empresas where empresas.cnae not in (Select cnae from cnae2))as resto
from dual)';

OPEN C FOR
Cadena2;



END MP_InformeSociosActividad;


End Prueba;

Thanks

[bensr]

Hi jamoji,
How about this:

Procedure MP_InformeSociosActividad
(FechaInicio IN varchar2 default null,
FechaFin IN varchar2 default null,
C OUT tipo_cursor,
Cadena2 OUT varchar2,
Cadena3 OUT varchar2 )
as

Cadena1 varchar2(250);
Cadena4 varchar2(4000);

BEGIN
...
END MP_InformeSociosActividad;


Cadena2/3 are now defined as out variables,

Then change your call in :

PARQUE.PRUEBA.MP_InformeSociosActividad
(IN_FechaInicio
,IN_FechaFin
,OUT_C
,OUT_Cadena2
,OUT_Cadena3);

[jamoji]

It doesn't work,because I don't return varchar2,I return some registers from some tables,so I have to get them into a cursor_type variable.
The problem is I can't join Cadena 1 and Cadena2,so I must get the results in 2 steps...

[bensr]

If this does not work, you have other problems than you wrote.
Alternatives:
declare the variables in the packages body:
CREATE OR REPLACE PACKAGE BODY "PARQUE"."PRUEBA"
as

Cadena2 varchar2(2000);
Cadena3 varchar2(2500);

Procedure MP_InformeSociosActividad
(FechaInicio IN varchar2 default null,
FechaFin IN varchar2 default null,
C OUT tipo_cursor)
as

Cadena1 varchar2(250);
...

Function GetCadena2
is
return varchar2
begin
return(Cadena2 );
end;

declare the variables in the package-header:
CREATE OR REPLACE PACKAGE "PARQUE"."PRUEBA"
as

Cadena2 varchar2(2000);
Cadena3 varchar2(2500);

...

Now they're known as global vars, and say:
MyVar := PARQUE.PRUEBA.Cadena2 ;