1 2 3 Previous Next 33 Replies Latest reply: Feb 26, 2014 11:28 PM by 936666 Go to original post RSS
      • 15. Re: Java pl sql vs pl sql
        936666

        Note 5

        I'd revisit the whole workflow to see if I could use EXTERNAL TABLE for the data importing instead of BLOB processing.... or some other method.

         

        Note 2

        "try/catch" type coding construct has been available for handling error within in multiple languages (including Java and PERL) since the last millennium.

        Again, lookup RAISE_APPLICATION_ERROR() for throwing your own ORA-.... errors that contain the "position information" in the error string.

        for Note 5

        I will be getting the input as BLOB not saving the blob in the directory.

        Blob will be having junk of characters as input , it is not saved in the external directory where we can reterive when we needed.it is the input parameter from the client, still the same can be done with EXTERNAL TABLES

        How  in this case the EXTERNAL TABLE will work if i get BLOB as input.

         

        for Note 2, Note 3 Note 1

        it is in development stage getting the line infomation where the error is thrown and commiting the records block of records instead of whole records.

         

        for Note 4

        not sure about BULK PROCESSING, will give a try on it.


        • 16. Re: Java pl sql vs pl sql
          936666

          Any suggestion how EXTERNALS TABLES can be used in this case.

          • 17. Re: Java pl sql vs pl sql
            Mike Kutz

            I gave you part of the answer back in Oct. 2013.  with a complete example:

            Re: how to split the blob byte array and insert in oracle

             

            Your problem is really your workflow.

            Why the {explicative} is your data, that you want in a table format, being inserted into the database as a BLOB, which is not a table format???

            You need to fix THAT problem.

            Consider using XML format instead of BINARY.  Then, you just need to use XMLTable() inside of an INSERT .. SELECT statement

            Remember, this procedure is only one step in a multistep workflow.

            (a quote from Bugs Bunny comes to mind.)

             

            Again, please update your code to 21st century methods.

            Drop the OUT parameters and add the following to the end of your WHEN OTHERS section

            RAISE_APPLICATION_ERROR(-20000, '...<place custom error code here that include the line number where the error occurred like this (' || i || ')' );

             

            MK

            • 18. Re: Java pl sql vs pl sql
              936666

              I need to extract the BLOB data and insert the data(Float,number,double) in the table, from the table i need to run different reports.

              Work flow for me is to read the incoming BLOB data and extract according to the predefined (byte information) format and insert them in the table and need to generate different reports based on the user input.

              If u see my code u will be able to understand what i am doing after reading the BLOB data, read the BLOB data and split based on the byte code and insert in the table and from that table reports and other work are generated.

              The example which u gave me was reading the data from directory.There is no such option for me , it is input(BLOB) for me.

               

              Thanks !

              • 19. Re: Java pl sql vs pl sql
                sb92075

                What does BLOB issue have to do with your initial post in this thread?

                • 20. Re: Java pl sql vs pl sql
                  936666

                  Parsing the BLOB and based on the byte size , inserting into table.

                  • 21. Re: Java pl sql vs pl sql
                    936666

                    I removed dbms_sql and added dynamic sql execute immediate i see for my code it is taking more time.

                    When i hide the execute immediate command it is taking less time.

                    why here in my case execute immediate  is taking more time than dbms_sql.

                    Removed all dbms_sql related codes and added execute immediate command after line 83 in my previous post.

                    • 22. Re: Java pl sql vs pl sql
                      Mike Kutz

                      936666 wrote:

                      Work flow for me is to read the incoming BLOB data

                      Do you realize that the fact that you are trying to deal with "incoming BLOB data" is your true problem?

                       

                      If the data is suppose to be in a table, then the {application} should be sending the database INSERT statements using BINDS and batch processing techniques.... not BLOB data.

                       

                      MK

                       

                      PS - UTL_FILE and ALTER TABLE ... LOCATION ()

                      You may need to use AQ for multiuser serialization.

                      • 23. Re: Java pl sql vs pl sql
                        936666

                        Mike Kutz wrote:

                         

                        If the data is suppose to be in a table, then the {application} should be sending the database INSERT statements using BINDS and batch processing techniques.... not BLOB data.

                         

                        MK

                         

                         

                        The data is not in table as i said it is INPUT, it will be from front end (Java or c# or c++ etc..)

                        Application will not do any insert statements it will be having only BLOB informations,pl sql statements will not be there inside the application.

                        • 24. Re: Java pl sql vs pl sql
                          Mike Kutz

                          Application will not do any insert statements

                          THIS IS YOUR BIGGEST PROBLEM!!!

                          The applications already has the data is some sort of Struct/Class with one element per row (ie "table").

                          Why do you not force them to do INSERT?

                           

                          They are already connecting to the database.... Otherwise, you need to explain how a BLOB/File gets into the database.

                          And if it is a File..... just mount that folder on the DB server and use EXTERNAL TABLEs....

                           

                          MK


                          • 25. Re: Java pl sql vs pl sql
                            936666

                            The applications already has the data is some sort of Struct/Class with one element per row (ie "table").

                            Why do you not force them to do INSERT?

                             

                            It is not one element per one row it is buch of junk characters(BLOB).

                            It consists of  combination of multiple column and multiple rows

                            eg 50columns +1000 rows as a single BLOB.

                            need to split the bytes and insert into table based on the each column length

                            Column1 4 byte , Column2 8 byte .... Column50 4 byte

                            total for 50 column + 1 row it is 200 byte

                            so for 1000 rows it is 200*1000 bytes as BLOB.

                            If u see my stored procedure the last parameter is P_count which is nothing but 1000 in this example.

                            I cant use EXTERNAL TABLEs since it is not saved in file or xml format.

                            • 26. Re: Java pl sql vs pl sql
                              rp0428

                              You were already told in BOTH of your prior threads (one of which I provided the link for earlier) for this question that you need to know/define the rules for delimiting the bytes in the BLOB.

                               

                              Since you never responded in those threads to answer the questions we ask why should we continue to try to help?

                               

                              Either answer the questions people ask or seek help elsewhere.

                              • 27. Re: Java pl sql vs pl sql
                                936666

                                For delimiting the bytes in the BLOB as i said it is byte length of the datatypes Float(4 bytes) Double(8 bytes) Number (4bytes).




                                • 28. Re: Java pl sql vs pl sql
                                  936666

                                  any suggestions

                                  • 29. Re: Java pl sql vs pl sql
                                    936666

                                    Mike,

                                    I cant use EXTERNAL TABLEs since it is not saved in file or xml format, since it is not saved anywhere, it is one of the input in the stored procedure.