5 Replies Latest reply: Feb 3, 2012 8:46 AM by user8663143 RSS

    Logical Data Service from a Physical Data Service that is based on PL/SQL

    user8663143
      Hi,

      I am unable to create a Logical data service from a PL/SQL generated Physical Data service. This data service has a method type as library. I want to create a Logical data service from this Physical data service to do some error handling. Please advice. Thanks

      Rahul
        • 1. Re: Logical Data Service from a Physical Data Service that is based on PL/SQL
          701304
          What error are you getting?

          Logical Data Service

          Try creating your physical DS as library function.
          • 2. Re: Logical Data Service from a Physical Data Service that is based on PL/SQL
            Mikereiche-Oracle
            Can you please explain in detail? Please show your physical data service.

            What does "unable to create a Logical data service from a ... Physical Data service" mean? - Logical Data services are not "created from" physical data services. They are just created. Once they are created, you can add functions to them, and those functions can call functions in other data services. Or you can add procedures to them, and those procedures can call procedures or functions in other data services.

            Note that a procedure cannot be called from a function. If you created the physical data service, with a procedure method (i.e. it can update the db), then you won't be able to call it from a function.

            functions : are read-only; a function can only call another function.
            procedures : can do updates; a procedure can call another procedure or a function
            • 3. Re: Logical Data Service from a Physical Data Service that is based on PL/SQL
              user8663143
              Hi Mike,

              As requested here is my Physical Data service from a Package.

              xquery version "1.0" encoding "UTF-8";

              (::pragma xfl <x:xfl xmlns:x="urn:annotations.ld.bea.com">
              <creationDate>2012-01-31T19:13:23</creationDate>
              <relationalDB name="PRiME_ODSI_DS" providerId="Oracle-9"/>
              </x:xfl>::)

              declare namespace f1 = "ld:physical/packages/PrescriberPrescriptionActionItems";

              import schema namespace t1 = "PrescriberPrescriptionActionItems" at "ld:physical/packages/schemas/GETPRESCRIBERACTIONITEMS.xsd";

              (::pragma function <f:function xmlns:f="urn:annotations.ld.bea.com" visibility="public" kind="library" isPrimary="false" nativeName="GETPRESCRIBERACTIONITEMS" nativeLevel2Container="GRM_APP" nativeLevel3Container="PKG_PRESCRIBER_DASHBOARD" style="storedProcedure">
              <nonCacheable/>
              <params xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdt="http://www.w3.org/2004/07/xpath-datatypes" xmlns:pn1="ld:physical/packages/PrescriberPrescriptionActionItem" >
              <param name="PRESCRIBERID" kind="in" xqueryType="xs:decimal" nativeTypeCode="3" nativeType="NUMBER"/>
              <param name="PRODUCTID" kind="in" xqueryType="xs:decimal" nativeTypeCode="3" nativeType="NUMBER"/>
              <param name="AUTHORIZATIONSTATUS" kind="in" xqueryType="xs:string" nativeTypeCode="12" nativeType="VARCHAR2"/>
              <param name="ASSOCIATEDPRESCRIBERID" kind="in" xqueryType="xs:decimal" nativeTypeCode="3" nativeType="NUMBER"/>
              <param name="ASSOCIATEDPRESCRIBERNAME" kind="in" xqueryType="xs:string" nativeTypeCode="12" nativeType="VARCHAR2"/>
              <param name="SORTBYFIELD" kind="in" xqueryType="xs:string" nativeTypeCode="12" nativeType="VARCHAR2"/>
              <param name="SORTBYORDER" kind="in" xqueryType="xs:string" nativeTypeCode="12" nativeType="VARCHAR2"/>
              <param name="STARTROW" kind="in" xqueryType="xs:decimal" nativeTypeCode="3" nativeType="NUMBER"/>
              <param name="ENDROW" kind="in" xqueryType="xs:decimal" nativeTypeCode="3" nativeType="NUMBER"/>
              <param name="RESPONSESTATUS" kind="out" xqueryType="xs:decimal" nativeTypeCode="3" nativeType="NUMBER"/>
              <param name="RESPONSEMESSAGE" kind="out" xqueryType="xs:string" nativeTypeCode="12" nativeType="VARCHAR2"/>
              <param name="PRESCRIBERACTIONITEMS" kind="out" xqueryType="pn1:PrescriberPrescriptionActionItem" nativeTypeCode="0" nativeType="RowSet"/>
              </params>
              </f:function>::)

              declare procedure f1:getPrescriberPrescriptionActionItems($PRESCRIBERID as xs:decimal, $PRODUCTID as xs:decimal?, $AUTHORIZATIONSTATUS as xs:string?, $ASSOCIATEDPRESCRIBERID as xs:decimal?, $ASSOCIATEDPRESCRIBERNAME as xs:string?, $SORTBYFIELD as xs:string?, $SORTBYORDER as xs:string?, $STARTROW as xs:decimal, $ENDROW as xs:decimal) as schema-element(t1:GETPRESCRIBERACTIONITEMS) external;


              Now I and to create a Logical Dataservice and want to call this fuctionality there. I can understand the Logical Dataservice can not be generated from Physical. But you can very well write fucntions and procedured and Map them to a physical dataservice.

              I want to do the same, in this case where physical dataservice is a library procedure how to do that. I have declared a xsd in logical layer and want to Map the return valve from this Physical Dataservice to be transformed in the XSD of Logical layer.

              Please let me know if you need more info. Thanks.

              Rahul
              • 4. Re: Logical Data Service from a Physical Data Service that is based on PL/SQL
                Mikereiche-Oracle
                functions : are read-only; a function can only call another function.
                You cannot call a procedure from an xquery function. This is because xquery is a declarative language - you declare what you want the result to be - you do not declare the procedure for obtaining the result. A function called from another function may or may not be called - or may even be called multiple times even though it is only specified once. Since procedures can do updates, it is important that you know exactly how many times the procedure is called, and that is the reason that calling procedures from functions is prohibited.

                If your storedProcedure does not do any updates, and you have mistakenly created it in the physical data service as a libraryProcedure instead of a libraryFunction - go back and recreate it as a libraryFunction.

                btw - somehow the pragma contains style="" which is not valid. It should be style="storedProcedure"
                • 5. Re: Logical Data Service from a Physical Data Service that is based on PL/SQL
                  user8663143
                  Thanks Mike, It is working now when I tried the way you have explained.

                  Regards
                  Rahul Mishra