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;
1 comment

Please sign in to leave a comment.