当前位置: 首页 > 工具软件 > LightDB > 使用案例 >

【lightdb】执行oracle存储过程兼容语法,提示ERROR: lightdb rownum not enable, syntax compatible type is not oracle.

金成济
2023-12-01

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=#

 类似资料: