PL/SQL :: Indexes Output Format?

Jan 16, 2013

currently i am using below version

BANNER                                                                        
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production          
PL/SQL Release 11.2.0.1.0 - Production                                          
CORE     11.2.0.1.0     Production                                                        
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production                         
NLSRTL Version 11.2.0.1.0 - Production                                          
{CODE}

here is my query to find out the list of indexes and positions in a single schema.SELECT

user_indexes.TABLE_NAME||CHR(9)||
user_indexes.INDEX_NAME||CHR(9)||
TRIM(user_ind_columns.COLUMN_NAME)||CHR(9)||
user_ind_columns.column_position ||CHR(9)||

[code]...

i am trying to spool the output to .txt file. in textfile the output should be in below format :

Table_name Index_name column_names
---------------- ---------------- ------------------
ACS_TEST ACS_TEST_IDX COL1,COL2
C_COLLEGE INX_COLL STUDENT_ID,FIRST_NAME,LAST_NAME,DEPT_ID

Note : Based on the position value the column name have to be  placed.

View 2 Replies


ADVERTISEMENT

SQL & PL/SQL :: How To Get Output In Excel Format

Nov 22, 2012

i need to extract data in excel file through sqlplus .

View 2 Replies View Related

SQL & PL/SQL :: Query To Get Output In Particular Format

Mar 8, 2010

I have attached an SQL script.I would like to have the output in the fashion given below -

PERIOD_START_DATEPERIOD_END_DATEEMPLOYEE_NUMBERFULL_NAMELoan AmountLoan TypePay Value Outstanding Amount
1-Jul-0931-Jul-0924200Jonathan48000048004000
1-Jul-0931-Jul-0924200Jonathan32600022702990

the one in bold and underlined is the Column heading.

View 5 Replies View Related

SQL & PL/SQL :: How To Format Output Of Column

Jun 10, 2011

I have output from select something like :

A_PID B_TEXT id_f
-------------- ----------------------------- -----------
23 B_text sample 9888888

and my question is how can I format the output to have it thinner .

I can do it for text: COLUMN FORMAT B_TEXT A5, but i dont know to apply it for fields with number e.g. A_PID column.

View 4 Replies View Related

SQL & PL/SQL :: How To Output Date Format

Nov 7, 2010

I'm trying to output 1/31/06 into January 31, 2006.Here is what I have so far

declare
v_dt DATE;
v_tx VARCHAR2(2000);
begin

[code]...

But it still won't display month as January.

View 11 Replies View Related

SQL & PL/SQL :: Get Output In Correct Format?

May 15, 2012

I have made below query :

select job,case when deptno=10 then ename ELSE 'null' end e10
,case when deptno=20 then ename ELSE 'null' end e20
,case when deptno=30 then ename ELSE 'null' end e30
from emp
order by 1;

the output is:

JOB E10E20E30
ANALYSTnullSCOTTnull
ANALYSTnullFORDnull
CLERKnullSMITHnull

[code]...

but I want data in one row, i mean for example in case of JOB as 'CLERK' the output should be : i.e.

JOB E1 E2 E3
CLERK MILLER ADAMS JAMES
CLERK SMITH

the complete correct output which I want is:

JOB E1 E2 E3
ANALYST SCOTT
ANALYST FORD
CLERK MILLER ADAMS JAMES

[code]....

View 3 Replies View Related

Format Output File By Command Spool

Jan 20, 2011

I use sqplus in oracle to output the command output to text file .

I use below set environment variable.

SQL> set echo off;
SQL> set linesize 3999;
SQL> set feedback off;
SQL> set feedback off;
SQL> set termout off;
SQL> set pagesize 0;
SQL> spool mapping.txt
select C_SIM_MSISDN,C_SIM_IMSI from RCA_SMART_CARD order by C_SIM_MSISDN;

In ouput file , it look like

SQL> select C_SIM_MSISDN,C_SIM_IMSI from RCA_SMART_CARD order by C_SIM_MSISDN;
060010007 10007
:
:
:
SQL> spool off;

any setting or command that allow me to remove the first sql command line" SQL>select XXXX" and the last command "SQL>spool off" after start up the "spool mapping.txt"

View 1 Replies View Related

SQL & PL/SQL :: Print Output Of Query In Excel Format?

Aug 4, 2010

Is it possible to print the output of a sql query in Excel format without using third party tools like Toad, Hora or any front end Application.

View 10 Replies View Related

Client Tools :: Spool Output In PDF Format?

Jan 3, 2011

I am looking forward for getting the output of audit records in a non-editable format as in pdf or something of that sort. Is it possible from Oracle 10g Rel-2?Can i spool output as PDF format?

View 6 Replies View Related

SQL & PL/SQL :: Output Data Format From Input Table?

May 18, 2011

how can i get the output format from given input table

View 2 Replies View Related

How To Set Exact Page Size For Query Output Format

Oct 2, 2012

I have following shell script :-

In our testing DB local Server , we are using following script ... We are connecting Via putty ...

CODEexport ORACLE_SID=testdb
sqlplus /nolog <<eof
conn sys/sys as sysdba

[Code].....

QUOTE 1. How can i set pagesize to get good format Our problem is output format is not good .. we set set pagesize from 0 to 120 .. no improvement .. " Again Re-Installed VMware tools also ... no improvbement ....

2. We are planning to set alert message if "Archive destination crossed 60 % Script automatically will generate alert message via mobile or mail.. JUst we will configure crontab ....

View 6 Replies View Related

Convert Oracle Sqlplus Output Into ASCII Format?

Sep 20, 2012

I am using oracle database 9iR2 (9.2.0.8) on windows 2003 server.

Is there any method to convert following output in to ASCII format ??

select ename from scott.emp;

ENAME
--------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS

View 6 Replies View Related

PL/SQL :: Arabic Letters In Sqlplus Spool Output In CSV Format

Jul 4, 2013

I have 11.2.0.3 Db in HP UX 11.31, I have to spool a table output in csv format which contains arabic letters in few columns. my spool output is not showing the arabic letters properly.

[oratest@]$ echo $NLS_LANGAmerican_America.
AL32UTF8 V$NLS_PARAMETERS NLS_LANGUAGE                                                    
AMERICANNLS_TERRITORY  
AMERICANLS_CURRENCY                                                    
$NLS_ISO_CURRENCY                                                
AMERICANLS_NUMERIC_CHARACTERS                                          
.,NLS_CALENDAR                                                    

[Code]....

View 0 Replies View Related

Client Tools :: Format Output File By Command Spool?

Jan 20, 2011

I use sqplus in oracle to output the command output to text file .

I use below set environment varialble.

SQL> set echo off;
SQL> set linesize 3999;
SQL> set feedback off;
SQL> set feedback off;
SQL> set termout off;
SQL> set pagesize 0;
SQL> spool mapping.txt
select C_SIM_MSISDN,C_SIM_IMSI from RCA_SMART_CARD order by C_SIM_MSISDN;

In ouput file , it look like

SQL> select C_SIM_MSISDN,C_SIM_IMSI from RCA_SMART_CARD order by C_SIM_MSISDN;
060010007 10007
:
:
:
SQL> spool off;

any setting or command that allow me to remove the first sql command line" SQL>select XXXX" and the last command "SQL>spool off" after start up the "spool mapping.txt"

View 8 Replies View Related

Reports & Discoverer :: Special Characters Coming In Text Output Format Of Oracle?

Jan 27, 2012

I have developed one rdf in Text Output Format.In this some special characters is coming for Text Output format of rdf.Shall i do any adjustments in layout? How to remove these special characters?

View 3 Replies View Related

Convert Indexes Of Non-partitioned Table To Hash Partitioned Indexes?

Sep 10, 2012

RDBMS - 11.1.0.7, I it possible to convert indexes of a non-partitioned table to hash partitioned indexes by retaining table as non-partitioned?

If yes, is this what it is Creating a Hash-Partitioned Global Index - can be created for partitioned and non-partitioned tables?

View 7 Replies View Related

SQL & PL/SQL :: How To Print The Multiple Output Statements In Single Output

Jul 1, 2013

DECLARE
JOBSFILE UTL_FILE.FILE_TYPE;
-- TAKE ALL JOB TITLES FROM JOBS
CURSOR JOBSCUR IS
SELECT *
-- DDOCNAME,DDOCTITLE,DSECURITYGROUP,DDOCAUTHOR,DDOCTYPE,DINDATE,PRIMARYFILE,EXTRACTIONDATE,BATCH_ID
FROM TARGET_UCM ;
[code].......

this is my plsql here to print table values i am using many utl_file.put_line statements is there any way to print all table values in a single utl_file.put_line.

View 2 Replies View Related

SQL & PL/SQL :: Splitting 1 Output Row Into 3 Output Rows

Sep 25, 2013

Currently I have a requirement where I need to create 2 more output rows using each result row.

In my requirement I am populating charges table with types of charges, on each line item of charges, I need to apply 2 types of taxes and populate it along with the charge line item. I will be storing charges in table charges and the 2 taxes to be applied in taxes table respectively. For each row of charges, i need to apply these 2 taxes present in taxes table resulting in 3 rows output.

--Create tables charges
create table charges
(
charge_type varchar2(10) ,
charge number
);

[Code]....

My expected output should be like below:

Item_type amount
-------------------- ----------
charge1 100
Charge1_tax1 10
Charge1_tax2 20
charge2 200
Charge2_tax1 20
Charge2_tax2 40

how I can achieve the expected output using a single sql query

View 6 Replies View Related

Indexes In Oracle

May 9, 2011

this is just a generic question. If a table has multiple Indexes on it say a table of 20 columns where 8 columns have indexes on it (each columns out of the 8 has an index on it and out of which three columns contain unique indexes on it).

If i run a query on this table where only two columns are specified in the where clause are the remaining 6 indexes on the table scanned during query execution.

View 1 Replies View Related

Find Ddl For Any Indexes?

Oct 9, 2012

on 11g R2 on Win 2008

How to find ddl for any indexes built on MY_VW ?

View 6 Replies View Related

Update Global Indexes?

Aug 15, 2011

If I have owner, table_name is there a query I can issue that will tell me if I have to add the "update global indexes" clause when dropping a partition from a table?

View 1 Replies View Related

SQL & PL/SQL :: Indexes On Materialized View

Mar 16, 2011

Why we can create indexes only on materialized view and not on normal views?

View 8 Replies View Related

SQL & PL/SQL :: Locking And Foreign Key Indexes

Sep 1, 2013

Say we have an employee(id_emp) table with a primary key on id_emp. We have also some history tables emp_stuff with columns say (id_emp, dat_event, some_stuff) with primary key id_emp, dat_event.

This means that we have a unique index on (id_emp,dat_event). We also have a foreign key id_emp that references employee(id_emp). When we update id_emp on employee, we still have a lock on emp_stuff. According to this (end of the page) :

Quote:So, in short, with releases prior to Oracle Database 11g Release 1, you will want an index on the foreign key of the child table if you do any of the following:

Update the parent table primary key
Delete from the parent table
Merge into the parent table

So is id_emp in emp_stuff considered as indexed (through the unique index of the primary key) or do we have to add an explicit index
like this CREATE INDEX emp_stuff ON emp_stuff(id_emp) to avoide child table locks?

View 33 Replies View Related

SQL & PL/SQL :: Using Function-based Indexes In SE?

Mar 29, 2012

I have noticed a very questionable phrase on an article updated in 2011: "Oracle SE may allow you to create a function-based index, but you must pay for an EE license to use FBI's." [URL] Is this true? I have tested a FBI on my SE and works just fine.

Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
SQL> create table t ( col varchar2(10));
Table created.
SQL> create index t_idx on t(upper(col));
Index created.
SQL> insert into t values('a');

[code]....

View 11 Replies View Related

SQL & PL/SQL :: Indexes For Query Performance?

Mar 16, 2010

Lot of my tables are just for join purpose , a table with just with two columns both FK , I did not add any composite PK to this table, lot my queries uses this table , because it does not have composite PK will the query be slow ? what I have to do to increase performance querying with such tables ?

View 5 Replies View Related

SQL & PL/SQL :: How Many Different Types Of Indexes Have In Oracle

Jul 12, 2012

How many different types of indexes we have in oracle?

View 2 Replies View Related

How Efficient Are Partitioned Indexes

Feb 22, 2013

Using Oracle 11.2.0.3

We are evaluating partition strategies with view to achieving performance gains in reporting in particular. How efficient are partitioned indexes in this regard e.g.

just partitioned indexes on an un partitioned table.

One large fact table with durrogate keys on which have bitmpa indexes which link to unique key in associated dimensions. Considering partitioning the bitmap index which links to the largest dimension and similarly partition the dimension key on largest dimension.

View 5 Replies View Related

SQL & PL/SQL :: Find Out Database Name For Indexes

Jan 15, 2013

I am trying to find out database name for the unusable indexes thru the query; I am using dba_indexes for the index name but not sure which view I need to join with to find the db name.

View 11 Replies View Related

Check That Indexes Need Rebuild?

Jul 31, 2013

How can i check that indexes need rebuild?

View 6 Replies View Related

Size Of Tables And Corresponding Indexes

Dec 13, 2012

Does size (number of rows) of table and corresponding indexes have influence on INSERT operations (bigger table - slower insert)?

I supposed that if select returns the same rows in small and big table, there will be no difference in performance?

View 4 Replies View Related







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