For getting the columns of view, ORG_MULTI_FACTCS_WITH_LOCTN use this query
SELECT table_name, column_name
FROM user_updatable_columns
WHERE table_name IN (
SELECT view_name
FROM user_views where view_name='ORG_MULTI_FACTCS_WITH_LOCTN');
---------
Another method is to use the commad DESCRIBE ORG_MULTI_FACTCS_WITH_LOCTN.
From Plsql developer File menu select New->Command Window
In the new window, after the line SQL>, type the command and press Enter
as below
SQL> DESCRIBE ORG_MULTI_FACTCS_WITH_LOCTN;
You can use the DESCRIBE to view your table and stored procedure details also
SELECT table_name, column_name
FROM user_updatable_columns
WHERE table_name IN (
SELECT view_name
FROM user_views where view_name='ORG_MULTI_FACTCS_WITH_LOCTN');
Perfect. It worked well. By the way I have still some question below. As I have deployed into new project. I need to learn new things. Thanks a lot for all your help. It is highly appreciated.
@all
I have a below query
select distinct
DATA_SRC_CD as Source,
FILE_NM as File_Name,
from PKG_SFI_REPORTS.DATALOAD2SUMMARYETLRPRT("FACTS","CM_DDL_PARTY_2011_01_12_08_24_09.DAT")
In the above Query PKG_SFI_REPORTS => Package, DATALOAD2SUMMARYETLRPRT=> Procedure.
"FACTS" and "CM_DDL_PARTY_2011_01_12_08_24_09.DAT" => Parameters
Now I need to know the Data Types of "DATA_SRC_CD" and "FILE_NM". How can I Know?
Please check my previous post to get the SP details.
N Rajesh
Use this to get Stored procedure information
SELECT package_name, object_name, argument_name, data_type FROM USER_ARGUMENTS;
So for getting information of package_name='PKG_SFI_REPORTS' use the below query,
SELECT package_name, object_name, argument_name, data_type FROM USER_ARGUMENTS
where package_name='PKG_SFI_REPORTS' and object_name='DATALOAD2SUMMARYETLRPRT';
By running the below query I am able to Know the Data types of the arguments of Stored Procedure. But I need to Know the data types of "DATA_SRC_CD","FILE_NM " columns. Can you help me?
SELECT package_name, object_name, argument_name, data_type FROM USER_ARGUMENTS
where package_name='PKG_SFI_REPORTS' and object_name='DATALOAD2SUMMARYETLRPRT';
N Rajesh
Participant
1212 Points
204 Posts
Re: How to see Columns and corresponding Datatypes in a Table
Jan 19, 2011 11:42 PM|LINK
For getting the columns of view, ORG_MULTI_FACTCS_WITH_LOCTN use this query
SELECT table_name, column_name
FROM user_updatable_columns
WHERE table_name IN (
SELECT view_name
FROM user_views where view_name='ORG_MULTI_FACTCS_WITH_LOCTN');
---------
Another method is to use the commad DESCRIBE ORG_MULTI_FACTCS_WITH_LOCTN.
From Plsql developer File menu select New->Command Window
In the new window, after the line SQL>, type the command and press Enter
as below
SQL> DESCRIBE ORG_MULTI_FACTCS_WITH_LOCTN;
You can use the DESCRIBE to view your table and stored procedure details also
SQL> DESCRIBE <TABLE_NAME>;
SQL> DESCRIBE <Package_name>.<sp name>;
Hope it helps.
thenraj
Member
8 Points
26 Posts
Re: How to see Columns and corresponding Datatypes in a Table
Jan 21, 2011 07:23 AM|LINK
@ Rajesh
I ran the below Query
SELECT table_name, column_name
FROM user_updatable_columns
WHERE table_name IN (
SELECT view_name
FROM user_views where view_name='ORG_MULTI_FACTCS_WITH_LOCTN');
and got the below output
TABLE_NAME COLUMN_NAME
ORG_MULTI_FACTCS_WITH_LOCTN ADDR_LINE2_TXT
ORG_MULTI_FACTCS_WITH_LOCTN POSTAL_CODE
and then what will be the Query to know the Data Types of "ADDR_LINE2_TXT","POSTAL_CODE" Columns?
Thanks in Advance
N Rajesh
Participant
1212 Points
204 Posts
Re: How to see Columns and corresponding Datatypes in a Table
Jan 21, 2011 07:46 AM|LINK
I think using this query, it is not possible to get the datatype of columns in View.
But you can use the DECRIBE View_name command as mentioned in my previous post.
thenraj
Member
8 Points
26 Posts
Re: How to see Columns and corresponding Datatypes in a Table
Jan 21, 2011 08:41 AM|LINK
When I ran the below Query. It is throwing Error. What could be the Correct Query?
DESCRIBE ORG_MULTI_FACTCS_WITH_LOCTN
N Rajesh
Participant
1212 Points
204 Posts
Re: How to see Columns and corresponding Datatypes in a Table
Jan 21, 2011 10:07 AM|LINK
Are you using the command window in Plsql developer as I mentioned in the previous post?
From Plsql developer File menu select New->Command Window
In the new window, after the line SQL>, type the command and press Enter
as below
SQL> DESCRIBE ORG_MULTI_FACTCS_WITH_LOCTN;
Or you can use the Object Browser to view the columns, which is the easiest way.
thenraj
Member
8 Points
26 Posts
Re: How to see Columns and corresponding Datatypes in a Table
Jan 21, 2011 12:20 PM|LINK
@ Rajesh
Perfect. It worked well. By the way I have still some question below. As I have deployed into new project. I need to learn new things. Thanks a lot for all your help. It is highly appreciated.
@all
I have a below query
select distinct
DATA_SRC_CD as Source,
FILE_NM as File_Name,
from PKG_SFI_REPORTS.DATALOAD2SUMMARYETLRPRT("FACTS","CM_DDL_PARTY_2011_01_12_08_24_09.DAT")
In the above Query PKG_SFI_REPORTS => Package, DATALOAD2SUMMARYETLRPRT=> Procedure.
"FACTS" and "CM_DDL_PARTY_2011_01_12_08_24_09.DAT" => Parameters
Now I need to know the Data Types of "DATA_SRC_CD" and "FILE_NM". How can I Know?
N Rajesh
Participant
1212 Points
204 Posts
Re: How to see Columns and corresponding Datatypes in a Table
Jan 24, 2011 01:54 AM|LINK
Please check my previous post to get the SP details.
So for getting information of package_name='PKG_SFI_REPORTS' use the below query,
SELECT package_name, object_name, argument_name, data_type FROM USER_ARGUMENTS
where package_name='PKG_SFI_REPORTS' and object_name='DATALOAD2SUMMARYETLRPRT';
thenraj
Member
8 Points
26 Posts
Re: How to see Columns and corresponding Datatypes in a Table
Jan 25, 2011 12:43 PM|LINK
@ Rajesh
By running the below query I am able to Know the Data types of the arguments of Stored Procedure. But I need to Know the data types of "DATA_SRC_CD","FILE_NM " columns. Can you help me?
SELECT package_name, object_name, argument_name, data_type FROM USER_ARGUMENTS
where package_name='PKG_SFI_REPORTS' and object_name='DATALOAD2SUMMARYETLRPRT';
thenraj
Member
8 Points
26 Posts
Re: How to see Columns and corresponding Datatypes in a Table
Jan 28, 2011 03:00 PM|LINK
Any update of my above question? Still it is not answered?