CODEWITHSUNDEEP
sqloracle-databaseplsql
thealchemist
thealchemist

Reputation: 407

Ways of handling unknown data type in oracle

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?

Upvotes: 0

Views: 2300

Answers (2)

William Robertson
William Robertson

Reputation: 16001

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).

Upvotes: 0

MT0
MT0

Reputation: 168361

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.

Upvotes: 2

Related Questions