473,704 Members | 6,529 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

The function return amount of data at a time (Oracle)

78 New Member
Hi all,

Normally, the stored function return one values per time.In oracle9i pipelined functions return the amount of data at a time,

the first step create a type object.
Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE package My_Types is
  2.   type lookup_row is record ( idx number, text varchar2(20) );
  3.   type lookups_tab is table of lookup_row;
  4. end My_Types;
  5.  
the second step create a function
Expand|Select|Wrap|Line Numbers
  1. create or replace function Lookups_Fn
  2.  
  3. (p_id in employees.department_id%type)
  4. return emp_Types.lookups_tab
  5.   pipelined
  6. is
  7.   cursor emp_cur is
  8.   select employee_id,last_name
  9.   from employees
  10.   where department_id=p_id;
  11.   v_row emp_Types.lookup_row;
  12. begin
  13.   open emp_cur;
  14.   loop
  15.   fetch emp_cur
  16.   into
  17.   v_row.employee_id,v_row.last_name;
  18.   exit when emp_cur%notfound;
  19.   pipe row ( v_row );
  20.   end loop;
  21.   return;
  22. end Lookups_Fn;
  23.  
Please use it in the future
Jul 20 '07 #1
1 3434
rk2006
1 New Member
Hi I tried to use the above .but some how this functions fails to return the set of values when used directly..say Lookups_fn('6') .pls advice
Aug 2 '07 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

9
4963
by: Penn Markham | last post by:
Hello all, I am writing a script where I need to use the system() function to call htpasswd. I can do this just fine on the command line...works great (see attached file, test.php). When my webserver runs that part of the script (see attached file, snippet.php), though, it doesn't go through. I don't get an error message or anything...it just returns a "1" (whereas it should return a "0") as far as I can tell. I have read the PHP...
0
4270
by: Bryan Jackson | last post by:
Greetings, (I am an Oracle newbie -- been working with SQLServer for quite some time, however. I'm using Oracle9i and Oracle9i JDeveloper v9.0.3.1 (build 1107) for my programming environment). I'm trying to get an Oracle function to return XML to a JSP page, but am having some problems (mostly Oracle errors). Let me start by showing you what I've done so far.
2
4184
by: spawncamper | last post by:
Hi - I posted here a few weeks back and people were kind enough to help. In lieu of my PL/SQL class I take in a few weeks, I was hoping to get some more advice. Here's my scenario: I need a function to return a table of data for Oracle8i. I'm fairly certain of the stucture for the function, but am hazy on the details.
2
8747
by: Konstantin Zakharenko | last post by:
Hello, Our QA team have running a lot of test scripts (for automated regression testing), they run them on the different databases (Oracle/MS SQL). Several of those tests are dependent on the current date/time. In order to be able to use them efficiently, we changed the current date/time on the QA database server to a specific date/time before starting the scripts, so we are sure the test scripts always run in the same environment.
89
6043
by: Cuthbert | last post by:
After compiling the source code with gcc v.4.1.1, I got a warning message: "/tmp/ccixzSIL.o: In function 'main';ex.c: (.text+0x9a): warning: the 'gets' function is dangerous and should not be used." Could anybody tell me why gets() function is dangerous?? Thank you very much. Cuthbert
21
3895
by: Hallvard B Furuseth | last post by:
Is the code below valid? Generally a value must be accessed through the same type it was stored as, but there is an exception for data stored through a character type. I'm not sure if that applies in this case though: #include <limits.h> unsigned foo(void) { static const union { unsigned char str;
28
4320
by: Larax | last post by:
Best explanation of my question will be an example, look below at this simple function: function SetEventHandler(element) { // some operations on element element.onclick = function(event) {
3
2482
by: Aaron | last post by:
I have been searching the boards trying to find an answer to this question and no luck. I am using a query similar to this: Select count(col1) from table1 I was having a hard time accessing the count information. After reading for a while the following SQL examples were given to correct this issue. Select count(col1) Blah from table1
10
1884
by: colin | last post by:
Hi, I profile my code and find its spending a lot of time doing implicit conversions from similar structures. the conversions are mainly things like this class Point { implicit conversion to vector3; //this conversion just returns positon Vector3 position;
0
9268
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9134
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9023
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8974
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
5927
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4437
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3132
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2474
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2078
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.