anyone here with general tips debugging oracle routines and/or packages with DataGrip (even getting them to start/work/debug)?
Issues I have are:
1 Me not understanding the last part of the code dataGrip generates
declare
result NUMBER;
someVar := 300198032;
begin
result := FN_GET_CLIENT_ORG_SEQ(V_someVar => someVar );
open ? for select result as result from dual;
end;
at the end has this " open ? for select result as result from dual; ", what the heck is the open ?
When clicking the button to debug it brings out this dialogue, tried different combinations (numbers, strings, etc.) but oracle complaints and does not run, I end up commenting that line in order to debug.
2 When doing stepping, I noticed that after entering some statements, the debug buttons get disabled (step over, step into, etc). Basically DataGrip just keeps thinking/hangs and I cannot continue to debug.
Example, in this case I have to step over in order for debug to keep working, if I do step into, it hangs
SELECT something, something
INTO somethingInto
FROM some table
Another example, any End statmens (that define the ending of the routine) need to be stepped over if not DataGrip hangs or goes into limbo.
3 Seems to be harder to have DataGrip debug routines contained in packages, either the debug process has issues starting, or step overs are probably the safest way to get it to work.
Any pro tips would be greatly appreciated
I don't know Datagrip so I can't help.
However, see if this helps with
what the heck is the open ?
Looks like a refcursor business. The following code uses the same open you saw in your code. It fetches employees that work in department whose deptno is equal to the result local variable's value.
SQL> set serveroutput on
SQL> declare
2 result number := 10;
3 rc sys_refcursor; --> this is your "?" - a refcursor
4 l_name emp.ename%type;
5 begin
6 open rc for select ename from emp --> this is your "open"
7 where deptno = result;
8
9 -- the following code just displays what's being fetched
10 loop
11 fetch rc into l_name;
12 exit when rc%notfound;
13 dbms_output.put_line(l_name);
14 end loop;
15 close rc;
16 end;
17 /
CLARK
KING
MILLER
PL/SQL procedure successfully completed.
SQL>