Oracle Hello World: Results sets from Procs

Posts   
 
    
ianvink
User
Posts: 393
Joined: 15-Dec-2006
# Posted on: 23-Oct-2020 04:05:19   

I am using LLBL to call Stored Procs that return resultsets, but the resultsets are never created by LLBL.

I am looking for some general help/strategies on this problem:

Most of the Stored Procedures, which I can not change, look like this:

PROCEDURE get_stuff
  (
    AAA                   IN  VARCHAR2,
    x_results         OUT SYS_REFCURSOR,
  )

IF somesecurity(AAA) = "NO ACCESS" THEN
     RETURN;
END IF

OPEN x_results          FOR
     SELECT fields....

You can see that the resultsets are never returned by LLBL because LLBL sends default values for all the params when it does a Sync and fails the security.

How can I get around this general issue?

Idea: If I could specify the values that are sent to the Proc, perhaps in the Catalog Details view? See screen shot, then when the proc is called, it will pass any gatekeeper code and then the SYS_REFCURSOR will come back

Attachments
Filename File size Added on Approval
like this.png 38,207 23-Oct-2020 04:19.07 Approved
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 23-Oct-2020 09:41:25   

The system offers support for specifying default values for types, not on a per-proc basis.

If all your procs have this same security measure, you can work around this in two ways:

  1. create a schema with the same procs but without the security and create the project from that, then use schema name overwriting at runtime (or rename the schema in the designer prior to generating code)

  2. modify the Oracle driver sourcecode so you send the value specific value for the AAA param. This is kind of tricky tho. You first have to override protected virtual void RetrieveStoredProcedureResultsetMetaData(DBSchema schemaToFill, IEnumerable<DBElementName> elementNames) in OracleSchemaRetriever and make sure the OracleDbDriver.CreateParameter method knows yuo're going to retrieve stored procedures. Then change in OracleDbDriver (the ODP.NET variant) the method CreateParameter so it passes the right value to the created parameter if the parameter is for the AAA parameter.

Frans Bouma | Lead developer LLBLGen Pro