[tibero7@rocky9.4:T724:/home/tibero]tbboot -version

Tibero 7 (DB 7.2.4) Build 303667
Linux bistro-build-centos_8.4 5.15.0-134-generic #145~20.04.1-Ubuntu SMP Mon Feb 17 13:27:16 UTC 2025 x86_64 x86_64 x86_64 GNU/Linux version (little-endian)

Patch files (FS02PS_700093a FS02PS_700103a FS02PS_344166f FS02PS_700110a FS02PS_342949f)
Compiled with "cc -no-pie -ggdb -fstack-protector-all -fno-strict-aliasing -fno-omit-frame-pointer -pipe -O3 -D_OPT_COMPILED -U_FORTIFY_SOURCE -D_USE_VIP -Wall -W -Werror-implicit-function-declaration -Wno-unused-parameter -Wpointer-arith -Wdeclaration-after-statement -DHAVE_CONFIG_H -D_WORDSIZE=64 -D_OS_LINUX -D_LINUX_X86_64 -D_X86 -DUSE_ASSERT -DTSAM -DTSAM_NO_ESDS_SEQUENCE -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -D_USE_FSYNC -D_CHECK_LICENSE -D_STBLOG -D_TAC -DFD_SETSIZE=8192 -D_CM_OBSERVER -D_USE_FALLOCATE -D_GNU_SOURCE -D_GNU_SOURCE -D_GNU_SOURCE -D_GNU_SOURCE -DHAVE_EPOLL"
Compiled at Oct 13 2025 01:59:51 build seq 303667 init rev {2023-11-24}

Method

1. Create Procedure

Create the procedure.

create or replace procedure view_script (tb_name varchar2)
is
    sql_text0 varchar2(4000);
    sql_text1 varchar2(4000);
    loop_chk  number;
    text_value varchar2(100);
    
begin
    select distinct 'create or replace force view ' ||
           a.owner || '.' || a.view_name || '(' into sql_text0
      from  all_views a
           ,all_tab_columns b
     where a.owner = b.owner
       and a.view_name = b.table_name
       and a.view_name = tb_name;
    dbms_output.put(sql_text0);
    
    loop_chk := 0;
    for aaa in (select column_name  from all_tab_columns where table_name = tb_name)
    loop
        loop_chk := loop_chk + 1;
        if loop_chk < 2 then
            dbms_output.put(aaa.column_name);
        else
            dbms_output.put(','|| aaa.column_name);
        end if;
    end loop;  
    
    dbms_output.put_line(')');
    dbms_output.put_line('as');
          
    select text into sql_text1
      from all_views 
     where view_name = tb_name;
    dbms_output.put_line(sql_text1||';');
end;
/

2. Set serveroutput on

Set serveroutput on.

SQL> set serveroutput on

3. Execute Procedure

Execute the procedure.

(Example of extracting view V_ACC_SUM)

SQL> exec view_script('V_ACC_SUM');

Test Details

SQL> CREATE OR REPLACE VIEW tibero.v_acc_sum AS
SELECT
    acc1.amt  AS acc1_amt,
    acc2.amt  AS acc2_amt,
    acc1.tamt AS acc1_tamt,
    acc1.amt + acc2.amt AS sum_amt
FROM tibero.acc1 acc1, tibero.acc2 acc2
WHERE acc1.accno = acc2.accno;   2    3    4    5    6    7    8

View 'TIBERO.V_ACC_SUM' created.


SQL> conn tibero/tmax
Connected to Tibero.

SQL> ls

NAME                            SUBNAME        TYPE
------------------------------- -------------- -----------
PK_ACC1_ACCNO                                  INDEX
PK_ACC2_ACCNO                                  INDEX
ACC1                                           TABLE
ACC2                                           TABLE
V_ACC_SUM                                      VIEW

SQL> conn sys
Enter Password:
Connected to Tibero.

SQL> SELECT count(*) from TIBERO.V_ACC_SUM;

  COUNT(*)
----------
    100000

1 row selected.

SQL> set serveroutput on
SQL> exec view_script('V_ACC_SUM');
create or replace force view TIBERO.V_ACC_SUM(ACC1_AMT,ACC2_AMT,ACC1_TAMT,SUM_AMT)
as
SELECT
    acc1.amt  AS acc1_amt,
    acc2.amt  AS acc2_amt,
    acc1.tamt AS acc1_tamt,
    acc1.amt + acc2.amt AS sum_amt
FROM tibero.acc1 acc1, tibero.acc2 acc2
WHERE acc1.accno = acc2.accno;

PSM completed.