Ways of handling unknown data type in oracle

Question

I have a statement that executes a sql like this:

execute immediate cursor_rule.rule_sql into rule_result ;

my problem is that the output of rule_sql can be anything from null, to boolean to a number.

How do I define rule_result in a situation like this?

rule_sql can't be boolean because Oracle SQL doesn't support that datatype. — APC
– APC, Commented Jun 29, 2017 at 7:04
No, that doesn't give a Boolean output. That query would simply return no rows. — Boneist
– Boneist, Commented Jun 29, 2017 at 7:32
@a_horse_with_no_name - My comment specifies SQL. And, as the OP's comments say they are executing SQL statements not PL/SQL blocks, my comment stands. — APC
– APC, Commented Jun 29, 2017 at 8:44
@a_horse_with_no_name - but the return value comes from the statement it executes: if the dynamic statement is SQL it cannot use boolean. — APC
– APC, Commented Jun 29, 2017 at 8:54

MT0 · Accepted Answer · 2017-06-29 08:54:58Z

2

You can use:

DECLARE
  rule_result VARCHAR2(4000);
BEGIN
  EXECUTE IMMEDIATE :your_sql INTO rule_result;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    NULL; -- Handle what should happen when the SQL returns zero rows.
  WHEN TOO_MANY_ROWS THEN
    NULL; -- Handle what should happen when the SQL returns two or more rows.
END;
/

If the result of your sql statement is a:

String data type then it gets stored in the rule_result as is.
numeric data type then Oracle will implicitly call TO_CHAR on it to convert it to a VARCHAR2 value exactly long enough to hold its significant digits.
DATE data type then Oracle will implicitly call TO_CHAR( date_value, NLS_DATE_FORMAT ) using the NLS_DATE_FORMAT session parameter as the format model to convert it to a string.
TIMESTAMP data type then Oracle will implicitly call TO_CHAR( timestamp_value, NLS_TIMESTAMP_FORMAT ) using the NLS_TIMESTAMP_FORMAT session parameter as the format model to convert it to a string.

answered Jun 29, 2017 at 8:54

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

nabeel Over a year ago

why not declare a variable using "type" or "rowtype" instead of VARCHAR2(4000) ?? this will always match to the correct datatype

MT0 Over a year ago

@nabeel The OP is using dynamic SQL so they do not know which COLUMN_NAME%TYPE or TABLE_NAME%ROWTYPE to define the variable as in the DECLARE part of the block.

William Robertson · Accepted Answer · 2017-07-09 22:58:56Z

You can parse the SQL statement using DBMS_SQL to discover the column data type. For example:

declare
    l_cursor_id pls_integer := dbms_sql.open_cursor;
    l_pointless_count pls_integer;
    l_desc_cols dbms_sql.desc_tab;
    l_sql long := 'select dummy as teststring, 123 as testnum, sysdate as testdate from dual';
begin
    dbms_sql.parse(l_cursor_id, l_sql, dbms_sql.native);
    dbms_sql.describe_columns(l_cursor_id, l_pointless_count, l_desc_cols);

    for i in 1..l_desc_cols.count loop
        dbms_output.put_line
        ( rpad(l_desc_cols(i).col_name,31) || lpad(l_desc_cols(i).col_type,4) );
    end loop;

    dbms_sql.close_cursor(l_cursor_id);
end;

Output:

TESTSTRING                        1
TESTNUM                           2
TESTDATE                         12

Type codes are defined in DBMS_TYPES and the documentation (which as I discovered last week do not necessarily agree).

Collectives™ on Stack Overflow

Ways of handling unknown data type in oracle

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related