DevOps, CI/CD and Automation

Good Morning,

Some background:

I'm with a university that has the Elucian (formerly Sungard Higher Education) Banner ERP product. We are using Oracle 11g on Solaris. Have been working to find a way so that I can use a collection type as an IN OUT in a stored procedure and be able to call and return this to a Pro*C program (mainly for the simplicity of file output, rather than using UTL_FILE to write directly from a package).

The Details

The process:

- 1 user defined object type, has 2 varchars and a number
- 1 table type of the above object type
- 1 stored procedure with an input and 2 outputs (the custom type and a status code)
- 1 pro*c program

Since the OCI libraries were not by default available to PROC, I added $ORACLE_HOME/rdbms/public to $ORACLE_HOME/precomp/admin/pcscfg.cfg. I ran OTT with the intype specifying the case and the 2 types I wanted to use in my Pro*C program. I added the /rdbms/public path explicitly in the makefile as well as specifying the intype as the outfile from OTT.

Following the limiting amount of documentation, I declared the two types in the following way:

CUSTOM_TYPE *variable_name
CUSTOM_TYPE_TABLE *variable name_table

This compiles fine. The next step according to docs is that you have to initialize them:

EXEC SQL ALLOCATE :variable name_table;
EXEC SQL ALLOCATE :variable_name;

I then try to call the stored procedure:

EXEC SQL EXECUTE
BEGIN
user.P_STORED_PROC(:value, :variable name_table, :status_code);
END;
END-EXEC;

I initially had a WHENEVER NOT FOUND DO loop, but for debugging just retrieving first:

EXEC SQL COLLECTION GET :variable name_table INTO :variable_name ;
EXEC SQL OBJECT GET column a, column b FROM :variable_name INTO :procvarA :procvarB;


EXEC SQL FREE :variable name_table;
EXEC SQL FREE :variable name;

This all SEEMS straight forward, following the documentation. The program compiles fine and no apparent issues.


The problem

When I run the program, I get the following on my SQL error checking: OCI-21700: object does not exist or is marked for delete. This happens for the two ALLOCATES and also for the stored procedure call. I've done tons and tons of searching forums and documentation but can't figure out why it won't allocate. The objects definitely exist under the user schema running the program. And I have had no problems calling stored procedures and packages in the past from Pro*C.

Can anyone shed some light on what I might be doing wrong? Or if something else has to be configured in order for the OCI stuff to work?

Thanks!

Harris