2 Replies Latest reply: Oct 3, 2011 6:44 AM by 891514 RSS

    Oracle Profile Provider Properties To Get Using PL/SQL

    891514
      Hello,

      I used ODP.NET with ASP.NET Web Site for authentication and profile management. I do successfully run the website with the following properties perfectly
      <properties>
      <add allowAnonymous="false" name="FirstName" type="String" serializeAs="String"/>
      <add allowAnonymous="false" name="LastName" type="String" serializeAs="String"/>
      </properties>

      I could set and get such properties from the code too. Now i want to get these property values via PL/SQL stored procedure in same database..How can i do that? -As i found that the property values stored as Name:Type:StartIndex:Lenght.

      Thanks in advance,
      Tareq Samy
        • 1. Re: Oracle Profile Provider Properties To Get Using PL/SQL
          gdarling - oracle
          Hi,

          It's not likely you'll get an answer for that. Oracle does not document the API's used by the ASP.NET providers, and provides the provider only for use as a "whole". We don't encourage directly interacting with the tables/procedures.

          Greg
          • 2. Re: Oracle Profile Provider Properties To Get Using PL/SQL
            891514
            Hi,

            really it was tough to find a working answer for it, until i found a solution for original provider using T-SQL. I converted the code to PL/SQL and matched it with the oracle provider model and voala...I get it work :)

            For all others looking for it, the code posted below and it's generic. effort appreciated by karpach through Richard Edwards at DNNStuff here .

            create or replace
            FUNCTION SR_ORA_ASPNET_PROF_GETPROPERTY (
            paramUserID RAW,
            paramPropertyName VARCHAR2
            )
            RETURN VARCHAR2
            AS

            ReturnValue VARCHAR2(500);
            PropNames VARCHAR2(500);
            PropValues VARCHAR2(500);
            PropPosition NUMBER;
            IdxStart NUMBER(3);
            PropLength NUMBER(3);

            BEGIN

            -- Note: Prefix the first property name with a colon. So we know that EVERY property name will be bookended with colons.
            SELECT ':' || to_char(PropertyNames) into PropNames
            FROM ora_aspnet_Profile
            WHERE UserId = paramUserID;

            SELECT to_char(PropertyValuesString) into PropValues
            FROM ora_aspnet_Profile
            WHERE UserId = paramUserID;

            PropPosition := instr(PropNames, ':' || paramPropertyName || ':S');

            IF PropPosition = 0 THEN
            ReturnValue := null;
            ELSE
            PropPosition := PropPosition + LENGTH(paramPropertyName) + 4;
            PropNames := substr(PropNames, PropPosition, length(PropNames));
            IdxStart := substr(PropNames, 1, instr(PropNames, ':') - 1) + 1;
            PropLength := substr(PropNames, instr(PropNames, ':') + 1, ((instr(PropNames, ':', instr(PropNames, ':') + 1)) - (instr(PropNames, ':') + 1)));
            ReturnValue := substr(PropValues, IdxStart, PropLength);
            END IF;

            RETURN ReturnValue;

            END SR_ORA_ASPNET_PROF_GETPROPERTY;

            Thanks,
            Tareq Samy