How to pass values in anonymous block with plsql table parameter

Question

Table abc has the following column

 approved_ain
    1
    2
    12
    34

i have a procedure

create or replace procedure abc( p_admin varchar2,
p_approved_ain  abc.approved_ain)--plsql table in parameter

begin

end;

now when i call this procedure in an anonymous block :-

declare 
l_Admin varchar2(100);
l_approved_ain abc.approved_ain;

begin
abc(l_Admin ,l_approved_ain);
commit;

end;

How can i pass values of the approved_ain of plsql table to this anonymous block.? that is i want to test it by passing the values of abc table approved_ain column.......

Answer :

 declare 
    l_Admin varchar2(100);
    l_approved_ain abc.approved_ain;

    begin
l_approved_ain(1) :=123;
l_approved_ain(2) :=4645;
    abc(l_Admin ,l_approved_ain);
    commit;

    end;

You can't pass parameters to anonymous PL/SQL block. Instead the block have to read the values from somewhere. — user272735
– user272735, Commented Jul 22, 2015 at 7:49
You can't have both table abc and procedure abc in the same schema, their names conflict. abc.approved_ain is a column reference not a data type reference unless abc is also your schema name. In which case how is abc.approved_ain defined? — Sentinel
– Sentinel, Commented Jul 22, 2015 at 15:56
If I execute create table abc (approved_ain number); then execute your create procedure statement I encounter ORA-00955: name is already used by an existing object changing the procedure name to abc_p yeields PLS-00488: 'ABC.APPROVED_AIN' must be a type — Sentinel
– Sentinel, Commented Jul 22, 2015 at 15:59

doberkofler · Accepted Answer · 2015-07-22 10:19:52Z

2

Given the fact that you only want to test, what about just setting the values in the anonymous block:

declare 
l_Admin varchar2(100) := 'string';
l_approved_ain abc.approved_ain := ???;

begin
abc(l_Admin ,l_approved_ain);
commit;

end;

answered Jul 22, 2015 at 10:19

doberkofler

10.5k26 gold badges92 silver badges150 bronze badges

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

Collectives™ on Stack Overflow

How to pass values in anonymous block with plsql table parameter

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related