Dynamic Pro*C SQL Requiring mult. PREPAREs. WHY?
Date: 1996/08/23
Message-ID: <321E29D1.3895_at_unixmail.ks.boeing.com>#1/1
We are trying to code a Pro*C (V1.6.8.0.0) program to loop thru a file and insert multiple records into a (variably named) table using dynamic INSERTs.
What we want to do is something like:
strcpy(string, "INSERT INTO table (....) VALUES (:x, :y)");
EXEC SQL PREPARE statement_name FROM :string;
while (records exist in the file) {
set the host variables
EXEC SQL EXECUTE statement_name USING :hv1, :hv2; }
First, the precompile choked with PCC-S-0014 on the EXECUTE statement, saying that the statement identifier was undeclared. So we put a DECLARE statement_name STATEMENT in (which we should not have had to do) and it compiled & linked okay, but then it choked at run time after doing one insert successfully with an ORA -1001. This message does not make much sense to me.
On a side note, we were able to successfully run the program by repeatedly doing the PREPARE and EXECUTE in the loop--but it takes forever.
Also we want to add arrays to speed up the insert if we can get by this
PREPARE problem.
PS: We are running Oracle 7.2.2.3.0 for what it is worth.
PSS: As far as I can tell, we don't have RELEASE_CURSOR=YES set as a precompile
option, if that matters.
ANYONE HAVE ANY IDEAS OR SEEN THIS BEFORE?
TIA.
-- _/_/_/_/_/ _/_/_/_/ _/_/_/_/ // John R. Call _/ _/ _/ _/ _/ // The Boeing Company _/ _/_/_/_/ _/ // Wichita, Kansas _/ _/ _/ _/ _/ _/ // john.r.call_at_boeing.com _/_/_/ _/ _/ _/ _/ _/_/_/_/ _/ // These are my opinions only...Received on Fri Aug 23 1996 - 00:00:00 CEST