{"id":1913,"date":"2025-02-15T10:27:40","date_gmt":"2025-02-15T15:27:40","guid":{"rendered":"https:\/\/molecularsciences.org\/content\/?p=1913"},"modified":"2025-02-13T10:29:21","modified_gmt":"2025-02-13T15:29:21","slug":"pl-sql-data-types","status":"publish","type":"post","link":"https:\/\/molecularsciences.org\/content\/pl-sql-data-types\/","title":{"rendered":"PL\/SQL Data Types"},"content":{"rendered":"\n

Data types define the kind of data that can be stored and manipulated in a PL\/SQL program. Oracle PL\/SQL provides a wide range of data types, categorized into scalar<\/strong>, composite<\/strong>, reference<\/strong>, and LOB (Large Object)<\/strong> data types. Understanding these data types is crucial for writing efficient and optimized PL\/SQL code.<\/p>\n\n\n\n

\n\n\n\n

1. Scalar Data Types<\/strong><\/h2>\n\n\n\n

Scalar data types hold a single<\/strong> value and do not have internal components. These are the most basic data types in PL\/SQL.<\/p>\n\n\n\n

Common Scalar Data Types<\/strong><\/h3>\n\n\n\n
Data Type<\/th>Description<\/th>Example<\/th><\/tr><\/thead>
VARCHAR2(size)<\/code><\/td>Variable-length character string (1 to 32,767 bytes)<\/td>VARCHAR2(50)<\/code><\/td><\/tr>
CHAR(size)<\/code><\/td>Fixed-length character string (1 to 32,767 bytes)<\/td>CHAR(10)<\/code><\/td><\/tr>
NUMBER(p,s)<\/code><\/td>Numeric value (p = precision, s = scale)<\/td>NUMBER(10,2)<\/code><\/td><\/tr>
BOOLEAN<\/code><\/td>Stores TRUE<\/code>, FALSE<\/code>, or NULL<\/code> (Only in PL\/SQL, not in SQL)<\/td>BOOLEAN<\/code><\/td><\/tr>
DATE<\/code><\/td>Stores date and time (YYYY-MM-DD HH:MI:SS)<\/td>DATE<\/code><\/td><\/tr>
TIMESTAMP<\/code><\/td>Stores date and time with fractional seconds<\/td>TIMESTAMP<\/code><\/td><\/tr>
BINARY_INTEGER<\/code><\/td>Integer value optimized for performance<\/td>BINARY_INTEGER<\/code><\/td><\/tr>
PLS_INTEGER<\/code><\/td>Similar to BINARY_INTEGER<\/code> but faster<\/td>PLS_INTEGER<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Example of Scalar Data Type Usage<\/strong><\/h3>\n\n\n\n
DECLARE
    v_name VARCHAR2(50);
    v_age NUMBER(3);
    v_is_active BOOLEAN;
    v_join_date DATE;
BEGIN
    v_name := 'John Doe';
    v_age := 30;
    v_is_active := TRUE;
    v_join_date := SYSDATE;
    
    DBMS_OUTPUT.PUT_LINE('Name: ' || v_name);
    DBMS_OUTPUT.PUT_LINE('Age: ' || v_age);
    DBMS_OUTPUT.PUT_LINE('Joining Date: ' || v_join_date);
END;
\/
<\/code><\/pre>\n\n\n\n
\n\n\n\n
2. Composite Data Types<\/strong><\/h2>\n\n\n\n
Composite data types can hold multiple<\/strong> values, making them useful for storing complex structures. These include RECORDS<\/strong>, PL\/SQL TABLES<\/strong>, VARRAYs<\/strong>, and NESTED TABLES<\/strong>.<\/p>\n\n\n\n
2.1 RECORD Data Type<\/strong><\/h3>\n\n\n\n
A RECORD<\/code> is similar to a struct<\/strong> in C or object<\/strong> in other languages, containing multiple fields of different types.<\/p>\n\n\n\n
Example of RECORD Usage<\/strong><\/h4>\n\n\n\n
DECLARE
    TYPE emp_record IS RECORD (
        emp_id NUMBER(5),
        emp_name VARCHAR2(50),
        salary NUMBER(10,2)
    );
    v_emp emp_record;
BEGIN
    v_emp.emp_id := 101;
    v_emp.emp_name := 'Alice';
    v_emp.salary := 5000;

    DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_emp.emp_id);
    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_emp.emp_name);
    DBMS_OUTPUT.PUT_LINE('Salary: ' || v_emp.salary);
END;
\/
<\/code><\/pre>\n\n\n\n
2.2 PL\/SQL TABLE (Associative Array)<\/strong><\/h3>\n\n\n\n
    \n
  • A one-dimensional, sparse collection indexed by either BINARY_INTEGER<\/strong> or VARCHAR2<\/strong>.<\/li>\n\n\n\n
  • Useful for in-memory data storage.<\/li>\n<\/ul>\n\n\n\n
    Example of PL\/SQL Table<\/strong><\/h4>\n\n\n\n
    DECLARE
        TYPE emp_table IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
        v_employees emp_table;
    BEGIN
        v_employees(1) := 'John';
        v_employees(2) := 'Alice';
        
        DBMS_OUTPUT.PUT_LINE('Employee 1: ' || v_employees(1));
        DBMS_OUTPUT.PUT_LINE('Employee 2: ' || v_employees(2));
    END;
    \/
    <\/code><\/pre>\n\n\n\n
    2.3 VARRAY (Variable-Size Array)<\/strong><\/h3>\n\n\n\n
      \n
    • A fixed-size<\/strong> collection of elements of the same type.<\/li>\n\n\n\n
    • Requires a size limit at declaration.<\/li>\n<\/ul>\n\n\n\n
      Example of VARRAY Usage<\/strong><\/h4>\n\n\n\n
      DECLARE
          TYPE arr_names IS VARRAY(3) OF VARCHAR2(50);
          v_names arr_names := arr_names('John', 'Alice', 'Bob');
      BEGIN
          FOR i IN 1..v_names.COUNT LOOP
              DBMS_OUTPUT.PUT_LINE('Name: ' || v_names(i));
          END LOOP;
      END;
      \/
      <\/code><\/pre>\n\n\n\n
      2.4 NESTED TABLE<\/strong><\/h3>\n\n\n\n
        \n
      • Similar to a VARRAY but does not have a predefined limit<\/strong> and supports DML operations<\/strong>.<\/li>\n<\/ul>\n\n\n\n
        Example of Nested Table Usage<\/strong><\/h4>\n\n\n\n
        DECLARE
            TYPE emp_nested IS TABLE OF VARCHAR2(50);
            v_emps emp_nested := emp_nested('John', 'Alice', 'Bob');
        BEGIN
            v_emps.EXTEND;
            v_emps(4) := 'Charlie';

            FOR i IN 1..v_emps.COUNT LOOP
                DBMS_OUTPUT.PUT_LINE('Employee: ' || v_emps(i));
            END LOOP;
        END;
        \/
        <\/code><\/pre>\n\n\n\n
        \n\n\n\n
        3. Reference Data Types (REF CURSOR)<\/strong><\/h2>\n\n\n\n
        A REF CURSOR<\/code> is a pointer to a result set<\/strong>, allowing dynamic SQL queries. It is useful for returning query results to applications.<\/p>\n\n\n\n
        Example of REF CURSOR<\/strong><\/h3>\n\n\n\n
        DECLARE
            TYPE emp_cursor IS REF CURSOR;
            v_cursor emp_cursor;
            v_name employees.first_name%TYPE;
        BEGIN
            OPEN v_cursor FOR SELECT first_name FROM employees WHERE department_id = 10;
            FETCH v_cursor INTO v_name;

            WHILE v_cursor%FOUND LOOP
                DBMS_OUTPUT.PUT_LINE('Employee: ' || v_name);
                FETCH v_cursor INTO v_name;
            END LOOP;
            
            CLOSE v_cursor;
        END;
        \/
        <\/code><\/pre>\n\n\n\n
        \n\n\n\n
        4. LOB (Large Object) Data Types<\/strong><\/h2>\n\n\n\n
        LOB types store large amounts of data<\/strong>, such as images, documents, and multimedia files.<\/p>\n\n\n\n
        LOB Data Types<\/strong><\/h3>\n\n\n\n
        Data Type<\/th>Description<\/th><\/tr><\/thead>
        BLOB<\/code><\/td>Stores binary large objects (e.g., images, audio, video)<\/td><\/tr>
        CLOB<\/code><\/td>Stores large character data (e.g., documents, XML)<\/td><\/tr>
        NCLOB<\/code><\/td>Stores Unicode character data<\/td><\/tr>
        BFILE<\/code><\/td>Stores a file reference to external OS files<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
        Example of Using CLOB<\/strong><\/h3>\n\n\n\n
        DECLARE
            v_large_text CLOB;
        BEGIN
            v_large_text := 'This is a long text stored in a CLOB column.';
            DBMS_OUTPUT.PUT_LINE(v_large_text);
        END;
        \/
        <\/code><\/pre>\n\n\n\n
        \n\n\n\n
        Conclusion<\/strong><\/h2>\n\n\n\n
        PL\/SQL provides a rich set of data types<\/strong> to handle different data storage and processing requirements:<\/p>\n\n\n\n
          \n
        • Scalar types<\/strong> store single values (e.g., VARCHAR2<\/code>, NUMBER<\/code>, BOOLEAN<\/code>).<\/li>\n\n\n\n
        • Composite types<\/strong> store multiple values in structured formats (RECORD<\/code>, VARRAY<\/code>, NESTED TABLE<\/code>).<\/li>\n\n\n\n
        • Reference types<\/strong> (REF CURSOR<\/code>) allow dynamic queries.<\/li>\n\n\n\n
        • LOB types<\/strong> handle large binary and character data (BLOB<\/code>, CLOB<\/code>).<\/li>\n<\/ul>\n\n\n\n
          Choosing the right data type is crucial<\/strong> for performance optimization and efficient database operations.<\/p>\n","protected":false},"excerpt":{"rendered":"
          Data types define the kind of data that can be stored and manipulated in a PL\/SQL program. Oracle PL\/SQL provides a wide range of data types, categorized into scalar, composite, reference, and LOB (Large Object) data types. Understanding these data types is crucial for writing efficient and optimized PL\/SQL code. 1. Scalar Data Types Scalar […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[515],"tags":[529],"class_list":["post-1913","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-pl-sql"],"_links":{"self":[{"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/posts\/1913","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/comments?post=1913"}],"version-history":[{"count":1,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/posts\/1913\/revisions"}],"predecessor-version":[{"id":1914,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/posts\/1913\/revisions\/1914"}],"wp:attachment":[{"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/media?parent=1913"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/categories?post=1913"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/tags?post=1913"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}