SQL & PL/SQL :: DESC All Tables

Oct 22, 2010

I am a newbie to Oracle and trying to write something to basically loop through all tables and do a DESC for each table,

I have the following code

set serveroutput on
begin
FOR cursor1 IN (SELECT * FROM ALL_TABLES where OWNER='ABC' ORDER BY TABLE_NAME)
LOOP
DBMS_OUTPUT.PUT_LINE(cursor1.TABLE_NAME);
FOR cursor2 IN (select * from cols where TABLE_NAME=cursor1.TABLE_NAME ORDER BY COLUMN_NAME)
LOOP
DBMS_OUTPUT.PUT_LINE(cursor2.COLUMN_NAME);
END LOOP;
END LOOP;
end;

Although the above code works, I would like to have the DESC TABLE_NAME instead of just displaying the column name so I can see the properties of the column.

I couldn't get the DESC part to work

View 5 Replies


ADVERTISEMENT

Alternative To DESC

Dec 12, 2006

For whatever reason, in PHP, DESC statements do not work. They're simply unrecognized and you get an OCI "invalid SQL statement" error. It may be some sort of security feature. I've also had the same result with the SQL scratchpad in the Java EM, but not in SQL Plus. Is there an alternative method for getting details on a table that I can use that also doesn't require being logged in as a SYSDBA?

View 5 Replies View Related

SQL & PL/SQL :: Asc / Desc Ordering Data

May 23, 2011

I am working on ORACLE 9I. I am ordering(ASC/DES) the data one column whose values are

SALES-1
SALES-14
SALES-15
SALES-2
SALES-3
SALES-11
SALES-12
SALES-13
SALES-4
SALES-5

When i do the ascending order dat looks like

SALES -1
SALES-11
SALES-12
SALES-13
SALES-14
SALES-15
SALES-2
SALES-3
SALES-4
SALES-5

but it should display as

SALES-1
SALES-2
SALES-3
SALES-4
SALES-5
SALES-11
SALES-12
SALES-13
SALES-14
SALES-15

View 2 Replies View Related

SQL & PL/SQL :: Desc Table That Can't Select

Nov 14, 2012

if you grant select on a table to a user then u revoke select on this table that user still can desc this table, is this ok? what if i don't want that user to desc it?

View 2 Replies View Related

SQL & PL/SQL :: How To Use Desc Inside Block

Aug 1, 2011

how to use desc inside pl sql block?

View 16 Replies View Related

9.2.0.8.0 Oracle Index Not Used In Order By Desc?

Oct 30, 2011

There are 2 setup of Oracle DB 9.2.0.8.0. There is a table T1 in both (one that is working 6.5 million rows and the other that is not working having 6.7 million rows). while doing - select * from t1 order by c1 desc; one is using index i1 and other is going for full table access.

View 1 Replies View Related

Server Utilities :: Append Tables Content To Existing Tables?

Nov 9, 2010

problem on oracle 11gR2 where i have to import data from a source database to an existing table without truncate or drop the target table in the target database.

we have found something called table_exist_action=append in impdp.

View 2 Replies View Related

Maintain Large Tables / Cleanup Data From Our Tables

May 18, 2011

I have to cleanup data from our tables (Production Environment) that contain millions of rows. The question is apart from the solution of the partitioned tables what alternative recommended solution suggests Oracle?

To delete these tables by using a cursor PL/SQL block or to import all the database and in the tables that we want to remove the old rows to use the QUERY option of the data pump utility.

I have used both ways and i have to admit that datapump solution is much much faster than the deletion that suffers from I/O disk.The question again is which method from these two is more reliable and less risky for the health of the database.

View 5 Replies View Related

Implementation Where Data From DB2 Tables Are Moved To Oracle Tables

Sep 3, 2012

I came across an implementation where data from DB2 tables are moved to Oracle tables, for BI solutioning, using some oracle procedures called from MS SQL DTS packages which are scheduled jobs.Just being curious, can this be done using OWB or ODI rather than the above detour. I suppose there are some changes being done in those procedures before the data is being loaded into Oracle tables, can't this be done using OWB/ODI? Can it be scheduled too as jobs using OWB/ODI?

View 1 Replies View Related

SQL & PL/SQL :: Audit Tables For Most Of Production Tables

May 29, 2013

In our schema we have corresponding audit tables for most of the production tables

Ex Table name Audit Table
EMP EMP_AU
DEPT DEPT_AU

Audit tables will have all the columns of production table along with audit columns AUDIT_DATE , AUDIT_OPERATION There are few production tables which are not having audit tables.I need to write a script to identify

1) Production tables where corresponding audit table is missing

2) Where there is column difference (In case any column missing in audit table) between Production table and Audit table

View 11 Replies View Related

SQL & PL/SQL :: Differences Among Records Tables And Tables Of Records

Sep 16, 2010

In Oracle database pl/sql 11.2: describe the differences between :

records,
tables, and
tables of records.

View 11 Replies View Related

SQL & PL/SQL :: Same Key For Few Tables

Aug 25, 2011

I've got table tab1 with sequence id column.

Also I've got tables tab2 and tab3 with indicatorid column in each table.

My need is to prevent inserting values into indicatorid column in tables tab2 and tab3 which are absent in id column in table tab1.

For example, at this moment id column in table tab1 has two values, 1 and 2. I can insert value 3 into indicatorid column in tables tab2 and tab3. I need to prevent from inserting value 3 into indicatorid column in tables tab2 and tab3 while it is absent in id column of tab1 table.

How do I perform it?

View 9 Replies View Related

Calling Tables From PL / SQL

Jan 22, 2013

I have the Table name Location and synonym named Location in my DB. I am trying to create the proc where I am tryting to call the table. But its not working.

Example:
CREATE PROCEDURE TESTPROC
AS
BEGIN
DBMS_OUTPUT.PUT_LINE('testing');
select count(*) from LOCATION;
END;

Compile error:
Error(5,22): PL/SQL: ORA-00942: table or view does not exist

View 2 Replies View Related

Joining Two Tables

May 19, 2011

can we link a table that contains a foreign key with a sequence table? i have a table that has a sequence bookid and i want to link this table to another table that contains a foreign key of bookid.can i link this two table and how?

View 1 Replies View Related

Shrink Tables - HWM?

Aug 22, 2012

if the command is successful:>alter table my_table shrink;The segment will be defragmented and the High Water Mark will be moved.But what is the importance of the HWM?

Whats the difference between commands?
>alter table my_table shrink; -- move HWM
>alter table my_table shrink compact; -- not move HWV

View 1 Replies View Related

SQL & PL/SQL :: Tables In Different Database?

Nov 19, 2010

I have one table in a particular database and will have to create another table in a different database. I will have to join these two tables from two different DB's.

How should I go about doing this? Is this a good practice? or is it always better to use a single DB?

What are the disadvantages of joining two tables from two different DB's?

View 1 Replies View Related

SQL & PL/SQL :: Migration Of Three Tables

Aug 29, 2013

Data migration for three tables. I have three table which are

1.npi_p_mig contain four fields (pr_id,mi_id,qty,sl_dt,fac_code)
2.np_detail(pr_id,mil_id,qty,sl_dt,facility)
3.np_ref_tab(facility,fac_code),

I need to migrated the data from based on two tables (np_detail,np_ref_tab) to new table npi_P_mig(pr_id,mi_id,qty,sl_dt,fac_code) table. i need sql script to migrate above two table to new table (npi_P_mig) .

View 4 Replies View Related

SQL & PL/SQL :: How To Sum Over Multiple Tables

Feb 21, 2010

I'm trying to do a sum over 2 different tables but can't get it to work...This is the idea:I have a table A with client ID, time-id (per day), purchase amount and segment code.

In another table (let call it B) I have a lot of client ID's and also their purchase amount, time-id and segment code. I want to sum the purchase amount for every client from table A and B for clients with certain segment code from table B.

This is what I have now:

select client_id, purchase_amountA+ purchase_amountB from tableA, tableB where
A.client_id = B.client_id
and time_id between 20090101 and 20091001
and B.segment_code = 'A'

This does the job, but it selects only client_id's which are in both tables. I want to select all client_id from table B with segment_code 'A' and add the purchase_amount from table A to their purchase amount from table B, at least, if they have any purchase amount in table A.

View 4 Replies View Related

SQL & PL/SQL :: Join On Two Tables

Jul 29, 2013

I have 2 tables SEC_MASTER_HISTA and SEC_MASTER_HISTB.

Now, I need to compare the data of the two tables column-wise.

Ideally the 2 tables should have the same security_alias values but in my case they do not as the two tables belong to 2 diff client models. There is however a main SECURITY_MASTERA and SECURITY_MASTERB tables which have the security_alias recorded and a primary_asset_id column value which can act as a link between SEC_MASTER_HISTA and SEC_MASTER_HISTB. But, I have not been able to figure out the exact query which will be ideal.

Attached are the table structures and the data it contains.

Note: I need to compare the Coupon and Freq column values of SEC_MASTER_HISTA and SEC_MASTER_HISTB.

View 23 Replies View Related

SQL & PL/SQL :: EMP And DEPT Tables

Dec 4, 2006

What is the structure and the values of the default EMP and DEPT table in oracle 9i....i accidentally dropped those 2 tables .... and i don't remember the structure of it.

View -1 Replies View Related

SQL & PL/SQL :: Multiple Tables

Sep 30, 2010

Video Rental Shop

Each customer has a video card , When Customer rent a CD , Shopkeeper register an issue date and a Return Date . If customer return CD after Return Date Then There will be a fine of 2 Dollor .

After every 6 Months The shop Keeper review each customer Account , and Send Gifts to those customer whose Total Amount is More than 50 Dollar .and also send letters to those whose Fines Are More than 20 Dollor .

Now I am unable to understand that how many table i need to create for this .

What i have created so far is given below ,

When Customer Rent a CD then Shopkeeper will submit Following Information .

Customer_id 101
Issue DateDATE
Expected_return_dateDATE
Original_return_date-
Fine -
Total_Amount -

And at the time of return , he will Put these information .

Customer_id 101
Issue DateDATE
Expected_return_dateDATE
Original_return_date DATE
Fine 2
Total_Amount5

But Do i need to create another table for each customer also ? That will store customer total amount , total Fines ,and shopkeeper will view it after every six months. Which type oo table i need to create ?

View 18 Replies View Related

SQL & PL/SQL :: Joining Two Tables

Apr 26, 2013

I have a table (promo_custom) with following colimns (id, id_promo, button_promo, button_submit, css, user, date) and other table (promo_buttons) with following columns (id, id_design, name, url, username, date) .

Columns promo_custom.button_promo and promo_custom.button_submit are foreign keys (references promo_buttons.id).

I want to create a view showing table promo_custom but instead of columns button_promo and button_submit I would like to show the column url of them.

View 11 Replies View Related

SQL & PL/SQL :: Relation Between Two Tables?

Dec 28, 2010

oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"

I have a main table : CLAIMS_MAIN, in which claim_id is a primary key I have another table : CLAIMS_TRANS , in which claim_id is not a unique key, I mean, there can be more than one records with the same claim_id.. but the column transaction_id is unique, but transaction_id column doesnt exist in the main claims table.

It is required to join both these tables on claim_id...

View 1 Replies View Related

SQL & PL/SQL :: Getting Result From Three Tables

Sep 24, 2012

CREATE TABLE FLIGHTS(
FLIGHT_NUM NUMBER PRIMARY KEY
)

[Code]...

how to get the name of all passengers who have bookings in all flight_num?

o/p is 555

View 2 Replies View Related

SQL & PL/SQL :: How To Add Tables Users

Jun 3, 2011

I have 4 tables as follows.

1.Deploybase(DeployId)
2.LicenseHistory(DeployId,UserId)
3.Users(UserId,User_Detail_id)
4.UserDetails(User_Detail_id)

Tables are joined using below joins.

Deploybase.DeployId=LicenseHistory.DeployId
LicenseHistory.User_id=Users.User_id
Users.User_Detail_id=UserDetails.User_Detail_id

When I join LicenseHistory,Users,UserDetails, all records are unique.

But the issues is: LicenseHistory table has duplicate records for DeployId column which is used to join table Deploybase. As a result when I join it to Deploybase, it gives me repeating entries which I don't want

I want Comments from LicenseHistory,Login from Users and User_name from UserDetails. There are multiple columns from 1st table i.e. Deploybase which I need.After reading on Exists operator, I made following query.

Select DeployId,comment from (select comments as comment,deploy_id as DeployId from LicenseHistory slh where exists (select deploy_id from symdb.symdb_deploybase sdb) and slh.comments is not null)

Above query gives me results from LicenseHistory table only. But I don't know how to add tables Users,UserDetails

View 1 Replies View Related

SQL & PL/SQL :: How To Compare The Four Tables

Jan 5, 2013

I Have Four Tables

1) Sal_master
structure is voc_no varchar2(7),voc_date date

2) sal_detail
structure is voc_no varchar2(7),item_code varchar2(10),quantity number(10,2)

3). delivery_master
structure is voc_no varchar2(7),voc_date date;

4) delivery_detail
structure is voc_no varchar2(7),item_code varchar2(10),quantity number(10,2)

I want to compare these four tables i have insert 10 rows in sal_master and sal_detail tables and 5 transaction in delivery tables how to compares 10 records of sal_master,detail with delivery_master and detail if not exist in delivery_master and detail tables then display only sal_master,detail records for example

Voc_no Sale Qty Deliver Qty Remaining Qty

S000075 10 5 5 if data not found from delivery master and detail then answer must be
S000075 10 0 10

View 2 Replies View Related

SQL & PL/SQL :: Field From Two Tables?

Sep 10, 2012

I have two master tables , one is supplier master and the other customer, the requirement is , i will pass a code as IN parameter and the result it should bring the appropriate name of that code , for example if i pass a code which exists in supplier master , it should get me the supplier name for that code , if i pass code which exists in customer ,it should bring customer name.

CREATE TABLE OM_CUST (CUST_CODE VARCHAR2(30),CUST_NAME VARCHAR2(240));
INSERT INTO OM_CUST VALUES ('1001','CUST1');
INSERT INTO OM_CUST VALUES ('1002','CUST2');
CREATE TABLE OM_SUPP (SUPP_CODE VARCHAR2(30),SUPP_NAME VARCHAR2(240));
INSERT INTO OM_SUPP VALUES ('2001','SUPP1');
INSERT INTO OM_SUPP VALUES ('2002','SUPP2');

View 6 Replies View Related

SQL & PL/SQL :: Union Of Two Tables?

Jul 1, 2010

how can i get a union of two pl/sql tables. ??

View 2 Replies View Related

SQL & PL/SQL :: Populate Tables Of Ref

Apr 12, 2012

I like to the following:

Objects: Car, Person

Person can own a lot of cars which i like to implement as table of ref in person!

When i poluplate the ref table, I always get null-references! Compiler is ok, no syntax errors or anything else

drop table person_tab;
drop table car_tab;
drop type person_type force;
drop type car_type force;
drop type owns_type force;

---------------------------Objects-----------------------------

create or replace type car_type as object
(cartype varchar2(10),
byear integer
);
/

create or replace type owns_type
as table of ref car_type;
/

create or replace type person_type as object
(fname varchar2(10),
owns owns_type
);
/

commit;

--------------------------Object Tabs--------------------------

create table person_tab of person_type
(fname primary key)
nested table owns store as owns_cars;
/

create table car_tab of car_type
(cartype Primary Key
);
/

commit;

------------------------Populate Tables------------------------

insert into person_tab values
('meier', owns_type());
/
insert into car_tab values
('audi', 2011);
/
insert into car_tab values
('vw', 2012);
/
insert into car_tab values
('bmw', 2011);

commit;

----------------------Populate Ref Tables----------------------

insert into table
(select p.owns
from person_tab p
where p.fname = 'meier')
select ref (c)
from car_tab c
where c.cartype = 'audi';
/

insert into table
(select p.owns
from person_tab p
where p.fname = 'meier')
select ref (c)
from car_tab c
where c.cartype = 'vw';
/
commit;

View 15 Replies View Related

Deadlock On Different Tables

Feb 18, 2013

I am working on 11g and AIX...We got deadlock recently and we need to investigate why it happen and resolution, so it will not happen in future. When we saw deadlock trace file ,it shows 2 sqls queries on different tables..and lock is exclusive lock.

----- Information for the OTHER waiting sessions -----
SELECT 1 FROM HOS_DTL WHERE ( HOS_LCN_DTL.HOS_LCN_DTL_ID = :1 ) FOR UPDATE WAIT 180

and

----- Current SQL Statement for this session ------
DELETE FROM EI_INV WHERE ( EI_INV.EI_INV_ID = :1 )

But these two tables not related at all 'HOS_DTL' and 'EI_INV' . Can deadlock happen if sqls queries fired on those table are not related at all and deadlock trace file can show those two sqls ..?

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved