Test environment:
Server: oracle server in docker, service name is jaw, reference Docker Installation of oracle and mysql under unbuntu
Client: instantclient client client, reference Create a streamlined version of oracle client and pro*c compiler environment under ubuntu
Configure the service name XE in / opt/oracle/product/network/admin/tnsnames.ora as follows:
XE = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = xe) ) )
I. Creating packages and body
emp_actions.sql
CREATE OR REPLACE PACKAGE emp_actions AS TYPE CharArrayTyp IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER; TYPE NumArrayTyp IS TABLE OF FLOAT INDEX BY BINARY_INTEGER; PROCEDURE get_employees( dept_number IN INTEGER, batch_size IN INTEGER, found IN OUT INTEGER, done_fetch OUT INTEGER, emp_name OUT CharArrayTyp, job_title OUT CharArrayTyp, salary OUT NumArrayTyp); END emp_actions; / CREATE OR REPLACE PACKAGE BODY emp_actions AS CURSOR get_emp (dept_number IN INTEGER) IS SELECT ename, job, sal FROM emp WHERE deptno = dept_number; PROCEDURE get_employees( dept_number IN INTEGER, batch_size IN INTEGER, found IN OUT INTEGER, done_fetch OUT INTEGER, emp_name OUT CharArrayTyp, job_title OUT CharArrayTyp, salary OUT NumArrayTyp) IS BEGIN IF NOT get_emp%ISOPEN THEN OPEN get_emp(dept_number); END IF; done_fetch := 0; found := 0; FOR i IN 1..batch_size LOOP FETCH get_emp INTO emp_name(i), job_title(i), salary(i); IF get_emp%NOTFOUND THEN CLOSE get_emp; done_fetch := 1; EXIT; ELSE found := found + 1; END IF; END LOOP; END get_employees; END emp_actions; /
II. pro*c Program
sample9.pc
/************************************************************* Sample Program 9: Calling a stored procedure This program connects to ORACLE using the SCOTT/TIGER account. The program declares several host arrays, then calls a PL/SQL stored procedure (GET_EMPLOYEES in the CALLDEMO package) that fills the table OUT parameters. The PL/SQL procedure returns up to ASIZE values. Sample9 keeps calling GET_EMPLOYEES, getting ASIZE arrays each time, and printing the values, until all rows have been retrieved. GET_EMPLOYEES sets the done_flag to indicate "no more data." *************************************************************/ #include <stdio.h> #include <string.h> #include <stdlib.h> EXEC SQL INCLUDE sqlca.h; typedef char asciz[20]; typedef char vc2_arr[11]; EXEC SQL BEGIN DECLARE SECTION; /* User-defined type for null-terminated strings */ EXEC SQL TYPE asciz IS STRING(20) REFERENCE; /* User-defined type for a VARCHAR array element. */ EXEC SQL TYPE vc2_arr IS VARCHAR2(11) REFERENCE; asciz username; asciz password; asciz sid; int dept_no; /* which department to query? */ vc2_arr emp_name[10]; /* array of returned names */ vc2_arr job[10]; float salary[10]; int done_flag; int array_size; int num_ret; /* number of rows returned */ EXEC SQL END DECLARE SECTION; long SQLCODE; void print_rows(); /* produces program output */ void sql_error(); /* handles unrecoverable errors */ int main() { int i; char temp_buf[32]; /* Connect to ORACLE. */ EXEC SQL WHENEVER SQLERROR DO sql_error(); strcpy(username, "scott"); strcpy(password, "tiger"); strcpy(sid, "XE"); EXEC SQL CONNECT :username IDENTIFIED BY :password USING :sid; printf("\nConnected to ORACLE as user: %s\n\n", username); printf("Enter department number: "); gets(temp_buf); dept_no = atoi(temp_buf);/* Print column headers. */ printf("\n\n"); printf("%-10.10s%-10.10s%s\n", "Employee", "Job", "Salary"); printf("%-10.10s%-10.10s%s\n", "--------", "---", "------"); /* Set the array size. */ array_size = 10; done_flag = 0; num_ret = 0; /* Array fetch loop. * The loop continues until the OUT parameter done_flag is set. * Pass in the department number, and the array size-- * get names, jobs, and salaries back. */ for (;;) { EXEC SQL EXECUTE BEGIN emp_actions.get_employees (:dept_no, :array_size, :num_ret, :done_flag, :emp_name, :job, :salary); END; END-EXEC; print_rows(num_ret); if (done_flag) break; } /* Disconnect from the database. */ EXEC SQL COMMIT WORK RELEASE; exit(0); } void print_rows(n) int n; { int i; if (n == 0) { printf("No rows retrieved.\n"); return; } for (i = 0; i < n; i++) printf("%10.10s%10.10s%6.2f\n", emp_name[i], job[i], salary[i]); } /* Handle errors. Exit on any error. */ void sql_error() { char msg[512]; int buf_len, msg_len; EXEC SQL WHENEVER SQLERROR CONTINUE; buf_len = sizeof(msg); sqlglm(msg, &buf_len, &msg_len); printf("\nORACLE error detected:"); printf("\n%.*s \n", msg_len, msg); EXEC SQL ROLLBACK WORK RELEASE; exit(1); }
3. makefile
TARGET = sample9 SRCS = sample9.c OBJS = sample9.o COMMLIB = -lpthread -lclntsh LINKER = $(CC) RM = /bin/rm -f PROC = proc PROCFLAGS = code=ANSI_C ireclen=2048 oreclen=2048 parse=none include=/usr/include\ include=/usr/include/linux\ include=/usr/local/include SQLCHECK=SEMANTICS userid=scott/tiger@XE CFLAGS = -I$(ORACLE_HOME)/sdk/include\ -L$(ORACLE_HOME)/lib .SUFFIXES: .o .c .pc .c.o: $(CC) -c $(CFLAGS) $< .pc.c: @echo "PROC ." $(PROC) iname=$*.pc $(PROCFLAGS) $(TARGET): $(SRCS) $(OBJS) $(CC) -o $@ $(OBJS) $(CFLAGS) $(COMMLIB) clean : $(RM) *.lis $(SRCS) $(TARGET) $(OBJS) $(TARGET:%=%.c)
Four, operation
$ make PROC . proc iname=sample9.pc code=ANSI_C ireclen=2048 oreclen=2048 parse=none include=/usr/include include=/usr/include/linux include=/usr/local/include SQLCHECK=SEMANTICS userid=scott/tiger@XE Pro*C/C++: Release 11.2.0.4.0 - Production on Saturday, May 26, 14:43:24 2018 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. //The default system option values are taken from: / opt/ora11g/instantclient_11_2/precomp/admin/pcscfg.cfg cc -c -I/opt/ora11g/instantclient_11_2/sdk/include -L/opt/ora11g/instantclient_11_2/lib sample9.c ........................................... $ ls emp_actions.sql makefile sample9 sample9.c sample9.lis sample9.o sample9.pc $ ./sample9 Connected to ORACLE as user: scott Enter department number: 10 Employee Job Salary -------- --- ------ CLARK MANAGER 2450.00 KING PRESIDENT 5000.00 MILLER CLERK 1300.00
5. Delete Packages and Packages
If you don't want to leave emp_actions after the test, delete them with the first two statements
SQL> drop PACKAGE BODY emp_actions; The package has been deleted. SQL> drop PACKAGE emp_actions; The package has been deleted.