Query To Know Number Of Columns In A Table?
Apr 4, 2008query to know number of columns in a table i.e.
if I want to know how many number of colums are present in a specific table then what would be the query.
ADVERTISEMENT
SQL & PL/SQL :: Query Two Columns With Datatypes As Number And Varchar
Jul 17, 2012Have table with two columns with datatypes as number and varchar and the values in A column like 1,2,3 AND B column values like a,b,c. Now need to display data in a single column as 1,a,2,b,3,c.
View 4 Replies View RelatedSQL & PL/SQL :: How To Pass Different Number Of Columns Dynamically To A Query
Apr 26, 2010I want to pass Number of columns dynamically to a query. I got success in SQL.
SQL> select &column_list from emp;
Enter value for column_list: empno,ename,sal
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 800
7499 ALLEN 1600
7521 WARD 1250
7566 JONES 2975
7654 MARTIN 1250
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7839 KING 5000
7844 TURNER 1500
7876 ADAMS 1100
7900 JAMES 950
7902 FORD 3000
7934 MILLER 1300
14 rows selected.
But the same i need to achieve in pl/sql. I try with the Ref cursor, but not succeeded.
PL/SQL :: Write Dynamic SQL To Query Unknown Number Of Columns
Jun 6, 2012I need to write a dynamic SQL in PL SQL to query an unknown number of columns. Let me take a simple example query here:
SELECT FIRST_NAME, LAST_NAME FROM VENDOR_CONTACTS
If I have known the number of columns, e.g. querying two columns: "FIRST_NAME" and "LAST_NAME", I can write a DYNAMIC SQL based on the template in table 8-2 of URL....
DECLARE
stmt_str varchar2(200);
cur_hdl int;
rows_processed int;
FIRST_NAME varchar2(200);
LAST_NAME varchar2(200);
BEGIN
cur_hdl := dbms_sql.open_cursor; -- open cursor
stmt_str := 'SELECT FIRST_NAME, LAST_NAME FROM VENDOR_CONTACTS';
[code]....
However, if I wish to write a dynamical sql to query these two columns for a more general purpose (which should meet the requirement to query different number of columns, e.g. three columns, FIRST_NAME, LAST_NAME, BIRTHDAY instead of two columns FIRST_NAME and LAST_NAME). To do this I first try to query the same two columns but using a different method, following URL.....My code for the same query has error, and I cannot solve it.
DECLARE
stmt_str varchar2(200);
cur_hdl int;
rows_processed int;
l_columns dbms_sql.desc_tab;
l_dummy NUMBER;
l_value NUMBER;
[code]....
SQL & PL/SQL :: How To Select All Columns From Table Except Those Columns Which Type In Query
Jan 21, 2011I have a two question.
Question 1:How to select all columns from table except those columns which i type in query
Question 2:How to select all columns from table where all columns are not null without type each column name which is in empty data
Reports & Discoverer :: SQL Union All - Query Block Has Incorrect Number Of Result Columns
Feb 1, 2012I have the following Union All query. It throws the following error in SQL plus
ERROR at line 27: ORA-01789: query block has incorrect number of result columns
After doing some google for the above error it suggests there are incorrect number of columns in the Union All query.I could not figure out the exact location well SQl Plus says error is on line 27 at the first opening bracket like
(Select distinct c.contact_code
Following is the SQL query
Select
tbl_contact.contact_code,
contact_title
||'.'||contact_name contact_name,
contact_address,
[Code] ......
SQL & PL/SQL :: Update Number Of Rows In Three Columns In Table?
Dec 13, 2011how to update the middle of plenty rows in the middle of the columns
sample_data
id name state REGION LOC
1 v A.p 1 1
2 a
3 g K.A 0 3
4 y
5 i T.N 1 0
6 l M.P 0 1
7 c U.P
This is sample data,and i have this kind of large data and i need to fill the rows which are empty. In three columns state,region,loc with data like 0,web_intimation,1,
SQL & PL/SQL :: Add Variable Number Of New Columns To Existing Table Temp
Feb 24, 2010I want to add a variable number of new columns to an existing table temp (with column provided).
Example:
NewColumnNo = 4
-> the columns shall be named rate_1, rate_2, rate_3 and rate_4
-> the values shall be = Column / NewColumnNo
The result shall be like this:
create table temp_res (prodid integer, rate_1 number, rate_2 number, rate_3 number, rate_4 number);
insert into temp_res values(1, 0.25, 0.5, 0.75, 1);
insert into temp_res values(2, 0.25, 0.5, 0.75, 1);
insert into temp_res values(3, 0.25, 0.5, 0.75, 1);
insert into temp_res values(4, 0.25, 0.5, 0.75, 1);
insert into temp_res values(5, 0.25, 0.5, 0.75, 1);
insert into temp_res values(6, 0.25, 0.5, 0.75, 1);
insert into temp_res values(7, 0.25, 0.5, 0.75, 1);
insert into temp_res values(8, 0.25, 0.5, 0.75, 1);
insert into temp_res values(9, 0.25, 0.5, 0.75, 1);
SQL & PL/SQL :: Finding Record In Table Having Value For Maximum Number Of Columns
Nov 25, 2011if there is any inbuilt function or way to find a row in the table that is having value for maximum number of columns.
For example, the table A has 5 columns
(c1,c2,c3,c4,c5) and it has 3 records(r1,r2,r3)
and
r1 has values only for c1,c2
r2 has values only for c1,c2,c3,c4
r3 has values only for c1
so I should get the result as "r3 has values for 4 columns & it is not having value for column c5".
Application Express :: How To Show Data From A Table Having Large Number Of Columns
Oct 8, 2013I have a report with single row having large number of columns . I have to use a scroll bar to see all the columns. Is it possible to design report in below format(half columns on one side of page, half on other side ofpage :
Column1DataColumn11DataColumn2DataColumn12DataColumn3DataColumn13DataColumn4DataColumn14DataColumn5DataColumn15DataColumn6DataColumn16DataColumn7DataColumn17DataColumn8DataColumn18DataColumn9DataColumn19DataColumn10DataColumn20Data I am using Apex 4.2.3 version on oracle 11g xe.
SQL & PL/SQL :: Query For Columns In Table Including Constraints?
Jul 23, 2010I want a query for all the columns in a table including there constraints and indexes of a "SCOTT" schema
View 7 Replies View RelatedSQL & PL/SQL :: Construct Query To Fetch Different Rows From Same Table In Different Columns
May 25, 2013Lets say I have a table in ORACLE database like:
ACC_ID | ACC_AMT
111 | 10000
111 | 12000
111 | 14000
222 | 25000
222 | 30000
333 | 18000
333 | 27000
333 | 13000
333 | 15000
I want to get the output as:
ACC_ID_1 | ACC_AMT_1 | ACC_ID_2 | ACC_AMT_2 | ACC_ID_3 | ACC_AMT_3
111 | 10000 | 222 | 25000 | 333 | 18000
111 | 12000 | 222 | 30000 | 333 | 27000
111 | 14000 | null | null | 333 | 13000
null | null | null | null | 333 | 15000
I need each different ACC_ID with ACC_AMT in different columns. The table may have other different ACC_ID also, but I will fetch only what I need. What is the best way to do this?
So far I have tried this:
SELECT
(CASE WHEN ACC_ID=111 THEN ACC_ID END) AS ACC_ID_1,
(CASE WHEN ACC_ID=111 THEN ACC_AMT END) AS ACC_AMT_1,
(CASE WHEN ACC_ID=222 THEN ACC_ID END) AS ACC_ID_2,
(CASE WHEN ACC_ID=222 THEN ACC_AMT END) AS ACC_AMT_2,
(CASE WHEN ACC_ID=333 THEN ACC_ID END) AS ACC_ID_3,
(CASE WHEN ACC_ID=333 THEN ACC_AMT END) AS ACC_AMT_3
FROM <TABLE_NAME>
But I am not getting the desired result.
PL/SQL :: String Search Query In Any Columns / Table In Database?
Sep 4, 2012I am trying to search a word which starts with 'FRA' in any columns and any tables.
I am unable to find what is generating a join datasets in the webservice from teh database as it is not apparent within the 100 tables.
I ahve looked into
Re: How to search in all rows and all columns?
Re: SQL Search Query?
but none of these queries is working out for me as I am a user with no tables on its own but rather a user quering other tables.I think its a tweak on which dat a dictionary I can view
select distinct substr (table_name, 1, 14) "Table",
substr (t.column_value.getstringval (), 1, 50) "Column/Value"
from all_cons_columns,
table
(xmlsequence
(dbms_xmlgen.getxmltype ('select ' || column_name
|| ' from ' || table_name
|| ' where upper('
|| column_name
|| ') like upper(''%' || 'fra'
|| '%'')'
).extract ('ROWSET/ROW/*')
)
) t
order by "Table";
running teh above query got me thsi error:
ORA-19202: Error occurred in XML processing
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_XMLGEN", line 288
ORA-06512: at line 1
19202. 00000 - "Error occurred in XML processing%s"
*Cause: An error occurred when processing the XML function
*Action: Check the given error message and fix the appropriate problem
SQL & PL/SQL :: Query To Add Total Number Of Count Of Each Table
Jun 13, 2011I want to know that whether we can write a query to add total number of count of each table.
for example:
select count(*) from emp;
count(*)
14
select count(*) from emp_1;
count(*)
15
select count(*) from emp_2;
count(*)
16
My question is that is it possible to add this all the three results and get the value as 45.
SQL & PL/SQL :: Query To Find Number Of Primary Keys Present In A Table?
May 17, 2011is there any query to find number of primary keys present in a table.
View 13 Replies View RelatedSQL & PL/SQL :: External Table Query (compare Number Records In File With External Table)
Jan 23, 2013I have got a procedure that successfully creates an oracle external table and populates it with the contents of a file. This works fine until I have a situation where one of the fields is a VARCHAR2(2) and I try to insert say, a 5 character value. When this happens the record in question does not get populated in the external table (and rightly so), but I could do with working out if there is a discrepancy in the number of records in the file and the number of records that actually make it into the table so I could inform the user that there is a problem.
I have attached the code that creates the external table and populates it.
ORA-01747 - Oracle Reserved Words - Query Table Matching Database With NUMBER Column?
Oct 1, 2012I am having issue with Oracle reserved words, one of the application is using table which has NUMBER as column. I am not able to query that table matching database with NUMBER column.
HERE
select a.*
from DOC a , FOLDER B
where a.NUMBER= B.INCIDENT_ID
and b.open = 'Closed'; I tried double quotes (“”) and sigle quotes too, none of them worked.
SQL & PL/SQL :: How To Generate Number (not Sequence Number) In Query
Mar 9, 2011I have the following select query that works perfectly fine. Returns 25 rows based on the descending order of the price.But, I want add one more expression to this list of columns in this query (apart from customer_id).
the expression should look like Cust-01 for the first customer from the below query all the way to Cust-25 for the last customer.But how can I can generate 01 to 25 in oracle?
select customer_id from
(select customer_id from capitalPLAN
where member_status = 'MEMBER' AND customer_id NOT in ('156','201','1385','2125','3906','165')
order by price desc
)
where rownum <= 25
SQL & PL/SQL :: Cursor For N Number Of Columns
Apr 21, 2010in retrieve column data in the cursor.My requirement is I created a table dynamically as I don't know how many fields will be there.And the table structure would be like this
Filed1 varchar2(10)
Filed2 varchar2(10)
-----------
-----------
Filedn-1 varchar2(10)
Filedn varchar2(10)
[code]...
As in the cur.filed value.
View With Variable Number Of Columns
Oct 21, 2011I've created a stored procedure which creates itself a view (a MV to be honest); the instructions to create this m.view are dinamically built insinde my procedure, so each time i run it, based on the different input parameters, i've got a different result (my output m.view can have three colums the first time, or ten the next time) how can I read my output view to put the data into file? I've tried with "select * bulk collect into my_array from my_ output_ view"...after declaring "my_array" as a varying array with the max number of colums I could ever have...but nothing: if the array dimension doesn't match the number of columns that i've on my view, i.e. i receive "ora-00947 not enough values" error.
Is there a method to dimension dinamucally the array to store my data? Or should I change the code to fetch some other way the data i need to put to a file?
SQL & PL/SQL :: Checking Variable Number Of Columns?
Apr 12, 2010My goal is this:I have a table which is being updated/changed by lay people with certain types of values. Constraints are given to them, but they need not conform to them as they update the table in excel.Now, I want to validate this table every-time before I use it. i.e. implement a script which can be run to verify is all the values are in the right format for further usage.
I have a variable number of columns (i.e. users can add further columns as their requirements change).From columns 3 to 'n' (depending on table given) the values should be 'Yes' or 'No'. How do I check this for a variable number of columns in PL/SQL?
Precompilers, OCI & OCCI :: How To Get Columns As NUMBER(30)
Aug 13, 2008in my oci applications,if i get a column of number that is in the scope of int,i can use value = *(int *)field.data; get the value,but if the column size is larger than 10,the code can't be available,how can i get the value.
View 6 Replies View RelatedPL/SQL :: Find The Range Of Number Between Two Columns
Jul 12, 2012I have a table with two columns Column1 and column2
Like such
create table testTable (column1 number(15), column2 number(15));
insert into testTable values (1,5);I need to find the numbers between column 1 and column 2 including the column 1's number and column's 2 number.
so my answer set should be
1,2,3,4,5
SQL & PL/SQL :: Different Number Of Result Sets While Adding Further Columns In Select Clause
May 4, 2010The below sql is giving different number of result sets while adding further columns in select clause.i.e After adding the columns 4,5,6 in the below query its giving different number of result set.In this case the result set count would be 5.
Before adding the columns 4,5,6,the result set count was 11.
SELECT PAYMENT_METHOD_MAP.NETTINGGROUP_ID,
PAYMENT_METHOD_MAP.CREDITPAYMENTMETHOD_CD,
PAYMENT_METHOD_MAP.DEBITPAYMENTMETHOD_CD,
PAYMENT_METHOD_MAP.AGENT_ID,
SETTLEMENT.NETTINGGROUP_ID,
SETTLEMENT.SETTLEMENTDATE
[code]....
SQL & PL/SQL :: Dynamically Concatenating Column Values Based Upon Number Of Grouped Columns
Feb 17, 2011My requirement is to concatenate two column values and place them in a new column.I have done it using self join but it limits the purpose,meaning when I have more than 2 values for grouped columns then it won't work.How to make this dynamic,so that for any number of columns grouped,I can concatenate.
SELECT a.co_nm, a.mnfst_nr, a.mnfst_qty,
a.mnfst_nr || ':' || a.mnfst_qty || ';' || b.mnfst_nr || ':'
|| b.mnfst_qty
FROM vw_acao_critical a JOIN vw_acao_critical b
ON a.co_nm = b.co_nm AND a.mnfst_nr = b.mnfst_nr
[code]......
What will be the case when I need to concatenate for more number of values.
like when co_nm has three bahs and manfst_nr and manfst_qty has 3 values for each for bah.and if three are having same_mnfst nr then I should use something dynamic.how to achieve this.
SQL & PL/SQL :: How Many Columns Will Have Query
Apr 29, 2012I'm a beginner in PL/SQL ! --> " get_sql_metadata(p_query IN Varchar2) RETURN VARCHAR2;" I have to display the names and columns of the query by using the package dbms_sql and how can I know how many columns will have my query.
View 4 Replies View RelatedSQL & PL/SQL :: Query Related To Getting Columns Name
Jan 20, 2012is there possible to get those column names on which we have applied dml operations like insert, update,delete if yes,then how .
View 5 Replies View RelatedSQL & PL/SQL :: Query To Get Columns Into Rows?
Aug 10, 2010I have one issue with the converting columns into rows.
Employee table has manager , additional manager and some other columns. If the employee has additional manager, then i need those employee record with 2 rows : 1st row - manager with other data , 2nd row - additonal manager with other data .
Orginal data is like this:
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ADDTIONAL_MGR ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- -------------
7369 SMITH CLERK 7902 17-DEC-80 800 20 7698
[code]....
I have tried this , using UNION ALL method, But it is taking more time.
SQL> ED
Wrote file afiedt.buf
1 SELECT DECODE (DUMMYTAB.NUM,'1',MGR, '2',addtional_mgr) MANAGER, empno, ename, job, hiredate, sal, comm, deptno
2 FROM emp E, (SELECT '1' NUM FROM DUAL UNION ALL SELECT '2' NUM FROM DUAL) DUMMYTAB
3* WHERE addtional_mgr IS NOT NULL
SQL> /
PL/SQL :: Query - Rows In Columns
Jul 27, 2012I have following table -
CREATE TABLE group_device
( group_id NUMBER (8) NOT NULL
, member_id NUMBER (4) NOT NULL
, device_id NUMBER (10) NOT NULL
, install_date DATE NOT NULL
, remove_date DATE
);
[Code]...
Current output does looks like below
10 1 123 23-MAY-12 28-MAY-12
10 1 456 28-MAY-12 01-JUN-12
10 1 789 01-JUN-12
10 2 999 04-MAY-12 17-MAY-12
10 4 1123 22-JAN-12 27-JAN-12
10 4 1456 27-JAN-12 28-JAN-12
Device_id - Replaced_device_id - remove_date :: For group_id=10
e.g. for member_id = 1, device_id=123 got replaced by device_id=456 on 28-May-12
device_id=456 got replaced by device_id=789 on 01-Jun-12
So output should look like this
10 123 456 28-May-12 -- Replaced
10 456 789 01-Jun-12 -- Replaced
10 789 -- Active
Similarly there will be many groups, so it should list based on group_id
SQL & PL/SQL :: Query With Multiple Columns
Dec 17, 2012Table-Name
ID Status description Tracking ID
1 Strat Frog 1
2 Start Dog 2
3 Process Frog 1
4 Completed Dog 2
5 Start Rabbit 3
6 Error Frog 1
7 Stop Rabbit 3
8 Start Elephant 4
9 process Elephant 4
10 Start Human 5
11 Stop Human 5
12 Start Butterfly 6
13 completed Butterfly 6
14 start lion 7
15 error lion 8
16 complted lion 8
17 start tiger 9
18 error tiger 9
select * from Table-Name where datetime < to_date('2012/12/06:06:00:00', 'yyyy/mm/dd:hh24:mi:ss')
And datetime > to_date('2012/12/04:22:00:00', 'yyyy/mm/dd:hh24:mi:ss')And not description in (Select * from Table-Name where Status like ('%Complete%' or Status like '%stop%') and description in (Select description from Table-Name where Status Like '%start%'));
Result should be " Frog and Elephant and tiger"
Start of every record(descrpition --status is Start)
End of every record ( status is stop or done or completed)
status process is in btwn (their will be mulitple records with name s//y to process...ie. process 1 ...process 2...process 3 )
Note:
tracking IDs may change up on error