Oracle package and stored procedure

[MrHelpMe]

Hello again,

I was wondering if someone could help me out with my learning. I am trying to totally disregard any understanding of SQL Server and start fresh with Oracle. I have created a stored procedure/package combo using oracle 9.2 as follows. Now could someone please explain the line PROCEDURE Test_123 (results_cursor IN OUT CURSOR_TYPE); Do I have 2 parameters in this procedure or one? Now how do I execute/run this. I am calling this procedure through crystal reports 9 and it is prompting me for a results_cursor. I have no idea what value should go in here. I thought the results of the select would populate the parameter. I am looking for a clear explaination. Thanks so much once again experts.

CREATE OR REPLACE PACKAGE GroupsPackage
AS
TYPE CURSOR_TYPE IS REF CURSOR;
PROCEDURE Test_123 (results_cursor IN OUT CURSOR_TYPE);
END;
/
CREATE OR REPLACE PACKAGE BODY GroupsPackage
AS
PROCEDURE Test_123 (results_cursor IN OUT CURSOR_TYPE)
IS
BEGIN
OPEN results_cursor FOR
SELECT Entry_id
FROM SHR_people
ORDER BY Entry_id;
END;
END;
/

 

[LKBrwnDBA]

You only have ONE parameter and the bug is in Crystal Reports which is trying to supply this parameter to the procedure.

Try defining the parameter as OUT only.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[MrHelpMe]

Oh I see, well I have tried what you asked and this is the error that I receive. Sorry I have no idea how to copy and paste into here so I will manually type it

Query Engine Error:Hy000:[Oracle][ODBC]ORA-06550:line 1 column 45LS-00567:Cannot pass NULL to a NOT NULL constrained formal paramter ORA-06550 line 1, column 45:
PLS-00363:Expression Null cannot be used as an assignement target ORA-06550:Line 1 Column 7:
PL/SQL:Statement Ignored
'

What is happening?

 

[MrHelpMe]

Hello all,

Well LkBrwnDBA you lead me in the write direction. I had suspicion it was related to the oracle driver after your response since I wanted to make sure first that I was not doing anything wrong in the proc. So looking at the cr_oracle_stored_procedures.pdf they confirmed to use the crystal for oracle ODBC driver. So I setup another DSN using the Crystal Oracle ODBC Driver 4.10 instead of the Oracle in OraHome92 driver. Please correct me if I am worng or whether I should be using another driver or if their is an updated driver that will function more efficiently with oracle. Thanks again.

 

[LKBrwnDBA]

Sorry I cannot help you more with Crystal Reports, haven't used it since the year 1999.

But I suspect you need to define some kind of 'cursor' variable before invoking the procedure.

HTH

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[MrHelpMe]

Hi LkBrwnDBA

You did help me as you lead me in the right direction. I posted a message on the crystal reports forum and synapsvampire helped me. Please refer to thread766-1219880. I switched my connection to use native oracle driver and so far so good. Runs perfectly. So all the experts including yourself have always helped me pinpoint or fix issues I have been having. More brains are better then 1 Thanks again LKBrwnDBA and I thought I'd include the thread to help others who are having the same issue or are new to Oracle like myself.