Home » SQL & PL/SQL » SQL & PL/SQL » Assigning PL/SQL Table type to Procedure parameter
Assigning PL/SQL Table type to Procedure parameter [message #187018] Thu, 10 August 2006 09:33 Go to next message
cpsinh@yahoo.com
Messages: 5
Registered: August 2006
Junior Member
Hi

I am trying to assign a table type to a paramenter in procedure, but it is giving me error. However, when I assign record it works fine. Can somebody tell me, why is like that?


Here is the code

Declare

Type ab_record is record(new_name abc.new_name%type,
ID NUM.ID_num%type
);
type ab_table is table of num%rowtype
index by binary_integer;

abc ab_table;

procedure test_method (ac in number,
b_out out abc)
IS
Begin
select *
into b_out
from num
where ID_num = ac;
end;

begin
test_method(103468, abc);
end;

I am getting following error

Error report:
ORA-06550: line 17, column 10:
PLS-00597: expression 'B_OUT' in the INTO list is of wrong type
ORA-06550: line 18, column 5:
PL/SQL: ORA-00904: : invalid identifier
ORA-06550: line 16, column 5:
PL/SQL: SQL Statement ignored

Appreciate your time and efforts.

thanks,

CPS
Re: Assigning PL/SQL Table type to Procedure parameter [message #187025 is a reply to message #187018] Thu, 10 August 2006 09:58 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
abc is not a datatype, it is a variable of type ab_table.
Your out-parameter should be of type ab_table
procedure test_method 
(ac    in     number,
 b_out    out ab_table
)
Re: Assigning PL/SQL Table type to Procedure parameter [message #187050 is a reply to message #187018] Thu, 10 August 2006 12:20 Go to previous message
cpsinh@yahoo.com
Messages: 5
Registered: August 2006
Junior Member
Sorry, that was the mistake, but the code was like this and I was
getting the previously mentioned errors

I forgot to modify that typing error.

procedure test_method (ac in number,
b_out ab_table)
IS
Begin
select *
into b_out
from num
where ID_num = ac;
end;

Thanks,

CPS
Previous Topic: INSERT INTO table (xxx) SELECT a,b,c FROM view doesn't work in 10g but works in 8i
Next Topic: Help with Logic
Goto Forum:
  


Current Time: Sun Dec 11 06:24:11 CST 2016

Total time taken to generate the page: 0.07258 seconds