Home » SQL & PL/SQL » SQL & PL/SQL » How can i calla function with dynamic SQL on ORACLE 7.3
How can i calla function with dynamic SQL on ORACLE 7.3 [message #223123] Wed, 07 March 2007 12:22 Go to next message
wolfheart
Messages: 2
Registered: March 2007
Junior Member
hey guys;
I need some help with a problem, i need run a function into a SQL query; This function call a dynamic SQL where this query it's obtained from a table. I create that function with his pragma directive; and this I still having the same message error: violate the pragma directive. The Oracle Versión is 7.3 and the Package is:

create or replace package TST_FN_SINONIMO
as
function SEGSINONIMO(v_emp in varchar2,v_cuadro in varchar2, v_vista in varchar2, v_paso in varchar2,v_seg1 in varchar2, v_seg2 in varchar2) RETURN VARCHAR2;
PRAGMA RESTRICT_REFERENCES (SEGSINONIMO,WNDS );
end;
/
Create or Replace Package BODY TST_FN_SINONIMO as
function SEGSINONIMO(v_emp in varchar2,v_cuadro in varchar2, v_vista in varchar2, v_paso in varchar2,v_seg1 in varchar2, v_seg2 in varchar2) RETURN VARCHAR2
is
v_res1 varchar2(255);
v_cod_status varchar2(255);
source_cursor integer;
ignore integer;
cursor sinonimous is
select C.IND_SINONIMO, C.STR_SINONIMO
from ACSEL.WF_PASOS P,
ACSEL.WF_CICLO C
where P.COD_EMPRESA=ltrim(rtrim(v_emp))
and P.COD_CUADRO=ltrim(rtrim(v_cuadro))
and P.COD_VISTA=ltrim(rtrim(v_vista))
and P.COD_PASO=ltrim(rtrim(v_paso))
and C.COD_MALLA = P.COD_MALLA
and C.COD_CICLO = P.COD_CICLO
and P.COD_EMPRESA = P.COD_EMPRESA;
begin
v_res1:= v_seg2;
FOR A IN sinonimous LOOP
IF a.IND_SINONIMO='N' OR a.STR_SINONIMO = ' ' THEN
v_res1:= v_seg2;
else
v_res1:= 'N/A';
source_cursor := dbms_sql.open_cursor;
DBMS_SQL.PARSE(source_cursor, a.STR_SINONIMO , DBMS_SQL.native);
DBMS_SQL.BIND_VARIABLE(source_cursor, ':p1',v_seg1);
DBMS_SQL.BIND_VARIABLE(source_cursor, ':p2',v_seg2);
DBMS_SQL.DEFINE_COLUMN(source_cursor, 1, v_res1,50);
ignore := DBMS_SQL.EXECUTE(source_cursor);
IF DBMS_SQL.FETCH_ROWS(source_cursor)>0 THEN
DBMS_SQL.COLUMN_VALUE(source_cursor, 1, v_res1);
else
v_res1:=v_seg2;
end if;
if v_res1 is null or v_res1=' ' then
v_res1:='N/A';
end if;
end if;
end loop;
IF DBMS_SQL.IS_OPEN(source_cursor) THEN
DBMS_SQL.CLOSE_CURSOR(source_cursor);
END IF;
RETURN v_res1;
EXCEPTION WHEN OTHERS THEN
RETURN v_seg2;
end;

end ;
/


For other hand, I try to generate a cursor from a string but doesn't work too. for example:

declare query varchar2(50);
begin
query = ' select * from TABLE';
open D for query;
end;

and this doesn't works too.

please I need help for this problem.

Thanks
Re: How can i calla function with dynamic SQL on ORACLE 7.3 [message #223208 is a reply to message #223123] Thu, 08 March 2007 00:30 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Why don't you show us the error message and the contents of the string used in the dynamic SQL.

Ross Leishman
Re: How can i calla function with dynamic SQL on ORACLE 7.3 [message #223298 is a reply to message #223123] Thu, 08 March 2007 06:28 Go to previous messageGo to next message
wolfheart
Messages: 2
Registered: March 2007
Junior Member
The error message is: PLS-00452: subprogram SEGSINONIMO violates its associated pragma. the querys are obtained for a column in a table and this is stored into a string (str_sinonimo); but all querys are "select" (i'm sure of that).
for example:

select decode(p.numpol,null,p.nropropuesta,p.numpol)
from table p
where p.numpol = to_number(:p2)
and :p1 = :p1

Thanks.

[Updated on: Thu, 08 March 2007 06:30]

Report message to a moderator

Re: How can i calla function with dynamic SQL on ORACLE 7.3 [message #223435 is a reply to message #223298] Thu, 08 March 2007 21:56 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Does the error happen at compile-time or at run-time?

If it is compile-time, it is probably objecting to the use of DBMS-SQL, in which it cannot GUARANTEE that you will not Write No Database State (WNDS); it doesn't know that all of your SQLs will be SELECTs so it's protecting itself in case they are not. In this case, you cannot perform dynamic SQL in a 7.3 function that is callable from a SQL statement.

If it is at runtime, I don't know what the problem is.

There are other solutions if you upgrade to a newer version of the database, but it could be a bit tricky if you stay in 7.3. You could possibly setup a dynamic query server that runs permanently in the background and communicates with consumer processes via DBMS_PIPE.

Ross Leishman
Previous Topic: Sql query
Next Topic: find value or datatype of a varriable
Goto Forum:
  


Current Time: Sun Dec 11 04:23:03 CST 2016

Total time taken to generate the page: 0.14407 seconds