Switching Oracle databases from Crystal Reports. Syntax of stored procedures.

I am switching Databases in Crystal Reports 9 in runtime using Report Designer Component (craxdrt9.dll)

Crystal Reports refer to Oracle stored procedure that has SQL statement :
SELECT 1
INTO aaa
FROM DUAL
WHERE EXISTS (SELECT *
             FROM TestTable
)
During running I've got error:
Fail to execute SQL statement. OCI Call: OCIStmtExecute

When I've changed SQL in Oracle stored procedure to
 SELECT COUNT(*)
 INTO aaa
 FROM TestTable
 
no exception occurred.

Is it correct behavior of Crystal Report Designer Component?
What are the syntax requirements for Oracle stored procedures that are called from Crystal Report?
lizzaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

EwaldLCommented:
i would think that an INTO clause would not be supported in general, although the simple select without nesting works fine for you.  why do you need the INTO here? could you drop it? It may also be better to specify the actual field name in the select part

Here is more info on using stored procedures with crystal
http://support.crystaldecisions.com/communityCS/TechnicalPapers/SCR_Oracle_Stored_Procedures.pdf.asp
http://support.crystaldecisions.com/communityCS/TechnicalPapers/storproc.pdf.asp
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lizzaAuthor Commented:
Test SQL for Stored procedures.
CREATE TABLE TestTable (a1 NUMBER);

INSERT INTO TestTable VALUES (1);

CREATE OR REPLACE PACKAGE cur_types
AS
   TYPE curTyp IS REF CURSOR;
END;
/

CREATE OR REPLACE PROCEDURE TestSP1 (cv OUT Cur_Types.CurTyp)
IS
   aaa   NUMBER;
BEGIN
   SELECT 1
     INTO aaa
     FROM DUAL
    WHERE EXISTS (SELECT *
                    FROM TestTable P
                   WHERE a1 < 0);

   --open dummy cursor
   OPEN cv FOR
      SELECT sysdate
        FROM dual;
END;
/


CREATE OR REPLACE PROCEDURE TestSP2 (cv OUT Cur_Types.CurTyp)
IS
   aaa   NUMBER;
BEGIN
   SELECT COUNT (*)
     INTO aaa
     FROM TestTable
     WHERE a1 < 0;

   --open dummy cursor
   OPEN cv FOR
      SELECT sysdate
        FROM dual;
END;
/
SP TestSP1 works only with original database. When change database at runtime, exception occurs.
SP TestSP2 works with original and changed databases.
0
frodomanCommented:
Just a thought, but when you change the database does: (SELECT * FROM TestTable P      WHERE a1 < 0) actually return anything?

The reason for your error could be that in the first SP you're selecting a fixed value INTO a variable but only if your WHERE EXISTS returns at least 1 record.  If it returns no records than you're doing an INSERT INTO that isn't selecting any data - an exception for Oracle.

The second SP works because your INSERT INTO is a COUNT, which will always return a value (whether the value is zero or otherwise).
0
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

lizzaAuthor Commented:
When I change database SELECT * FROM TestTable P WHERE a1 < 0 return nothing (in original database too).
Actually I use the same database, but different database users.
I just wonder that TestSP1 works ALWAYS correctly for original user, but throws exception after changing user at runtime. (Table TestTable has the same content for both users)
Now I know how to avoid such situations. I'll change all SP to use COUNT instead of using EXISTS, but this will decrease the performance.

0
frodomanCommented:
The alternative is to catch the exception and handle it in your sp but that has overhead of it's own...

EXCEPTION
   WHEN NO_DATA_FOUND THEN    
       (Your code here);

END;
0
mlmccCommented:
I don't know about CR9 but previous versions of Crystal have had trouble with SQL with multiple select clauses which could explain why the second version works while the first doesn't.

mlmcc
0
AnnetteHarperCommented:
I don't think that the errors that you're getting have anything to do with running the procedures from Crystal Reports. Have you tried just executing them from SQL*Plus logged in as the two different users?

Since you're only populating TestTable with one record where a1=1, the Select statement in TestSP1 should ALWAYS result in an error being raised and therefore the Open cursor statement would never be executed, resulting in an error when Crystal tries to fetch data from it. The Select statement in TestSP2 will ALWAYS return something even if it's zero, so the logic will continue and the cursor will be opened.

So, the real question is why TestSP1 works even for the original user? Either TestTable is populated with something where a1 is less than 0 for that user, or some other odd condition.
0
AnnetteHarperCommented:
lizza,

Just feel like I should reiterate. You can use a Select Into statement in a stored procedure. There is no reason that you can't. I've done it. You can do pretty much anything in the stored procedure as long as it results in an open REF Cursor for Crystal. Crystal doesn't know or care what happens inside the procedure as long as it returns one IN OUT REF CURSOR parameter and no other OUT parameters. This is a different situation than creating a SQL Command where a SELECT INTO would not be allowed since it is a PL/SQL statement.

The INTO is not what is causing your problem here. Your problem MUST be related to the fact that the cursor is not getting opened properly because there is an error condition encountered before the open statement. Verify that at least one record exists in the TestTable for each user, that that record has a1=1, and change the WHERE a1 < 0 to WHERE a1 > 0, and I think you'll get success for both schemas. If not, please let me know.

If you must do a Select Into that may fail due to "No Data Returned" you should create exception logic so that your cursor will still get opened anyway.

Annette Harper
0
frodomanCommented:
Annette - I believe that's what I already said, but I agree :-)  - Frodoman
0
AnnetteHarperCommented:
Yes, you did, I should have mentioned that. I apologize.
0
frodomanCommented:
Not a problem!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.