How a PL/SQL stored procedure, SQL Command and temp table work together in Crystal Reports

[Former Member]

Hi all,

I don't know if this is an appropriate question, but there are so many masters of Crystal out there, I figured it couldn't hurt to ask.

I am currently working with reports that are based on PL/SQL stored procedures and the temp tables they complete with data.

When I add the temp table and stored procedure with the Database Expert, they are not linked.

The parameters are built into the stored procedure - I get that and see that when I enter them, the report runs and comes back with data from the temp tables.

I almost get this - I find it strange having worked formerly completely with straight database connections, but I can kind of see how it would work.

What I really don't get is another piece in the report.  For security reasons, the user who logs in their user id into a stored procedure parameters gets checked for their access privileges.

This is done by creating a SQL command something like this:

SELECT logon_id

FROM users

WHERE logon_id = '{?PARAM_USER_ID}'

Then the SQL command is linked to the stored procedure via logon_id.

Would anyone be able to explain to me, in simple terms, how Crystal makes this work?  Also - if you have time time, would you be able to explain to me how Crystal processes the stored procedure and temp tables in the correct order?

Any feedback would be greatly appreciated.

Thank you,

Erin

[DellSC · Accepted solution]

The link will filter the data coming back from the stored procedure so that the user will only see what they have access too.  However, this is a really bad way of doing this.

In Crystal, when you link a command and/or stored procedure to anything Crystal will pull ALL of the data into memory and then do any joining and filtering there instead of pushing the joins and filters to the database where they're handled much more efficiently. This can significantly slow down the report.

There are a couple of better options:

1.  Update the SP to take the user id as a parameter and filter the data there.

2.  Create a view that will add the logon_id to the data from the stored procedure and then use that view in the report.

3.  Create a single command in the report that will add the logon_id to the data from the stored procedure.

-Dell

[Former Member · Accepted solution]

Hi Dell,

Thank you for your prompt reply.  Unfortunately, I am not in a position (on contract) to alter how the client wants the reports set up, as they have been doing it this way since, I believe 2005.

I somewhat understand what you are saying; however, there is a parameter for logon_id in the stored procedure and that is being linked to the SQL command stored procedure, so I guess I figured that the stored procedure parameter took the logon_id and passed it to the SQL command.

I am MUCH more familiar with T-SQL SQL and the views and stored procedures that are created in SQL Server - my PL/SQL is rudimentary and at a query-only level.

I will most certainly try to pass on this information to my client before I leave, but regardless, I appreciate the input.  I, too, as little as I grasp the whole concept, believe there is an easier way - I just wanted to understand how it was all working.

Am I correct that the stored procedure is passing the logon_id parameter result to the SQL command?  Or is the SQL command passing it to the stored procedure?

Thank you again for your expertise.

Sincerely,

Erin

[DellSC · Accepted solution]

It sounds like the report is passing the parameter to both the command and the stored proc.  But without looking at it, I have no way of knowing for sure.

-Dell

[Former Member · Accepted solution]

Thank you for letting me know.  I appreciate your time and knowledge.  Take care.