Versions Compared

Key

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

...

  • Stored Procedure for User-Defined Function Output

    Code Block
    languagesql
    -- 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
    languagesql
     -- 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;
    /

...

  • 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
    languagebash
    $ 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

...