2 Replies Latest reply on Oct 3, 2011 11:44 AM by 891514

    Oracle Profile Provider Properties To Get Using PL/SQL


      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
      <add allowAnonymous="false" name="FirstName" type="String" serializeAs="String"/>
      <add allowAnonymous="false" name="LastName" type="String" serializeAs="String"/>

      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

          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.

          • 2. Re: Oracle Profile Provider Properties To Get Using PL/SQL

            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
            paramUserID RAW,
            paramPropertyName VARCHAR2
            RETURN VARCHAR2

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


            -- 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;
            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;


            Tareq Samy