This discussion is archived
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 Pro
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 ).

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points