I new to using Oracle, so I could really use the help.
I have a procedure:
create or replace procedure Demo_Name
(someId IN number default null,
planShapes IN string,
OutputCursor OUT SYS_REFCURSOR ) is
planShape st_geometry;
begin
IF(planShapes is not null) THEN
planShape := sde.st_geomfromtext(planShapes, 2039);
ELSE planShape := null;
end If;
OPEN OutputCursor for
select distinct
p.name as "name"
from table p
where (someId is not null)
and (planShape is null or (sde.st_envintersects(p.SHAPE, planShape) = 1 and sde.st_intersects(p.SHAPE, planShape) = 1));
end Demo_Name;
error: ora-20004 error generating shape from text
The issue is that planShapes can’t handle the amount of characters that this parameter may get. So I tried changing the type.So far, I tried to change planShape’s type to: VARCHAR2
and CLOB
with no luck.
VARCHAR2
got me the same error.
CLOB
didn’t get an error number but I did run into an issue:
When Testing with CLOB
type: it won’t regeister the input and remains empty.
I don’t want to work with arrays, because I know for sure that the input should be contained inside a single clob
.
I don’t know how to solve this issue. What type should I use? Is there any reason why CLOB
shouldn’t work?
Sapir Shahar is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.