| Fetching Multiple Records from Table Type in Pro*C [message #170899] |
Fri, 05 May 2006 19:05 |
Narasimha_indu
Messages: 1
Registered: May 2006
Location: India
|
Junior Member |
|
|
Hi,
Iam developing Pro*c Program, Which calls Stored Procedure. One of the Out Parameter in Stored Procedure is Table type, which will contain More than 100 records and Number of Columns to be fetched is 3.
The Normal Proctise, Which Iam doing is Iam creating Individual variable, for each and every daya existing on the Table type.
For Example:
The table type Name is p_user_info_tab, contains 3 columns as
UserName, Ident_No and Acess_status which all are varchar's.
Assume, the sp is sp_user_ino(p_user_info_tab).
The way Iam retrieving data is
:tab_count := p_user_info_tab.count
for i = 1 .. tab_count;
if i = 1 then
:username1 := p_user_info(i).UserName;
:useriden1 := p_user_info(i).Ident_No;
:userstat1 := p_user_info(i).Access_Status;
elseif i = 2 then
:username2 := p_user_info(i).UserName;
:useriden2 := p_user_info(i).Ident_No;
:userstat2 := p_user_info(i).Access_Status;
.... And so on the till to 100 records.
endif;
in the above code, the usage of variables is really combursome, and also no readability etc.
If any body have an Idea on reducing the Number of lines code above Please Let me know. Please do not advise to use cursor. Because there are some other isuues to go for cursor. So the design is stricked to the above code.
Thanks in Advance.
Thanks & Regards,
Narasimha Rao
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
