6 Replies Latest reply: Dec 21, 2010 10:29 PM by 776422 RSS

    using code of odisqlunload in a procedure...

    776422
      hi...

      I m using code for ODISQLUNLOAD in a procedure....
      code is :

      Command on target:

      OdiSqlUnload "-FILE=" "-DRIVER=<%=odiRef.getInfo("SRC_JAVA_DRIVER")%>" "-URL=<%=odiRef.getInfo("SRC_JAVA_URL")%>" "-USER=<%=odiRef.getInfo("SRC_USER_NAME")%>" "-PASS=<%=odiRef.getInfo("SRC_ENCODED_PASS")%>" "-FILE_FORMAT=FIXED" "-ROW_SEP=\r\n" "-DATE_FORMAT=yyyy/MM/dd HH:mm:ss" "-CHARSET_ENCODING=ISO8859_1" "-XML_CHARSET_ENCODING=ISO-8859-1"
      "-FETCH_SIZE=5000"
      "select 1 from dual "

      but when i execute it ..it gives me error saying : invalid SQL statement

      I tried
      select 1 from dual...etc but error remains the same....

      my target technology is file......

      where i m going wrong??
        • 1. Re: using code of odisqlunload in a procedure...
          Guru Sankar
          Hi,

          You cant select FILE as target technology when u are using ODI Tools. You need to select ODI Tools ( in 11g) or Sunopsis API (in 10g) as such.

          So i assume ur requirement is to export data from RDBMS table "dynamically" using Odi tools.

          If my understanding is right. Do the below steps to achieve the same.

          1. In the procedure, create an OPTION, lets say, UNLOAD_DIR, Type- Value, Description- Unload Directory, Default Value- D:\SampleData (here u need to mention ur directory, in case if you changed ur directory in future u can only need to change the code here).

          2. Create a new step in Procedure and add the below code in Command on Target, selecting ODI TOOLS or SUOPSIS API as technology,

          OdiSqlUnload "-FILE=<%=snpRef.getOption("UNLOAD_DIR")%>\<%=snpRef.getSession("SESS_NO")%>.txt" "-DRIVER=<%=odiRef.getInfo("SRC_JAVA_DRIVER")%>" "-URL=<%=odiRef.getInfo("SRC_JAVA_URL")%>" "-USER=<%=odiRef.getInfo("SRC_USER_NAME")%>" "-PASS=<%=odiRef.getInfo("SRC_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 sysdate from dual

          Command on source:

          Technology- Oracle
          Logical Schema- Whichever points to Oracle
          Context- Global/Whichever points to the above logical schema.

          Output:

          In D:\SampleData, u may find a file name with the session number and value as sysdate.

          Makes sense?

          Thanks,
          Guru
          • 2. Re: using code of odisqlunload in a procedure...
            Bouch
            have you set up your source schema in your procedure?
            • 3. Re: using code of odisqlunload in a procedure...
              776422
              Hi Guru,

              Thanks for your kind reply...

              My case is like this::

              We have two tables 1) Investment(Account regrading info) and 2) Investor(Customer regarding Info)...

              In both, Account number column is common

              Each Account Number may have multiple customers(like joint Account)

              So While Performing Table to File Scenario,

              We want to write in file like
              first record in file contain account regarding Info and
              Then all customer related Info Correspond to that Account Number
              again next account regarding Info Then its customers info like this

              we have total 3 millions records

              so please suggest me some efficient logic for same.






              Thank you again...
              • 4. Re: using code of odisqlunload in a procedure...
                776422
                hi....

                yes ,source schema and technology is already set...

                thanks
                • 5. Re: using code of odisqlunload in a procedure...
                  774570
                  Ritika,

                  I believe for your requirement you can write using Jython and Java and write to File , using the associated SQL statement.
                  • 6. Re: using code of odisqlunload in a procedure...
                    776422
                    hi,

                    Thanks for the help....

                    I have to perform Lpad ,Rpad,To_char etc at column level in both tables.............
                    I was doing it in there Table TO File interfaces.......
                    Now according to ur suggestion where will i do these transformations in Java code??
                    column size is around 50 in both tables.......


                    earlier i had done looping in these interfaces using variables....but it was vry slow.........

                    so kindly guide me how to perform this using java....

                    Thanks