Home » Developer & Programmer » Precompilers, OCI & OCCI » Passing VARCHAR2 arrays from ProC to PLSQL
Passing VARCHAR2 arrays from ProC to PLSQL [message #272389] Thu, 04 October 2007 17:05 Go to next message
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)



Re: Passing VARCHAR2 arrays from ProC to PLSQL [message #272427 is a reply to message #272389] Fri, 05 October 2007 00:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Avoid colors but use code tags and formatting:
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.

Regards
Michel
Re: Passing VARCHAR2 arrays from ProC to PLSQL [message #272544 is a reply to message #272427] Fri, 05 October 2007 08:32 Go to previous messageGo to next message
whatfur
Messages: 29
Registered: July 2006
Junior Member
Mike,


I had read the guide and I personally think
colors augment the readability. I also did
not have lines over 80 I don't believe and I
did preview it and it looked exactly as I intended.

But thanks...
Re: Passing VARCHAR2 arrays from ProC to PLSQL [message #272554 is a reply to message #272544] Fri, 05 October 2007 08:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Code tags make the code easier to read.
Some colors hurt the eyes.

It's up to you to post as you want but there I can't read it so can't help you.

Regards
Michel
Re: Passing VARCHAR2 arrays from ProC to PLSQL [message #272558 is a reply to message #272554] Fri, 05 October 2007 09:32 Go to previous messageGo to next message
whatfur
Messages: 29
Registered: July 2006
Junior Member


Mike,

I had already taken for granted you couldn't help me as
you are too busy out being the house mother.

Thanks anyway.
Re: Passing VARCHAR2 arrays from ProC to PLSQL [message #272584 is a reply to message #272558] Fri, 05 October 2007 10:39 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
OK.

Regards
Michel
Previous Topic: HELP: Pro*C -- Update table using host array
Next Topic: COMMIT RELEASE; vs COMMIT;
Goto Forum:
  


Current Time: Thu Mar 28 05:42:20 CDT 2024