5 Replies Latest reply on Dec 12, 2017 8:38 AM by Tim_Smith

    Virtual Tables: how to pass a parameter to a virtual table

    Alessandro Corti Seasoned Veteran

      Informatica Platform 10.1

       

      We developed a virtual table VT1 that reads the source data from a Logical Object LO1.

      The Logical Data Object LO1 has a has a parameter P1 (the name of the owner of the source table).

      The only way I found to assign a value to P1 in VT1 is:
      Properties->Data Object Parameters->Instance Value->Specify By Value

      And Deploy the Application so the user is able to query the VT1.

      As the P1 can change, depending to the final user, is another a way to assign the value to P1?

        • 1. Re: Virtual Tables: how to pass a parameter to a virtual table
          Seasoned Veteran

          I don't know of a way to do exactly what you are talking about.  But I can think of a way that might accomplish your requirement.

           

          One option is to use pass through security (defined on the LO1's source connections) and let the source database decide what data to expose.  This might work if the source system supports changing the default schemas per user or has security on views that reduces the data down to what the user is allowed to see.  This would require some experimentation.

           

          You could also use pass through security as a means to ask the database "who is this user?"  For example, by executing an oracle query "SELECT user, 1 from DUAL".  You then join by the second column with the hard wired value "1".  Ideally you could filter data based on the user's name.

           

          You might also be able to use row level security to filter out data users aren't supposed to see.

           

          One thing to keep in mind is caching.  If caching is turned on then none of the pass through security techniques will work because all the data will be cached.

           

          Cheers,

          Tim

          • 2. Re: Virtual Tables: how to pass a parameter to a virtual table
            Alessandro Corti Seasoned Veteran

            In my environment, the virtual table VT should read a physical table PT.

             

            In January 2017, The PT table is created into the schema TEST_201701:

             

            select * from TEST_201701.PT

             

            In February 2017, The PT table is created into the schema TEST_201702:

             

            select * from TEST_201702.PT

             

             

            And so on...

             

            So, the virtual table should have a parameter p_schema to point the actual owner (schema) of the table.

            • 3. Re: Virtual Tables: how to pass a parameter to a virtual table
              Seasoned Veteran

              I imagine you are using a reporting tool or some other means of executing SQL to submit the query.  So I am not sure what options you have available.

               

              There is no way to submit a SQL statement "select * from VT" and specify a parameter.  SQL does not have that construct.

              1 of 1 people found this helpful
              • 4. Re: Virtual Tables: how to pass a parameter to a virtual table
                Alessandro Corti Seasoned Veteran

                The Support wrote:

                 

                We have verified internally. Virtual table will allow you to fire a query but there is no way to pass a parameter
                file to assign the value to a parameter.

                • 5. Re: Virtual Tables: how to pass a parameter to a virtual table
                  Seasoned Veteran

                  I can give you a trick if it would be useful.  There are some limitations since it can impact pushing down filter criteria.

                   

                  This approach allows you to inject text into your mapping.  The mapping then looks something like this:

                   

                  flat_file->Java_transformation->(output port includes parameter)->SQL_Transformation

                   

                  The flat file is a dummy file with only one record.  We just need something as a source.  The java transformation asks the IDS engine what it found for the query and sends that text out through ports which can be used by later transformations.  This could be used to pass credentials to a web service, inject SQL for direct execution, etc.

                   

                  Here are some example usages:

                  Select * from shared_folder where subfolder='tim' and filename='*'

                  Select col1, col2 from anytable where anytable.name='tablename_to_inject'

                   

                  I can see if I can find an example if you are interested.

                   

                  Regards,

                  Tim