10 Replies Latest reply: Feb 18, 2013 4:32 AM by Bhabani Ranjan RSS

    How to specify path to read E$ tab records into .xls file using odisqlunlod

    Phanikanth
      Hi

      Can any one help me how to specify specific path in odisqlunload tools which is useful for both windows and linux.
      I am developing and testing in windows and moving generated scenario into linux box to test for testing people
      If in case any error out records are populated in E$ table then how that records will populated on xls file, later I am sending that attachement for email notification to concern people

      Below code is present in odisqlunload tool

      OdiSqlUnload "-FILE=d:\ODI_Error_Out_Files\Notification_Error_Records.xls" "-DRIVER=oracle.jdbc.OracleDriver" "-URL=jdbc:oracle:thin:@10.75.114.146:1521:POCWCDS" "-USER=wcds" "-PASS=h2yXeih4hFlXXV,QaMeRR2Fy" "-FILE_FORMAT=VARIABLE" "-ROW_SEP=\r\n" "-DATE_FORMAT=yyyy/MM/dd HH:mm:ss" "-CHARSET_ENCODING=ISO8859_1" "-XML_CHARSET_ENCODING=ISO-8859-1"
      select * from E$_notification

      Please help how to make a single code which is useful and work on both windows and linux
      Any sugession willl help me

      Thanks in advance

      Regards,
      Phanikanth
        • 1. Re: How to specify path to read E$ tab records into .xls file using odisqlunlod
          CezarSantos-http://odiexperts.com
          Hi,

          For Windows and Linux do you use distinct Contexts?



          Cezar Santos
          http:\\OdiExperts.com
          • 2. Re: How to specify path to read E$ tab records into .xls file using odisqlunlod
            Phanikanth
            Hi

            Thank you for your resposnse

            Yes, I will use distinct context for both (windows and linux)

            Regards,
            Phanikanth
            • 3. Re: How to specify path to read E$ tab records into .xls file using odisqlunlod
              Phanikanth
              Hi

              I have found the soluntion with help of below URL to make dynamic file path which is usful for any platform below is the code, but still the issue dynamic direcotry location is not yet resolved.

              URL: using code of odisqlunload in a procedure...

              OdiSqlUnload "-FILE=<%=snpRef.getOption("UNLOAD_DIR")%>\<%=snpRef.getSession("SESS_NO")%>.xlsx" "-DRIVER=<%=odiRef.getInfo("DEST_JAVA_DRIVER")%>" "-URL=<%=odiRef.getInfo("DEST_JAVA_URL")%>" "-USER=<%=odiRef.getInfo("DEST_USER_NAME")%>" "-PASS=<%=odiRef.getInfo("DEST_ENCODED_PASS")%>" "-FILE_FORMAT=VARIABLE" "-ROW_SEP=\r\n" "-DATE_FORMAT=yyyy/MM/dd HH:mm:ss" "-CHARSET_ENCODING=ISO8859_1" "-XML_CHARSET_ENCODING=ISO-8859-1"
              select * from <%=odiRef.getTable("L", "ERR_NAME", "W")%>

              I have added default path D:\unload_dir path in ODI procedure option and it is also not performing well.


              But I am ubale to reads the E$ table name, I want to read the error out records from E$ table for that I have passed the below select query as SQL statement parameter

              select * from <%=odiRef.getTable("L", "ERR_NAME", "W")%>

              But it is not reading the E$ table giving an error as ORA-00900: invalid SQL statement and when I saw the Code tab, I found below query

              OdiSqlUnload "-FILE=D:\Unload_Dir\988360.xlsx" "-DRIVER=oracle.jdbc.OracleDriver" "-URL=jdbc:oracle:thin:@10.75.114.146:1521:POCWCDS" "-USER=wcds" "-PASS=<@=snpRef.getInfo("DEST_ENCODED_PASS") @>" "-FILE_FORMAT=VARIABLE" "-ROW_SEP=\r\n" "-DATE_FORMAT=yyyy/MM/dd HH:mm:ss" "-CHARSET_ENCODING=ISO8859_1" "-XML_CHARSET_ENCODING=ISO-8859-1"
              select * from WCDS.

              (where WCDS is my target DB schema name)

              In top, there is no table name
              please any one help me to resolve this error cause, it is very urgent

              Note: I moving only ODI scenario to linux box because (EWR environment present in linux box)

              Thanks,
              Phanikanth

              Edited by: Phanikanth on Feb 17, 2013 10:12 PM

              Edited by: Phanikanth on Feb 17, 2013 11:07 PM
              • 4. Re: How to specify path to read E$ tab records into .xls file using odisqlunlod
                Bhabani Ranjan
                2nd Issue:For E$ table:

                Create one more step in odi KM: May be just after create E$ step.

                Comand on Target:

                <@
                String error_tab_name="<%=odiRef.getTable("L", "ERR_NAME", "W")%>";
                @>


                Now in odisqlUnload you can use this as follows
                OdiSqlUnload "-FILE=<%=snpRef.getOption("UNLOAD_DIR")%>\<%=snpRef.getSession("SESS_NO")%>.xlsx" "-DRIVER=<%=odiRef.getInfo("DEST_JAVA_DRIVER")%>" "-URL=<%=odiRef.getInfo("DEST_JAVA_URL")%>" "-USER=<%=odiRef.getInfo("DEST_USER_NAME")%>" "-PASS=<%=odiRef.getInfo("DEST_ENCODED_PASS")%>" "-FILE_FORMAT=VARIABLE" "-ROW_SEP=\r\n" "-DATE_FORMAT=yyyy/MM/dd HH:mm:ss" "-CHARSET_ENCODING=ISO8859_1" "-XML_CHARSET_ENCODING=ISO-8859-1"
                select * from <@=error_tab_name@>


                1st issue: For path name based on linux
                If you can get the OS details using java then you can store the path into a java variable accordingly. For example
                <@
                if os==windows
                then v_path="D:\unload_dir ";
                else if os==linux
                then then v_path="/home/phaniknath/file_dir";
                @>

                Note: This is not the exact code. Just a logic to be implemented.
                I will update you as soon as i can.

                Thanks
                Bhabani
                • 5. Re: How to specify path to read E$ tab records into .xls file using odisqlunlod
                  Bhabani Ranjan
                  Use below codes inside a procedure with java beanshell technology. let me know how it is going.

                  <@
                  String OS = System.getProperty("os.name").toLowerCase();
                  String v_path="";
                  if((OS.indexOf("win") >= 0))
                  v_path="D:\file_dir";
                  else if (OS.indexOf("mac") >= 0)
                  v_path="path details";
                  else if (OS.indexOf("nix") >= 0 || OS.indexOf("nux") >= 0 || OS.indexOf("aix") > 0 )
                  v_path="/home/phaniknath/file_dir";
                  else if (OS.indexOf("sunos") >= 0)
                  v_path="soliaris path";
                  @>

                  So final code goes here
                  OdiSqlUnload "-FILE=<@=v_path@>\<%=snpRef.getSession("SESS_NO")%>.xlsx" "-DRIVER=<%=odiRef.getInfo("DEST_JAVA_DRIVER")%>" "-URL=<%=odiRef.getInfo("DEST_JAVA_URL")%>" "-USER=<%=odiRef.getInfo("DEST_USER_NAME")%>" "-PASS=<%=odiRef.getInfo("DEST_ENCODED_PASS")%>" "-FILE_FORMAT=VARIABLE" "-ROW_SEP=\r\n" "-DATE_FORMAT=yyyy/MM/dd HH:mm:ss" "-CHARSET_ENCODING=ISO8859_1" "-XML_CHARSET_ENCODING=ISO-8859-1"
                  select * from <@=error_tab_name@>
                  • 6. Re: How to specify path to read E$ tab records into .xls file using odisqlunlod
                    Phanikanth
                    Hi Bhabani,

                    I have written below code in KM itself and select technology as Java BeanShall

                    Code:

                    <@
                    String OS = System.getProperty("os.name").toLowerCase();
                    String v_path="";
                    if((OS.indexOf("win") >= 0))
                    v_path="D:\Unload_Dir\<%=snpRef.getSession("SESS_NO")%>.xlsx";
                    else if (OS.indexOf("mac") >= 0)
                    v_path="path details";
                    else if (OS.indexOf("nix") >= 0 || OS.indexOf("nux") >= 0 || OS.indexOf("aix") > 0 )
                    v_path="/odi_a/Middleware/logs/wcds/odi_logs/<%=snpRef.getSession("SESS_NO")%>.xlsx";
                    else if (OS.indexOf("sunos") >= 0)
                    v_path="soliaris path";
                    @>

                    OdiSqlUnload "-FILE=<@=v_path@>" "-DRIVER=<%=odiRef.getInfo("DEST_JAVA_DRIVER")%>" "-URL=<%=odiRef.getInfo("DEST_JAVA_URL")%>" "-USER=<%=odiRef.getInfo("DEST_USER_NAME")%>" "-PASS=<%=odiRef.getInfo("DEST_ENCODED_PASS")%>" "-FILE_FORMAT=VARIABLE" "-ROW_SEP=\r\n" "-DATE_FORMAT=yyyy/MM/dd HH:mm:ss" "-CHARSET_ENCODING=ISO8859_1" "-XML_CHARSET_ENCODING=ISO-8859-1"
                    select * from <%=odiRef.getTable("L","ERR_NAME", "W")%>

                    It was executed well and below is the Execution code of the above code

                    Execution Code:

                    <@
                    String OS = System.getProperty("os.name").toLowerCase();
                    String v_path="";
                    if((OS.indexOf("win") >= 0))
                    v_path="D:\Unload_Dir\1341360.xlsx";
                    else if (OS.indexOf("mac") >= 0)
                    v_path="path details";
                    else if (OS.indexOf("nix") >= 0 || OS.indexOf("nux") >= 0 || OS.indexOf("aix") > 0 )
                    v_path="/odi_a/Middleware/logs/wcds/odi_logs/1341360.xlsx";
                    else if (OS.indexOf("sunos") >= 0)
                    v_path="soliaris path";
                    @>

                    OdiSqlUnload "-FILE=<@=v_path@>" "-DRIVER=oracle.jdbc.OracleDriver" "-URL=jdbc:oracle:thin:@10.75.114.146:1521:POCWCDS" "-USER=wcds" "-PASS=<@=snpRef.getInfo("DEST_ENCODED_PASS") @>" "-FILE_FORMAT=VARIABLE" "-ROW_SEP=\r\n" "-DATE_FORMAT=yyyy/MM/dd HH:mm:ss" "-CHARSET_ENCODING=ISO8859_1" "-XML_CHARSET_ENCODING=ISO-8859-1"
                    select * from WCDS.E$_CDS_COMPANY

                    Please confirm me if the above Code is correct or not, if Not, please correct it and DESC_ENCODE_PASS is not encoding the password

                    Regards
                    Phanikanth

                    Edited by: Phanikanth on Feb 18, 2013 1:09 AM
                    • 7. Re: How to specify path to read E$ tab records into .xls file using odisqlunlod
                      Bhabani Ranjan
                      Code is correct but need little modification if you want the session number to be appended in the file name.

                      <@
                      String OS = System.getProperty("os.name").toLowerCase();
                      String v_path="";
                      if((OS.indexOf("win") >= 0))
                      v_path="D:/Unload_Dir";
                      else if (OS.indexOf("mac") >= 0)
                      v_path="path details";
                      else if (OS.indexOf("nix") >= 0 || OS.indexOf("nux") >= 0 || OS.indexOf("aix") > 0 )
                      v_path="/odi_a/Middleware/logs/wcds/odi_logs";
                      else if (OS.indexOf("sunos") >= 0)
                      v_path="soliaris path";
                      @>

                      OdiSqlUnload "-FILE=<@=v_path@>/<%=snpRef.getSession("SESS_NO")%>.xlsx" "-DRIVER=oracle.jdbc.OracleDriver" "-URL=jdbc:oracle:thin:@10.75.114.146:1521:POCWCDS" "-USER=wcds" "-PASS=<@=snpRef.getInfo("DEST_ENCODED_PASS") @>" "-FILE_FORMAT=VARIABLE" "-ROW_SEP=\r\n" "-DATE_FORMAT=yyyy/MM/dd HH:mm:ss" "-CHARSET_ENCODING=ISO8859_1" "-XML_CHARSET_ENCODING=ISO-8859-1"
                      select * from WCDS.E$_CDS_COMPANY

                      I am not sure why the password is not in encoded format. I am looking into it and will inform you soon.

                      Bhabani
                      http://dwteam.in
                      • 8. Re: How to specify path to read E$ tab records into .xls file using odisqlunlod
                        Bhabani Ranjan
                        If you are excuting the odiSqlUnload command using procedure with oditools technology then use
                        <@=snpRef.getInfo("SRC_ENCODED_PASS") @>
                        in the password field. In command on source select the technology as oracle and the logical schema containing the E$ table.

                        Hopefully i have answered to all of your doubts.

                        Thanks
                        Bhabani
                        http://dwteam.in
                        • 9. Re: How to specify path to read E$ tab records into .xls file using odisqlunlod
                          Phanikanth
                          Thanks bhabani

                          but <@=snpRef.getInfo("SRC_ENCODED_PASS") @> this code is for encode the source DB password where as my ODI and Target DB are in same data base and same SID.

                          Hope this may not useful as per the requirement.

                          Regards,
                          Phanikanth
                          • 10. Re: How to specify path to read E$ tab records into .xls file using odisqlunlod
                            Bhabani Ranjan
                            Its not specific to only source that you are using in interface. Its specific to the logical schema data you have selected in the command on source of a procedure.

                            So if you select the target logical schema it will get target dataserver encoded password. If you have both source and target under one user then you can selected any logical schema.
                            Its just a direction to get the encoded password which will be helpful to login to the user and get the details from the table. .

                            As you have to use the E$ table, go for the logical schema that points to staging ( having these temp tables ).