Exploring New DBMS_SQL Subprograms in EDB Postgres Advanced Server 16

EDB Postgres Advanced Server 16 has brought a suite of new subprograms in the DBMS_SQL package, enhancing the capabilities and flexibility for database developers and DBAs. In this blog post, we’ll explore some of these additions, including describe_columns3, describe_columns2, to_cursor_number, to_refcursor, define_array, bind_array, bind_array_raw, define_column_rowid, and column_value_rowid. We’ll delve into their functionalities, provide working code examples, and discuss the benefits these functions offer.

describe_columns3 and describe_column2

These functions provide detailed information about the columns in a query’s result set.

• describe_columns3: Offers an extended description, including precision and scale of columns.
• describe_columns2: An enhanced version of the original describe_columns, providing additional details compared to its predecessor.

Benefits: Enhanced metadata retrieval for dynamic SQL, aiding in precise result set processing and validation.

to_cursor_number and to_refcursor

• to_cursor_number: Converts a ref cursor to a DBMS_SQL cursor number.
• to_refcursor: Converts a DBMS_SQL cursor number to a ref cursor.

Benefits: Seamless conversion between cursor types enhances compatibility and integration with different EDB-SPL and SQL code parts.

define_array, bind_array and bind_array_raw

• define_array: Defines an array for bulk fetch operations.
• bind_array/bind_array_raw: Binds an array for bulk DML operations.

Benefits: Facilitates bulk operations for improved performance and efficiency in handling large data sets.

define_column_rowid and column_value_rowid

• define_column_rowid: Defines a column of type ROWID in a query’s result set.
• column_value_rowid: Retrieves the ROWID value from a specified column in the result set.

Benefits: Allows easy retrieval and handling of ROWID values, crucial for row identification and manipulation.

DBMS_SQL Subprograms’ Examples

Following are some examples of the above subprogram with output in EDB-SPL

describe_column3

DECLARE
    cursor_id INTEGER;
    column_count INTEGER;
    desc_tab DBMS_SQL.DESC_TAB3;
BEGIN
    -- Open a cursor
    cursor_id := DBMS_SQL.OPEN_CURSOR;

    -- Parse a query
    DBMS_SQL.PARSE(cursor_id, 'SELECT empno, ename, sal FROM emp', DBMS_SQL.NATIVE);

    -- Describe the columns
    DBMS_SQL.DESCRIBE_COLUMNS3(cursor_id, column_count, desc_tab);

    -- Loop through the described columns
    FOR i IN 1 .. column_count LOOP
        DBMS_OUTPUT.PUT_LINE('Column ' || i || ':');
        DBMS_OUTPUT.PUT_LINE('Name: ' || desc_tab(i).col_name);
        DBMS_OUTPUT.PUT_LINE('Data Type: ' || desc_tab(i).col_type);
        DBMS_OUTPUT.PUT_LINE('Max Length: ' || desc_tab(i).col_max_len);
        -- Other column attributes can be accessed similarly
    END LOOP;

    -- Close the cursor
    DBMS_SQL.CLOSE_CURSOR(cursor_id);
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
        -- Close the cursor if open
        IF DBMS_SQL.IS_OPEN(cursor_id) THEN
            DBMS_SQL.CLOSE_CURSOR(cursor_id);
        END IF;
END;

Column 1:
Name: empno
Data Type: 2
Max Length: 22
Column 2:
Name: ename
Data Type: 1
Max Length: 10
Column 3:
Name: sal
Data Type: 2
Max Length: 22

EDB-SPL Procedure successfully completed

to_cursor_number and to_refcursor

DECLARE
  ref_cursor SYS_REFCURSOR;
  cursor_number INTEGER;
  query VARCHAR2(1000);
  a_employee_id emp.empno%TYPE;
  a_name emp.ename%TYPE;
BEGIN
  -- Open a REF CURSOR for a query
  query := 'SELECT empno, ename FROM emp WHERE deptno = 10';
  OPEN ref_cursor FOR query; -- Assuming department_id = 10

  -- Convert REF CURSOR to DBMS_SQL cursor number
  cursor_number := DBMS_SQL.TO_CURSOR_NUMBER(ref_cursor);

  -- Define columns for the cursor
  DBMS_SQL.DEFINE_COLUMN(cursor_number, 1, a_employee_id);
  DBMS_SQL.DEFINE_COLUMN(cursor_number, 2, a_name);

  -- Fetch rows from the cursor
  WHILE DBMS_SQL.FETCH_ROWS(cursor_number) > 0 LOOP
    DBMS_SQL.COLUMN_VALUE(cursor_number, 1, a_employee_id);
    DBMS_SQL.COLUMN_VALUE(cursor_number, 2, a_name);
    DBMS_OUTPUT.PUT_LINE('Employee ID: ' || a_employee_id || ' Employee Name: ' || a_name);
  END LOOP;

  -- Close the cursor
  DBMS_SQL.CLOSE_CURSOR(cursor_number);
EXCEPTION
  WHEN OTHERS THEN
    IF DBMS_SQL.IS_OPEN(cursor_number) THEN
      DBMS_SQL.CLOSE_CURSOR(cursor_number);
    END IF;
    RAISE;
END;

Employee ID: 7782 Employee Name: CLARK
Employee ID: 7839 Employee Name: KING
Employee ID: 7934 Employee Name: MILLER

EDB-SPL Procedure successfully completed

DECLARE
  ref_cursor SYS_REFCURSOR;
  cursor_number INTEGER;
BEGIN
  OPEN ref_cursor FOR SELECT * FROM emp;
  cursor_number := DBMS_SQL.TO_CURSOR_NUMBER(ref_cursor);
  -- Convert back if needed
  ref_cursor := DBMS_SQL.TO_REFCURSOR(cursor_number);
END;

EDB-SPL Procedure successfully completed

define_column_rowid, column_value_rowid and describe_column2

SET default_with_rowids TO ON;

CREATE TABLE EMPLOYEE AS SELECT LEVEL AS ID, 'Employee_'||LEVEL AS NAME, LEVEL*1000 AS SALARY FROM DUAL CONNECT BY LEVEL <= 5;

DECLARE
  cur NUMBER;
  desc_t DBMS_SQL.DESC_TAB2;
  col_cnt NUMBER;
  status NUMBER;
  row_id ROWID;
  name VARCHAR2(100);
  salary NUMBER;
  name_arr DBMS_SQL.VARCHAR2_TABLE;
BEGIN
  -- Open a cursor
  cur := DBMS_SQL.OPEN_CURSOR;

  -- Parse the SQL statement
  DBMS_SQL.PARSE(cur, 'SELECT ROWID, NAME, SALARY FROM EMPLOYEE', DBMS_SQL.NATIVE);

  -- Describe the columns
  DBMS_SQL.DESCRIBE_COLUMNS2(cur, col_cnt, desc_t);

  -- Define the output columns
  DBMS_SQL.DEFINE_COLUMN_ROWID(cur, 1, row_id);
  DBMS_SQL.DEFINE_COLUMN(cur, 2, name, 100);
  DBMS_SQL.DEFINE_COLUMN(cur, 3, salary);

  -- Execute the SQL statement
  status := DBMS_SQL.EXECUTE(cur);

  -- Fetch the rows and print the row IDs, names, and salaries
  LOOP
    IF DBMS_SQL.FETCH_ROWS(cur) = 0 THEN
      EXIT;
    END IF;

    DBMS_SQL.COLUMN_VALUE_ROWID(cur, 1, row_id);
    DBMS_SQL.COLUMN_VALUE(cur, 2, name);
    DBMS_SQL.COLUMN_VALUE(cur, 3, salary);
    DBMS_OUTPUT.PUT_LINE('Row ID: ' || row_id || ', Name: ' || name || ', Salary: ' || salary);
  END LOOP;

  -- Close the cursor
  DBMS_SQL.CLOSE_CURSOR(cur);
END;
Row ID: 1, Name: Employee_1, Salary: 1000
Row ID: 2, Name: Employee_2, Salary: 2000
Row ID: 3, Name: Employee_3, Salary: 3000
Row ID: 4, Name: Employee_4, Salary: 4000
Row ID: 5, Name: Employee_5, Salary: 5000

EDB-SPL Procedure successfully completed

define_array, bind_array and bind_array_raw

CREATE TABLE PERSON AS SELECT LEVEL AS ID, 'Person_'||LEVEL AS NAME FROM DUAL CONNECT BY LEVEL <= 5;

DECLARE
  p_ids dbms_sql.number_table;
  c NUMBER;
  dummy NUMBER;
  p_name VARCHAR2(100);
BEGIN
  -- Define the array of IDs
  p_ids(1) := 2;
  p_ids(2) := 3;
  p_ids(3) := 4;

  -- Open a cursor
  c := DBMS_SQL.OPEN_CURSOR;

  -- Parse the SQL statement
  DBMS_SQL.PARSE(c, 'SELECT name FROM PERSON WHERE id IN (:num_array)', DBMS_SQL.NATIVE);

  -- Define the output column
  dbms_sql.define_column(c, 1, p_name, 100);

  -- Bind the array to the SQL statement
  DBMS_SQL.BIND_ARRAY(c, ':num_array', p_ids);

  -- Execute the SQL statement
  dummy := DBMS_SQL.EXECUTE(c);

  -- Fetch the rows and print the names
  LOOP
    EXIT WHEN dbms_sql.fetch_rows(c) <= 0;
    dbms_sql.column_value(c, 1, p_name);
    dbms_output.put_line(p_name);
  END LOOP;

  -- Close the cursor
  DBMS_SQL.CLOSE_CURSOR(c);
END;
Person_4

EDB-SPL Procedure successfully completed

Conclusion

The introduction of these new subprograms in EDB Postgres Advanced Server 16’s DBMS_SQL package is a testament to the evolving nature of database technology. They offer greater flexibility, efficiency, and precision in handling dynamic SQL operations, especially where complex data types and bulk processing are involved. By integrating these functionalities, developers and DBAs can significantly enhance their database operations, paving the way for more advanced and optimized data handling solutions.