oracle: PL/SQL Block

[markuman]

It looks like usql dosn't support oracle PL/SQL blocks

DECLARE
  l_message VARCHAR2( 255 ) := 'Hello World!';
BEGIN
  DBMS_OUTPUT.PUT_LINE( l_message );
END;
/

usql oracle://user:password@somedb.eu-central-1.rds.amazonaws.com/BIPDB -f /tmp/hello.sql --single-transaction
Connected with driver oracle (Oracle Database 19.0.0.0.0)
error: oracle: ORA-06550: line 2, column 45:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

   * & = - + ; < / > at in is mod remainder not rem
   <an exponent (**)> <> or != or ~= >= <= <> and or like like2
   like4 likec between || multiset member submultiset
error: oracle: ORA-06550: line 2, column 35:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

   := . ( % ;
error: oracle: ORA-00900: invalid SQL statement

[kenshaw]

@markuman Hi, apologies I didn't see this sooner. usql most definitely supports any query. The issue is that Oracle expects this to be executed as a single block, and usql has no understanding of a block. It can only detect when a query is complete when it finds a semi-colon.

Long/short: you need to escape your semi-colons with the backslash:

$ usql or://
Connected with driver oracle (Oracle Database 21.0.0.0.0)
Type "help" for help.

or:system@localhost/orasid=> DECLARE
or:system@localhost/orasid->   l_message VARCHAR2( 255 ) := 'Hello World!'\;
or:system@localhost/orasid-> BEGIN
or:system@localhost/orasid->   DBMS_OUTPUT.PUT_LINE( l_message )\;
or:system@localhost/orasid-> END;
DECLARE 1
or:system@localhost/orasid=> \p
DECLARE
  l_message VARCHAR2( 255 ) := 'Hello World!';
BEGIN
  DBMS_OUTPUT.PUT_LINE( l_message );
END;
or:system@localhost/orasid=>

usql has supported this for about 2 years or more I believe. I don't recall when I specifically added this, but more than one database that requires declarations like this make use of it.