Oracle Private Database - Create Context

Oracle Private Database - Create Context

Postby vcechin » Wed 27 Jan 2016 10:57

Greeting:

I need to intercept the connection of OracleDataSource (ASP.NET) because I use the Oracle Private Database feature, and post the connection event, have to pass the parameters of the oracle surrounding context in my session, currently in a client / server use the resource below. I wonder what the best practice of using this feature in ASP.NET

My platform:
Minha plataforma:
Windows 10, Visual Studio 2010, Dot Connect for Oracle 8.5.558.0

Example of the resource currently used in client / server:

"CREATE CONTEXT CTX_CTX_ID USING PCK_CTX_ID;
/
CREATE OR REPLACE PACKAGE PCK_CTX_ID AS
PROCEDURE SET_CONTEXT(CTX_NAME VARCHAR2, CTX_ID VARCHAR2);
END;
/
CREATE OR REPLACE PACKAGE BODY PCK_CTX_ID IS
PROCEDURE SET_CONTEXT(CTX_NAME VARCHAR2, CTX_ID VARCHAR2) IS
SINC_ID_ VARCHAR2(30);
BEGIN
DBMS_SESSION.SET_CONTEXT(CTX_NAME,'CTX_ID',CTX_ID);
END;
END;
/"

Command that must pass before executing the select component of OracleDataSource:

PCK_CTX_ID.SET_CONTEXT('CTX_CTX_ID', CTX_ID_);
PCD_SET_CLIENT_INFO(TO_CHAR(ACU_ID_) || ';' || IP_ || ';' || TO_CHAR(LANGUAGE_) || ';' || VERSION_ || ';' || TO_CHAR(CTX_ID_) || ';' || TO_CHAR(COM_ID_) || ';' || ACU_NAME_, LOGIN_);


Best regards,

Vanderlei
vcechin
 
Posts: 4
Joined: Sat 12 Sep 2009 22:30

Re: Oracle Private Database - Create Context

Postby Shalex » Wed 27 Jan 2016 12:14

Try using the Initialization Command connection string parameter:
Code: Select all
"Initialization Command=BEGIN PCK_CTX_ID.SET_CONTEXT('CTX_CTX_ID', CTX_ID_); PCD_SET_CLIENT_INFO(TO_CHAR(ACU_ID_) || ';' || IP_ || ';' || TO_CHAR(LANGUAGE_) || ';' || VERSION_ || ';' || TO_CHAR(CTX_ID_) || ';' || TO_CHAR(COM_ID_) || ';' || ACU_NAME_, LOGIN_); END;"
Shalex
Devart Team
 
Posts: 7536
Joined: Thu 14 Aug 2008 12:44

Re: Oracle Private Database - Create Context

Postby vcechin » Wed 27 Jan 2016 13:17

Hello,

The problem that the parameters values should I pass the session I just have after personal login page. The connection string I still do not have the data to be reported.
Required for the parameters in the time before the select command OracleDataSource component.
Please send me an example.

Best regards,

Vanderlei
vcechin
 
Posts: 4
Joined: Sat 12 Sep 2009 22:30

Re: Oracle Private Database - Create Context

Postby Shalex » Thu 28 Jan 2016 07:14

vcechin wrote:The problem that the parameters values should I pass the session I just have after personal login page.
Your scenario is not clear. Why don't you want to set the Initialization Command connection string parameter after personal login page but before using OracleDataSource component?
Shalex
Devart Team
 
Posts: 7536
Joined: Thu 14 Aug 2008 12:44

Re: Oracle Private Database - Create Context

Postby vcechin » Thu 28 Jan 2016 10:29

I believe it has not properly explained the process.

1-Login User personnel webform.aspx and makes the reading of information the user is entitled to access (CONTEXT).
2-Apply context to private database (DBMS_SESSION.SET_CONTEXT) and SYS.DBMS_APPLICATION_INFO.SET_CLIENT_INFO
3-In OracleDataSource component performs "SELECT * FROM SALES" where the records returned are only those belonging to his CONTEXT

My question is: My conection string is in the web.config in this way when using the select component in OracleDataSource, I have to inform the data from the CONTEXT, because I have no access to the connection of OracleDataSource component.

In the above example you entered to use the parameter "Initialization Command", but as dynamic around it so I can change the parameters, remembering that I use connectionstring located in the web.config file.

Please send me any example making the connection dynamic string through the web.config file and using the parameter "Initialization Command"
thanks for listening

Best regards

Vanderlei
vcechin
 
Posts: 4
Joined: Sat 12 Sep 2009 22:30

Re: Oracle Private Database - Create Context

Postby Shalex » Thu 28 Jan 2016 15:05

Assuming your WebForm1.aspx includes this grid view:
Code: Select all
        <asp:GridView ID="GridView1" runat="server" OnInit="GridView1_Init">
        </asp:GridView>

Create the GridView1_Init event handler in WebForm1.aspx.cs:
Code: Select all
        // OracleMonitor monitor;

        protected void GridView1_Init(object sender, EventArgs e) {

            // monitor = new OracleMonitor() { IsActive = true };

            GridView1.DataSource = new OracleDataSource("Server=orcl1120;User Id=scott;pwd=tiger;Initialization Command=\"begin null; end;\"", "select * from emp");
            GridView1.DataBind();
        }

1. Use dbMonitor to trace the database activity (for debugging).
2. Replace null; within Initialization Command with your actual commands.
3. For more information about the OracleDataSource component, refer to https://www.devart.com/dotconnect/oracle/docs/?SqlDataSource.html.
Shalex
Devart Team
 
Posts: 7536
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle