11 Replies Latest reply: Jul 18, 2014 10:24 AM by rp0428 RSS

    external tables access

    913578

      Hi,

       

      I am connecting my production database(remote) using Oracle client on my local machine.

      I have one requirement, that Update one of my db table based on the information they send, they are sending it in a csv file, so i thought to create an external table for that file.

      Currently i have limited privilleges to test this, so wanted to theoratically clear first.

       

      for the above requirement Can i do..

      -create a directory on my local machine,

      -place the file in the directory

      -create an external table so that in the code i can use the external table

       

      This is how i am doing.

       

      CREATE DIRECTORY DIR_UPDATE AS 'C:\';

      CREATE TABLE EXT_TABLE (
      ITEM_ID NUMBER,
      OLD_VALUE VARCHAR2(100)
      NEW_VALUE VARCHAR2(100)
      )
      ORGANIZATION EXTERNAL (
      TYPE ORACLE_LOADER
      DEFAULT DIRECTORY DIR_UPDATE
      ACCESS PARAMETERS
      ( RECORDS DELIMITED BY NEWLINE
      BADFILE 'EXT_TABLE.BAD'
      LOGFILE 'EXT_TABLE.LOG'
      FIELDS TERMINATED BY ','
      )
      LOCATION ('EXT_TABLE.CSV')
      )
      REJECT LIMIT UNLIMITED;


      BEGIN
      FOR I IN (SELECT ITEM_ID,OLD_VALUE, NEW_VALUE FROM EXT_TABLE)
      LOOP
        UPDATE ITEM_DETAILS SET VALUE=I.NEW_VALUE WHERE ITEM_ID=I.ITEM_ID AND VALUE=I.OLD_VALUE;
        COMMIT;
      END LOOP; 
      END;
      /

      DROP TABLE EXT_TABLE;
      DROP DIRECTORY DIR_UPDATE;

       


      My question is if i have .csv file with me, then can i run this on my client machine??

        • 1. Re: external tables access
          Hoek

          Files that you want to query through an external table have to reside in a directory on the database server.

          • 2. Re: external tables access
            913578


            Thanks Hoek for your quick response.

             

            Do we have any alternate solution for this requirement? instead of using external tables.

            • 3. Re: external tables access
              Hoek

              If I understand correctly you want to update a table in a remote production database based on a .csv file you have locally. You must have another database (local) since you seem to have a database link.

              Depending on your schema it should be possible to read the .csv locally and update through the database link.

              • 4. Re: external tables access
                913578

                it is difficult to get one more data base.

                 

                how about passing some string or xml file to the plsql code instead of extenal table?

                 

                i think for passing xml file we need not to have a direcoty on server right?

                 

                please suggest if you have any better options.

                • 5. Re: external tables access
                  ascheffer

                  Use Sql-loader to load the csv-file into a (staging/temporary) table on your remote database.

                  • 6. Re: external tables access
                    913578

                    Even for sql loader method, we have to create a directory on DATABASE server.

                     

                    I am thinking of doing complete Client side execution.

                    • 7. Re: external tables access
                      Hoek

                      What pl/sql code do you mean? Can you give an example?

                      • 8. Re: external tables access
                        913578

                        the BEGIN .....END block which i mentioned in the first/top post.

                         

                        the update query..one

                        • 9. Re: external tables access
                          ascheffer

                          SQL*Loader is a clienttool. You don't need a directory on the remote server. All you need is table on the remote database into which you can load the data.

                          • 10. Re: external tables access
                            Hoek

                            Wait a second. How are you actually connecting to the remote database? Are you using a database link or not? Your update statement is not using a database link, or is that a synonym instead of the actual table name (item_details)?

                            Oh, and don't commit in loops, commit only when your entire transaction had completed successfully.

                            • 11. Re: external tables access
                              rp0428

                              See the Utilities doc for the answer to your original question:

                              http://docs.oracle.com/cd/E11882_01/server.112/e22490/et_concepts.htm

                              Location of Data Files and Output Files

                              The access driver runs inside the database server. This is different from SQL*Loader, which is a client program that sends the data to be loaded over to the server. This difference has the following implications:

                              •   The server must have access to any files to be loaded by the access driver.
                              •   The server must create and write the output files created by the access driver: the log file, bad file, discard file, and also any dump files created by the ORACLE_DATAPUMP access driver.

                              The access driver requires that a directory object be used to specify the location from which to read and write files. A directory object maps a name to a directory name on the file system. For example, the following statement creates a directory object named ext_tab_dir that is mapped to a directory located at /usr/apps/datafiles.


                              That solution requires a directory object and the file to be on the server

                              Even for sql loader method, we have to create a directory on DATABASE server.

                               

                              No - you don't.

                              I am thinking of doing complete Client side execution.

                              Sql*Plus IS a 'complete Client side execution.

                               

                              You could also write a simple Java application and use JDBC to load the data.

                               

                              For server-side you could load that simple Java application into the database and IT can load the file from your client machine.