9 Replies Latest reply: May 15, 2014 6:23 AM by 1452149 RSS

    sql copy LONG variable limits

    1452149

      Hi, In usage of sql copy it tells

       

      "The SQL*Plus SET LONG variable limits the length of LONG columns that you copy. If any LONG columns contain data longer than the value of LONG, COPY truncates the data."


      Does this mean if our long variable length 100 then it cuts 20 of 100 and inserts 80?

        • 1. Re: sql copy LONG variable limits
          BluShadow

          Why are you using LONG variables?  They're deprecated.  You should be using CLOBs.

           

          What exactly are you trying to do?

           

          Re: 2. How do I ask a question on the forums?

          • 2. Re: sql copy LONG variable limits
            1452149

            Thanks for the reply,

             

            The table was created using long variables. We are trying to copy the table with long columns from one db to another db. We have some trouble using datapump.

            But when we examine the copied table in target db we see that long variables length is 40 for all rows. Is there way to overcome this limitation?

             

            Thank you, sorry for the start of the discussion.

            • 3. Re: sql copy LONG variable limits
              BluShadow

              What has datapump got to do with the SQL*Plus client tool's "SET LONG" command?

               

              Post an example of the table and data and the code you are using to try any copy the data.

              • 4. Re: sql copy LONG variable limits
                Hoek

                "Simply put ... if you have a LONG, and you wish to do more than just display it, you must use TO_LOB to convert it during either an  INSERT INTO <table> or a  CREATE TABLE <table_name> AS. Everything else is just wasting time.
                  Now if only we could get Oracle to stop using it.
                "

                See:

                Morgan's Library Oracle Database How Can I Working with the LONG Data Type SQL PL/SQL Demos Tutorials Education Sample C…

                • 5. Re: sql copy LONG variable limits
                  1452149

                  Hi,

                  we had some problems using datapump import and opened an sr about this.

                   

                  For alternative solution we try to use sqplus copy command. But we see there is a limitation about length of the long columns.It is 80. We wonder if there is a way to set unlimited for long columns length while using sql copy.

                   

                  Thank you.

                  • 6. Re: sql copy LONG variable limits
                    1452149

                    Thanks for the reply.

                     

                    I did not prefer to use long columns. But the table was created with this data type. It is asked from us to copy this table from one db to another in daily job.

                    • 7. Re: sql copy LONG variable limits
                      padders

                      > We wonder if there is a way to set unlimited for long columns length while using sql copy

                       

                      Not 'unlimited' but you can SET LONG 2000000000 which is the maximum supported length.

                      • 8. Re: sql copy LONG variable limits
                        Hoek

                        Just recreate the table and just never ever use LONG datatype for columns again. 

                        As Blu already said: LONG is DEPRECATED.

                        Even Oracle says:

                        "Do not create tables with LONG columns. Use LOB columns (CLOB, NCLOB, BLOB) instead. LONG columns are supported only for backward compatibility."

                        http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements001.htm#SQLRF00201

                        Using LONG has a lot of drawbacks:

                         

                        "The use of LONG values is subject to these restrictions:

                        • A table can contain only one LONG column.
                        • You cannot create an object type with a LONG attribute.
                        • LONG columns cannot appear in WHERE clauses or in integrity constraints (except that they can appear in NULL and NOT NULL constraints).
                        • LONG columns cannot be indexed.
                        • LONG data cannot be specified in regular expressions.
                        • A stored function cannot return a LONG value.
                        • You can declare a variable or argument of a PL/SQL program unit using the LONG data type. However, you cannot then call the program unit from SQL.
                        • Within a single SQL statement, all LONG columns, updated tables, and locked tables must be located on the same database.
                        • LONG and LONG RAW columns cannot be used in distributed SQL statements and cannot be replicated.
                        • If a table has both LONG and LOB columns, then you cannot bind more than 4000 bytes of data to both the LONG and LOB columns in the same SQL statement. However, you can bind more than 4000 bytes of data to either the LONG or the LOB column.

                        In addition, LONG columns cannot appear in these parts of SQL statements:

                        • GROUP BY clauses, ORDER BY clauses, or CONNECT BY clauses or with the DISTINCT operator in SELECT statements
                        • The UNIQUE operator of a SELECT statement
                        • The column list of a CREATE CLUSTER statement
                        • The CLUSTER clause of a CREATE MATERIALIZED VIEW statement
                        • SQL built-in functions, expressions, or conditions
                        • SELECT lists of queries containing GROUP BY clauses
                        • SELECT lists of subqueries or queries combined by the UNION, INTERSECT, or MINUS set operators
                        • SELECT lists of CREATE TABLE ... AS SELECT statements
                        • ALTER TABLE ... MOVE statements
                        • SELECT lists in subqueries in INSERT statements "

                         

                        Just recreate the table, use TO_LOB as shown in the link in my previous reply.

                        Recreating a table can be done in a simple, single SQL statement.

                        • 9. Re: sql copy LONG variable limits
                          1452149

                          Thanks for the replies.