Re: Problem with embedded dynamic pl/sql in/out parameters

In article <35k20q$irc_at_access3.digex.net>, cobrien_at_access3.digex.net (Cary B. O'Brien) writes:
|> OK. I really tried to follow the example for method 4 for
|> embedded sql (Oracle version 7). And it works, up to a point. But I still
|> have some questions.
|>
|> I am trying to call a stored procedure from C, using embedded
|> sql. My program goes something like this:
|>
|> a) I do the EXE SQL PREPARE, with 'begin :rc := (pl/sql function call)end;'
|> and get back a SQLDA * called bind_dp, which tells me the names
|> and lengths of the placeholders. Note that the stored procedures
|> have output and (sometimes) in/out parameters.
|>
|> b) I call EXEC SQL DECLARE to set up a cursor
|>
|> c) I put the values for the placeholders in the bind_dp->V[i], and
|> lengths in bind_dp->L[i].
|>
|> d) I call EXE SQL OPEN C USING DESCRIPTOR bind_dp; which should
|> execute my stored procedure. Sometimes it does, sometimes I
|> get a value conversion error.
|>
|> e) Do a CLOSE
|>
|>
|> My questions are:
|>
|> 1) When setting up the bind descriptor WHAT SHOULD THE LENGTH (bind_dp->L[i])
|> fields be? If they are set to the length of the input variable, the
|> output variable (if longer) gets truncated (or I get a conversion
|> error). If i set it to the actual length of the buffer, does my
|> input variable get padded with spaces? If it is padded with
|> spaces, does oracle care?
|>

The L field should be the length of the buffer. This is how Oracle knows how much space you have. If T is 3 (integer) for example, you probably will want something like V[i] = (char *)malloc)sizeof(int) and L[i] = sizeof(int). If L is something other than 1, 2, 4, or 8 (only if you are on a 64 bit machine) than you will get a conversion error, since Oracle doesn't support integers of other lengths (neither do most computers). If T is 1, then L should be either the maximum size of a blank-padded buffer, or the actual size of the data. If T is 5, then L should be at least the actual size of the data, including the null terminator, which needs to be there. You don't want it if T is 1.

|>
|> 2) How can I tell, for an arbitrary stored procedure call, if the
|> parameters are input, output, or in/out?
|>

OCI has a procedure called odessp() which will tell you this information. You can also use the dbms_describe package (see $ORACLE_HOME/rdbms/admin/dbmsdesc.sql).

|>
|> Thanks In Advance

No problem :)

|>
|> Cary O'Brien
|> cobrien_at_access.digex.net
|>
|>
Received on Mon Sep 19 1994 - 19:08:26 CEST