Oracle insert from function

Question

I am converting SQL Server stored procedures to Oracle. In SQL Server you can insert into a table from a function call.

Here is the SQL Server:

INSERT INTO t_tmpl( rel_class_code, rel_side, template_id, template_name, template_desc )
SELECT rel_class_code, ls_rel_side, obj_id, name, description
FROM etmf_get_templates_for_rel( ps_rel_class_code, ls_rel_side, pi_called_by )

The error message I get when converting this to Oracle is "PL/SQL: ORA-00933: SQL command not properly ended".

Does anyone know what this statement should look like in Oracle?

Thanks!!!

How is your function defined?

kurosch
– kurosch

2010-11-29 18:47:29 +00:00
Commented Nov 29, 2010 at 18:47 — kurosch
– kurosch, Commented Nov 29, 2010 at 18:47

Leigh Riffel · Accepted Answer · 2010-11-29 18:54:54Z

3

If your function returns a pipelined result set you just need to put the function inside TABLE as follows:

INSERT INTO t_tmpl
   (rel_class_code, rel_side, template_id, template_name, template_desc)
   SELECT rel_class_code, ls_rel_side, obj_id, name, description
   FROM TABLE(
      etmf_get_templates_for_rel(ps_rel_class_code, ls_rel_side, pi_called_by)
      )

answered Nov 29, 2010 at 18:54

Leigh Riffel

6,6813 gold badges38 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle insert from function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related