Advanced Queueing (Oracle 8i) & VB6 ORA-25215 - OO4O and Wizards(Archived)

Hi all.
I am trying to dequeue a queue I created in Oracle 8i using VB6 & OO4O, however when I call Q.Dequeue I get the error ORA-25215 'user_data type and queue type do not match'.
Does anyone have any ideas ??
I am new to Oracle & queues so I appologise for any stupid mistakes.
The Queue payload type:
CREATE type aq.message_type as object (
subject VARCHAR2(30),
text VARCHAR2(80));
The suspect code:
Dim Q As OraAQ
Dim QMsg As OraAQMsg
Set OO4OSession = CreateObject("OracleInProcServer.XOraSession")
Set empDb = OO4OSession.OpenDatabase("AQ_DUAL", "aq/aq", 0)
Set Q = empDb.CreateAQ("AQQUEUE")
'Specify the message value.
Set QMsg = Q.AQMsg(ORATYPE_OBJECT, "MESSAGE_TYPE", "AQ")
Set QMsg = Q.AQMsg
'HERE for Error ORA-25215 'user_data type and queue type do not match'
Q.Dequeue

Related

Special Characters

Special character problem
We store xml-messages in a Oracle 9i Release 2 database in a column of type CLOB.
These messages can contain special characters like éêûä etcetera. These special characters get correctly displayed within TOAD and available applications on the database.
The problem arises when using a combination of VB.NET webservices and Oracle's ODP.NET (versie 9.2.0.7.0) to retrieve messages out of the database. At this moment the xml-messages get corrupted at retrieval and as a result it becomes a invalid XML that does not validate against the schema's (XSD's). This results in a error from the webservice instead of the result for the retrieve request.
The xml-messages also get inserted using the webservices, but like I already said everything looks good in TOAD. The webservices run on a Microsoft IIS webserver.
Is there anybody who knows where to search for the problem or better knows the solution ?
Regards,
Jørn Schurink 
The problem posted by Jørn is also discussed in this thread:
Re: ORA-31011: XML parsing failed for AL32UTF8
We've been able to reproduce the problem. It only occurs when using a stored procedure to retrieve XML data containing special characters.
For some reason tags after the special characters get "missing", which results into invalid XML.
Here follows information to reproduce the problem.
Versions
------------------------------------
Oracle 9.2.0.6
Oracle Client 10.1.0.2
ODP.NET 10.1.0.400 (also tried 9.2.0.4 and 9.2.0.7)
.NET Framework version 1.1 (with and without service pack 1 for Framework 1.1)
Table definition
------------------------------------
DROP TABLE TEST CASCADE CONSTRAINTS;
CREATE TABLE TEST
(
XMLELEM SYS.XMLTYPE
)
LOGGING
NOCACHE
NOPARALLEL;
Stored procedure
------------------------------------
-- "Set define off" turns off substitution variables.
Set define off;
CREATE OR REPLACE FUNCTION CECURE_CLIENT.FNC_GET_XML_TEST RETURN XMLTYPE IS
tmpVar XMLTYPE;
/******************************************************************************
NAME: FNC_GET_XML_TEST
PURPOSE:
REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 4/3/2006 1. Created this function.
NOTES:
Automatically available Auto Replace Keywords:
Object Name: FNC_GET_XML_TEST
Sysdate: 4/3/2006
Date and Time: 4/3/2006, 9:38:17 AM, and 4/3/2006 9:38:17 AM
Username: (set in TOAD Options, Procedure Editor)
Table Name: (set in the "New PL/SQL Object" dialog)
******************************************************************************/
BEGIN
SELECT XMLELEM INTO tmpVar FROM TEST where rownum = 1;
RETURN tmpVar;
END FNC_GET_XML_TEST;
/
XML DATA INSIDE TABLE
------------------------------------
<kks_transaction xmlns="http://schemaserver/schemas/1.0/">
<transaction_source_code>GAM</transaction_source_code>
<contract_request_nr>3003247</contract_request_nr>
<transactiontype_code>AKP</transactiontype_code>
<transaction_status>10</transaction_status>
<event_id>10000000235</event_id>
<request_registration_date>2006-02-24</request_registration_date>
<contract_start_date>2006-03-10</contract_start_date>
<contract_duration>1</contract_duration>
<action_code>KKS</action_code>
<card_start_date>2006-02-24</card_start_date>
<card_end_date>2008-03-09</card_end_date>
<contract_revenue>5300</contract_revenue>
<card_rail_plus>false</card_rail_plus>
<days_of_choice>false</days_of_choice>
<choice_of_customer>true</choice_of_customer>
<request_channel>WWW</request_channel>
<bankaccount>
<bankaccount_type>G</bankaccount_type>
<bankaccount_nr>1234567</bankaccount_nr>
<account_owner_name>F. Püna-Segura Closs</account_owner_name>
<account_owner_city>EDE GLD</account_owner_city>
<direct_debit>true</direct_debit>
</bankaccount>
<request_nr>3003247</request_nr>
<request_type>50</request_type>
<payment_amount>5300</payment_amount>
</kks_transaction>
.NET Code snippet (download project files -> http://www.nieuwegiessen.nl/work/ns/TestXMLType.zip)
------------------------------------
' Connection string
Dim dConnectionString As String = "User Id=test;Password=test;Data Source=testdb"
' Create connection
Dim dConnection As OracleConnection = New OracleConnection(dConnectionString)
Try
' Open connection
dConnection.Open()
' Create new Command
Dim dCommand As New OracleCommand
With dCommand
.CommandText = "FNC_GET_XML_TEST"
.CommandType = CommandType.StoredProcedure
.XmlCommandType = OracleXmlCommandType.None
.Connection = dConnection
End With
' Create output parameter
Dim dParameter As New OracleParameter
With dParameter
.ParameterName = "RETURN_VALUE"
.OracleDbType = OracleDbType.XmlType
.Direction = ParameterDirection.ReturnValue
End With
' Add parameter
dCommand.Parameters.Add(dParameter)
' Execute command
dCommand.ExecuteNonQuery()
' Cast result to OracleXmlType and save cast into xmlResultsOracle
Dim xmlResultsOracle As OracleXmlType = CType(dParameter.Value, OracleXmlType)
' Show results as string
Console.WriteLine(xmlResultsOracle.Value)
' Get XMLDocument
Dim xmlResultsDoc As XmlDocument = xmlResultsOracle.GetXmlDocument()
' Show XMLDocument
Console.WriteLine(xmlResultsDoc.InnerXml)
Catch ex As Exception
' An exception occured
Console.WriteLine(ex.Message + Environment.NewLine + ex.StackTrace + Environment.NewLine + ex.Source)
Finally
' Close connection
If dConnection.State = ConnectionState.Open Then
dConnection.Close()
End If
Console.ReadLine()
End Try 
An important issue I forgot to mention in the previous post is the character set used by the client/ server etc.
Client: WE8MSWIN1252
Server: WE8MSWIN1252

Dequeue XML from Oracle AQ and insert data into table

Using PL/SQL in a stored procedure, I need to dequeue a message from an Oracle AQ queue, parse the XML content and insert the data in an Oracle table. This is being done in Oracle 9.2.x.
The payload for the message consists of 2 attributes, APPLICATION_DATA (VARCHAR2(32) and MESSAGE_BODY (CLOB). The MESSAGE_BODY is an XML document. A sample of the message is shown below:
(M_NAME=MSG_LAW_WR_CRE_UPD, <?xml version = "1.0"?>
<MSG_LAW_WR_CRE_UPD ID = "WMIS-4744" SRC_SYSTEM_CODE = "WMIS" SRC_SYSTEM_INSTANCE = "WMIS">
<WR:CMP_LAW_WR_CRE_UPD xmlns:WR = "WR" >
<WR:DATETIME>12/19/2005</WR:DATETIME>
<WR:RUSCODE>100</WR:RUSCODE>
<WR:WRNO>38213</WR:WRNO>
</WR:CMP_LAW_WR_CRE_UPD>
</MSG_LAW_WR_CRE_UPD>)
This message needs to be dequeued from a queue named INBOUND, parsed and inserted into a table named INFO_TO_ACCT (CREATEDATE date, RUSCODE varchar2(10), WRNO number).
Any help would be appreciated. 
SELECT EXTRACTVALUE(xmltype('<?xml version = "1.0"?>
<MSG_LAW_WR_CRE_UPD ID = "WMIS-4744" SRC_SYSTEM_CODE = "WMIS" SRC_SYSTEM_INSTANCE = "WMIS">
<CMP_LAW_WR_CRE_UPD>
<DATETIME>12/19/2005</DATETIME>
<RUSCODE>100</RUSCODE>
<WRNO>38213</WRNO>
</CMP_LAW_WR_CRE_UPD>
</MSG_LAW_WR_CRE_UPD>'),'//DATETIME/text()') FROM DUAL;
hope this helps.

[Solved] Error in record creation or modification via OraDynaset

Hello everybody,
I am obtaining an error, and I have to admit that I am searching for the reason for about a few hours. So, I am wondering whether I am going to search still a long time.
The topic is to create or modify an Oracle table record via a recordset.
Here is the table description :
CREATE TABLE tabMoteur(
     tymClef      CHAR( 5),
     motIndiceMoteur     CHAR( 5),
     motCouple      NUMBER(4, 0),
     motPuissance      NUMBER(4, 0),
     motDateAF      DATE,
     CONSTRAINT pkIdxMoteur PRIMARY KEY(tymClef, motIndiceMoteur)     
);
Here is the code :
-------- begin --------------
Dim OraSession As OraSession
Dim OraDatabase As OraDatabase
Dim RsMoteur As OraDynaset
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
Set DB = OraSession.OpenDatabase("USER", "USER/USER", 0&)
Set RsMoteur = DB.CreateDynaset("SELECT * FROM tabMoteur", 0&)
With RsMoteur
  Set OraFields = .Fields
  .AddNew
    OraFields("tymClef") = 'ALA'
    OraFields("motIndiceMoteur") = '795'
  .Update
  .Refresh
End With
------------ end ------------
I put a pause on .Update, and there I display OraFields("tymClef") and OraFields("motIndiceMoteur").
For each, I obtain a value of ("?").
Of course I did that because I observed the error after an update.
Does this feel consistent to you ?
I hope not ...
But where is the mistake ?
CHAR(5) seems to be a correct data type to receive 'ALA', is not it ?
I also tried to create OraFields after the AddNew, just in case ...
I have the same error on another table with a one field primary key.
I obtained correct results a few days ago on other recordsets, so I presume there is no problem with the connection to the database. 
Got it !
I am familiarized with Microsoft's syntax where Value is the default property of most objects, particularly a field of a recordset.
This is not the case with OraField, and I must type :
OraFields("tymClef").Value = "ALA"
Thanks to people who took time to read my question, and, I presume, to search for an answer.

Oracle ODBC driver with Access Application

I get a problem when using MS-Access 2007 DAO recordset built on table hosted by an Oracle database 10g and connected thru ODBC
The table scheme is :
idPartenaire : number(11)
libelle : varchar2(250)
code : varchar2(5)
actif : number(1)
idCollege :numner(11)
idPartenaire is generated by a sequence read in a trigger fired before insert on th table.
The code I use in Access is :
The query associated with the form is :
"select * from table1"
and the event procedure connected to a button on the form is :
Private Sub Commande0_Click()
Dim rs As DAO.Recordset
Me.Recordset.AddNew
Me.Recordset!libelle = CStr(Rnd) ' some value
Me.Recordset!code = “”
Me.Recordset!actif = -1
Me.Recordset!idCollege = 1
Me.Recordset.Update
Me.Recordset.Bookmark = Me.Recordset.LastModified
Dim idtemp As Long
idtemp = Me.Recordset.idPartenaire
Me.Requery
Me.Recordset.FindFirst "idPartenaire = " & idtemp
Set rs = Nothing
End Sub
When I run this code, I get an error 3167 (current record is deleted) on the line :
idtemp = Me.Recordset.idPartenaire
If I change the ligne :
Me.Recordset!code = “”
By :
Me.Recordset!code = “xxxx”
or if I comment this line, the error does not appear and I get the right value in idtemp variable.
I tried also with the same table in a SQL Server database table. In this case it works fine in all cases.
So, I seems that the problem is due to the Oracle ODBC driver. I tried with different versions of Oracle ODBC drivers (v10.0.2.4, v11.1.6)
I search on Internet but I have not found any explanation or solution.
Does anybody experiment the same problem (and find a solution)?
Gilles Roussel.

Error 3001

Does someone know what i am doing wrong here?
i get an error 3001: Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another which is coming from the last line below..?
'Initialize ADO objects
     Set rsAcct = CreateObject("ADODB.Recordset")
     
     'Generate Account sql string
     strSQLAcct = "SELECT Account FROM dbo.TG_Account"
     'Intiialize the block processor (Pass Active API & Script object References)
     Set objBP = CreateObject("upsWBlockProcessorDM.clsBlockProcessor")
     objBP.Initialize API, SCRIPTENG
     
     'Call the Connect Action of the integration block to connect to the TG Table
     Set API.IntBlockMgr.IntegrationMgr.PobjIntegrate = objBP.ActConnect("AfterValidate")
     
     'Open Account recordset using the varCon property of the block processor as the connection
     rsAcct.Open strSQLAcct, API.IntBlockMgr.IntegrationMgr.PobjIntegrate.varCon, 3, 1 
Ben,
If dbo.TG_Account is a table in your FDM database, then you should be able to populate the recordset with:
strSQLAcct = "SELECT Account FROM dbo.TG_Account"
Set rsAcct = DW.DataAccess.farsFireHose(strSQLAcct, False) 
thank you Larry.
This is working indeed.
cheers

Categories

Resources