Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

  • Stored Procedure for User-Defined Function Output

    Code Block
    -- Output stored procedure and user-defined function names to the screen.
    -- EXEC showProcedures;
    CREATE OR REPLACE PROCEDURE showProcedures
    AS
     CURSOR C1 IS
        SELECT U.USER_NAME, PROC.PROC_NAME,
               DECODE(PROC.OBJECT_TYPE, 0, 'PROCEDURE', 1, 'FUNCTION')
          FROM SYSTEM_.SYS_PROCEDURES_ PROC, SYSTEM_.SYS_USERS_ U
         WHERE PROC.USER_ID = U.USER_ID;
        V1 CHAR(40);
        V2 CHAR(40);
        V3 CHAR(20);
    BEGIN
        SYSTEM_.PRINTLN('----------------------------------------------------------------------------------------------------');
        SYSTEM_.PRINT(' USER_NAME                               PROC_NAME');
        SYSTEM_.PRINTLN('                               PROCEDURE/FUNCTION');
        SYSTEM_.PRINTLN('----------------------------------------------------------------------------------------------------');
        OPEN C1;
        LOOP
            FETCH C1 INTO V1, V2, V3;
            EXIT WHEN C1%NOTFOUND;
            SYSTEM_.PRINT(' ');
            SYSTEM_.PRINT(V1);
            SYSTEM_.PRINT(V2);
            SYSTEM_.PRINTLN(V3);
        END LOOP;
        SYSTEM_.PRINTLN('----------------------------------------------------------------------------------------------------');
        CLOSE C1;
    END;
    /
  • Stored procedure to check the contents of the stored procedure

    Code Block
     -- Output the contents of the specified stored procedure on the screen. 
    -- EXEC showProcBody('USER_NAME', 'PROCEDURE_NAME');
    CREATE OR REPLACE PROCEDURE showProcBody(p1 IN VARCHAR(40), p2 IN VARCHAR(40)) 
    AS
    CURSOR C1 IS
        SELECT SYSTEM_.SYS_PROC_PARSE_.PARSE
          FROM SYSTEM_.SYS_PROC_PARSE_
         WHERE SYSTEM_.SYS_PROC_PARSE_.PROC_OID = (SELECT P.PROC_OID
                  FROM SYSTEM_.SYS_PROCEDURES_ P,
                       SYSTEM_.SYS_USERS_ U
                 WHERE U.USER_ID = P.USER_ID
                   AND P.PROC_NAME = p2
                   AND U.USER_NAME = p1)
         ORDER BY SYSTEM_.SYS_PROC_PARSE_.SEQ_NO;
    V1 VARCHAR(4000);
    BEGIN
        OPEN C1;
        SYSTEM_.PRINTLN('---------------------------------');
        SYSTEM_.PRINT(P1);
        SYSTEM_.PRINTLN(' PROCEDURE');
        SYSTEM_.PRINTLN('---------------------------------');
        SYSTEM_.PRINTLN('');
        LOOP
          FETCH C1 INTO V1;
          EXIT WHEN C1%NOTFOUND;
          SYSTEM_.PRINTLN(V1);
        END LOOP;
        CLOSE C1;
    SYSTEM_.PRINTLN('');
    SYSTEM_.PRINTLN('---------------------------------');
    END;
    /

How to Execute user-defined stored procedure

  • Check the list of stored procedures and user-defined functions

     

    Code Block
    languagesql
    iSQL> exec showProcedures;
  • Check the contents of the stored procedure

     

    Panel

    iSQL> exec showProcBody('USER_NAME', 'PROC_NAME');

Using the aexport utility

...

aexport is a utility that saves database object creation statements to a file. After performing aexport, the contents of the stored procedure can be checked in the created file.

Execute aexport - all objects

  • The creation statement of all stored procedures can be checked in ALL_CRT_PROC.sql among the files created after executing aexport.

    Code Block
    languagebash
    $ aexport
    Code Block
    titleExample
    languagebash
    $ aexport
    -----------------------------------------------------------------
         Altibase Export Script Utility.
         Release Version 4.3.9.223
         Copyright 2000, ALTIBASE Corporation or its subsidiaries.
         All Rights Reserved.
    -----------------------------------------------------------------
    Write Server Name (default:127.0.0.1) :                    # Enter the Altibase server IP
    Write UserID : sys                                                         # Enter sys
    Write Password :                                                          # Enter Altibase server IP sys user password
    ##### TBS #####
    ##### User #####
    ** input user USER1's password (default - same with USER_NAME):     # 사용자 패스워드 입력. 
    ** input user EHEE's password (default - same with USER_NAME): 
    ** input user ALTITEST's password (default - same with USER_NAME): 
    ##### Synonym #####
    ##### Table #####
    ** ALTITEST.ORDERS
    ** SYS.DEMO_EX2
    ** SYS.DEPARTMENT
    ** SYS.EMPLOYEE
    ** SYS.T1
    ** USER1.T1
    
    ##### QUEUE #####
    ##### Sequence #####
    ##### Procedure & Function #####
    ##### View #####
    ##### Replication #####
    ##### TRIGGER #####
    -------------------------------------------------------
      ##### Follow script files are Generated. #####
      1. run_il_out.sh   : [ iloader formout, data-out script ]
      2. run_is.sh       : [ isql table-schema script ]
      3. run_il_in.sh    : [ iloader data-in script ]
      4. run_is_index.sh : [ isql table-index script ]
      5. run_is_fk.sh    : [ isql table-foreign key script ]
      6. run_is_repl.sh    : [ isql replication script ]
    -------------------------------------------------------

Execute aexport for each user

  • If the user executes aexport after entering the database user name in the -u option of aexport and the password of the user in the -p option, only the object schema owned by the user is extracted.
  • For the stored procedure information, refer to the ALL_CRT_PROC.sql file.
  • How to execute

    Code Block
    $ aexport -u user_name -p user_password -s Altibase_Server_IP
     
    Or, 
     
    $ aexport 
    Write Server Name (default:127.0.0.1) :     # Enter the Altibase server IP          
    Write UserID :            # Enter the object owner or sys user. Whatever is typed, the result is the same.
    Write Password :       # Password of the user entered above

Execute aexport for each object

  • The object option allows extracting only specific object schemas. This option is available as of ALTIBASE HDB version 5.5.1 or later.
  • For stored procedures, values can be given in the format -object user name.procedure name.
  • As a result, a file with a name in the form of user name_procedure name_CRT.sql is created.
  • How to execute

    Code Block
    languagebash
    $ aexport -s Altibase_Server_IP  -u user_name -p user_password -object user_name.procedure_name
     
    또는 
     
    $ aexport -object user_name.procedure_name
    Write Server Name (default:127.0.0.1) :       # Enter the Altibase server IP       
    Write UserID :            # Enter the object owner or sys user. Whatever is typed, the result is the same. 
    Write Password :       # Password of the user entered above