How to get the column names of a Oracle table in Oracle Apps application

[Manoj Pulipati]

Hi Everyone,

Please let me know how to get the column names of a Oracle table in Oracle Apps application using QTP.

My Requirement:

I am having an oracle table in a application. My requirement is to validate whether Emp No and Emp Name columns are present in that table or not. 

Note :

We can get the column names in a table for a Web or Sibel application but i need the syntax for oracle apps application.

Thanks in Advance,
Manoj

[Shalabh Dixit]

Hi Manoj,

Following are the functions you can use. Read the functions and use them as per your usage. They are generic and will work on SQL and Oracle database.


''========================================================================================
'(1.) Function to Connect to the Database
''========================================================================================
Function ConnectDatabase(Byval strConName,Byval strDSNName,Byval strServerName,Byval strDataBaseName,Byval strUserID,Byval strPassword)
    'MsgBox "Inside 'ConnectDatabase' Function"
    On Error Resume Next
    'Assign Database Connection Information to the DB Variables
    DataSource = strDSNName
    Server = strServerName
    UserID_DB = strUserID
    Password_DB = strPassword
    DatabaseName_DB = strDataBaseName
    con=strConName
    Dim connItems(2)
    Dim connState
    'Declare Database Recordset variable
    conString = cstr("DSN="&DataSource&chr(59)&"Server="&Server&chr(59)&"UID="&UserID_DB&chr(59)&"Password="&Password_DB&chr(59)&"DATABASE="&DatabaseName_DB)
    'Create Database Connection Object
    Set conn = CreateObject("Adodb.Connection")
    'Open Database Connection
    conn.Open conString
    'Verify the Successful Database Connection Establishment
    If conn.State=1 Then
      
        Reporter.ReportEvent micPass,"Database Connection Success"," Database connection for "&DatabaseName_DB &"Database has been established Successfully"
        'MsgBox "Connection Estblished Successfully"
    Else
       
        Reporter.ReportEvent micFail,"Database Connection Failure"," Database connection for "&DatabaseName_DB &"Database is not been established successfully"
        'MsgBox "Connection Not Estblished"
    End If
    connItems(0) = conn.State
    connItems(1) = conn
  
    ConnectDatabase = connItems
End Function
''========================================================================================



''========================================================================================
'(15.) Function to Get All The Column-Names of a Table
''========================================================================================
Function GetColumnNamesFromTable(DBConnectionProperty,DBConnectionState,DBLanguage,str_TableName)
    On Error Resume Next
    If DBConnectionState = 1 Then
        If UCase(DBLanguage) = "SQL" Then
            str_ColumnNames_Query = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" & str_TableName & "'"
        Else If UCase(DBLanguage) = "ORACLE" Then
                str_ColumnNames_Query = "SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = '" & str_TableName & "'"
        End If
        End If
        arr_ColumnNameList = ExecuteSQLQuery(DBConnectionState,DBConnectionProperty,str_ColumnNames_Query)
        GetColumnNamesFromTable = arr_ColumnNameList
    End If
End Function
''========================================================================================

''========================================================================================
'(18.) Function to get Column number of columns with particular Name
''========================================================================================
Public Function IsColumnExist(DBConnectionState,DBConnectionProperty,DBLanguage,str_TableName,str_ColName)
    On Error Resume Next
    bln_IsColExist = False
    int_Col_Number = 0
    arr_ColNames_List = GetColumnNamesFromTable(DBConnectionProperty,DBConnectionState,DBLanguage,str_TableName)
    For int_ColName_Index = 0 To UBound(arr_ColNames_List)
        If Instr(1,arr_ColNames_List(int_ColName_Index),str_ColName,1) > 0 Then
            bln_IsColExist = True
            int_Col_Number = int_Col_Number + 1
            Exit For
        Else
            int_Col_Number = int_Col_Number + 1
        End If
    Next
    IsColumnExist = bln_IsColExist
End Function
''========================================================================================




Regards
Shalabh Dixit
My QTP Blog

--
You received this message because you are subscribed to the Google
"QTP - HP Quick Test Professional - Automated Software Testing"
group.
To post to this group, send email to Mercu...@googlegroups.com
To unsubscribe from this group, send email to
MercuryQTP+...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/MercuryQTP?hl=en

[Manoj Pulipati]

Shalabh,

I want to get columns name from oracle apps application not from oracle database.

Thanks,
Manoj

[Rahul Bengali]

Did you find any solution to this problem. If yes please let me know.

[Amit Kumar]

It may help you...

Dim con,rs
Set con=createobject("adodb.connection")
Set rs=createobject("adodb.recordset")
con.provider="microsoft.jet.oledb.4.0"
con.open"d:\db.mdb"
rs.open"select*from EMP",con
For i = 0 To rs.Fields.Count - 1
column_name=rs.Fields(i).Name
Msgbox column_name
Next

--

--
You received this message because you are subscribed to the Google
"QTP - HP Quick Test Professional - Automated Software Testing"
group.
To post to this group, send email to Mercu...@googlegroups.com
To unsubscribe from this group, send email to
MercuryQTP+...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/MercuryQTP?hl=en
 

---
You received this message because you are subscribed to the Google Groups "QTP - HP Quick Test Professional - Automated Software Testing" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mercuryqtp+...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Rahul Bengali]

Sorry but I am working on a frontend oracle apps.

I dont need to connect to db server etc. 

there is a control called oracle table on front end and I need column names for that.

[Kasturi]

Hi Manoj,

Did you find any solution to this problem? could you please let me know?

However, I need to retrieve data from the table cell in a Oracle apps application.

[Kasturi]

Hello Rahul,

Did you find any solution to this? Could you please let me know?

I need to retrieve data from the table cell on a frontend oracle app.

Regards
Kasturi

[Varun Tomar]

Dear Friend,

The first internet portal "www.HireDoor.com" which give you freedom to hire as much as possible and find work with no limit across the world.

The basic advantages of portal are:-

1. No cost at all.Totally free.

2. No conditions of type of Use.Use as you like.

a. Work as freelancer.

b. Find Jobs.

c. Post Jobs.

3. No Rescritions limit to use on monthly basis.

4. Share your Personal information.No need to give any mediation fees or charge.

5. Less Than one minute registration with your email address only.

Register Now http://hiredoor.com/sign_upvad.html

Go through the link "Howitswork" if you find any problem.

You can give any feed back by link "feed back" or directly mail us "hire...@gmail.com".

Best of Luck,

Hire Door Email Support Team

--

--
You received this message because you are subscribed to the Google
"QTP - HP Quick Test Professional - Automated Software Testing"
group.
To post to this group, send email to Mercu...@googlegroups.com
To unsubscribe from this group, send email to
MercuryQTP+...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/MercuryQTP?hl=en
 

---
You received this message because you are subscribed to the Google Groups "QTP - HP Quick Test Professional - Automated Software Testing" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mercuryqtp+...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

--
Regards

Varun Tomar

[udayanem]

Hi Manu,

Though i didnt work on OracleTable, here is one clue.

In WebTable, we use like WebTable.getROProperty("column names") ' which will return string with column names.
After that we can split the array based on ";"

str=WebTableObj.getROProperty("column names")
arr=split(str,";")

In the same way, can you try for OrableTable?

Thanks,
Uday

On Wednesday, 22 February 2012 22:10:33 UTC+5:30, manu wrote: