Support of DBMS_OUTPUT

[a-langer]

I noticed that the DBMS_OUTPUT package does not output anything to the console, for example if you run:

BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello');
END;

Then SQL CONSOLE: MESSAGES will be empty. Is this feature not supported or am I doing something wrong?

[hashhashu]

Hi, it's updated, please see if it satisfy your needs.

[a-langer]

Judging by the commit https://github.com/hashhashu/sqltools-Oracle-driver/commit/33afe65048e0c0daaadd157211babd8d04cdda24, you added the implementation through the pipelined function. I have some concerns about this method:

• What happens if the user does not have enough rights to create types and functions in the database? In this case, the query will terminate with an error?
• What happens if the types\functions with the names no_dorow\no_dofetch were created earlier to perform business logic? In this case, will their logic be overwritten?
• The file driver.ts#L164 has a check to include the string 'DBMS_OUTPUT'. What happens when 'DBMS_OUTPUT' is called from another function\procedure and this line is not in the body of the SQL query itself? In this case, the buffer will not be read?

In this documentation https://node-oracledb.readthedocs.io/en/latest/user_guide/plsql_execution.html#using-dbms-output suggests another way to fetch DBMS_OUTPUT with node-oracledb - bind an output string when calling the DBMS_OUTPUT.GET_LINE() procedure, print the string, and then repeat until there is no more data. An example implementation can be found in the dbmsoutputgetline.js. This way, in my opinion, will be more universal.

[hashhashu]

Thanks for your detailed reply, i will try the way you metioned.

[hashhashu]

Hi, it's updated, see if it has any problems, thanks.

[a-langer]

I have executed the following code:

BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello1');
    DBMS_OUTPUT.PUT_LINE('Hello2');
    DBMS_OUTPUT.PUT_LINE('Hello3');
END;

And got the output to the console with the text of the script: The output was expected to contain three lines of text:

Hello1
Hello2
Hello3

[a-langer]

Result set includes all messages on single line: In this case the result set was expected empty, because select query not executed.

[hashhashu]

yes, I put a summary page for the not select querys, and you can see the more detailed result when you right click and choose open editor with ...

[a-langer]

OK, if you open it in a text editor, then the messages are displayed correctly. But how convenient is it to open a message in a text editor every time? The DBMS_OUTPUT package is used to debug PL\SQL code, and for this you need to see the sequence of messages output to the console. As far as I understand, this view cannot display multi-line text, then it might make sense to direct the output of debug messages to a separate VSCode channel, which will be displayed on the standard "Output" tab? An example of creating a channel https://stackoverflow.com/a/58139566/19707292.

[hashhashu]

Hi, sorry for late reply, it's been busy lately. it's updated, you can see the output in the "output" tab and choose "SQLTOOLS" channel

[a-langer]

Looks great, but unfortunately I can't test it:

Driver Oracle is not installed.

I tried reinstalling "sqltools-Oracle-driver" and "sqltools" but it doesn't help. I think that these are my local problems, I suggest closing this issue. Thanks for the work you've done!

[hashhashu]

"Driver Oracle is not installed." i guess it has something to do with the activation rule, i accidently remove the activation sentance and only when you open a sql file it will be activated, it should be good the next update.