Page History

...

Stored Procedure for User-Defined Function Output

Code Block

language	sql

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

language	sql

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

language	bash

$ 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

...

Versions Compared

Old Version 3

New Version Current

Key