Return Multiple Rows From Stored Procedure?

May 26, 2010

How to return multiple rows from the stored procedure in ORACLE..

View 2 Replies


ADVERTISEMENT

SQL & PL/SQL :: Inserting Multiple Rows From Stored Procedure

Jun 20, 2012

Below are the data for rows that I want to insert into CUSTOMER_PRODUCT table from a stored procedure.

Instead of making round trips twice to insert these two rows, I looking for a way to pass in the data for both those rows and then insert them from within the stored procedure in one shot.

The stored procedure will be invoked by Java and .NET.

Sample Data for CUSTOMER_PRODUCT:

ROW 1:
CUSTOMER_ID : 1000
PRODUCT_TYPE : PROD123
IS_MEMERSHIP : Y
IS_EMAIL_SUBSCRIPTION: Y

ROW 2:
CUSTOMER_ID : 1001
PRODUCT_TYPE : PROD123
IS_MEMERSHIP : Y
IS_EMAIL_SUBSCRIPTION: Y

Question 1:
Should collection be used? (or) is there any other approach that could be utilized?

Question 2:
Are there any performance concerns in passing collection and iterating it to fetch value to insert into CUSTOMER_PRODUCT table?

I'm running Oracle 10g.

View 3 Replies View Related

SQL & PL/SQL :: Return Only Multiple Rows

Apr 30, 2010

I need to return results for the following query only when there are two or more rows found by the following:

SELECT DISTINCT D.PUBLICATION,
D.ADI,
D.DISTRICT,
D.ACCOUNT,
D.ROUTE,
[code]......

Expected results would be:

1 145 CRF TUANR000 VM BILLETING 16-APR-10 5 5 23-APR-10 48623
1 145 CRF TUANR000 VM BILLETING 16-APR-10 5 4 23-APR-10 48629

View 19 Replies View Related

Windows / .NET :: Return String From Oracle Stored Procedure Using Excel 2003 VBA

Apr 12, 2013

I am trying to return a value of an oracle stored procedure using Excel VBA.

OS: Win XP SP3
Excel 2003
Ora Client: 11g

By trying different things I have noticed, that I could have troubles with the ODBC-connection. Maybe I am not using the right one. To store data returned from select statements I have an ODBC-Connection (Driver: Oracle in XE), which works perfectly, e.g.:

'-----------------------
Sub Extract_Data()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim db_name, UserName, Password As String
cn.Open db_name, USerName, Password

[code]....

When debugging the connection string, I find the Provider=MSDASQL5.1.

View 2 Replies View Related

SQL & PL/SQL :: Function Return Multiple Rows

Jun 23, 2006

can i have a pl/sql function that can return multiple rows

may be the syntax will be like

create or replace function multiple() returns ...
begin
select candidateid from tbl_candidateinfo;

..code to return the result of above statement to calling program..

end;

and functions will be called as

select candidateid from .. where candidateid in( select multiple());

View 13 Replies View Related

PL/SQL :: Function That Return Multiple Rows

Jun 6, 2013

I need a function that should return output of this query

SELECT b.branding_code, c.name_desc
FROM
development.brandings b, godot.company c
WHERE b.company_id = c.company_id;

This above function return 30 rows and I am not giving any input

Function using cursor,pipeline

View 5 Replies View Related

PL/SQL :: Datatype Name For Multiple Rows Return

Jun 7, 2013

If I return multiple rows says ex:100 rows then what will be data type for that return values.

View 1 Replies View Related

SQL & PL/SQL :: Getting Output From Multiple Rows Return Only One Row In Oracle?

Mar 27, 2013

EMP table

create table emp1 (empno number,deptname varchar2(30),deptno number,sal number);
insert into emp1 values (1,'Bank',10,1000);
insert into emp1 values (1,'Finance',20,400);
insert into emp1 values (2,'Finance',20,4000);
insert into emp1 values (3,'Account',30,3000);
commit;
select * from emp1;

actual output :

empno deptname deptno sal
1 Bank 10 1000
1 Finane 20 400
2 Finane 20 4000
3 Account 30 3000

Expected output :

empno deptname deptno sal
1 Bank 10 1000
2 Finane 20 4000
3 Account 30 3000

I am looking a output like above one. if any empno belongs to deptname Bank then give priority to that values else go to other dept like Finance but empno 1 should return only one row.i.e Bank dept only.

I have to return only one row based on dept values for one empno. how to do in sql or plsql?

View 11 Replies View Related

PL/SQL :: How To Update Multiple Records Using Stored Procedure

Feb 27, 2013

I want to update records which returns more than 1 row using store procedure. i tried with ref_cursor but failed to update,

View 1 Replies View Related

PL/SQL :: Insert Multiple Records On A Database Using Stored Procedure

Mar 25, 2013

I want to insert multiple records on a database using a stored procedure.

View 11 Replies View Related

SQL & PL/SQL :: Stored Procedure / Extract Data From Excel To Multiple Tables

Oct 14, 2010

This is my first time running a stored procedure. The procedure is already written.

We have various related table. I need to use this stored procedure and extract information from an excel sheet into the multiple tables.

View 8 Replies View Related

SQL & PL/SQL :: Stored Procedure With Multiple Table Join With Aggregate Functions

Jun 15, 2013

I have 2 tables order_items and items.

Order_items Items
Item_id Item_id
Quantity Price

In normal sql statement: select sum(order_items.quantity*items.price) sales_price
from order_items,items
where order.item_id=items.item_id;

I have to put this logic in either a stored procedure or Function just to calculate sum(order_items.quantity*items.price) and store the aggregated value as Sales_price in DB. Then we have to call this from Informatica Stored procedure Transformation where we will have only one output port as Sales_price,this is to load into summary table. All the aggregate calculations and joining of 2 tables should be done on DB side and only one output should be populated when we execute this procedure.

View 2 Replies View Related

SQL & PL/SQL :: Retrieving Multiple Values In Stored Procedure Using Variables And Cursors

Oct 6, 2011

The goal is to create a stored procedure that will retrieve multiple values from a table.

GUI is in Java and they will trigger our procedure to show list of all employees and their roles , doj etc.

So I wrote the following procedure.
---------------------------------
create or replace
PROCEDURE emp_test(
c_cursor OUT SYS_REFCURSOR)
AS
BEGIN
OPEN c_cursor
FOR
SELECT emp_name, emp_doj, emp_role FROM emp_table ;
END;
---------------------------------

I'm using sql developer, stored procedure is compiled and I can manually run it by right clicking on the procedure and click 'Run'.

When I intend to run it by executing the script "Execute Procedure name ", I get errors.

In SQL Developer, I open new SQL file and key in

EXECUTE emp_test;

Highlight it and run the script, here is the list of errors that I get.

-------------------------------------------
Error starting at line 18 in command:
execute frm_lst
Error report:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'emp_test'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:
%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
--------------------------------------------

Issue 2:

Instead of using cursor, is there a way to use multiple parameters and insert the data retrieved from select column_name from table into these parameters and build it in a stored procedure. I tried something like below which did not work.
____________________________________________________

CREATE OR REPLACE PROCEDURE emp_test1
(e_name OUT emp_name%TYPE,
e_dob OUT Edob%TYPE)
IS
BEGIN
SELECT emp_nam, Edob
INTO e_name, e_dob
FROM emp_table
END emp_test1;
End;
______________________________________________________

Just so you know, there is no input parameter or input feed, when called the procedure should return all the values.

View 7 Replies View Related

SQL & PL/SQL :: Store All Rows Of Columns Into Single Variable / Use In Inside Of Stored Procedure

Mar 6, 2012

i want to store all rows of columns into single variable and then use in inside of SP

declare
CUR_REC SECURITY_TYPE%rowtype;
begin
select *
into CUR_REC
from SECURITY_TYPE;
[code]....

it return ORA-01422: exact fetch returns more than requested number of rows error. Is any chance to implemented above scenario in oracle 10g

View 4 Replies View Related

PL/SQL :: Call More Than One Stored Procedure In New Stored Procedure

Dec 24, 2012

Execute sp1 param1...param6
Execute sp2 param1...param8
Execute sp3 param1...param4

All these stored procedures deals with insert/updated transactions . i need to create a new stored procedure to execute all this in a single stored procedure which will be something like

create procedure sp4(param1...param8)
as
begin
Execute sp1 param1...param6
rollback if any error
Execute sp2 param1...param8
rollback if any error
Execute sp3 param1...param4
rollback if any error
end;

View 6 Replies View Related

SQL & PL/SQL :: Min / Max With Multiple Row Return

Apr 22, 2010

Client managerCont. Start DateCont. End Date
abcman11-Jan-0830-Jun-08
abcman21-Jul-0831-Dec-08
abcman11-Jan-0930-Jun-09
abcman11-Jul-0931-Dec-09
abcman21-Jan-1031-Mar-10
abcman21-Apr-1031-Aug-10

I need to code a SQL statement (Not PL/SQL) to display following records:

Client managerCont. Start DateCont. End Date
abcman11-Jan-0830-Jun-08
abcman21-Jul-0831-Dec-08
abcman11-Jan-0931-Dec-09
abcman21-Jan-1031-Aug-10

View 4 Replies View Related

PL/SQL :: Return More Rows Than Specified

Sep 30, 2013

I am fetching records from many voluminous tables having multiple joins based on filter criteria filled from frond end application. As per the selected criteria, I want to have a pre-check if query would return more than 1000 rows then I have to show user a message saying that he should refine the search. Is there any performant way to query db and dynamically find record count and stops executions if it is going return more than the specified no of rows.

View 5 Replies View Related

Why Dba_feature_usage_statistics Return No Rows

Aug 30, 2012

I have a database in 10g in entreprise edition but i don't understand why the dba_feature_usage_statistics return no rows ? On some database when i look at dba_ feature_usage_statistics Protection Mode - Maximum Performance is used and active why?

View 4 Replies View Related

PL/SQL :: Merge Multiple Rows Into Single Row (but Multiple Columns)

Oct 17, 2012

How to merge multiple rows into single row (but multiple columns) efficiently.

For example

IDVal IDDesc IdNum Id_Information_Type Attribute_1 Attribute_2 Attribute_3 Attribute_4 Attribute_5
23 asdc 1 Location USA NM ABQ Four Seasons 87106
23 asdc 1 Stats 2300 91.7 8.2 85432
23 asdc 1 Audit 1996 June 17 1200
65 affc 2 Location USA TX AUS Hilton 92305
65 affc 2 Stats 5510 42.7 46 9999
65 affc 2 Audit 1996 July 172 1100

where different attributes mean different thing for each Information_type. For example for Information_Type=Location

Attribute_1 means Country
Attribute_2 means State and so on.

For example for Information_Type=Stats

Attribute_1 means Population
Attribute_2 means American Ethnicity percentage and so on.

I want to create a view that shows like below:

IDVal IDDesc IDNum Country State City Hotel ZipCode Population American% Other% Area Audit Year AuditMonth Audit Type AuditTime
23 asdc 1 USA NM ABQ FourSeasons 87106 2300 91.7 46 85432 1996 June 17 1200
65 affc 2 USA TX AUS Hilton 92305 5510 42.7 46 9999 1996 July 172 1100

View 1 Replies View Related

SQL & PL/SQL :: How To Return Multiple Values From A Function

Aug 28, 2010

I want multiple values from a function. I want to use this function in a SQL query. Here i'm giving my try.

SQL> CREATE TABLE TEMP
2 (
3 ID NUMBER(1),
4 SAMPTYPE VARCHAR2(20 BYTE),
5 SALARY NUMBER(10)
6 )
7 /

Table created.

SQL> INSERT INTO TEMP VALUES(1,'ABC',10000);

1 row created.

SQL> INSERT INTO TEMP VALUES(2,'PQR',20000);

1 row created.

SQL> INSERT INTO TEMP VALUES(3,'JPD',5000);

1 row created.

SQL> COMMIT;

Commit complete.
[code]...

Here i get result as ABC*10000, but i want two separate values as ABC,10000. how can i do this via function.

View 6 Replies View Related

PL/SQL :: Single Query Return Multiple Value

Sep 6, 2012

I have a sql query as below :

select order_number,
(select decode(hcp.contact_point_purpose,'ABC',hcp.email_address,'CDE',hcp.email_address,null)
from hz_contact_points,
hz_parties hz
WHERE hz.party_id=hcp.owner_table_id) Email
FROM oe_order_headers_all h
WHERE h.order_number='102'
....................
..............

Actually the problem i am facing is the inner select query is returning multiple row , so my main query is erroring out, i need to capture the multiple row.

In the above example the inner decode statement returning two mail address, I need to capture that, but while executing the whole query it is erroring out as saying single query returns multiple values. capture multiple values

View 3 Replies View Related

SQL & PL/SQL :: How To Use Case When Return Multiple Values

Mar 13, 2012

I have a table second_table which has a username and code field. A username (not the primary key) may be entered multiple times with different codes, or a single code 'ALL', in which case, the codes have to be fetched from 'third_table'. I am unable to form a 'case' clause to handle the fact that a list has to returned when the 'code' field from the second_table returns 'ALL'.

e.g.

Second_table
username code
A ALL
B 23
B 56
B 33

Third_Table
code
67
78

So, when the user asks the codes for user A, he should get 67 and 78 and when he asks for the user B, he should get 23,56 and 33

View 13 Replies View Related

SQL & PL/SQL :: How To Return Last 2 Rows In History Table

Nov 23, 2011

I have a history table which has a new record written to it for every time static data is updated. What I want to do is return any record that has been changed on a daily basis returning both the new record and the previous record so you can see the 2 records one below the other and compare.I have written the below but it returns all records per account:

SELECT H.Timestamp,H.AccountNo FROM History H
where exists (select H2.AccountNo,count(*) from History H2
where H2.AccountNo = H.AccountNo
group by H2.AccountNo having count(*) > 1 )
Where H.Timestamp > '20111101'
order by H.AccountNo,H.Timestamp

View 18 Replies View Related

SQL & PL/SQL :: Query To Return Two Different Concatenated Rows

Jan 10, 2012

I have not found a solution for this, but it could be that I don't know what to search for.

I need to edit the following statement:

SELECT a.id||a.name||a.amount*2 as transaction
FROM a
WHERE a.amount IN (500, 1000)

To return:

TRANSACTION
------------------------------------
123SMITH1000
123SMITHADJUSTED AMOUNT PER X
456JONES2000
456JONESADJUSTED AMOUNT PER X

The returned rows need to be in this format to be executed in another database.

Can this be done?

View 8 Replies View Related

SQL Query - Return Operation Over Multiple Dates

Feb 16, 2011

I am new to SQL and I am just wondering if there is a solution to a problem I am having.I am using the piece of code below.Essentially what I am doing is selecting a field from a table and ordering that field in descending order. Using the Row_Number feature I then specify which Row I want to return.

Every day the row I will want is the Count of field1 for that day divided by 100 minus 1. This returns a single value of field1 and a single value of R.

I perform this operation every day. The only fields I change every day are the dates and the value of R. I use a seperate piece of SQL code to calculate R each day.

My problem is I have to often populate historical tables with this data. I can only run the code once for each day and for each value of R. Is there anyway I can alter this code such that it can return multiple values of field1 over several dates?The only way I can think of is to repeat the code multiple times using UNION but I am hoping there is a more efficient way.

SELECT *
FROM (SELECT Field1,
ROW_NUMBER() OVER (ORDER BY field1 desc ) R
FROM table
WHERE date >= TO_DATE ('20110215', 'YYYYMMDD')
AND date < TO_DATE ('20110216', 'YYYYMMDD')
)
WHERE R = 1227
--Note: 1227 = (count(field1)/100)-1

View 5 Replies View Related

SQL & PL/SQL :: Select Query To Return Parent Rows?

Apr 1, 2012

I need to get the super set(parent) record from a relational table. Following is the structure of the table,

create table relation (data_field1 number(20),data_field2 number(20),data_field3 number(20),

primary key (data_field1,data_field2,data_field3) );
insert into relation (data_field1, data_field2, data_field3) values (-10,123,334);
insert into relation (data_field1, data_field2, data_field3) values (200,123,334);
insert into relation (data_field1, data_field2, data_field3) values (300,123,334);
insert into relation (data_field1, data_field2, data_field3) values (400,123,334);

[code].....

Here '-10' will be considered as parent(super set) for all other values. If i have records like below,

-10 200 300
100 200 300
123 200 300
521 201 300

Output should only contain two rows. A super set row and other distinct row(second column value is not equivalent to super set).

View 14 Replies View Related

SQL & PL/SQL :: Why Does SELECT DISTINCT Return Different Rows When Used With ORDER BY

Jan 4, 2013

nex lines showing SELECT DISTINCT return different rows when used with ORDER BY.

CREATE TABLE M1( ID_ NUMBER, A NUMBER, B NUMBER);
CREATE TABLE V1 ( A NUMBER, B2 NUMBER);
DELETE FROM M1;
DELETE FROM V1;
INSERT INTO M1 (ID_, A,B) VALUES (107, 5,1);
INSERT INTO M1 (ID_, A,B) VALUES (108, 11,1);
INSERT INTO M1 (ID_, A,B) VALUES (109, 17,1);

[code]....

View 11 Replies View Related

PL/SQL :: Return The Rows Of The Table Where Column Contains Specific Value First

Feb 5, 2013

I want my query to return the rows of the table where a column contains a specific values first in a certain order, and then return the rest of the rows alphabetized.

For Example:

Country
ALBANIA
ARGENTINA
AUSTRALIA
....
CANADA
....
USA
....

Now i want USA and CANADA on top in that order and then other in alphabetized order.

View 5 Replies View Related

SQL & PL/SQL :: To Return 100 Values By Using The Procedure

Aug 26, 2010

I want to return 100 values by using the procedure. In that we have to declare 100 OUT parameters. So it's a time consuming process.

Instead of declaring 100 OUT parameters. How can we return 100 values in a single shot.

View 1 Replies View Related

PL/SQL :: Return Values From Procedure

Dec 19, 2012

the following procedure successfully compiled and executed. How to access the values returning from this procedure in another procedure or PLSQL block.

CREATE OR REPLACE PROCEDURE test IS

cursor c1 is SELECT a.idnumber, a.idp FROM holdings a;
r1 c1%rowtype;

BEGIN

open c1;
loop
fetch c1 into r1;
exit when c1%notfound;
DBMS_OUTPUT.put_line (r1.idnumber||'--'||r1.idp);
end loop;
close c1;
END;

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved