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;
Only when the size of the text is bigger than the size of the string I get the 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?
