How to print or return a value from anonymous block in PL/SQL without using dbms_output?

Question

I'm trying to use PL/SQL block in zabbix (ODBC connection), but I can't return the value. I need to use PL/SQL block because I need to use the command pragma exception_init

For example, to print the value of the following code:

declare begin dbms_output.put_line('x'); end;

I need to use set serverouput on in SQLPLUS.

But ODBC and Zabbix does not understand the command set serverouput on. How to return the value using ODBC?

Or assign a value to a bind variable and access the value of that from your code? — Alex Poole
– Alex Poole, Commented Nov 20, 2023 at 23:08
Can you elaborate on exactly what you are trying to do? It is conceivable that you just want begin :variable := 'x'; end; but I have no idea what Zabbix is or whether it would accept that sort of construct. It is conceivable that you want to run a bunch of dbms_output.get_line calls after the script runs to fetch data from the dbms_output buffer. Neither of those would be particularly idiomatic approaches, though, so my guess is you're approaching your problem from the wrong angle — Justin Cave
– Justin Cave, Commented Nov 21, 2023 at 17:36

Jon Heller · Accepted Answer · 2023-11-27 05:18:36Z

2

You may be able to run your PL/SQL block using a PL/SQL function in a common table expression. The results are returned as regular SQL data instead of trying to send it to DBMS_OUTPUT.

SQL> with function get_text return varchar2 is
  2  begin
  3     return 'x';
  4  end;
  5  select get_text
  6  from dual
  7  /

GET_TEXT
--------
x

There are a few potential problems with this approach. The syntax only works on Oracle 12c and above. The statement terminator can cause some problems depending on your environment - you will likely need to remove the slash from the end. And some environments won't even allow you to run this because they incorrectly think that all SELECT statements must begin with the keyword SELECT.

answered Nov 27, 2023 at 5:18

Jon Heller

36.9k6 gold badges84 silver badges150 bronze badges

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

Collectives™ on Stack Overflow

How to print or return a value from anonymous block in PL/SQL without using dbms_output?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related