| Overloading system stored procedures [message #37502] |
Tue, 12 February 2002 08:19  |
sulinder singh
Messages: 10
Registered: January 2002
|
Junior Member |
|
|
Hi Everyone,
I have a very peculiar need
I am using row level security in my database which is setting polcies based on application userid and pwd.
To set the session i hv used dbms_session and to create policy hv used dbms_rls, which is working fine through PL/SQL and other front end tools
My need is to use table on which policies have been enabled in crystal reports, and crystal reports does allow me to make a report based on a stored procedure which returns a result set.
The problem is it does not allow me to call another procedure with in that stored procedure, hence i cannot call dbms_sessoin with my stored procedure on which crystal reports is creatign a report
I was thinking if i could do all that dbms_session is doing and return a ref cursor it would solve my problem by which i could hv overlaoded the procedure and solved my purpose, i do hv mailed this same question on crystal reports forum...but since i am desperate for a solution, i am trying aall ways..
Can any one throw some time on this
Thanks in Advance.
Thanks Regards
Sulinder Singh
|
|
|
|
| Re: Overloading system stored procedures [message #37507 is a reply to message #37502] |
Tue, 12 February 2002 11:08  |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
|
Once the proc is executed in Oracle, crystal reports has no control over whether the proc can call another proc or not. The user you are connecting through with crystal reports is surely the problem. You need direct grants not through roles. Have you tested the proc you are calling from crystal from sqlplus using the same user?
|
|
|
|
| Re: Overloading system stored procedures [message #37518 is a reply to message #37502] |
Tue, 12 February 2002 20:15 |
sulinder singh
Messages: 10
Registered: January 2002
|
Junior Member |
|
|
I have Executed the same procedure thorugh SQL PLUS it gives me proper output i had granted right with statement
grant all on dbms_session to username
grant all on dbms_rls to username
grant create any context to username
login username/password@hoststring
create or replace context contextname using procedurename
If Crystal Reports does allow dbms_output to be called within that procedure it should allow dbms_session also to be executed within the procedure.
Thanks in Advance
Sulinder Singh
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
