Addtable and ORATYPE_NUMBER - OO4O and Wizards(Archived)

i used Addtable in Visual Basic 6.0
.Parameters.AddTable "opt_idou_qy", gcORAPARM_OUTPUT, gcORATYPE_NUMBER, intRec,
Set objOpIdouQy = .Parameters("opt_idou_qy")
in PL/SQL, opt_idou_qy is a table of number(6).
but when it return to VB, its value = " 2". why is there many space before value?


How to insert Sdo_Geometry from VB

I have some queries for you guys. After trying several times, I still cannot make it work.
     Here is the scenario:
     I am retrieving a Polygon object from Oracle Spatial DB and I wanted to insert it into another Spatial Database.
I am using the Oracle Object for OLE (OO4O). The Polygons contain attributes as well. I intend to insert
the Attributes and the Geometries at the same time.
Please find my code below for your reference.
Public Function ConnectToOracle(sDBName As String, sUserAndPassword As String) As OraDatabase
Dim OraSession As OraSession
Dim OraDatabase As OraDatabase
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
Set OraDatabase = OraSession.OpenDatabase(sDBName, sUserAndPassword, ORADB_DEFAULT)
Set ConnectToOracle = OraDatabase
End Function
Public Function RetrieveRecord(sBldgID As String) As String
Dim oraDB As OraDatabase
Dim DS As OraDynaset
Dim Ordinates As OraCollection
Dim sdo_geometry As OraObject
Dim sql As String
Dim sGeom As String
Dim sBldg_ID As String, sMainBldg_ID As String, sEstate_ID As String, sCat_ID As String, sBldg_Type As String, sCat_Type As String
Dim sStatCode As String, sName As String, sMRC As String
Dim DteCreate, DteDelete, dteUpdate
Dim sAttributes As String
Set oraDB = ConnectToOracle("service1", "user1/pwd1")
sql = "select a.bldg_id,a.main_bldg_id,a.estate_id,a.cat_bldg_id,a.bldg_type, " & _
"a.cat_type,a.bldg_type,a.status_code,,a.centroid_mrc, a.dte_create,a.dte_delete, " & _
"a.dte_update, b.geometry from bldg a, bldg_geom b where a.bldg_id='" & sBldgID & "' and b.bldg_id=' " & sBldgID & "'"
Set DS = oraDB.CreateDynaset(sql, ORADB_DEFAULT)
If Not DS.EOF Then
Set sdo_geometry = DS.Fields("geometry").Value
If Not IsNull(sdo_geometry.Item("sdo_ordinates").Value) Then
Set Ordinates = sdo_geometry.Item("sdo_ordinates").Value
sBldg_ID = DS.Fields("bldg_id").Value
sMainBldg_ID = DS.Fields("main_bldg_id").Value
sEstate_ID = IIf(IsNull(DS.Fields("estate_id").Value), """", DS.Fields("estate_id").Value)
sCat_ID = DS.Fields("cat_bldg_id").Value
sBldg_Type = DS.Fields("bldg_type").Value
sCat_Type = DS.Fields("cat_type").Value
sStatCode = DS.Fields("status_code").Value
sName = DS.Fields("name").Value
sMRC = DS.Fields("centroid_mrc").Value
DteCreate = CDate(DS.Fields("dte_create").Value)
If (IsNull(DS.Fields("dte_delete").Value)) Then
DteDelete = DateTime.Date
DteDelete = CDate(DS.Fields("dte_delete").Value)
End If
If (IsNull(DS.Fields("dte_update").Value)) Then
dteUpdate = DateTime.Date
dteUpdate = CDate(DS.Fields("dte_update").Value)
End If
MsgBox "No geometry!"
End If
' Concatenate all the Geometries separated by comma
For i = 1 To Ordinates.Size
If i = 1 Then
sGeom = Ordinates(i)
sGeom = sGeom & "," & Ordinates(i)
End If
Next i
sAttributes = "'" & sBldg_ID & "','" & sMainBldg_ID & "','" & sEstate_ID & "','" & sCat_ID & "','" & sBldg_Type & "','" & _
sCat_Type & "','" & sStatCode & "','" & sName & "','" & sMRC & "','" & _
DteCreate & "','" & DteDelete & "','" & dteUpdate & "'"
End If
Set DS = Nothing
Call InsertSingleRecord(sAttributes, sGeom)
End Function
Public Function InsertSingleRecord(sAttributes As String, sGeometry As String)
Dim oraDB As OraDatabase
Dim sql As String
Set oraDB = ConnectToOracle("service2", "user2/pwd2")
sql = "Insert into bldg(bldg_id,main_bldg_id,estate_id,cat_bldg_id,bldg_type,cat_type,status_code,name,centroid_mrc,geom) " & _
"values ( " & sAttributes & ", " & _
"2003, NULL, NULL, " & _
"MDSYS.SDO_ELEM_INFO_ARRAY (1,1003,1), " & _
"MDSYS.SDO_ORDINATE_ARRAY ( " & sGeometry & " )" & _
") " & _
oraDB.ExecuteSQL sql
Set oraDB = Nothing
End Function
After running this code, I successfully retrieved the data and able to manipulate it by concatenating them and stored in a variable to be inserted to database. During the insertion comes the problem, the error message is "ORA-00907: missing right parenthesis".
I tried to manually execute the SQL Statement in the SQL Plus and it gives me the same error message. My observation is that the SQL Plus truncates the statement at a certain length. By the way, I checked the length of the resulting SQL from VB and it gives me 3096 characters. I am not sure if there is a limitation on the SQL statement length.
I cannot figure out what's wrong with this code. I hope you can help me.
I would appreciate any feedback from you guys and thank you in advance.
Best Regards,
Arnold Higuit
Hi Arnold,
Can you write the statement you are executing to a file so we can see the contents of the variables? The error message seems pretty specific, and if it is accurate we should be able to see where the comma is missing.
There is a limit of 999 ordinates in the ordinate array when executing a sql statement without a bind variable (that is, you cannot hardwire more than 999 values).
Feel free to post the expanded version of your statement and folks can take a look at it.
Hi Daniel,
Thank you for your reply. Here is the SQL Statement that was stored in the variable. I extracted this statement from the immediate window of VB IDE while debugging the code.
Insert into bldg(bldg_id,main_bldg_id,estate_id,cat_bldg_id,bldg_type,cat_type,status_code,name,centroid_mrc,geom) values ( 'P0750109270947202318','P0750109270947202318','"','P0750109270947202318','BT6','GO1','SC2','Istana','19489D8262', MDSYS.SDO_GEOMETRY ( 2003, NULL, NULL, MDSYS.SDO_ELEM_INFO_ARRAY (1,1003,1), MDSYS.SDO_ORDINATE_ARRAY ( 103.843218549901','1.30748186974166','103.843111698469','1.30753323638373','103.843175054448','1.3076666268715','103.843218549901','1.3076457365646','103.843286364106','1.30761206827051','103.843309190232','1.30765384888431','103.84336940088','1.30762445775988','103.84334855182','1.30758014498768','103.843450820054','1.30752724096372','103.843387104607','1.30739810088471','103.843380274743','1.30740307476731','103.843372007012','1.30738697747455','103.843413076066','1.30736617760188','103.843373624612','1.30728487723001','103.843367873147','1.30728704765151','103.843359875016','1.30727303034601','103.843375601678','1.30726724255536','103.84337901661','1.30727167383258','10
218549901','1.30720498324404','103.84322585379','1.30720205696391','103.843278414575','1.30731373992449','103.843285873506','1.30730731909423','103.843297196702','1.30733381632332','103.843290906038','1.30733589631059','103.843326313493','1.30741005237836','103.843336378556','1.30740326981119','103.843341860421','1.30741855319589','103.843218549901','1.30748121514446 )) )
The error message is:
Run-time error '440':
SQL Execution error, 0RA-00907: missing right parenthesis
And here is the equivalent statement and error in SQL Plus after I copied and pasted the code.
SQL> Insert into bldg(bldg_id,main_bldg_id,estate_id,cat_bldg_id,bldg_type,cat_type,status_code,name
2 values ('P0750109270947202318','P0750109270947202318','"','P0750109270947202318','BT6','GO1','S
_ARRAY ( 103.843218549901,1.30748186974166,103.843111698469,1.30753323638373,103.843175054448,1.3076
4 3.84338998034,1.30726751385805,103.843385846475,1.30725919390898,103.843432397393,1.30723839403
5 19495298,1.3068781308289,103.843067214483,1.30697272503243,103.843021292631,1.30699614749774,10
6 218549901,1.30720498324404,103.84322585379,1.30720205696391,103.843278414575,1.30731373992449,1
89,103.843218549901,1.30748121514446 )) )
SQL> /
ERROR at line 4:
ORA-00907: missing right parenthesis
We are actually dealing with large polygons and this one is just an example of a building outline.
The other objects we have are Boundaries and Islands. I am worried about the 999 ordinates limitation. I can just hope that our largest Boundary would not reach that number.
I hope you can spot the problem. Once again many thanks.
Best Regards,
Hi Arnold,
When I unwrapped the stuff that was posted and cleaned it up I saw this:
Insert into bldg(bldg_id,main_bldg_id,estate_id,cat_bldg_id,bldg_type,cat_type,status_code,name,centroid_mrc,geom) values ( 'P0750109270947202318','P0750109270947202318','"','P0750109270947202318','BT6','GO1','SC2','Istana','19489D8262', MDSYS.SDO_GEOMETRY ( 2003, NULL, NULL, MDSYS.SDO_ELEM_INFO_ARRAY (1,1003,1),
MDSYS.SDO_ORDINATE_ARRAY( 103.843218549901','1.30748186974166','103.843111698469','1.30753323638373','103.843175054448','1.3076666268715',
'103.843218549901','1.30748121514446 )) )
If you notice, the first and last values in the SDO_ORDINATE_ARRAY are missing the open and close single quotes respectively. This being numeric, you don't need the single quotes, but if you use them they should surround the value.
You are right to worry about the 999 ordinate limit, and you may want to get some practice with bind variables.
Hope this is it. 
Hi Daniel,
I also noticed the quotes and I actually removed it already since it is of numeric type. After running the statement, the error is still the same.
I simplified the statement and reduced the number of geometries to be inserted and it works in the SQL Plus. But in VB, the program hangs even if I just inserted 4 geometries which is very much valid statement in SQL Plus.
I continually did this method just to track the minimum required length of sql statement and I found out that it indeed limited to approx 999 characters. Here are the valid statements I generated. There are two of them, one with a single Attribute (bldg_id) and the rest are geometries and the other one is a set of Attributes and the rest are a reduced number of geometries.
Insert into bldg (bldg_id,geom) values ( 'P0750109270947202318', MDSYS.SDO_GEOMETRY ( 2003, NUL
L, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(103.843218549901,1.3074818697
Insert into bldg(bldg_id,main_bldg_id,estate_id,cat_bldg_id,bldg_type,cat_type,status_code,name
,centroid_mrc,geom) values ( 'P0750109270947202318','P0750109270947202318','"','P0750109270947202318
','BT6','GO1','SC2','Istana','19489D8262', MDSYS.SDO_GEOMETRY ( 2003, NULL, NULL, MDSYS.SDO_ELEM_INF
O_ARRAY (1,1003,1), MDSYS.SDO_ORDINATE_ARRAY ( 103.843218549901,1.30748186974166,103.843111698469,1.
475,1.30725919390898 )));
I came to a conclusion that there is no way I can insert the SDO_GEOMETRY into Oracle Spatial using OO4O. The program hangs. By the way we are using Oracle version
I think I need to use some other methods. You've mentioned about Bind Variables. I am not very familiar with this as I'm just an Oracle newbie. Can you please refer me to some articles or tutorials on how to apply this or better still if you have some examples to share with me. I appreciate it very much.
To give you some idea on the application I am currently working on, I am developing a middle tier for the AutoCAD and Oracle Spatial. We are migrating our DWG based format into centralized Oracle Spatial DB. We are using VBA to do this.
If you came across with this same application, your advise or recommendation would certainly be valued.
Thank you.
Best regards,
Just Upgrade your AutoCAD to Autodesk MAP.
Map is the GIS Vertical for Autocad.
The functionallities for storing and retreiving data from and to Oracle Spatial are all their.
Good luck
Hi Luc,
Thanks for the reply. I am fully aware of Map capability regarding Oracle Spatial, but it is a pity that our current Map version is 5 and our Oracle version is, which means that they are not compatible at all. Based on my experience on these two Apps, during the connect process, the AutoCAD Map simply shut off. Another thing is that our application is heavily customized which we cannot simply depend on the native functionalities of Map. That is why we are opting for OO4O. But having learned the limitations of OO4O in using transaction insert to Spatial DB, the only option I'm considering now is through PL/SQL.
I haven't tried passing an SDO_GEOMETRY from OO4O to PL/SQL. For those experts who has some sample codes, appreciate it very much if you can share it. Thank you.
Best regards,

binding with a LIKE query

I am using PHP and ORACLE and am having trouble with binding. I can get a basic bind to work, but I am having trouble with a more complex query like this:
$oracle_search_name = 'some input text';
$query1 = "SELECT source_title FROM records WHERE UPPER(source_title) LIKE UPPER('%$oracle_search_name%')";
It works OK without binding, but how can I replace $oracle_search_name with a bind variable? Simply replacing the $ sign with a colon doesn't seem to work. 
Try this.
-- cj
echo "<pre>";
$conn = oci_connect("hr", "hr", "//localhost/XE");
$query = "select last_name from employees
          where upper(last_name) like upper('%'||:bv||'%')";
$bv = "to";  // string to match
$stid = oci_parse($conn, $query);
oci_bind_by_name($stid, ":BV", $bv);
oci_fetch_all($stid, $res);
echo "</pre>";
?>For tuning tips see
Hey cj,
Thanks for the help! That seems to do the trick.
I noticed that you capitalized the bind variable in the OciBindByName() function. Is that required? Here is my example:
OciBindByName($s, ":MY_BIND_VARIABLE", $my_bind_variable, -1); // Capitalized ":MY_BIND_VARIABLE"
Also, another question: Will Oracle automatically understand to bind multiple instances of the bind variable like this?
SELECT tid, col1, col2, col3 FROM test1 WHERE UPPER(col1) LIKE UPPER('%'||:my_bind_variable||'%') OR UPPER(col2) LIKE UPPER('%'||:my_bind_variable||'%')
That seems to work correctly but I am having trouble finding a detailed reference online about binding in Oracle.
Thanks again for the input. 
My random capitalizing is a habit. Oracle is case insensitive in this
When a bind variable name is repeated in a statement, you only need to
call oci_bind_by_name() once.
There is some a lot of information if you search for "bind" in the
Oracle doc at:
The most comprehensive section on binding that I found was
though not all of it applies to PHP.
Depending on your actual statements, I wonder if using Oracle 10g
regular expressions functions like REGEXP_LIKE would be easier/faster
than your UPPER calls.
-- cj

Problems with parameters passed to package function via ODBC/ASP

If someone knows the answer to this one, I'll be extremely grateful!
I am trying to call a function from a package, from an ASP web page.
Depending on what I try, I get one of two errors:
1. Microsoft OLE DB Provider for ODBC Drivers (0x80040E10)
No value given for one or more required parameters
2. Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Oracle][ODBC]Syntax error or access violation.
I am able to call this function through SQL Developer, using the following code:
p_result varchar2(1);
p_code varchar2(3);
p_code := 'W';
p_result := BANINST1.gb_stvcamp.F_CODE_EXISTS(p_code);
That's great, but I need to be able to do this in a web application. The problem seems to be getting the parameters through. The basic set up I'm trying looks like this:
Dim objCmd
Dim objParameter, objParameter2
Set objCmd = CreateObject("ADODB.Command")
objCmd.ActiveConnection = Db
objCmd.CommandType = adCmdStoredProc
Set objParameter = objCmd.CreateParameter
objParameter.Name = "p_code"
objParameter.Type = 200 'varchar
objParameter.Direction = 1 '1=input, 2=output, 3=in/out, 4=returnvalue
objParameter.Size = 3
objCmd.Parameters.Append objParameter
objCmd("p_code") = "W"
Set objParameter2 = objCmd.CreateParameter
objParameter2.Name = "output"
objParameter2.Type = 200 'varchar
objParameter2.Direction = 2 '1=input, 2=output, 3=in/out, 4=returnvalue
objParameter2.Size = 1
objCmd.Parameters.Append objParameter2
objCmd.CommandText = "call BANINST1.gb_stvcamp.f_code_exists(?);"
I've tried it with every imaginable combination of { } braces in CommandText, with and without "call", with p_code in the parenthesis, hard-coding a literal value as a the parameter, without the output parameter, changed order of the parameters, etc. etc.
In general, I get the first error (No value given for required parameter") if I include a ? as the parameter. Anything else (empty, commas, etc) produces the second error ('syntax error').
I am using the latest ODBC driver from Oracle which is running on my PC with the Oracle SQL Developer (which was required before I could install or use it).
Any clues? This is fairly important as we need to be able to call Banner (ERP) API's through other programs.... but I'm at the end of my rope on this one!
Thank you for any advice!!
I finally got this to work. Here is the code I used in my ASP, to call an Oracle package (function) that encapsulated a Banner API:
Set objParameter = objCmd.CreateParameter("p_result",200,4,1,"")
objCmd.Parameters.Append objParameter
set objParameter = objCmd.CreateParameter("p_code",200,1,3,"")
objCmd.Parameters.Append objParameter
objCmd("p_code") = "Z"

How can I use dbms_lob.substr in ASP.NET Razor code

I'm working on an internal site with statistics from vendor designed tables and I have to pull information out of a CLOB and render it as text for the users to read. The original query works beautifully in SQL Developer, but when I use it in Razor code for a Web Page I'm getting a ORA-06502 error. I have to presume that the error is due to using dbml_log.substr to extract the first 4000 characters of the CLOB.Here is the query in the form for SQL Developer:SELECT m.cdts, m.msg_num, UPPER(dbms_lob.substr(m.msg_text,4000,1)), m.cterm, m.cpers, d.destination, p.lname FROM destinations, d, messa m, persl p WHERE m.msg_num=d.item_number AND m.cpers=p.empid AND m.cdts BETWEEN :sdate AND :fdate AND m.msg_text NOT LIKE 'Ackn%' ORDER BY m.cdts DESC;In Microsoft's Razor syntax, the equivalent code is:var sDate = qYr.ToString() + qMon.ToString() + sDay.ToString() + sHr; var fDate = qYr.ToString() + qMon.ToString() + fDay.ToString() + fHr; var qry = "SELECT m.cdts AS cdts, m.msg_num AS msgNum, UPPER(dbms_lob.substr(m.msg_text,4000,1)) AS msgTxt, m.cterm AS cterm, m.cpers AS cpers, d.destination AS destination, p.lname AS lastName FROM destinations d, messa m, persl p WHERE m.msg_num=d.item_number AND m.cdts BETWEEN '20131212' AND '20131213' AND m.msg_text NOT LIKE 'Ackn%' AND m.cpers=p.empid ORDER BY m.cdts DESC"; Thank you in advance for any help I can get on this one.
What is the error text? If it's something like "numeric or value error: character string buffer too small", it could be that you're returning too much text in substr. It can support up to 4000 BYTES, not characters.
Sorry, I should have put that in there. It is returning "numeric or value error: character string buffer too small" Using your suggestion, I moved it down to dbms_lob.substr(m.msg_text,1000,1) and that seems to work just fine. That should cover most of our needs, so I'm not going to mess with it from there.  Thank you for the help on that!!!

Unable to return only Integer values (

My program is written in VB and opens an ODBC connection and
does a query. A loop is then run over the returned records.
The loop iterates the expected number of times, but all of the
retrieved integer data appears to be zeros (all integer values
were retrieved).
We are using the ActiveX ADO revision 2.0 library (it is what
the client has) in our environment and the clients. We are
accessing an Oracle database.
Our environment is Windows 2000 server, the client environment
is Windows NT 4.0.
Here is what I have demonstrated to myself so far:
I can correctly retrieve columns that are strings in the
database with a select statement.
I CANNOT retrieve columns that are integers in the database with
a select statement (both value types selected in the same select
This problem occurs at the client's locations, but not in ours.
I can correctly update columns in the database that are either
strings or integers.
Could this be setting specific?
The code that returns the data:
Set rsAudit = New ADODB.Recordset
QSQL = "Select messageid, type, message_audit_id from
kc_messageaudit where status = 'NOT PROCESSED';"
With rsAudit
'Allows cursor to move only forward
.CursorType = adOpenForwardOnly
'Provides record-by-record locking
.LockType = adLockPessimistic
'.Open QSQL, Cn, adOpenDynamic, , adCmdText
.Open QSQL, Cn, adOpenUnspecified,
adLockUnspecified, -1
MsgId = rsAudit("messageid")
SeqNumb = rsAudit("message_audit_id")
Any suggestions would be greatly appreciated. 
Additional Info. The test environment in which there are no
problems is