5 Replies Latest reply: Aug 28, 2014 12:46 AM by D_P RSS

    retrieving data from multiple schemas

    ksbabu

      Hi Experts,

      i have requirement like this, i have three schemas like

       

      1--SRC_SCOTT

      2--SRC_HR

      3--SRC_MARKETING

      I want to retrieve data from these three schemas,in odi procedure in command on source how to write sql statement without hardcoding schema names

      please help me

      Regards

      ksbabu

        • 1. Re: retrieving data from multiple schemas
          HimanshuP

          Hi

          You can write like

          select * from <%=OdiRef.getSchemaName("logical_schema","D")%>.tab_name

           

          Thanks

          • 2. Re: retrieving data from multiple schemas
            ksbabu

            Hi HimanshuP,

            i have little bit knowledge on API REFERENCE FUNCTIONS

            Here the thing is we need to retrieve data from multiple schemas, <%=OdiRef.getSchemaName("logical_schema","D")%> refer multiple schemas or single schema,

            at the end of my interview,i asked  interviewer,he told me we can use <%=OdiRef.getOption(),iwant to know clearly please help me

             

            Regards

            ksbabu

            • 3. Re: retrieving data from multiple schemas
              Nishikant-Oracle

              Hi

              We can use getObjectName() with table name and logical schema name as parameters.

               

              Regards

              Nishikant

              • 4. Re: retrieving data from multiple schemas
                umit

                Hi,

                 

                I think there is not any code like this : <%=OdiRef.getSchemaName("logical_schema","D")%>  if you don't have option named "logical_schema".


                There is one way :


                I've just made one example for this.But it's easier to make it hardcoded:)

                 

                1. Create a procedure  and write there

                select '<%=odiRef.getOption("NAME")%>'  from <%=odiRef.getOption("TABLE_NAME")%>

                2. Add two option under this procedure,under Designer Navigator. One of these named NAME and the other one is TABLE_NAME . Type is "Text"

                3.Create a package.Take this procedure into package.When you click your procedure in package,you will see Properties under it.

                   Go to 'Options' tab. You will see NAME and TANLE_NAME option are here.Add two values.

                 

                I added 1 and dual.

                Executed.

                "select '1' from dual" returned.

                 

                Regards





                • 5. Re: retrieving data from multiple schemas
                  D_P

                  As mentioned earlier

                  This can be achieved using the getSchemaName() method:

                  The usage are as follows:

                  public java.lang.String getSchemaName(
                  java.lang.String pLogicalSchemaName,
                  java.lang.String pLocation)

                  public java.lang.String getSchemaName(
                  java.lang.String pLogicalSchemaName,
                  java.lang.String pContextCode,
                  java.lang.String pLocation)

                  public java.lang.String getSchemaName(
                  java.lang.String pLocation)

                  public java.lang.String getSchemaName()

                   

                  Usually in a project the logical schema do not change frequently, To resolve to a correct physical schema,just mention the logical schema in the function and let odi take the context at the execution time, then LS+context will resolve to a proper physical schema and at the time of code execution the code will be replaced with the correct schema name.

                   

                  Please check this link as well:

                  getSchemaName()