Skip to Content
0
Former Member
Aug 20, 2015 at 06:43 PM

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

101 Views

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