How to Read Oracle Database table object data using Labveiw

[Bharat_Waj]

This is database in Oracle table named NUMBER_TESTING;

Name                Null?      Type

NUMBER                         VARCHAR2(20)
DATE_TIME                     DATE
TESTDATA                     MESUREITEM

where TESTDATDA is (ITEM_CODE,ITEM_VALUE) If i use the below command

SELECT LOT_NUMBER,DATA_TIME, (SELECT ITEM_VALUE FROM TABLE(TESTDATA) WHERE

ITEM_CODE='VOLTAGE') FROM NUMBER_TESTING;

in Oracle SQL Plus i can get the value stored for item code VOLTAGE.

But i am not sure how to do the above using Labview 7.1 & Database Toolkit. Whenever i try

to use labview it always returns me error as defined below.

ORA-00911: Invalid Identitfier; datatype not supported.

It would be great if someone shares their experience for the above issue & give me some

valuable suggesetion.

Thanks

Bharat

[NathanT]

Bharat,
     
Have you taken a look at the database examples in LabVIEW?  They can be found in the Example Finder under Toolkits and Modules>>Database Connectivity.  What version of Oracle are you using?  According to the readme file linked below, if you are using Oracle 7 or 8 you will need to install the Oracle Client Software on your machine.

Database readme [link removed; article no longer available]

Where in LabVIEW is the ORA-00911 error being generated? 

Here is a tutorial on using the Database Connectivity Tools in LabVIEW
Tutorial  [link removed; article no longer available]

Message Edited by Support on 01-10-2008 11:29 AM

[Bharat_Waj]

Hello Nathan,

Thanks for your reply.

I have attached 2 different LV vi's i tried for this issue. Perhaps it will give a clear picture of the error message i receive. Whenever the vi try to execute SQL query it shows error in Conn Execute.vi

I can read data from database if the datatype is string, DT,etc. I only face issue to read object as it always shows datatype not supported.

Looking forward for your valuable comments.

rgds,

Bharat

[NathanT]

Bharat,
     The Word document with the screen shots was helpful.  The only Knowledge Base article that I found with the -2147217900 error is this one:

Knowledge Base 2U3E287V

Please take a look at the ANSI SQL format and see if your database object names are causing the error.

Thanks,

Nathan

Message Edited by NathanT on 08-08-2006 07:18 PM

[NathanT]

Bharat,
       I just thought of something else.  What is MESUREDATA?  Is it a cluster of data?  What are ITEM_CODE and ITEM_VALUE datatypes?

Thanks,

Nathan

[Bharat_Waj]

Hi nathan,

Thanks for your reply.

Please find the attachment for description of Measure Data. This object is not created by LV & it was created using Oracle SQL Plus. I tried to create similar object using LV but in vain.

Also there should not be any issue related to object names as there is no problem in writing data's to these objects names using the below SQL command

insert into NUMBER_TESTING(LOT_NUMBER,DATE_TIME,TESTDATA) values ('123456',SYSDATE,MESUREITEM(OBJ_ITEM('voltage','100')))

I only face problems to read value stored against these objects. I am not sure what SQL query i should send to read values stored against these objects.

Looking forward for your comments

thanks
Bharat

[Matt_S.]

As soon as you find out what command it takes to do the read in ASCII we could definitely get that going for you in LabVIEW. Have you had success with any other ADE trying to get values from that object?

Regards,
Matt S.

[Bharat_Waj]

Hi Matt,

Using Visual Basic it's possible to read the value from this database table. However, i still cannot find the way to read using LABVIEW. Any good suggestions? 

This command works in both Oracle SQL Plus as well as VB but not in Labview.

SELECT LOT_NUMBER,DATA_TIME, (SELECT ITEM_VALUE FROM TABLE(TESTDATA) WHERE ITEM_CODE='VOLTAGE') FROM NUMBER_TESTING;

It is really strange when i can write data using LV to this database table why not retrieve data??

insert into NUMBER_TESTING(LOT_NUMBER,DATE_TIME,TESTDATA) values ('123456',SYSDATE,MESUREITEM(OBJ_ITEM('voltage','100')))

Looking forward for some suggestions

Thanks

Bharat