asked on pl/sql table definition
In PL/SQL (Oracle) how does one obtain information about a table (e.g. column names, column datatypes)?
I am not able to run queries on the database from a 3rd party application (like Oracle SQL Developer). I can run the queries from within my code, however (C#.NET 4.0).
Could I run this definition query and then temporarily export the text to a Label on the page?
I am not able to run queries on the database from a 3rd party application (like Oracle SQL Developer). I can run the queries from within my code, however (C#.NET 4.0).
Could I run this definition query and then temporarily export the text to a Label on the page?
Oracle DatabaseSQL
Last Comment
slightwv (䄆 Netminder)
ASKER
slightwv - thank you for the detailed reply. I had to edit my initial question, however, to include a limitation I am facing. Could you take a look at the additions and assist? Thanks.
How are you connecting with .Net that you cannot use sqlplus or similar tool?
>>Could I run this definition query and then temporarily export the text to a Label on the page?
You can do whatever you want with the resultset.
>>Could I run this definition query and then temporarily export the text to a Label on the page?
You can do whatever you want with the resultset.
ASKER
Thanks for the reply. What code would one use to send this text to a label?
If you are not familiar with ASP.NET, here is some sample code:
MyLabel.Text = "some text to send to label";
I'm not sure how to query and grab the string that I would assign to the label as listed above.
If you are not familiar with ASP.NET, here is some sample code:
MyLabel.Text = "some text to send to label";
I'm not sure how to query and grab the string that I would assign to the label as listed above.
I am familiar with .Net.
What data access provider are you using? I suggest Oracle's ODP.Net.
You are asking for several rows and columns to be returned from the query. What single value do you want in the label?
I suggest you use a gridview or old datagrid. Then you just bind the datasource to a datareader.
If you can explain your expcted results better, I'll do what I can to assist.
What data access provider are you using? I suggest Oracle's ODP.Net.
You are asking for several rows and columns to be returned from the query. What single value do you want in the label?
I suggest you use a gridview or old datagrid. Then you just bind the datasource to a datareader.
If you can explain your expcted results better, I'll do what I can to assist.
You can run the query from every environment that can connect to Oracle. Almost evry framework is able to connect to Oracle.
ASKER
Thanks for the information. My goal is to get all of the column names and column data types for this table. I need some data from it, but can't query it due to not having this information about the table structure.
I am using...
using Oracle;
using Oracle.DataAccess.Client;
using System.Data;
OracleConnection oraConn = new OracleConnection(Configura
OracleCommand oraCmd = new OracleCommand(sqlstmt1, oraConn);
oraCmd.CommandType = CommandType.Text;
I am using...
using Oracle;
using Oracle.DataAccess.Client;
using System.Data;
OracleConnection oraConn = new OracleConnection(Configura
OracleCommand oraCmd = new OracleCommand(sqlstmt1, oraConn);
oraCmd.CommandType = CommandType.Text;
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
I figured out how to connect to the database with a connection tool, but you assistance helped. Thanks!
Glad you were able to get the information you needed.
Since I had a close VB.Net example, I figured I would go ahead and tweak it and post it.
Maybe you or a future reader of this question can make use of it.
Since I had a close VB.Net example, I figured I would go ahead and tweak it and post it.
Maybe you or a future reader of this question can make use of it.
<%@ import namespace = "System" %>
<%@ import namespace = "System.Data" %>
<%@ import namespace = "Oracle.DataAccess.Client" %>
<%@ import namespace = "Oracle.DataAccess.Types" %>
<html>
<title>Gridview Sample</title>
<body>
<script language="vb" runat="server">
sub getdata(sender as object, e as eventargs)
Dim con As New OracleConnection("User Id=bud;Password=bud;Data Source=bud;")
Dim cmd as OracleCommand = new OracleCommand()
cmd.Connection = con
cmd.CommandType = CommandType.Text
cmd.commandText = " select column_name, data_type, data_length, data_precision from user_tab_columns where table_name=:table_name "
Dim param1 as OracleParameter = cmd.Parameters.Add("table_name", OracleDbType.varchar2, _
50, UCase(table_name.text), ParameterDirection.Input)
try
con.open()
genericDataGrid.DataSource = cmd.executeReader()
genericDataGrid.DataBind
catch ex as exception
response.write("Error: " & ex.message)
finally
con.close()
cmd.Dispose()
end try
end sub
</script>
<form runat="server">
Table name: <asp:textbox id="table_name" columns="40" runat="server" />
<asp:button id="runit" text="Go" onclick="getdata" runat="server" />
<br/>
<br/>
<asp:gridview id="genericDataGrid" runat="server" />
</form>
</body>
</html>
select column_name, data_type from user_tab_columns where table_name='YOUR_TABLE_NAM
There are other columns in that view that describe, precision etc...
If you are using sqlplus or compatible tool:
describe your_table_name;