Solved

pl/sql table definition

Posted on 2012-03-12
10
476 Views
Last Modified: 2012-03-12
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?
0
Comment
Question by:adskarcox
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37710802
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;
0
 

Author Comment

by:adskarcox
ID: 37710817
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.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37710835
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.
0
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 

Author Comment

by:adskarcox
ID: 37710863
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.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37710879
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.
0
 
LVL 48

Expert Comment

by:schwertner
ID: 37710895
You can run the query from every environment that can connect to Oracle. Almost evry framework is able to connect to Oracle.
0
 

Author Comment

by:adskarcox
ID: 37710897
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;
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 37710909
Still not sure why you cannot use sqlplus but OK.

I'll work on a quick C# example.
0
 

Author Closing Comment

by:adskarcox
ID: 37710930
I figured out how to connect to the database with a connection tool, but you assistance helped.  Thanks!
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37710987
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

0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question