We help IT Professionals succeed at work.

pl/sql table definition

adskarcox
adskarcox asked
on
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?
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
PL/SQL is Orale's procedureal code.  You just need SQL.

select column_name, data_type from user_tab_columns where table_name='YOUR_TABLE_NAME';

There are other columns in that view that describe, precision etc...

If you are using sqlplus or compatible tool:
describe your_table_name;

Author

Commented:
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.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
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.

Author

Commented:
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.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
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.
CERTIFIED EXPERT
Top Expert 2008

Commented:
You can run the query from every environment that can connect to Oracle. Almost evry framework is able to connect to Oracle.

Author

Commented:
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(ConfigurationManager.ConnectionStrings["MY_CONN"].ConnectionString);
                OracleCommand oraCmd = new OracleCommand(sqlstmt1, oraConn);
                oraCmd.CommandType = CommandType.Text;
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
Still not sure why you cannot use sqlplus but OK.

I'll work on a quick C# example.

Author

Commented:
I figured out how to connect to the database with a connection tool, but you assistance helped.  Thanks!
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
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.


<%@ 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>

Open in new window

Explore More ContentExplore courses, solutions, and other research materials related to this topic.