Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> SQLCA sqlcode as -20000
libra20
post Jul 13 2017, 01:46 PM
Post #1


Newbie
*

Group: Members
Posts: 3
Joined: 13-July 17
Member No.: 52,078



Helllo,

I am running a update query from my ProC code, when i run the exact same query from sql developer it updates several 1000 rows in my DB. the query is printed by log so I copy it as it is to sqldeveloper to test.
This is oracle 12 DB, I have run this in another oracle 12 test DB and it ran fine.

But when the query is executed from the ProC code -

EXEC SQL FOR :records EXECUTE SQL_STMT USING DESCRIPTOR BindDesc ;

the sqlca.sqlcode returned is -20000 and number of rows updated is 0

sqlca.sqlcode = -20000
sqlca.sqlerrd [ 2 ] = 0

there is no oracle error.

What does this mean ?

thanks
Go to the top of the page
 
+Quote Post
burleson
post Jul 13 2017, 05:07 PM
Post #2


Advanced Member
***

Group: Members
Posts: 13,492
Joined: 26-January 04
Member No.: 13



Hi Libra, and welcome to the forum!

The sql ode -20000 can be a buffer overflow error.

Are you using a host variable for the update?

If so, check out the convbufsz operator:

https://docs.oracle.com/cd/A58617_01/server.../a58233/adv.htm


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
libra20
post Jul 13 2017, 10:09 PM
Post #3


Newbie
*

Group: Members
Posts: 3
Joined: 13-July 17
Member No.: 52,078



QUOTE (burleson @ Jul 13 2017, 05:07 PM) *
Hi Libra, and welcome to the forum!

The sql ode -20000 can be a buffer overflow error.

Are you using a host variable for the update?

If so, check out the convbufsz operator:

https://docs.oracle.com/cd/A58617_01/server.../a58233/adv.htm


thankyou Donald!!

My query is like this -

update tableA set
A1=null,
A2=null,
A3=null,
A4=null,
A5=null where tableA.rowID in (
select rowID from table1, table2, table3, table4, table5,tableA where
<6 table joins>
..some more conditions with 3 bind variables )

this updated about 300000 in DB when run from sqldeveloper. this query is dynamically generated by joining different parts from a DB table based on some user condition.

Did you mean to apply convbufsz for A1,A2,A3,A4,A5 ? or to the 3 bind variables
I am wondering how to use that since these variables dont exist in the code. is there any other way to overcome buffer issue ?

thanks !
Go to the top of the page
 
+Quote Post
burleson
post Jul 14 2017, 05:00 AM
Post #4


Advanced Member
***

Group: Members
Posts: 13,492
Joined: 26-January 04
Member No.: 13



Hi Libra,

Just for testing, recompile the program with literals instead of bind variables.

If it runs, we know that it's an issue with the bind variables!

Also, since you are building the sql dynamically, there is a chance that the host/bind variable syntax is malformed.

Are you building the string that contains the update sql in pl/SQL?

I have not written a C program in eons, sorry!

How do you define the RAM work area (PGA) in pro*c?

Maybe with an "alter session" or a malloc()?

These working pro*c examples may help:

https://docs.oracle.com/cd/A57673_01/DOC/ap...ch05.htm#toc076

Lastly, make sure that you are using the syntax to trap all SQL codes!

Let us know! Good luck!


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
libra20
post Jul 17 2017, 02:33 PM
Post #5


Newbie
*

Group: Members
Posts: 3
Joined: 13-July 17
Member No.: 52,078



QUOTE (burleson @ Jul 14 2017, 05:00 AM) *
Hi Libra,

Just for testing, recompile the program with literals instead of bind variables.

If it runs, we know that it's an issue with the bind variables!

Also, since you are building the sql dynamically, there is a chance that the host/bind variable syntax is malformed.

Are you building the string that contains the update sql in pl/SQL?

I have not written a C program in eons, sorry!

How do you define the RAM work area (PGA) in pro*c?

Maybe with an "alter session" or a malloc()?

These working pro*c examples may help:

https://docs.oracle.com/cd/A57673_01/DOC/ap...ch05.htm#toc076

Lastly, make sure that you are using the syntax to trap all SQL codes!

Let us know! Good luck!



thanks Donald! I found out the issue was with one of values given to update, there was a space character in string literal, removing that helped
Go to the top of the page
 
+Quote Post
burleson
post Jul 19 2017, 06:39 PM
Post #6


Advanced Member
***

Group: Members
Posts: 13,492
Joined: 26-January 04
Member No.: 13



Great!

Glad that you got it working!


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 22nd August 2017 - 02:17 AM