lightdb@postgres=# DECLARE
lightdb@postgres$# CURSOR c_emp IS
lightdb@postgres$# SELECT last_name,salary FROM employees_curs WHERE ROWNUM<6 ORDER BY last_name;
lightdb@postgres$# v_ename employees_curs.last_name%TYPE;
lightdb@postgres$# v_salary employees_curs.salary%TYPE;
lightdb@postgres$# BEGIN
lightdb@postgres$# OPEN c_emp;
lightdb@postgres$# LOOP
lightdb@postgres$# FETCH c_emp INTO v_ename,v_salary;
lightdb@postgres$# IF c_emp%FOUND THEN
lightdb@postgres$# --DBMS_OUTPUT.PUT_LINE('Name='||v_ename||',salary='||v_salary);
lightdb@postgres$# raise notice 'IF,c_emp FOUND=%' , c_emp%FOUND;
lightdb@postgres$# raise notice 'Name=%,salary=%' , v_ename,v_salary;
lightdb@postgres$# ELSE
lightdb@postgres$# raise notice 'ELSE,c_emp FOUND=%' , c_emp%FOUND;
lightdb@postgres$# EXIT;
lightdb@postgres$# END IF;
lightdb@postgres$# END LOOP;
lightdb@postgres$# END;
lightdb@postgres$# /
2022-05-26 20:21:36.085103T ltsql lightdb@postgres [local] client backend DO XX000[2022-05-26 20:21:29 PDT] 0 [4281] ERROR: lightdb rownum not enable, syntax compatible type is not oracle.
2022-05-26 20:21:36.085103T ltsql lightdb@postgres [local] client backend DO XX000[2022-05-26 20:21:29 PDT] 0 [4281] CONTEXT: SQL statement "SELECT last_name,salary FROM employees_curs WHERE ROWNUM<6 ORDER BY last_name"
PL/oraSQL function inline_code_block line 7 at OPEN
2022-05-26 20:21:36.085103T ltsql lightdb@postgres [local] client backend DO XX000[2022-05-26 20:21:29 PDT] 0 [4281] STATEMENT: DECLARE
CURSOR c_emp IS
SELECT last_name,salary FROM employees_curs WHERE ROWNUM<6 ORDER BY last_name;
v_ename employees_curs.last_name%TYPE;
v_salary employees_curs.salary%TYPE;
BEGIN
OPEN c_emp;
LOOP
FETCH c_emp INTO v_ename,v_salary;
IF c_emp%FOUND THEN
--DBMS_OUTPUT.PUT_LINE('Name='||v_ename||',salary='||v_salary);
raise notice 'IF,c_emp FOUND=%' , c_emp%FOUND;
raise notice 'Name=%,salary=%' , v_ename,v_salary;
ELSE
raise notice 'ELSE,c_emp FOUND=%' , c_emp%FOUND;
EXIT;
END IF;
END LOOP;
END;
ERROR: lightdb rownum not enable, syntax compatible type is not oracle.
CONTEXT: SQL statement "SELECT last_name,salary FROM employees_curs WHERE ROWNUM<6 ORDER BY last_name"
PL/oraSQL function inline_code_block line 7 at OPEN
lightdb@postgres=# show lightdb_syntax_compatible_type ;
lightdb_syntax_compatible_type
--------------------------------
off
(1 row)
lightdb@postgres=# alter database postgres set lightdb_syntax_compatible_type to oracle;
ALTER DATABASE
lightdb@postgres=# show lightdb_syntax_compatible_type ;
lightdb_syntax_compatible_type
--------------------------------
off
(1 row)
lightdb@postgres=# exit
[lightdb@localhost ~]$ lt_ctl -D $PGDATA restart
2022-05-26 20:26:01.083172T @ postmaster 00000[2022-05-26 20:22:10 PDT] 0 [4283] LOG: received fast shutdown request
waiting for server to shut down....2022-05-26 20:26:01.083650T @ postmaster 00000[2022-05-26 20:22:10 PDT] 0 [4283] LOG: aborting any active transactions
2022-05-26 20:26:01.084920T @ pg_cron launcher 57P01[2022-05-26 20:22:10 PDT] 0 [4292] FATAL: terminating connection due to administrator command
2022-05-26 20:26:01.085040T @ postmaster 00000[2022-05-26 20:22:10 PDT] 0 [4283] LOG: background worker "logical replication launcher" (PID 4293) exited with exit code 1
2022-05-26 20:26:01.086666T @ postmaster 00000[2022-05-26 20:22:10 PDT] 0 [4283] LOG: background worker "pg_cron launcher" (PID 4292) exited with exit code 1
2022-05-26 20:26:01.086986T @ checkpointer 00000[2022-05-26 20:22:10 PDT] 0 [4287] LOG: shutting down
2022-05-26 20:26:01.088054T @ checkpointer 00000[2022-05-26 20:22:10 PDT] 0 [4287] LOG: checkpoint starting: shutdown immediate
2022-05-26 20:26:01.094404T @ checkpointer 00000[2022-05-26 20:22:10 PDT] 0 [4287] LOG: checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.002 s, sync=0.003 s, total=0.008 s; sync files=2, longest=0.003 s, average=0.002 s; distance=0 kB, estimate=0 kB
2022-05-26 20:26:01.136902T @ postmaster 00000[2022-05-26 20:22:10 PDT] 0 [4283] LOG: database system is shut down
done
server stopped
2022-05-26 20:26:01.211264T @ postmaster 00000[2022-05-26 20:26:01 PDT] 0 [4330] LOG: LightDB autoprewarm: prewarm dbnum=0
2022-05-26 20:26:01.214954T @ postmaster 00000[2022-05-26 20:26:01 PDT] 0 [4330] LOG: pgaudit extension initialized
waiting for server to start....2022-05-26 20:26:01.330819T @ postmaster 00000[2022-05-26 20:26:01 PDT] 0 [4330] LOG: starting LightDB 13.3-22.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2022-05-26 20:26:01.331184T @ postmaster 00000[2022-05-26 20:26:01 PDT] 0 [4330] LOG: listening on IPv4 address "0.0.0.0", port 5432
2022-05-26 20:26:01.331219T @ postmaster 00000[2022-05-26 20:26:01 PDT] 0 [4330] LOG: listening on IPv6 address "::", port 5432
2022-05-26 20:26:01.336323T @ postmaster 00000[2022-05-26 20:26:01 PDT] 0 [4330] LOG: listening on Unix socket "/tmp/.s.LIGHTDB.5432"
2022-05-26 20:26:01.340457T @ startup 00000[2022-05-26 20:26:01 PDT] 0 [4333] LOG: database system was shut down at 2022-05-26 20:26:01 PDT
2022-05-26 20:26:01.344162T @ postmaster 00000[2022-05-26 20:26:01 PDT] 0 [4330] LOG: database system is ready to accept connections
2022-05-26 20:26:01.355012T @ pg_cron launcher 00000[2022-05-26 20:26:01 PDT] 0 [4339] LOG: pg_cron scheduler started
done
server started
[lightdb@localhost ~]$ ltsql
ltsql (13.3-22.2)
Type "help" for help.
lightdb@postgres=# show lightdb_syntax_compatible_type ;
lightdb_syntax_compatible_type
--------------------------------
Oracle
(1 row)
lightdb@postgres=#