Home » Developer & Programmer » Precompilers, OCI & OCCI » problem executing a procedure
problem executing a procedure [message #293795] Tue, 15 January 2008 01:54 Go to next message
dinuka
Messages: 30
Registered: January 2008
Location: Sri Lanka
Member

Hi,
I wrote the following procedure.

CREATE OR REPLACE PROCEDURE sequence_no(pref IN char, lotsize IN integer, newseqno OUT integer)
AS
seqnew integer:=0;
BEGIN
SELECT SEQ_NO INTO newseqno from SEQUENCENUMBERS WHERE PREFIX = pref;
seqnew:= newseqno + lotsize;
UPDATE SEQUENCENUMBERS SET SEQ_NO=seqnew WHERE PREFIX = pref;
END sequence_no;
/

what this does is basically get the sequence number from the table and then update that value to the 'seqnew' value. And i have written the following code in c++ to call this procedure. But i get a segmentation fault when i try to do this. Is the procedure wrong?

Statement * seqst;

seqst = conn->createStatement();
seqst->setAutoCommit(true);
int seqno = 0;
seqst->setSQL("BEGIN sequence_no(:1, :2,:3); END:");
int quantity;
int sq = 100;
seqst->setString(1, "orders");
seqst->setInt(2,sq);
seqst->registerOutParam(3,OCCIINT, sizeof(quantity));
seqst->executeUpdate();
Re: problem executing a procedure [message #293796 is a reply to message #293795] Tue, 15 January 2008 02:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is the fourth question you posted in a couple of days.
We already told you to format your code:
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 to verify.
Always post your Oracle version (4 decimals).

Now DO IT.

In addition, we didn't give a feedback for your last questions. Does it work now? Feedback is appreciated by those who spend time to help you.

Regards
Michel
Re: problem executing a procedure [message #293797 is a reply to message #293795] Tue, 15 January 2008 02:13 Go to previous messageGo to next message
dinuka
Messages: 30
Registered: January 2008
Location: Sri Lanka
Member

Hi
Sorry about the formatting. No i still could not solve the getString problem. Your help will be greatly appreciated.

thank you

Dinuka
Re: problem executing a procedure [message #293800 is a reply to message #293795] Tue, 15 January 2008 02:20 Go to previous messageGo to next message
dinuka
Messages: 30
Registered: January 2008
Location: Sri Lanka
Member

CREATE OR REPLACE PROCEDURE Sequence_No
(pref IN CHAR,
LotSize IN INTEGER,
NewseqNo OUT INTEGER)
AS
seqNew INTEGER := 0;
BEGIN
SELECT seq_No
INTO NewseqNo
FROM SequenceNumbers
WHERE Prefix = pref;

seqNew := NewseqNo + LotSize;

UPDATE SequenceNumbers
SET seq_No = seqNew
WHERE Prefix = pref;
END Sequence_No;
/
Re: problem executing a procedure [message #293801 is a reply to message #293795] Tue, 15 January 2008 02:21 Go to previous messageGo to next message
dinuka
Messages: 30
Registered: January 2008
Location: Sri Lanka
Member

Thats the best i could do. Is it ok now ?
Re: problem executing a procedure [message #293811 is a reply to message #293801] Tue, 15 January 2008 02:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you read the link I posted?
Don't you see http://www.orafaq.com/forum/theme/orafaq/images/b_code.gif button?

Regards
Michel
Re: problem executing a procedure [message #293813 is a reply to message #293795] Tue, 15 January 2008 02:49 Go to previous messageGo to next message
dinuka
Messages: 30
Registered: January 2008
Location: Sri Lanka
Member

But i did format it using the tool given in this site. Whats wrong still? I dont understand. please help me if possible.
Re: problem executing a procedure [message #293819 is a reply to message #293813] Tue, 15 January 2008 03:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From the link:
Quote:
please be sure to use formatting tags:
[code] Enter your code here.[/code]

Did you read it?
Don't you see the "Preview message" button to check what you are going to post?

Regards
Michel
Re: problem executing a procedure [message #293822 is a reply to message #293795] Tue, 15 January 2008 03:20 Go to previous messageGo to next message
dinuka
Messages: 30
Registered: January 2008
Location: Sri Lanka
Member

CREATE OR REPLACE PROCEDURE sequence_no(pref IN char, lotsize IN integer, newseqno OUT integer)
AS
seqnew integer:=0;
BEGIN
SELECT SEQ_NO INTO newseqno from SEQUENCENUMBERS WHERE PREFIX = pref;
seqnew:= newseqno + lotsize;
UPDATE SEQUENCENUMBERS SET SEQ_NO=seqnew WHERE PREFIX = pref;
END sequence_no;
/


 Statement * seqst;

seqst = conn->createStatement();
seqst->setAutoCommit(true);
int seqno = 0;
seqst->setSQL("BEGIN sequence_no(:1, :2,:3); END:");
int quantity;
int sq = 100;
seqst->setString(1, "orders");
seqst->setInt(2,sq);
seqst->registerOutParam(3,OCCIINT, sizeof(quantity));
seqst->executeUpdate();
Re: problem executing a procedure [message #293823 is a reply to message #293795] Tue, 15 January 2008 03:21 Go to previous messageGo to next message
dinuka
Messages: 30
Registered: January 2008
Location: Sri Lanka
Member

Thanks, i didnt see that before. Ok i have formatter the code. please help me. i can call the procedure from sql plus if i commented out the OUT variable. but i want the OUT variable so that i can get the new sequence number. but within the code if i put "call sequnce_no('orders',100)" i dnt get any error. but nothing happens as well.

Can you please tell me how to call a procedure from OCCI?
Re: problem executing a procedure [message #293830 is a reply to message #293823] Tue, 15 January 2008 03:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
seqst->setSQL("BEGIN sequence_no(:1, :2,:3); END:");


Do you see ":" after "END", it should be ";".
With code formatting it is immediate to see that. With standard font, it is almost impossible.

Regards
Michel
Re: problem executing a procedure [message #293832 is a reply to message #293795] Tue, 15 January 2008 03:49 Go to previous messageGo to next message
dinuka
Messages: 30
Registered: January 2008
Location: Sri Lanka
Member

i did that, but still no luck. getting segmentation fault. Is the procedure wrong? i just want the value returned. is there any other way of getting a value returned without using OUT variables?
Re: problem executing a procedure [message #293848 is a reply to message #293795] Tue, 15 January 2008 04:49 Go to previous messageGo to next message
vicenzo
Messages: 28
Registered: December 2007
Location: Paris
Junior Member
What tool are you using (GCC, MS Visual studio, borland ?)

Are you using the correct dlls for your compiler ? (Because occi is provided for specifics compilers and versions).

Make sure the dll you're using are the ones made for your compiler and its version.

Are you using unicode / ansi ?

As Michel said, first correct your pl/sql block (ending by ';' and not ':')

Your problem is still the same than the getString() one and i think it must linked to the dlls or the charset for getting an seg fault with the given code...
Re: problem executing a procedure [message #293859 is a reply to message #293795] Tue, 15 January 2008 05:49 Go to previous messageGo to next message
dinuka
Messages: 30
Registered: January 2008
Location: Sri Lanka
Member

im using g++ 4.1.3 and using the libocci.so along with the appropriate include files. Is it the problem with my compiler?
Even i saw this in one forum saying that occi was built using gcc 3.4.4.. even my gcc is gcc 4.1.3. Could this be a problem?
Re: problem executing a procedure [message #293866 is a reply to message #293795] Tue, 15 January 2008 06:22 Go to previous messageGo to next message
vicenzo
Messages: 28
Registered: December 2007
Location: Paris
Junior Member
Hi,

google is your friend !
It took me 10 seconds on google to find related problems between the libocci compiled with GCC 3.4.x and progams compiled with GCC 4.x.y.

1 / Get the lastest occi patches from Oracle :

http://www.oracle.com/technology/tech/oci/occi/occidownloads.html

2 / Have a look on google for anwsers (many similar cases reported)

3 / go back to GCC 3.4.x

4 Good Luck !

Re: problem executing a procedure [message #293876 is a reply to message #293795] Tue, 15 January 2008 07:15 Go to previous message
dinuka
Messages: 30
Registered: January 2008
Location: Sri Lanka
Member

Hi,
Thanks vicenzo. I will definitely try doing it with gcc 3.4.3. i did see a thred saying that it will work only upto gcc 4.0. so will definitely give it a try. In that same thread they had mentioned that the getString problem was also a bug which was fixed with that version. will definitely try it and see. will give the feed back so that others wont have to go through what i went through to find a solution. Smile


Thanks everyone for you coorporation
Previous Topic: Facing alot of problems, any help is greatly appreciated
Next Topic: OCI API's details
Goto Forum:
  


Current Time: Thu Mar 28 14:13:19 CDT 2024