| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
12-16-11, 07:56
|
|
Registered User
|
|
Join Date: Dec 2005
Posts: 42
|
|
|
Cannot connect to Oracle store procedure from Crystal Report
|
|
Hello all,
I got an Oracle store procedure as bellowed:
================================================== ========
CREATE OR REPLACE PROCEDURE SP_RETRIEVE_SCHOOL_INFO
(P_YEAR IN VARCHAR2,
P_CLASS IN VARCHAR2,
RS IN OUT SYS_REFCURSOR)
IS
P_RET_STATUS VARCHAR2(5);
BEGIN
OPEN RS FOR
SELECT NAME, (SELECT ADDRESS WHERE SCHOOL_RECORD.ADDRESS = ADDRESS FROM BOOK_ADDRESS) AS VERIFIED_ADDRESS, AGE
FROM SCHOOL_RECORD
WHERE YEAR_NO = P_YEAR AND CLASS_CD = P_CLASS;
P_RET_STATUS:=SQLCODE;
EXCEPTION
WHEN NO_DATA_FOUND THEN
P_RET_STATUS:=SQLCODE;
WHEN OTHERS THEN
P_RET_STATUS:=SQLCODE;
END SP_RETRIEVE_SCHOOL_INFO;
================================================== ======
This store procedure compiled without errors in TOAD. I am developing a crystal report that using Database Expert to connect to this store procedure, but I could not due to the following error:
ORA-06550: line x, column y:
PLS-00306: wrong number of types of arguments in call to 'SP_RETRIEVE_SCHOOL_INFO'
PL/SQL: statement ignored
I am not familiar with SYS_REFCURSOR in Oracle SP, please help me to solve my issue. Anything need to change on my current SP??? Thanks in advance.
|
Last edited by avt2k6; 12-18-11 at 00:01.
Reason: clarifying
|
12-16-11, 08:42
|
|
Lead Application Develope
|
|
Join Date: Jun 2004
Location: Liverpool, NY USA
Posts: 2,222
|
|
Try the following
Code:
CREATE OR REPLACE PROCEDURE SP_RETRIEVE_SCHOOL_INFO
(P_YEAR IN VARCHAR2,
P_CLASS IN VARCHAR2,
RS OUT SYS_REFCURSOR)
IS
P_RET_STATUS VARCHAR2(5);
BEGIN
OPEN RS FOR
SELECT NAME, b.ADDRESS VERIFIED_ADDRESS, AGE
FROM BOOK_ADDRESS b,SCHOOL_RECORD a
WHERE YEAR_NO = P_YEAR AND CLASS_CD = P_CLASS
and a.address = b.address(+);
END SP_RETRIEVE_SCHOOL_INFO;
__________________
Bill
You do not need a parachute to skydive. You only need a parachute to skydive twice. 
|
Last edited by beilstwh; 12-16-11 at 09:35.
|
12-16-11, 09:30
|
|
Registered User
|
|
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 6,416
|
|
|
|
remove/delete/eliminate whole, complete & entire EXCEPTION handler code.
It is a bad bug waiting to bite you.
__________________
You can lead some folks to knowledge, but you can not make them think.
The average person thinks he's above average!
For most folks, they don't know, what they don't know.
|
|
12-16-11, 09:35
|
|
Lead Application Develope
|
|
Join Date: Jun 2004
Location: Liverpool, NY USA
Posts: 2,222
|
|
Agreed, and the sys_refcursor should only be out
__________________
Bill
You do not need a parachute to skydive. You only need a parachute to skydive twice.
|
|
12-16-11, 11:16
|
|
Lost Boy
|
|
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 3,629
|
|
What do you need a stored procedure for? Why don't you simply use a SELECT statement in your report?
If I read it correctly, it could have been rewritten as
Code:
select
r.name,
a.address,
r.age
from school_record r,
book_address a
where r.address = a.address
and r.year_no = :p_year
and r.class_cd = :p_class;
Furthermore, as I don't see any reason to join these two tables, the final result might have been
Code:
select
r.name,
r.address,
r.age
from school_record r
where r.year_no = :p_year
and r.class_cd = :p_class;
:P_YEAR and :P_CLASS are report parameters (I don't know how you qualify them in Crystal Reports - is it a colon sign, an ampersand & or something different, but I guess you know it).
|
|
12-16-11, 11:33
|
|
Registered User
|
|
Join Date: Dec 2005
Posts: 42
|
|
|
Last edited by avt2k6; 12-21-11 at 19:07.
|
12-16-11, 14:29
|
|
Registered User
|
|
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 6,416
|
|
|
__________________
You can lead some folks to knowledge, but you can not make them think.
The average person thinks he's above average!
For most folks, they don't know, what they don't know.
|
|
12-17-11, 00:32
|
|
Registered User
|
|
Join Date: Dec 2005
Posts: 42
|
|
Hello all,
After your suggestions, I tried but it produced same error. I like to add more information that I already did, so you know what I need help for your Oracle expertise. I figured out my error came from SP when filling the dataset using parameter store procedure. Therefore, I eliminated the crystal report issue. In a brief, I could not fill dataset that call from store procedure. I really appreciate your inputs to solve my issue rather than what you think but not experience. For example, why I need SP rather than SQL select? I don't know whether or not you have been working on Crystal Report before, but I think SP is the right choice. Anyways, I re-checked my code to call stored procedure from VB.NET and found out that I misspelled the parameter name. Nothing wrong with SP at all after I found why. Oracle above error that never point out where the error was. That why it is hard to find. My experience after this error, check to make sure the parameter match between SP and code behind. Hope to help anyone else to run into this error.
|
Last edited by avt2k6; 12-21-11 at 19:13.
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
could not connect to Oracle store procedure from CR!fro
