Forum Stats

  • 3,825,244 Users
  • 2,260,486 Discussions
  • 7,896,465 Comments

Discussions

Pass rowtype from SQL to PL/SQL

MortenBraten
MortenBraten Member Posts: 303 Bronze Badge
edited Mar 3, 2017 8:47AM in Database Ideas - Ideas

I wish it was possible to pass a rowtype from a SQL statement to PL/SQL, like this:

create or replace function get_row_text (p_row in emp%rowtype) return varchar2

as

begin

  return p_row.empno || ' ' || p_row.ename;

end get_row_text;

select get_row_text(e.*) as the_text

from emp e

This currently fails with ORA-01747: invalid user.table.column, table.column, or column specification

See also https://twitter.com/mortenbraten/status/832642914136186884

- Morten

MortenBratenSven W.Mike Kutzctrieb1409339ulohmannThorsten KettnerJeffrey KempdirkvanhauteSamuel Nitsche
12 votes

Active · Last Updated

Comments

  • Samuel Nitsche
    Samuel Nitsche Member Posts: 1 Red Ribbon

    This!

    Would allow to use Views as API

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,817 Red Diamond
    edited Jul 29, 2020 3:20AM

    This!

    Would allow to use Views as API

    Not impossible:

    SQL> create or replace type TEmpFoo is object(  2          empno           number(4),  3          ename           varchar2(10),  4          job             varchar2(10),  5          mgr             number(4),  6          hiredate        date,  7          sal             number(7,2),  8          comm            number(7,2),  9          deptno          number(4) 10  ); 11  /Type created.SQL> SQL> create table emp_foo of TEmpFoo  2  nologging as select * from emp;Table created.SQL> SQL> create or replace function FunkyFoo( empRow TEmpFoo ) return varchar2 is  2  begin  3          return( empRow.ename||' employed as '||empRow.job );  4  end;  5  /Function created.SQL> SQL> select  2          FunkyFoo(value(e))      as LINE  3  from       emp_foo e;LINE------------------------------SMITH employed as CLERKALLEN employed as SALESMANWARD employed as SALESMANJONES employed as MANAGERMARTIN employed as SALESMANBLAKE employed as MANAGERCLARK employed as MANAGERSCOTT employed as ANALYSTKING employed as PRESIDENTTURNER employed as SALESMANADAMS employed as CLERKJAMES employed as CLERKFORD employed as ANALYSTMILLER employed as CLERK14 rows selected.

    Just that it only works with object tables - which begs the question why can this approach/concept not be applied to any SQL cursor's projection? Besides the technical complexities involved... ;-)

  • Cherif bh
    Cherif bh Member Posts: 158 Bronze Badge

    Hello,

    I think this need very interesting.

    but I think you can use "Dynamic SQL Macros –Polymorphic Views" / avaibale from 19.

    You can adopt it for more complex use.

    you run a bellow example

    https://livesql.oracle.com/apex/livesql/s/kyvrljg9j9wnxcr2q9sb4sa0l

    Bellow an example.

    CREATE OR REPLACE FUNCTION get_row_text (t DBMS_TF.Table_t)                  RETURN varchar2 SQL_MACRO is

    BEGIN

     RETURN q'{ select

         p_row.empno ||' ' || p_row.ename text from t p_row

          

     }';

    END;

    /

    Thanks

    Cherif