[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Oracle stored Procedure (P1 in numeric, p2 out varchar)

Posted on 2006-03-30
3
Medium Priority
?
938 Views
Last Modified: 2007-12-19
Hi

I need to create an oracle stored procedure with 2 parameters
p1 and p2
P1 is input parameter of type numeric
p2 is output character of type varchar2(200)


My select statement is
select Field1 from table1 where field2 =:P1

This select statement will return more than 2 values

I need to loop through this two values concatnate the 2 or more values returned and send it out as an output parameter.

I tried creating a cursor and then looping through am not able to get the procedure to work.

Could any one please help.

Thanks

Madhavi
0
Comment
Question by:SMadhavi
1 Comment
 
LVL 4

Accepted Solution

by:
Harish_Rajani earned 2000 total points
ID: 16331857
You Can create a procedure in following lines :



CREATE OR REPLACE PROCEDURE  MY_TEST_PROC (Field1 IN NUMBER, Field2 OUT VARCHAR2)
AS
CURSOR My_Cursor(X Number)
IS
SELECT employee_name FROM emp
WHERE  employee_id >= X;

bigstring VARCHAR2(2000);

BEGIN

FOR recX IN My_Cursor(Field1)
LOOP
bigstring := bigstring||recX.employee_name;
END LOOP;

Filed2 :=bigstring;

END MY_TEST_PROC;

Rgds,
HR
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question