0

I use MariaDB 10.5.6-MariaDB with InnoDB 10.5.6 and every time I try to do a select or whatever within a pl/sql block then it says 'Function does not exist'. What am I doing wrong? Is there a library that needs to be sourced before pl/sql can be used? It would not make sense.

This works (on its own, outside of begin/end) and I get the result back: 94

select count(1) from information_schema.all_plugins;

This does not work

BEGIN
    select count(1) from information_schema.all_plugins;
END;
/

Error:

Error starting at line : 1 in command -
BEGIN
    select count(1) from information_schema.all_plugins;
END;
Error report -
FUNCTION count does not exist

Neither this works:

BEGIN
    declare rowCount decimal;
    select count(1) into @rowCount from information_schema.all_plugins;
END;
/

Error:

Error starting at line : 1 in command -
BEGIN
    declare rowCount decimal;
    select count(1) into @rowCount from information_schema.all_plugins;
END;
Error report -
FUNCTION count does not exist

This does not work either

BEGIN
    select 'hello world';
END;
/

Error:

Error starting at line : 1 in command -
BEGIN
    select 'hello world';
END;
Error report -
FUNCTION 'hello does not exist

And from the CLI client

MariaDB [(none)]> delimiter //
MariaDB [(none)]> begin
    -> select 'hello';
    -> end;
    -> //
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'select 'hello';
end' at line 2
MariaDB [(none)]> begin
    -> select "hello";
    -> end;
    -> //
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'select "hello";
end' at line 2
MariaDB [(none)]> begin
    -> select 'hello';
    -> end
    -> //
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'select 'hello';
end' at line 2
MariaDB [(none)]> begin
    -> select "hello";
    -> end
    -> //
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'select "hello";
end' at line 2
ben
  • 122
  • 9
  • 1
    What database are you attempting to actually use? You say plsql, but that **is not** a database, it is a propitiatory language for Oracle databases. Your code blocks would seem to back that up, but your select does NOT run outside a block. Information_schema does not exist in Oracle. Perhaps you want Postgres, but then your code blocks are incorrectly formatted. Secondly, why would you expect a MariaDB function to work in any other database, where did you find it in the documentation? – Belayer Nov 23 '20 at 17:50
  • Like @Belayer I am wondering what PL/SQL has to do with MariaDB. – William Robertson Nov 24 '20 at 21:56
  • The database is `MariaDB 10.5.6-MariaDB`, as indicated in the first line in the question. I have experience in oracle databases and these codes and anonymous blocks are frequently referred as [Procedural Language for SQL](https://en.wikipedia.org/wiki/PL/SQL) aka PL/SQL there. If MariaDB has a different name for the same concept then I am sorry but was not aware of that, but the question is the same: What is wrong with those anonymous blocks and why cannot the row count be assigned to a variable? – ben Nov 25 '20 at 19:00
  • In addition to the row count, the documentation refers that the dbms_output equivalent in MariaDB is like `Select "hello world";` in the anonymous block. Consequently, the logic dictates that `select count(1) ...;` should also write to the dbms_output equivalent of MariaDB, that would help in writing such PL/SQL like things, such as anonymous blocks. But, unfortunately the hello world does not work for me either, as I have mentioned in the question. I am completely lost with this. – ben Nov 25 '20 at 19:06
  • MariaDB does not have any analogue of PL/SQL's anonymous blocks, so any attempt to make such an analogue work is doomed to failure. Oracle and MariaDB are different database systems, and it seems you've underestimated how different they are. – Luke Woodward Nov 29 '20 at 15:27

0 Answers0