Home » Developer & Programmer » Precompilers, OCI & OCCI » Pro*C using OCI (21700: object does not exist or is marked for delete) (11g, Solaris)
Pro*C using OCI (21700: object does not exist or is marked for delete) [message #558126] Tue, 19 June 2012 09:23 Go to next message
harris_a
Messages: 2
Registered: June 2012
Location: Ontario
Junior Member
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
Re: Pro*C using OCI (21700: object does not exist or is marked for delete) [message #558314 is a reply to message #558126] Wed, 20 June 2012 15:07 Go to previous messageGo to next message
harris_a
Messages: 2
Registered: June 2012
Location: Ontario
Junior Member
Resolved: because of the way the job submission works with the ERP system (calling a batch pro*c job), you need some additional grants to the object types.
Re: Pro*C using OCI (21700: object does not exist or is marked for delete) [message #558341 is a reply to message #558314] Thu, 21 June 2012 01:18 Go to previous message
Michel Cadot
Messages: 58847
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback.

Regards
Michel
Previous Topic: Pro*C XML sample
Next Topic: It's better keep connected to Oracle or it's better reconnect after work?
Goto Forum:
  


Current Time: Wed Aug 20 15:29:18 CDT 2014

Total time taken to generate the page: 0.11239 seconds