Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2003-11-19
11
Medium Priority
?
943 Views
Last Modified: 2012-06-21
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?
0
Comment
Question by:lizza
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 13

Accepted Solution

by:
EwaldL earned 375 total points
ID: 9778023
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
 

Author Comment

by:lizza
ID: 9778324
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
 
LVL 42

Expert Comment

by:frodoman
ID: 9778887
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
Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

 

Author Comment

by:lizza
ID: 9779078
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
 
LVL 42

Expert Comment

by:frodoman
ID: 9779176
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 9779320
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
 
LVL 3

Expert Comment

by:AnnetteHarper
ID: 9779324
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
 
LVL 3

Expert Comment

by:AnnetteHarper
ID: 9783017
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
 
LVL 42

Expert Comment

by:frodoman
ID: 9786971
Annette - I believe that's what I already said, but I agree :-)  - Frodoman
0
 
LVL 3

Expert Comment

by:AnnetteHarper
ID: 9787032
Yes, you did, I should have mentioned that. I apologize.
0
 
LVL 42

Expert Comment

by:frodoman
ID: 9787094
Not a problem!
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

636 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