ORACLE PL/SQL ROWTYPE code completion

Answered

When a function that returns a table rowtype is inside a PL/SQL block, Data Grip code completion works fine and suggest all table columns

The problem is when a function is created as store function or within a package, Data Grip code completion doesn't work and highlight code as an error.

Complete script here

CREATE TABLE employees (
empno NUMBER,
name VARCHAR2(50) NOT NULL,
job VARCHAR2(50) NOT NULL,
CONSTRAINT pk_employees PRIMARY KEY (empno)
);


INSERT INTO employees (empno, name, job)
VALUES (1, 'Steven Feuerstein', 'Oracle Developer Advocate');

INSERT INTO employees (empno, name, job)
VALUES (2, 'Tom Kyte', 'Senior Technical Architect');


CREATE PACKAGE pkg_employee AS
FUNCTION one_row(p_emno employees.empno%TYPE) RETURN employees%ROWTYPE;
END;


CREATE PACKAGE BODY pkg_employee AS
FUNCTION one_row(p_emno employees.empno%TYPE) RETURN employees%ROWTYPE AS
rec employees%ROWTYPE;
BEGIN
SELECT *
INTO rec
FROM employees
WHERE empno = p_emno;

RETURN rec;
END;
END;


DECLARE
name employees.name%TYPE;

FUNCTION one_employee(p_emno employees.empno%TYPE) RETURN employees%ROWTYPE IS
rec employees%ROWTYPE;
BEGIN
SELECT *
INTO rec
FROM employees
WHERE empno = p_emno;

RETURN rec;
END;
BEGIN
-- Data Grip code completion works fine, the suggestion list
-- shows all employees table columns
name := one_employee(2).name;
dbms_output.put_line(name);

-- Data Grip code completion doesn't work and highlight code as an error
name := pkg_employee.one_row(1).name;
dbms_output.put_line(name);
END;
0
6 comments

Any news on this ? Is this in any near future release ?

0

This issue has been resolved, now it works perfectly.

0
Hi Magnus,

We have not received any similar reports about this experiencing this issue, so we're assuming it to be fixed. Are you still observing this behavior?
0

Yes, both on types in a package spec :
 

And also on rowtype variable :
 

DataGrip version 2024.3.4

Working on oracle 12 and 19

0
Thanks for letting me know that, Magnus.

I couldn't reproduce the issue using the example provided in the youtrack ticket above. It could be a very specific case, though, so please file an issue and include examples of your packages and we'll investigate it as soon as the issue is with us

https://youtrack.jetbrains.com/newIssue?project=DBE
0

Please sign in to leave a comment.