QUERY ON sys_refcursor

Question

0.00/5 (No votes)

See more:

Hi i am using Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 and i am new to oracle word, working in MS SQL SERVER.

I want to know is this procedure is correct or not.

1)
My requirement is to get details for the given mobile no:, only one record will be returned
and also if there is no such mobile no. Invalid Mobile No message should be given to the client
vb.net.

CREATE OR REPLACE PROCEDURE GetSenderDetails (In_MobNo IN NUMBER,
SenderResultSet OUT SYS_REFCURSOR)
IS
BEGIN
OPEN SenderResultSet FOR
SELECT SENDERID,CHAPARTNERID,BCAGENT,SENDERNAME
FROM SenderMaster
WHERE SENDERMOBILENO=In_MobNo;
IF SQL%NOTFOUND THEN
Raise_application_error(-20011, '(Invalid Mobile No:');
END IF;
END;
/

2) This procedure is called to get recevier details for the given senderid. result set will 'n' records and the same if no senderid is not there message should be 'INVALID SENDER ID'

CREATE OR REPLACE PROCEDURE GetReceiverDetails (In_SenderID IN NUMBER,
ReceiverResultSet OUT SYS_REFCURSOR)
IS
BEGIN
OPEN ReceiverResultSet FOR
SELECT RECEIVERID,CHAPARTNERID,RECEIVERNAME
FROM ReceiverMaster
WHERE SenderID= In_SenderID ;

IF SQL%NOTFOUND THEN
Raise_application_error(-20011, '(SenderID:');
END IF;
END;

I want run in sql*plus how to execute this SP.

NOTE: IS THIS GOOD OR ANY BETTER WAYS TO RETURN RESULT SET IN ORACLE.

Thanks in advance.

With Regards

Heljeeve

Posted 10-Oct-13 23:46pm

Heljeeve

Updated 11-Oct-13 0:15am

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

bluesathish · Answer 1 · 2013-10-30T23:32:00

Solution 1

I've changed your sender procedure as below, have a look at this and it'll give you a clear idea. Similarly you can write for Receiver procedure.

SQL

CREATE OR REPLACE PROCEDURE GetSenderDetails (In_MobNo IN NUMBER,
SenderResultSet OUT SYS_REFCURSOR)
IS
V_COUNT NUMBER;
BEGIN

SELECT COUNT(*) INTO V_COUNT FROM SenderMaster
WHERE SENDERMOBILENO=In_MobNo;

IF V_COUNT > 0 THEN
	OPEN SenderResultSet FOR
	SELECT SENDERID,CHAPARTNERID,BCAGENT,SENDERNAME
	FROM SenderMaster
	WHERE SENDERMOBILENO=In_MobNo;
ELSE
	OPEN SenderResultSet FOR 
	SELECT 'INVALID MOBILENO' FROM DUAL;
END IF;

END;

Regards,
BlueSathish

Posted 30-Oct-13 23:32pm

bluesathish

Comments

Heljeeve 1-Nov-13 4:51am

Hi, thanks for your help. But i have one more question abt this. Is there any other wrokaround for getting the row count, with out using count(*) because sendermaster table will be having more than 1 crore records, so 2 select statements will be a problem, and this procedure will called frequently.

In SQL SERVER we use @@ROWCOUNT to get check the existenece of records.

Regards

Heljeeve.

bluesathish 7-Nov-13 0:03am

You can use Count(ur_column_name) instead of count(*). Here ur_column_name is any unique indexed column for your table, it'll retreived quickly.