| Passing VARCHAR2 arrays from ProC to PLSQL [message #272389] |
Thu, 04 October 2007 17:05  |
whatfur
Messages: 29
Registered: July 2006
|
Junior Member |
|
|
Hello,
I have Oracle9i release 9.2.0.6.0
running on UNIX os level 5300-06
Pro*C/C++: Release 9.2.0.6.0.
My problem is that I would rather not have
to TRIM on the plsql side yet things are getting
passed with trailing spaces. In other programs
I have done it seemed to work without trimming
in the past.
IN Pro*C I created:
boolean ConnectRtu ()
{
boolean v_status;
typedef char vc1_arr[9];
int aa;
EXEC SQL BEGIN DECLARE SECTION;
EXEC SQL TYPE vc1_arr IS VARCHAR2(9) REFERENCE;
vc1_arr single_rtu;
vc1_arr g_rtuname[20];
EXEC SQL END DECLARE SECTION;
strncpy(g_rtuname[0], "VIENNA",8);
strncpy(g_rtuname[1], "Moscow",8);
EXEC SQL EXECUTE
BEGIN
pkg_rtds.rvucheck(:g_rtuname);
END;
END-EXEC;
...
rvucheck looks like:
*from header TYPE RTUNameTabType IS TABLE OF VARCHAR2(9) INDEX BY BINARY_INTEGER;
procedure rvucheck(rtuname IN RTUNameTabType)
is
i NUMBER;
found1 NUMBER:=0;
found2 NUMBER:=0;
transfer_fail number:=0;
BEGIN
pkg_jobt.put_line (0,'rtuname= '||rtrim(substr(rtuname(1),1,8))||'XX') ;
pkg_jobt.put_line (0,'rtuname= '||rtrim(substr(rtuname(2),1,8))||'XX') ;
select 11 into found1 from rtu where rtu_name=rtuname(1);
select 12 into found2 from rtu where rtu_name=rtuname(2);
EXCEPTION
WHEN others THEN
pkg_jobt.put_line (0,'others = ' );
END rvucheck;
pkg_jobt output:
rtuname= VIENNA XX
rtuname= Moscow XX
others = (no data found)
**Side note is there is an Oracle demo sample of something
very similar and the TABLE TYPE is defined to be equal to
the table field length and one less than the typedef
length and if I try to do that it fails also. (so I'm
thinking their sample has a problem also as they do
nothing with lengths and if theirs gets
copied with spaces there selects out of the table
using the passed key would not work IMO)
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
