6 Replies Latest reply on Dec 3, 2018 4:30 PM by rp0428

    from mediumText to LONG RAW

    AnnieM

      Hi,

       

      I am migrating a MySQL 5.7 .sql dump file to Oracle 10g.  I am using SQL Developer 2.1

      The file contains JSON data (array) within a field with MySQL data type, mediumText

       

      I want to convert that to LONG RAW so it is usable by Powerbuilder 7.0

       

      But SQL Developer will not convert it to LONG RAW, only CLOB.

       

      Is there a way to convert it to LONG RAW, perhaps through first modifying the data type within MySQL?

       

      These are valid MySQL data types, https://dev.mysql.com/doc/refman/5.7/en/data-types.html

       

      CHAR and VARCHAR

      BINARY and VARBINARY

      BLOB and TEXT

      ENUM

      SET

      JSON

      LONGTEXT

      MEDIUMTEXT

      TEXT

       

      Thank you for reading and for any help you can offer!

      Annie

        • 1. Re: from mediumText to LONG RAW

          But SQL Developer will not convert it to LONG RAW, only CLOB.

          Correct - you should be using LOB columns for LONG or LONG RAW data.

           

          Even the doc for your ancient 10.2 version tells you not to use LONGs.

          https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements001.htm

          Oracle strongly recommends that you convert LONGRAW columns to binary LOB (BLOB) columns. LOB columns are subject to far fewer restrictions than LONG columns. See TO_LOB for more information.

          So don't use it.

          I want to convert that to LONG RAW so it is usable by Powerbuilder 7.0

          PowerBuilder supports LOB coumns so there is still no reason to try to use LONG RAW.

          • 2. Re: from mediumText to LONG RAW
            AnnieM

            Thank you!

            And good to see someone who knows Powerbuilder

             

            Powerbuilder 7.0 is not allowing a query of a table with type CLOB.

             

            The error message I receive to select * from kuali.actions  (last field there is a CLOB) OR try to create a datawindow referencing the CLOB is:

            So that is why were hoping to get the JSON into a LONG field, because PB 7.0 does support that.


            Thank you, again,
            Annie

            • 3. Re: from mediumText to LONG RAW
              Glen Conway

              Trying with a slightly more modern version of SQL Developer might help.  For example, this (non-Oracle) discussion

              https://dba.stackexchange.com/questions/51739/which-sql-developer-version-will-be-compatible-with-oracle-9i/53446

              contains an answer for someone wanting to work with an even older 9i DB:

              In practice I managed to work with Oracle 9i using following configuration:

              SQL Developer 4.0.2.15 Java 1.7 Oracle JDBC driver ojdbc6.jar 11.2.0.3.0.

              On the latest SQL Developer (17.4.0.355) I managed to connect to 9i and execute sql queries but I got errors when tried to view stored procedures.

              So maybe try that and see if you can convert the mediumText to BLOB instead of CLOB.

              • 4. Re: from mediumText to LONG RAW
                Gaz in Oz

                A more convoluted solution would be to use Heterogeneous Services (HS) otherwise known as Database Gateways (DG). In 11.2.0.2.0 the program is called dg4odbc. You set up an ODBC connection to mysql, set up the Oracle listener, tnsnames.ora, dg4odbc.ini and a database link using the "HS" tnsnames.ora entry to connect to mysql via dblink. This method, certainly in 11.2.0.2.0 anyway, presents the mysql mediumtext column as an Oracle LONG column and if you really need the column as LONG RAW, convert the column at the mysql side, via a mysql view on the actual table perhaps.

                For example:

                From the mysql command-line utility

                mysql> describe x;
                +-------+------------+------+-----+---------+-------+
                | Field | Type       | Null | Key | Default | Extra |
                +-------+------------+------+-----+---------+-------+
                | a     | float(9,3) | YES  |     | NULL    |       |
                | b     | char(9)    | YES  |     | NULL    |       |
                | c     | mediumtext | YES  |     | NULL    |       |
                | d     | blob       | YES  |     | NULL    |       |
                +-------+------------+------+-----+---------+-------+
                4 rows in set (0.00 sec)
                
                mysql>
                

                 

                ...and from Oracle sqlplus using the dblink named... mysql:

                SQL> describe x@mysql;
                 Name                                Null?    Type
                 ----------------------------------- -------- ------------------------
                 a                                            FLOAT(24)
                 b                                            CHAR(9)
                 c                                            LONG
                 d                                            LONG RAW
                
                SQL>
                

                Not sure why you would want LONG RAW for a "text" field. As you can see, Oracle presents the mysql blob datatype to LONG RAW, and as others have already stated, LONG/LONG RAW should be converted.

                Anyway using the dblink in Powerbuilder should present the mediumtext mysql column as a LONG and blob mysql column as LONG RAW, if you can setup and use a link.

                 

                (the 10.2.0.1.0 program installed along with the DB is called hsodbc(.exe if on windows) which would exhibit the same behavior IMO).

                • 5. Re: from mediumText to LONG RAW
                  AnnieM

                  Thank you, Gaz!

                   

                  I appreciate the suggestion!

                  In PowerBuilder 7, I cannot directly access a CLOB value either through a SQL statement within the Database Painter nor through creating a datawindow.

                  I receive an error message that I need to use an embedded SQL statement.

                   

                  Hence, the thought we need to store it - until we upgrade - in a LONG or LONG raw field.

                   

                  Again, thank you so much!

                  Annie

                  • 6. Re: from mediumText to LONG RAW

                    Powerbuilder 7.0 is not allowing a query of a table with type CLOB.

                     

                    The error message I receive to select * from kuali.actions (last field there is a CLOB) OR try to create a datawindow referencing the CLOB is:

                    As the PowerBuilder docs show you need to use SELECTBLOB when dealing with LOBs.