Forum Stats

  • 3,817,327 Users
  • 2,259,314 Discussions
  • 7,893,750 Comments

Discussions

Problem inserting data using PL/SQL procedure

750073
750073 Member Posts: 1
edited Jan 29, 2010 4:59AM in SQL & PL/SQL
I have long column and inserting data using PL/SQL procedure. The issue is though column long can store 2gb data, pl/sql has constraint of 32K so i cannot insert data more den 32k. And in reality my data can be more
Tagged:

Answers

  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge
    And in reality my data can be more
    Where does this data come from? What is this data, is it text or image or something else?

    Why are you using LONG its deprecated. You must be using BLOB or CLOB.
  • 730428
    730428 Member Posts: 2,087
    If you can use CLOB columns and DBMS_LOB package to manipulate it.

    Otherwise you can user a string concatenation:
    declare
    var1 varchar2(32000);
    var2 varchar2(32000);
    var3 varchar2(32000);
    begin
    insert into mytab (longcol) values var1||var2||var3);
    end;
    /
    
    Max
    [My Italian Oracle blog|http://oracleitalia.wordpress.com/2010/01/23/la-forza-del-foglio-di-calcolo-in-una-query-la-clausola-model/]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
  • Avinash Tripathi
    Avinash Tripathi Member Posts: 1,614 Bronze Badge
    edited Jan 29, 2010 4:59AM
    Hi,
    LONG data type in obsolete. use CLOB instead. Please post your Oracle Version also.

    from the Oracle Docs:


    LONG Data Type

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

    LONG columns store variable-length character strings containing up to 2 gigabytes -1, or 231-1 bytes. LONG columns have many of the characteristics of VARCHAR2 columns. You can use LONG columns to store long text strings. The length of LONG values may be limited by the memory available on your computer. LONG literals are formed as described for "Text Literals".

    Oracle also recommends that you convert existing LONG columns to LOB columns. LOB columns are subject to far fewer restrictions than LONG columns. Further, LOB functionality is enhanced in every release, whereas LONG functionality has been static for several releases. See the modify_col_properties clause of ALTER TABLE and TO_LOB for more information on converting LONG columns to LOB.

    You can reference LONG columns in SQL statements in these places:

    SELECT lists

    SET clauses of UPDATE statements

    VALUES clauses of INSERT statements

    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

    Triggers can use the LONG data type in the following manner:

    A SQL statement within a trigger can insert data into a LONG column.

    If data from a LONG column can be converted to a constrained data type (such as CHAR and VARCHAR2), then a LONG column can be referenced in a SQL statement within a trigger.

    Variables in triggers cannot be declared using the LONG data type.

    :NEW and :OLD cannot be used with LONG columns.

    You can use Oracle Call Interface functions to retrieve a portion of a LONG value from the database.



    http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/sql_elements001.htm#i54330

    Cheers,
    Avinash

    Edited by: Avinash Tripathi on Jan 29, 2010 3:28 PM
This discussion has been closed.