CODEWITHSUNDEEP
oracle-databasestored-proceduresplsqlparameter-passing
julealgon
julealgon

Reputation: 8182

How to inline a recordtype variable during a procedure call?

I'm trying to pass a SELECT statement directly as a stored procedure parameter, but I'm unable to. If I add a local variable and Select INTO it, it works fine.

Here is an example:

DECLARE
    something MyTable%ROWTYPE;
    PROCEDURE Test(param IN MyTable%ROWTYPE) as
    BEGIN
        ...
    END;
BEGIN
    SELECT 
      SYS_GUID() as ID,
      'X' as COLUMN1,
      'Y' as COLUMN2,
      'Z' as COLUMN3
      INTO something
      FROM DUAL
      WHERE rownum < 2;

    Test(something);
END;

This works fine as expected, but if I attempt to inline the something variable, it fails:

DECLARE
    PROCEDURE Test(param IN MyTable%ROWTYPE) as
    BEGIN
        ...
    END;
BEGIN
    Test((SELECT 
      SYS_GUID() as ID,
      'X' as COLUMN1,
      'Y' as COLUMN2,
      'Z' as COLUMN3
      FROM DUAL
      WHERE rownum < 2));
END;

ORA-06550: line 11, column 11: PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:

( - + case mod new not null continue avg count current exists max min prior sql stddev sum variance execute forall merge time timestamp interval date pipe

  • & - + ; / at for mod remainder rem and or group having intersect minus order start union where connect || multiset

Why does it not accept my inline select statement but works when storing the result into a variable? What do I need to do to allow the inline statement to work as I intend?

Upvotes: 0

Views: 154

Answers (1)

Jon Heller
Jon Heller

Reputation: 36817

Oracle cannot directly use SQL statements in PL/SQL expressions. (Although it is possible in Postgres and possibly other database systems.)

If you want to pass SQL statements around it might be easier to use SYS_REFCURSOR, like this:

CREATE TABLE mytable(id varchar2(32), x varchar2(1), y varchar2(1), z varchar2(1));

DECLARE
    v_cursor SYS_REFCURSOR;

    something MyTable%ROWTYPE;
    PROCEDURE Test(p_cursor sys_refcursor) as
    BEGIN
        FETCH p_cursor INTO something;
        dbms_output.put_line(something.id);
    END;
BEGIN
    OPEN v_cursor for
    SELECT 
        SYS_GUID() as ID,
        'X' as COLUMN1,
        'Y' as COLUMN2,
        'Z' as COLUMN3
        INTO something
        FROM DUAL;

    Test(v_cursor);
END;
/

Upvotes: 2

Related Questions