How To Retrieve Table Schema

May 22, 2007

I'd like to know which SQL command (if there is any) I can use to display the current schema of a table. I'm particular interested in knowing which columns are indexes.My privileges to the DB are quite basic (I think that I'nm only allowed to run SELECT statements

This is the Oracle version that I'm using:

Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
PL/SQL Release 9.2.0.6.0 - Production
CORE 9.2.0.6.0 Production
TNS for HPUX: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production

View 2 Replies


ADVERTISEMENT

PL/SQL :: Use Loops To Retrieve Name Of Emp Table Of Scott Schema

Jan 12, 2013

i am trying to use loops to retrieve the ename of emp table of scott schema , then under they are retrieved , all "sal" values appear under them , i do not want employee's salary under each employee , i need retrieve the 14 record of enames , then under them the 14 record of sal column appears , so i used this code :

declare
cursor emp_cur is
select ename from emp;
emp_rec emp_cur%rowtype ;
[code]....

View 16 Replies View Related

SQL & PL/SQL :: Query To Retrieve Value In Specified Schema

Jan 6, 2011

I need a query to retrieve a particular string from all the columns and all tables in a specified schema.

For example:i had a value "Current" in a particular column in a particular table. i need to find this value if i don't know the table name and column name.

View 2 Replies View Related

Client Tools :: How To Create A Table In Another Schema As In Existing Schema

Apr 26, 2010

I would like to create a table in another schema(CBF) as already exist in my schema(TLC) without data but related indexes,synonyms and grants should be include.

How could I do this without using export import. I am using TOAD 9.0.1.

View 10 Replies View Related

Export/Import/SQL Loader :: Imp Records Of One Schema Into Another Schema Of Same Table

Nov 3, 2012

I had done following steps,

schemas(toy,toys)

1) i open the session of toy schema

First i taken backup of table

create table bck20121103_himan as select * from himan;

Backup table is created.

After taking the Backup table

delete himan;(deleting the records)

2) i log in to another session(toys)

exp toys/toys@orcl file=20121103TOYs.DMP TABLES=(HIMAN) /* Particular table is taken*/

3) i log in to toy schema

imp toy/toy@orcl file=<dump file name> TABLES=(HIMAN) INDEXES=N IGNORE=Y

i tried the above statement it taken so much of time..

Later i tried

I log in to toy session

i rename the table with other name.

later i imported

imp toy/toy@orcl file=<dump file name> TABLES=(HIMAN) IGNORE=Y FULL=Y

it's successfully imported.

View 3 Replies View Related

Retrieve Nth Row From Table

Oct 12, 2010

I have googled so much but unable to find the explanation how the following query work.

SELECT * FROM EMP a WHERE 2 = (SELECT COUNT(rowid) FROM EMP b WHERE b.rowid <= a.rowid);

View 2 Replies View Related

SQL & PL/SQL :: Retrieve Only Last Rank Row From Table?

Dec 2, 2010

how do i retrieve only last rank row from table ?

View 7 Replies View Related

Retrieve Data From A Table For Certain IDs

Apr 7, 2013

I am writing a SQL inside an application to retrieve data from a table for certain IDs or for All employees based on the user's input. I create a variable :SELECTION for the where clause. When I run the SQL, I receive the error of missing right parenthesis. There is no error if I hard code the IDs.

If the input is 'All' -> the variable :SELECTION is blank so if the SQL :

select name, address, ID from employee :SELECTION

will be

select name, address, ID from employee

If the input of users ID as '1234','9999' - > the :SELECTION = ' where ID in (:VARIABLEID)'
:VARIABLEID = '1234','9999'

so the SQL:
select name, address, ID from employee :SELECTION

should be

select name, address, ID from employee where ID in ('1234','9999')

However, I got the missing right parenthesis error. I suspect it is something with the single quote, so I tried different ways, but it is still not working.

View 1 Replies View Related

SQL & PL/SQL :: How To Retrieve Duplicate Rows From A Table

Jun 14, 2010

How to retrieve duplicate rows from a table, suppose i don't know any column names. Without hard-coding column names I need to find.

View 5 Replies View Related

SQL & PL/SQL :: Retrieve Hierarchical Data From Same Table

Nov 10, 2011

Look into the below table:

TABLE :- EMPLOYEE
________________________
| ID | SUPERVISOR |
|_______________________|
| A101 | B102 |
|________|______________|
| | |
| B102 | C104 |
|________|______________|
| | |
| C104 | D108 |
|________|______________|
| | |
| D108 | E104 |
|________|______________|

Here B102 is supervisor of A101 and C104 is supervisor of B102 and so on. I want to get this data into new table in below format

TABLE :- Hierarchy
________________________________________________________________
| ID |SUPERVISOR_1 |SUPERVISOR_2 |SUPERVISOR_3 |SUPERVISOR_4|
|______________________|_____________|_____________|____________|
| A101 | B102 | C104 | D108 | E104 |
|________|_____________|_____________|_____________|____________|
| | | | | |
| B102 | C104 | D108 | E104 | NULL |
|________|_____________|_____________|_____________|____________|
| | | | | |
| C104 | D108 | E104 | NULL | NULL |
|________|_____________|_____________|_____________|____________|
| | | | | |
| D108 | E104 | NULL | NULL | NULL |
|________|_____________|_____________|_____________|____________|
| | |
| E104 | NULL | NULL NULL NULL
|________|_____________|_____________|_____________|____________|

I want to insert 1st two rows into Hierarchy table, then I would like to update Supervisor_2 to Supervisor_4. Here I don't want to use 'CONNECT BY PRIOR', as it take more time to execute (there are millions of records).SQL code for same.

View 7 Replies View Related

SQL & PL/SQL :: Unable To Retrieve Rows From A Table

Mar 7, 2012

I have created a table called ct_temp and it's structure is:

create table ct_temp(id char(2),srs number(3),amt number);

Result : Table Created

I now insert the following rows into the table:

id srs amt

1 62 30000
2 65 50000
3 65 70000
4 65 80000
5 62 16000
6 65 10000
7 65 100000
8 65 10

Commit

Then I issue the following query to retrieve data based on a specific criteria (Actually I have condensed the table and data because in Production, I have large number of rows; so for simplicity I am using 8 rows with one table)

criteria : I then want to retrieve the following:

for srs = 62, all values that is greater than 10,000

Answer: select decode(srs,62,ab.amt)temp1 ,decode(srs,65,ab.amt)temp2 from ct_temp ab

where decode(srs,62,ab.amt)>10000

Works like a charm and retrives the results.

Now comes the big issue

I want to retrieve the values for srs = 62 which is greater than 10,000 and at the same time I also want to retrieve the values for srs = 65 which is less than srs = 62.

Typically I wrote the query as:

select decode(srs,62,ab.amt)temp1 ,decode(srs,65,ab.amt)temp2 from ct_temp ab
where decode(srs,62,ab.amt)>10000
and decode(srs,65,ab.amt)<decode(srs,62,ab.amt)

I should atleast get one row for srs = 65 which is id # 8 but it displays blank rows or "no rows returned".

I am actually preparing a ad-hoc report for the business analyst and stuck at this step due to which I am unable to proceed any further. I have used DECODE function because of the requirement specified by Business Analyst.

I have tried the following in the office:

using EXISTS operator = no luck
using INLINE VIEW = no luck

Is there any way around?

View 26 Replies View Related

SQL & PL/SQL :: How To Retrieve Data That Does Not Exists In A Table

Dec 13, 2011

I have the following table with the some sample data:

|| Filename || receiving_site || originating_site ||
| fileA | siteA | HQ |
| fileA | siteA | siteA |
| fileB | siteA | HQ |
| fileA | siteB | HQ |
| fileA | siteB | siteB |
| fileB | siteB | HQ |
| fileB | siteB | siteB |

The table works in such way:

1. For each of the Filename, if originating_site is not the same as receiving_site, it means that the file has been sent to receiving_site but has not been acknowledged received yet.

2. For each of the Filename, if originating_site is the same as receiving_site, it means the file has been sent and received by the receiving_site.

My task is to list out all the Filename per receiving_site that has been sent, but not received yet by the receiving_site. For example from the sample data above, I am expecting to see that siteA, fileB has not been received yet.

How can I do that? I had tried MINUS and NOT EXISTS command, but I am just not able to get the result that I want.

View 2 Replies View Related

SQL & PL/SQL :: Retrieve Subset Of Data From Table

Jan 20, 2012

How to retrieve first hundred records from a table ?

FYI
---

The table size is 5 GB
The table count is 127922653
Table has 14 columns

Table is partitioned as well.

The table has 10 partitions.

View 17 Replies View Related

SQL & PL/SQL :: How To Retrieve 100th Row To 120th Row From Table

Jul 18, 2012

A table has 150 records. How do you retrieve 100th row to 120th row from that table ?

View 9 Replies View Related

Table Locking When Many Users Retrieve Data

Sep 2, 2009

I have a "select ... into" in my PL/SQL and it doesn't retreive any data(I suppose just certain columns because if it doesn't retreive a row then the PL/SQL should throw an error) when there are many users accessing this table. It shouldn't be some table locking right? Because if it is, it should wait until the table is available then it will retreive data. Currently it just proceeds with the PL/SQL and selecting nothing in that query. what details should I look at to trace the cause of this problem.

View 4 Replies View Related

PL/SQL :: How To Retrieve Data Delete From Main Table

Nov 10, 2012

I am using oracle 11g database.

unforunatly i delete the data from main table. and i operated alter stmt.

now how do i retrieve the data..??

View 5 Replies View Related

Server Utilities :: Import A Schema From One Database Schema To Another Schema B?

Aug 10, 2010

I want to import a schema from one database schema to another schema b from db STBTST to STATST and from schema CMSSTAGINGB to CMSSTAGINGA

I first want to test this to my own schema (mvanmannekes) CMSSTAGINGA is filled at the moment.

So i've created a dump from STBTST-CMSTAGINGB For importing im using this statement:

impdp mvanmannekes/password schemas=cmsstagingb remap_tablespace=cmsliveb_data:cmslivea_data
remap_tablespace=cmsliveb_index:cmslivea_index
remap_schema=cmsstagingb:mvanmannekes directory=expdp_dir dumpfile=cmstagingb.dmp

I'm getting this:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "MVANMANNEKES"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "MVANMANNEKES"."SYS_IMPORT_SCHEMA_01": mvanmannekes/********
schemas=cmsstagingb remap_tablespace=cmsliveb_data:cmslivea_data

[code]....

View 4 Replies View Related

Windows :: How To Retrieve The Blob Column Data From Table

Mar 7, 2011

I use the oracle 10g database.I am trying to insert and retrive the image.Inserting an image is done.but while retrieving an image iam getting an run time exception in java "java.sql.sqlexception:general error".i am not able to understand this.

The code to insert the image is
FileInputStream fi=new FileInputStream(f);
int size = fi.available();
System.out.println("j"+size);
byte b1[] = new byte[size];
i=fi.read(b1,0,size);
System.out.println("i"+i);
st.executeUpdate("insert into image1 values('b1',"+k+")");
when i am retrieving the image i tried like this
ResultSet rs=st.executeQuery("select imagecolumn from tablename");
here iam getting an exception as i named above.

View 4 Replies View Related

SQL & PL/SQL :: Unable To Retrieve Multiple Values / Possibility From A Table?

Jun 1, 2010

I have a table called table1 and its structure is as follows:

rtrn_id number,
entp_abn number,
litm_line_item_nbr number
country_code varchar 2(10)

I am trying to find retrieve value/row where for a particular rtrn_id , the possibility of having a country code of "CA"(for Canada) and "US" or "HK" (US or Hong Kong respectively) exists.

I framed my query the following way and it does not retrieve any result in Production and I believe there are a few values/rows in the "table1".

The query framed is:

Query 1:

SELECT rtrn_id,entp_abn,litm_line_item_nbr,country_code
FROM table1
where schd_version_yr = 2010 and prog_program_cd = '01'
and sched_nbr = '000' and (litm_line_item_nbr = '023' and country_code 'CA')
and (litm_line_item_nbr = '016' and country_code = 'CA')

The above query does not retrieve any rows

Where as if I run a query individually it retrieves tons of rows

(Individual query :

SELECT rtrn_id,entp_abn,litm_line_item_nbr,country_code
FROM table1
where schd_version_yr = 2010
and prog_program_cd = '01'
and sched_nbr = '000'
and (litm_line_item_nbr = '016' and country_code = 'CA')

-----Retrieves tons of rows as the country code = Canada for line item # 16

SELECT rtrn_id,entp_abn,litm_line_item_nbr,country_code
FROM table1
where schd_version_yr = 2010
and prog_program_cd = '01'
and sched_nbr = '000'
and (litm_line_item_nbr = '016' and country_code 'CA')

-----Retrieves tons of rows as the country code IS NOT Canada for line item # 23.

There is a possibility that there are rows that have country Code as Canada and not Canada for a particular ID whose line item numbers could be 16, 23.. is there any better way to frame my Query1 to retrieve values/rows?

View -1 Replies View Related

Forms :: Select Table Name And Retrieve Its Related Data

May 29, 2012

I wanna have a form which has a list box which is including of all table names, by selecting the required table by user all column name and its related data should be retrieve.I have a cursor which give me all field names related to the table by I do not know how can I retrieve table data:

1. How to add item through block base on number of column which selected table has.

2. How to set value of table column in these mentioned column which added in the block.

3. And at the end retrieve the table data.

View 1 Replies View Related

SQL & PL/SQL :: Retrieve All Data From Table And Print Out Each Customer Info One By One

Nov 15, 2010

How similar is PL/SQL to SQL? I'm trying to retrieve all data from a customer table and print out each customer information one by one:

DECLARE
CURSOR all_customer IS
SELECT *
FROM customer;
BEGIN
DBMS_OUTPUT.PUT_LINE(all_customer);
END;

but I really don't know the correct syntax. The Powerpoint slides the professor gave is not useful at all.

View 13 Replies View Related

Forms :: Hierarchical Tree - Retrieve Values And Insert Into Table

Apr 19, 2010

My Form consists two Hierarchical trees. When I select a node from first tree and I press Move Right (>>) Button the selected node should move to the second tree. Similarly when I select a node from second tree and press move Left button(<<) it should move to the first tree.

I also want to know want to insert values Into New Table using node values from The Displayed Hierarchical Tree. How to retrieve values using populate_Group_from_tree and insert into table.

View 2 Replies View Related

Forms :: How To Retrieve Records From Table Without Using Data Block Wizard

Jun 17, 2012

i want to retrieve records of empno from SCOTT.EMP talbe in form but without data block wizard using ..how .. and what logic will be in coding

DECLARE
TYPE EROC IS TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER;
E1 EROC;
NO_EMPNOVARCHAR2(2000);
[code]....

just caling here EMPNO and :v_empno is fields on form...

View 1 Replies View Related

Performance Tuning :: Retrieve Data From View Or Mention Table Names Directly?

Aug 11, 2011

base on performance it is better to retrieve data from view or mention the table names directly?

I have a select statement in from clause one of my table is view (which is having data collected from four tables) my question is whether performance of querry will be improved if i use directly all tables( four tables of a view) instead of a view

View 9 Replies View Related

Creating Table From Existing Table In Another Schema?

Jan 4, 2009

I am creating a table from another existing table in another schema. The existing table contains data. When I am using the query- create table m _voucher as select * from ipm.m_voucher,I am getting the whole data of m_voucher but I want empty m_voucher table, so what will be the query to get the empty m_voucher table?

View 4 Replies View Related

SQL & PL/SQL :: Update A Table Using Another Schema Table Of Same Style?

Aug 14, 2011

Can we update a table using another schema table of same style?

Example:

Update employees
set employee_name=hr.employees.employees_name
where employee_id=100;

View 8 Replies View Related

SQL & PL/SQL :: Oracle - How To Move Table To Another Schema

Feb 6, 2008

How can I move a table to another schema in Oracle?”

View 33 Replies View Related

SQL & PL/SQL :: Update Job-id Of Employees Table Of HR Schema?

Jan 8, 2012

I am trying to update of job_id column of employees table for employee number 205 two times one after another. First time job_id column of employees table for employee number 205 is updated with new job_id. But second time job_id column of employees for employee number 205 table can not be updated. Oracle returns the following errors

HR:orcl > update employees set JOB_ID='AC_MGR' where employee_id=205;
update employees set JOB_ID='AC_MGR' where employee_id=205
*
ERROR at line 1:
ORA-00001: unique constraint (HR.JHIST_EMP_ID_ST_DATE_PK) violated
ORA-06512: at "HR.ADD_JOB_HISTORY", line 10
ORA-06512: at "HR.UPDATE_JOB_HISTORY", line 2
ORA-04088: error during execution of trigger 'HR.UPDATE_JOB_HISTORY'

As there is composite primary key using employee_id and start_date column.how to update same employee job_id twice.AS we can see from job_history table, the record for employee 200 is as follow

EMPLOYEE_ID START_DATE END_DATE JOB_ID DEPARTMENT_ID
----------- ----------- ---------- ---------- -------------
200 17-SEP-1987 17-JUN-1993 AD_ASST 90
200 01-JUL-1994 31-DEC-1998 AC_ACCOUNT 90

So how can i do the same for employee 205 without changing hire_date after first job_id update. Since for every update of job_id fires trigger. To insert row in job_history table employee id and start_date must be unique each time. HERE hire_date of employees table is used as start_date of job_id table. how it was possible for employee id 200 to change job_id twice?

View 4 Replies View Related

Security :: Creating Table In Other Schema

Dec 29, 2011

I have a query ,

I need to create a structure DATABASE=>SCHEMA=>TABLE as

DB=>SC=>EMPLOYEE ...but after connecting database i could create table only user my user schema(own schema)only . I want to create a new schema called SC as public and need to create a table .

View 2 Replies View Related

SQL & PL/SQL :: Global Temporary Table DDL In Schema

Dec 21, 2011

How to allow only "CREATE GLOBAL TEMPORARY TABLE" DDL in a schema. I have to restrict all DDLs performing by a particular schema except GT Table.

View 8 Replies View Related







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