Method and apparatus for executing stored procedures in a foreign database management system

• US 5,768,589 A
• Filed: 07/12/1996
• Issued: 06/16/1998
• Est. Priority Date: 07/12/1996
• Status: Expired due to Term

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle Corporation
• Inventors: Meijer, Edwin W., Simonis, Bert, Hayes, Eleanor M., Bradley, Kirk A.
• Primary Examiner(s): Kulik, Paul V.
• Application Number:
  US08/680,561
• Time in Patent Office: 704 Days
• Field of Search: 395/684, 395/680, 395/610, 395/601-604, 395/612, 707/10, 707/101, 707/1-4
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2443 Stored procedures
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching

Abstract

A method and apparatus for transparently invoking from a first database management system (DBMS) a stored procedure residing at a second DBMS. The first DBMS initially receives an instruction from a user to execute the stored procedure. In response, an invoking instruction executable at the second DBMS is automatically generated for causing the second DBMS to execute the stored procedure. The invoking instruction is sent to the second DBMS. In response to the invoking instruction, the second DBMS executes the stored procedure and provides the results to the first DBMS, which provides the results to the user. The fact that the stored procedure resides at the second, rather than the first, DBMS is transparent to the user.

Claims

1. A computer-implemented method for invoking from a first database management system (DBMS) a stored procedure residing at a second DBMS, said method comprising the steps of:

a) receiving, at the first DBMS, an instruction to execute the stored procedure, the stored procedure residing at the second DBMS;

b) in response to said instruction to execute the stored procedure, generating an invoking instruction for invoking execution of the store procedure;

c) sending said invoking instruction to the second DBMS;

d) receiving information from the second DBMS representing results of an execution of the stored procedure; and

e) obtaining, in response to receiving the instruction to execute the stored procedure, a description of the stored procedure from the second DBMS.

2. The method of claim 1, wherein the description of the stored procedure obtained from the second DBMS includes a procedure name, a procedure type, and optionally includes procedure parameters.

3. The method of claim 2, wherein said procedure parameters are selected from a group consisting of parameter datatype, parameter precision, parameter scale, nullable information, and parameter name.

4. The method of claim 3, wherein in response to a parameter datatype including a record datatype, each element of said record datatype is treated as a separate parameter.

5. The method of claim 1, further including the following steps prior to the step of generating the invoking instruction:

f) generating a procedure specification from the description of the stored procedure obtained from the second DBMS, the procedure specification presented in a format compatible with the first DBMS; and

h) verifying any parameter provided with the instruction to execute the stored procedure is compatible with a corresponding parameter of the procedure specification.

6. The method of claim 5, wherein the invoking instruction is generated from the description of the stored procedure obtained from the second DBMS.

7. The method of claim 6, wherein the invoking instruction includes a name of the stored procedure and a parameter value if data is to be processed by executing the stored procedure.

8. A computer-readable medium having stored thereon data representing a sequence of instructions for invoking from a first database management system (DBMS) a stored procedure residing at a second DBMS, said sequence of instruction which, when executed by a processor, cause said processor to perform the steps of:

a) receiving, at the first DBMS, an instruction to execute the stored procedure, the stored procedure residing at the second DBMS;

b) in response to said instruction to execute the stored procedure, generating an invoking instruction for invoking execution of the store procedure;

c) sending said invoking instruction to the second DBMS; and

d) receiving information from the second DBMS representing results of an execution of the stored procedure; and

e) obtaining, in response to receiving the instruction to execute the stored procedure and prior to generating the invoking instruction, a description of the stored procedure from the second DBMS.

9. The computer-readable medium of claim 8, wherein the description of the stored procedure obtained from the second DBMS includes a procedure name, a procedure type, and optionally includes procedure parameters.

10. The computer-readable medium of claim 9, wherein said procedure parameters are selected from a group consisting of parameter datatype, parameter precision, parameter scale, nullable information, and parameter name.

11. The computer-readable medium of claim 10, wherein, in response to a parameter datatype including a record datatype, each element of said record datatype is treated as a separate parameter.

12. The computer-readable medium of claim 8, further including instructions which, when executed by the processor, would cause the processor to perform the following steps prior to the step of generating the invoking instruction:

f) generating a procedure specification from the description of the stored procedure obtained from the second DBMS, the procedure specification in a format compatible with the first DBMS;

g) verifying a parameter of the instruction to execute the stored procedure is compatible with a corresponding parameter of the procedure specification.

13. The computer-readable medium of claim 12, wherein the invoking instruction is generated from the procedure specification.

14. The computer-readable medium of claim 13, wherein the invoking instruction includes a name of the stored procedure, a parameter name if a parameter value is to be generated by executing the stored procedure, and a parameter value if data is to be processed by executing the stored procedure.

15. A computer system having a first database management system (DBMS), said computer system comprising:

a processor; and

a storage device having stored therein a sequence of instructions for invoking from the first DBMS a stored procedure residing at a second DBMS, said sequence of instruction which, when executed by said processor, cause said processor to perform the steps of;

a) receiving, at the first DBMS, an instruction to execute the stored procedure residing at the second DBMS;

b) in response to said instruction, generating an invoking instruction processable by the second DBMS for causing the second DBMS to execute the store procedure;

c) sending said invoking instruction to the second DBMS;

d) receiving information from the second DBMS representing results of an execution of the stored procedure; and

e) obtaining, in response to receiving the instruction to execute the stored procedure and prior to generating the invoking instruction, a description of the stored procedure from the second DBMS.

16. The computer system of claim 15, wherein the description of the stored procedure obtained from the second DBMS includes a procedure name, a procedure type, and optionally includes procedure parameters.

17. The computer system of claim 16, wherein said procedure parameters are selected from a group consisting of parameter datatype, parameter precision, parameter scale, nullable information, and parameter name.

18. The computer system of claim 17, wherein, in response to a parameter datatype including a record datatype, each element of said record datatype is treated as a separate parameter.

19. The computer system of claim 15, wherein said sequence of instructions further includes instructions which, when executed by the processor, cause the processor to perform the following steps prior to the step of generating the invoking instruction:

f) generating a procedure specification from the description of the stored procedure obtained from the second DBMS, the procedure specification in a format compatible with the first DBMS; and

g) verifying a parameter of the instruction to execute the stored procedure is compatible with a corresponding parameter of the procedure specification.

20. The computer system of claim 19, wherein the invoking instruction is generated from the procedure specification.

21. The computer system of claim 20, wherein the invoking instruction includes a name of the stored procedure, and a parameter value if data is to be processed by executing the stored procedure.