http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1940897819496
Manish -- Thanks for the question regarding "Passing array from C to plsql", version 8i
Submitted on 11-Nov-2001 9:57 Central time zone
Last updated 24-May-2004 8:12
You Asked
the problem is that i am passing an array from my pro*c program to
oracle and the array when reaches the procedure all its elements becomes
null!!
the array in pro*c before the EXEC SQL block is absolutely fine and has
all the elements, i've seen this by printing the elements. also after
the end-exec i.e. after the call to the procedure the array is fine(this
array is an IN OUT parameter. but eithin the pl/sql block and in the
procedure also the array elements become null.though amazingly the
arry.count indicated that the NUMBER of elements received is correct.
but unfortunately they become null!!
i've wracked all my brain but still don't know why it is happening!
the code is somewhat like
pro*c
-----------
EXEC SQL BEGIN DECLARE SECTION;
int dimension;
varchar ps_store[100][4];
EXEC SQL ARRAYLEN ps_store(dimension);
EXEC SQL END DECLARE SECTION;
i'm accesiing the array in pro*c as follows
strcpy((char *)ps_store[ll_ctr].arr,"0030");
ll_ctr++;
strcpy((char *)ps_store[ll_ctr].arr,"0032");
ll_ctr++;
strcpy((char *)ps_store[ll_ctr].arr,"0034");
ll_ctr++;
strcpy((char *)ps_store[ll_ctr].arr,"0035");
here printing the array elements gives correct values!!
the call to procedure is as follows
EXEC SQL EXECUTE
DECLARE
error_message VARCHAR2(255);
error_no NUMBER;
relations_exist VARCHAR2(1);
return_code BOOLEAN;
BEGIN
return_code := NRD_SP_CNV_DELETE_RECORDS.VAL_STYLE (:ps_store,
:pl_step_id,
:pl_time,
error_no,
error_message);
if return_code = TRUE
then
whatever....
END;
END-EXEC;
now the pacakge code is like
declarations
TYPE STORE_ARRAY IS TABLE OF VARCHAR(4) INDEX BY BINARY_INTEGER;
FUNCTION VAL_STYLE
(i_store_in IN OUT STORE_ARRAY,
i_step_id IN NUMBER,
i_time IN NUMBER,
val_error_no IN OUT NUMBER,
val_error_message IN OUT VARCHAR2) RETURN BOOLEAN;
now i access the elements as usual in this array
i_store_in(i)
but all these are null!!!
i am unable to solve this problem.
Could you explain me how to achieve this.
and we said...
actually it is not 100% fine, you have defined it as: varchar ps_store[100][4]; now a varchar is really a struct with a length and a array of data, like this: struct { unsigned short len; unsigned char arr[4]; } ps_store[100]; (although pro*c might padd out the arr element to more then 4) so, technically your code: strcpy((char *)ps_store[ll_ctr].arr,"0030"); is copying 5 bytes into a 4 byte variable (don't forget the NULL terminator). Varchar's are NOT null terminated. The piece of the code you are TRULY forgetting is: ps_store[ll_ctr].len = strlen( ps_store[ll_ctr].arr ); you've never set the length field and it's my guess they are init'ed to zero -- hence the length as far as pro*c is concerned is 0 bytes (they are NULL). Additionally, I cannot tell (since I don't have all of the code) if you are filling in all 100 array elements or not, I only see 4 of them -- that can be a problem as well. The code more properly would be: static void process( void ) { int i; varchar ps_store[100][5]; int nelems; /* use arraylen so we can constrain the size of the array dynamically at run time. If I fill in N array elements, that's all that will be sent, otherwise pro*c will ALWAYS send 100 elements */ exec sql arraylen ps_store(nelems); varchar msg[4000]; short msg_i; for( i = 0; i < 10; i++ ) { sprintf( ps_store[i].arr, "%04d", i ); ps_store[i].len = strlen( ps_store[i].arr ); /* make sure to set that LENGTH attribute!!! } nelems = 10; /* note that for VARCHARS that come OUT of a plsql block, we must set the max length. using a null indicator here as well */ msg.len = 4000; msg_i = -1; /* null */ exec sql whenever sqlerror do sqlerror_hard(); /* now lets test it... */ exec sql execute declare type array is table of varchar2(4) index by binary_integer; l_data array; l_msg varchar2(4000); begin l_data := :ps_store; for i in 1 .. l_data.count loop l_msg := l_msg || chr(10) || 'l_data(' || i || ') = ' || l_data(i); end loop; :msg:msg_i := l_msg; end; end-exec; if ( !msg_i ) printf( "%.*s\n", msg.len, msg.arr ); else printf( "message was NULL\n" ); } The output from this is: l_data(1) = 0000 l_data(2) = 0001 l_data(3) = 0002 l_data(4) = 0003 l_data(5) = 0004 l_data(6) = 0005 l_data(7) = 0006 l_data(8) = 0007 l_data(9) = 0008 l_data(10) = 0009 showing that it works.
Reviews | |
---|---|
Great answer November 12, 2001 - 12pm Central time zone
Reviewer: Joel from Columbus, OH USA Thanks, Tom Which is the more efficient April 15, 2004 - 3am Central time zone
Reviewer: Neil from UK I have a requirement to pass a host array into a pl/sql server-side pl/sql package. Would it be
more efficient to pass the array into a pl/sql block defined in the host program and let it call
the server-side package for each element of the array, or should I code a further procedure in the
server-side package to receive the array and have it call the relevant procedure for each element?
Either way, the array will go across the network, right? Followup April 15, 2004 - 8am Central time zone: plsql blocks "defined" in the host are SENT to the server to be compiled and executed.
plsql does not talk to itself over the network -- if you have a plsql block "in your client code",
that string is sent to the server where it executes.
Just bind the input array to the procedure and execute it. Host arrays into pl/sql and back April 21, 2004 - 11am Central time zone
Reviewer: Neil from UK I would like to pass a number host arrays into my pl/sql block. Some of them will already be
populated, and some need to be populated by a server side package. The pl/sql block loops through
the arrays that are populated and calls the server side package for each populating those arrays
that empty. That's the theory.
I know you can make an assignment within a pl/sql block from a host array to a pl/sql table after
making the required definintions:
pl_sql_table := :host_array;
But can you assign a pl/sql table to a host array like this
:host_array := pl_sql_table; ?
If so, does one need to nul-terminate the new elements outside the block? Or to put it another way April 29, 2004 - 3am Central time zone
Reviewer: Neil from UK Is there any way of returning an array from pl/sql to a pro*c host program? Followup April 29, 2004 - 8am Central time zone: static void process( )
{
EXEC SQL BEGIN DECLARE SECTION;
varchar input_array[25][50];
int input_dim;
exec sql arraylen input_array(input_dim);
varchar output_array[25][50];
int output_dim;
exec sql arraylen output_array(output_dim);
int num_elements;
int i;
EXEC SQL END DECLARE SECTION;
EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
input_dim = 17;
for( i = 0; i < input_dim; i++ )
{
sprintf( input_array[i].arr, "Element %d", i );
input_array[i].len = strlen(input_array[i].arr );
}
output_dim = 0;
exec sql execute
begin
for i in 1 .. :input_array.count
loop
:output_array(i) := upper(:input_array(i));
end loop;
:output_dim := :output_array.count;
end;
end-exec;
printf( "output dim = %d\n", output_dim );
for( i = 0; i < output_dim; i++ )
printf( "%d) %.*s\n", i, output_array[i].len, output_array[i].arr );
}
Looks promising May 18, 2004 - 10am Central time zone
Reviewer: Neil from UK But I couldn't get it to work. It compiles and it runs, but I get no output.
I'm using 8.1.7 on HP - should this matter? Followup May 18, 2004 - 4pm Central time zone: just ran it on 8174, worked - -should work. if no output at all -- put in more prints and see what
is happening a little more. It wasn't giving me any output because I wasn't connected to the database! Doh! How stupid is tha?
I just went back to it to see if I could get it to work, and the penny dropped! Sorry for wasting
your time!
|
댓글 없음:
댓글 쓰기