Topic: SQL*Plus and PL/SQL >> populating varray
|
Disclaimer: The purpose of all dbapool forums including OCP and Other Oracle Certification forums is to help each other with specific issues but not to share dump and copyrighted exam content, materials or intellectual property.
You may review the entire Oracle Certification Program Candidate Agreement online Here.
|
|
|
|
| kavya |
Posted: Nov 21, 2007 03:42:54 AM |
Total Post: 2
Joined: Nov, 2006
|
Hi,
I couldnt populate the array with user input,it is prompting for user input only once.please find the code below and let me know what mistake i have done.
i have pasted the execution output below the code.
create or replace package test_pkg
is
type car_name is varray(20) of varchar2(50);
procedure p_test;
end test_pkg;
/
create or replace package body test_pkg
is
procedure p_test
is
cname car_name;
name varchar2(20);
begin
cname :=car_name();
for i in 1 .. 5
loop
cname.extend;
name:='&name';
cname(i):=name;
end loop;
for i in 1 ..5
loop
dbms_output.put_line(cname(i));
end loop;
end p_test;
end;
SQL> @test
Package created.
28 /
Enter value for name: a
old 14: name:='&name';
new 14: name:='a';
Package body created.
SQL> ed test
SQL> exec test_pkg.p_test;
a
a
a
a
a
PL/SQL procedure successfully completed.
|
|
|
 snehotosh |
| Posted: Dec 16, 2007 04:17:40 AM |  |
|
Total Post: 2
Joined: Dec, 2007
|
Hi,
you can test this:
*******************************************************
create or replace package test_pkg
is
type car_name is varray(20) of varchar2(50);
cname car_name;
procedure p_initialize;
procedure p_insert(p_name varchar2);
procedure p_display;
i number:=0;
end test_pkg;
/
create or replace package body test_pkg
is
procedure p_initialize
is
begin
cname :=car_name();
end;
procedure p_insert(p_name varchar2)
is
begin
dbms_output.put_line(i);
if i=0 then
test_pkg.p_initialize;
end if;
cname.extend;
i:=i+1;
cname(i):=p_name;
end;
procedure p_display
is
begin
for i in 1 ..cname.count
loop
dbms_output.put_line(cname(i));
end loop;
end;
end test_pkg;
/
***********
RUN THE PROCEDURE
begin
test_pkg.p_insert('&name');
test_pkg.p_display;
end;
Note:
In your case you are using "&" at the compile time.So when u are compiling the procedure the input which you are giving become hardcoded and the proc is getting compiled with this value.In this case for loop is working with the same value.
Cheers!!!
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
|
|
|
|
Forum Rules & Description
Who Can Read The Forum? Any registered user or guest
Who Can Post New Topics? Any registered user
Who Can Post Replies? Any registered user
|
| |
Get FREE Magazines
|
Top 10 Forum User
|
| Murtuja Khokhar | 857 |
| Mohammed Taj | 746 |
| positive fanatic | 483 |
| Jayanta Sur | 479 |
| Vigyan Kaushik | 386 |
| Gopu Gopi | 333 |
| Gitesh Trivedi | 322 |
| Vinoth Kumar | 264 |
| neeraj sharma | 258 |
| Ramesh Jois | 246 |
|
|
