SQL & PL/SQL :: Spooling Xml Column Into One Single Row?

Jun 17, 2013

I have been trying to spool a table into a .lst. The table is spooled correctly, but there is a column which has xmlType data into. The problem is it does not write the xml into a single row. Every time it find a xml node, the spooling gets indented.

My code is as follows:

-- Establece opciones de formato.
set echo off;
set feedback off;
set heading off;
set recsep off;
set verify off;
set embedded off;
set long 1000000;
set pagesize 0;
SET LINESIZE unlimited;
set trimout off;
set trimspool on;
set serveroutput on size unlimited;
set term on;

-- Definicion de variables del script
prompt Introducir Creador de la tabla
prompt Creador de la tabla: &&1
prompt Introducir Nombre de la tabla
prompt Nombre de la tabla: &&2
prompt Introducir Directorio del fichero
prompt Directorio del fichero: &&3
prompt Introducir Nombre del fichero
prompt Nombre del fichero: &&4
prompt Introducir Caracter separador de campos
prompt Caracter separador de campos: &&5
prompt Campo fecha: &&6

-- Redirige la salida al fichero

spool /&&3/&&4
SELECT H5279_CD_EMP || '&&5' ||
H5279_LOCAL_ID || '&&5' ||
H5279_LOG_TYPE || '&&5' ||
H5279_GLOBL_ID || '&&5' ||
TO_CHAR(H5279_CREATED, 'YYYY-MM-DD HH24:MI:SS.FF3') || '&&5' ||

[Code] .......

where the field LOGDATA contains the xml. And the output im getting is as follows(I have attached the file)

0049|16052B0E44E3A30037E3CB59|4|16052B0E44E3BF0037D9CB4E|2013-06-12 11:51:16.387|16052B0E44E3A90037E2CB58|n31001|172.31.184.2|trxOpService|BDPESP_CIC_SAN_ENS_V1|||INTERNET|ALN_BDPGPO_Localizperscomun|| OI_LocalizarPersPorDocumentoComun|||ISWSBKSD149|frNixc6Vlic01jhixt5TkfT|16052A9553AE930037FAC740| <?xml version="1.0" encoding="ISO-8859-1"?>

[Code]....

View 5 Replies


ADVERTISEMENT

Spooling Column Headers Correctly

Sep 13, 2012

Im trying spooling a file in DB with below mentioned details.

SET SERVEROUTPUT OFF
set heading on
SET PAUSE OFF
SET FEED OFF
SET PAGES 0
[code]........

file .sql has 20 odd columns.

When file is spooled, it shows no columns headers at the top, why is it happening, I don't want to spool column headers separately.

View 12 Replies View Related

SQL & PL/SQL :: How To Convert Multiple Column Values Into Single Column

Jul 19, 2013

CREATE TABLE TYPE
(
c1_type VARCHAR2 (10),
c2_type VARCHAR2 (10),
c3_type VARCHAR2 (10),
c4_type VARCHAR2 (10),
c5_type VARCHAR2 (10),
c6_type VARCHAR2 (10),
[code]......

actual output of the below query, but i want to display in different way

select * from type;

C1_TYPE C2_TYPE C3_TYPE C4_TYPE C5_TYPE C6_TYPE C7_TYPE C8_TYPE C9_TYPE
Region_D Region_E Region_F Region_D Region_E Region_D Region_M Region_D Region_E

The expected output should be like this below, how to write a query or which built in function used to get the below result,

Region_D
Region_D
Region_D
Region_D
Region_E
Region_E
Region_E
Region_F
Region_M

View 4 Replies View Related

SQL & PL/SQL :: Spooling From XML

Mar 29, 2010

I am using Oracle 10g and I have XML stored in column XMLTYPE of my table. I want the XML in a flat file and process it to C exe.
My XML Structure is like this

<Data>
<Tag1>
<id>val1</id>
</Tag1>
<Account>
<Number>1111119&</Number>
</Data>

When I try to spool the XML i get the o/p without new line format as

<Data><Tag1><id>val1</id></Tag1><Account><Number>1111119&</Number</Data>

I want the o/p with new line after every tag and replace character like & to & . i can use utl_file too but spool is easy

View 2 Replies View Related

SQL & PL/SQL :: Displaying A Single Row Into Column?

Mar 26, 2013

I just want to see single rows data as column.

select * from emp where rownum=1;
EMPNOENAMEJOBMGRHIREDATE SAL COMMDEPTNO
7369SMITHCLERK790217-DEC-80 00-00-00 800 20

How can it show as

column_name column_value
EMPNO 7369
ENAME SMITH
JOB CLERK and so on...

View 13 Replies View Related

SQL & PL/SQL :: Duplicate On A Single Column

Sep 23, 2010

I have a table with multiple columns and I need to retrieve the ones where column a stores duplications for records where column b is discrepant

this is the table

NAME_ID PHONE_NUMBER
12345 +41 22 595 5555
12345 +41 22 595 5555
12342 +41 22 595 5500
12340 +41 22 595 5555

the query should return

NAME_ID PHONE_NUMBER
12345 +41 22 595 5555
12340 +41 22 595 5555

The closest I got was with the below, but this also returns duplicates within the same NAME_ID.

select phone_number, name_id
from name_phone
where (phone_number) in
(select phone_number
from name_phone
group by phone_number
having count(*) > 1)
group by phone_number, name_id
order by phone_number

View 3 Replies View Related

SQL & PL/SQL :: Spooling Every 25 Rows In Excel?

Oct 11, 2012

I need to create a program wherein it will create an excel files through spool command for every 25 records only. So if my select statement will return 100 records, it will create 4 excel files, 25 records for each excel file. i have these sample codes,it should be the logic, but then i'm having syntax error.

set serveroutput on size 1000000
SET PAGESIZE 0
SET LINESIZE 299

[Code].....

View 8 Replies View Related

Loading Single Column Into Table?

Nov 16, 2012

I am trying to load a single column into a Table. Below is the ctl file:

OPTIONS (ERRORS=10000)
LOAD DATA
TRUNCATE
INTO TABLE "ECOMM"."CC_ECOMM"
TRAILING NULLCOLS
(EMAIL_ID)

Calling loader:

sqlldr silent=ALL userid=username/password@DB control=/home/DEV/control/CC_ECOMM.CTL log=/home/DEV/log/Unsubs.log bad=/home/DEV/log/Unsubs.bad discard=/home/DEV/log/Unsubs.dis data=/home/DEV/files/CC_ECOMM.TXT

Though the count in the Table after load is the same as the input file, they are all NULL.

I tried adding FIELDS TERMINATED BY X'A' for new line and also FIELDS TERMINATED BY X'D' for carriage return. Both times bad file was created and the records that were loaded were again NULL.

The input file has a list of emails:
iatraveler2008@aol.com
iaz65@aol.com
2blue2brown@comcast.net
2c3mwilson@embarqmail.com
abigailolschan@comcast.net
imisskoco@aol.com

I tried FIELDS TERMINATED BY X'10' and FIELDS TERMINATED BY X'13' too for new line and carriage return respectively. This time there were no bad file created, but the Table has Null values.

Log File contents:

Number to load: ALL
Number to skip: 0
Errors allowed: 10000
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional
Silent options: FEEDBACK, ERRORS and DISCARDS

[code]....

View 4 Replies View Related

SQL & PL/SQL :: Replace Multiple Value In Single Column?

Jun 7, 2011

I am facing some problem, while fetching the result that I want to. I have a table with name "test", there are two columns:

"id" type int
"text_data" type varchar2(2000)

Sample Data:
ID TEXT_DATA
------- ------------
10 Hi Deepak, My designation id is dsha21. Thanks Rohit

Now I tried to replace the value for "Deepak","dsha21" and "Rohit" using nested replace function and I succeded but that was for static. Now while creating SQL procedure where I am going to make the values of "Deepak","dsha21" and "Rohit" some static variables. I want to pass the values to be replaced with static parameter.

If I give you simple example of my requirement that would be example of a sms send to all customers by a telephone company. Content is same only the Name of customer is replaced everytime.

View 10 Replies View Related

SQL & PL/SQL :: Copy Single Column From One Table To Another?

May 27, 2010

How can i copy a single column from one table to another. Table 1 has a column with data in it, table 2 has the column but is empty, i want to copy data for a single column from table1 to table 2. By the way these table have multiple columns.

View 8 Replies View Related

SQL & PL/SQL :: Update Single Column In X Table

Sep 2, 2011

i need a query for update..the logic is

i have to update a single column(x.c) in x table.here the condition is x.a is not null and x.b is not null x.d is null then update x.c=x.b for each row.

View 2 Replies View Related

Create A Table Without Any Single Column In It?

Nov 15, 2012

Is it possible to create a table without any single column in it?

View 1 Replies View Related

PL/SQL :: Converting Multiple Column In Single?

Apr 9, 2013

need to create a table with single column by using select statement with multiple columns
For Ex- i have 1 row with 10 columns (may be more than 10) like
'A','B','C','D','E','F','G','H',I','J'
i written sql like
select 'A','B','C','D','E','F','G','H','I','J' from dual

result is - 'A','B','C','D','E','F','G','H','I','J' with 10 columns

Now i need output lik this using SQL
Text
------
'A'
'B'
'C'
'D'

[code]...

sort out this problem.

View 6 Replies View Related

PL/SQL :: Convert Rows To Single Column

Dec 11, 2012

I have a table where i want the output to be a single column

ex: select from t1;*
query output_

rownum col_1
1     8217
2     6037
3     5368
4     5543
5     5232

i would like the result to be : *8217,6037,5368,5543,5232*

i did look on the web but can't find a solution that is easily understood.

View 9 Replies View Related

Fetching Multiple Rows For Single Column

Jun 25, 2012

I am trying to write a script where a particular post code from a table is having more than 3 telephone numbers.Both the columns are in the same table. How to fetch.

Table is P_Order
Columns are DELIVERY_POSTCODE and TEL_NO...
Condition DELIVERY_POSTCODE has more than 3 TEL_NO

View 1 Replies View Related

SQL & PL/SQL :: Query On Single Column To Display As Rows

Aug 5, 2010

I has a table of structure of varchar2 datatype.

NO
----------
1-2
3-4
5-6

desired output is:

SQL>1
2
3
4
5
6

The table has single column & the values may differ,that is, they may have 1-2-3-...-n in a single row, but the desired output is to be in the rows as shown above.

I tried concepts of SQL up to my knowledge, but I failed. The query to be done only in SQL.complete this query.

View 10 Replies View Related

SQL & PL/SQL :: Query To Convert Data In Single Column?

Oct 11, 2012

I have a below requirement let me know the optimized query.

Create table test_123 ( sr varchar2(1000));

Insert into test_123 values ('1,2,3,4,5');
Insert into test_123 values ('6,7,9,10,11');
Insert into test_123 values ('9,2,3,8,5');

I need output as :

Sr_output

1
2
3
4
5
6
7
8
9
10
11

I need all the values in the column sr in a single column and mutiple rows.

View 2 Replies View Related

SQL & PL/SQL :: Select Multiple Values Into Single Column

Oct 5, 2011

I have following tables with data as under:

table1: table2:
column1 (char) column1 (char) column2 (num)
A A 10
B A 20
C B 15
D C 12
E D 25
D 9

I need to generate output as :

column1 column2
A A10, A20
B B15
C C12
D D25,D9
E null

Is there anyway to achieve this thru simple SELECT ...and if not, then thru any PL/SQL construct..?

View 5 Replies View Related

SQL & PL/SQL :: Update Two Table Column In Single Query

May 25, 2012

How to update two table column in single query ?

example :

update table1 t1 ,table2 t2
set t1.column = 'Yes',t2.column='Yes'
where t1.emp_code =t2.emp_code ;

View 1 Replies View Related

SQL & PL/SQL :: Append More Rows In Single Clob Column

Apr 9, 2013

I have a query which returns nearly 20k rows, as per the requiremnet we need to append all these rows in specific format and insert into single clob column.in the below procedure test_clob.textt is clob field.

CREATE OR REPLACE PROCEDURE pro_test
v_mas_seq NUMBER (9);
v_gov_total NUMBER (20, 2);
v_emp_total NUMBER (20, 2);
v_text_exp CLOB;
v_pageaccess VARCHAR2 (15);
v_dto NUMBER (7) := 4011486;
v_batchno NUMBER (20) :=
[code]....

View 15 Replies View Related

PL/SQL :: Update A Single Column Data In 10 Tables?

Oct 2, 2012

We have to update a single column data in about 10 tables which has child/parent table relations, pk/fk constraints.. The column that we are updating is a part of primary key in half of the tables and part of foreign key in the other half tables.. I'm thinking of disabling all the foreign key constraints in the tables then update the column data then enable the foreign key constraints in these tables.

View 7 Replies View Related

Spooling Into Excel - Retrieves Data From Different Table

Dec 25, 2010

i have a requirement..i have a script which retrieves data from different table and my requirement is using spool, i need the data from each table to be spooled into different sheets in the same excel sheet.

CODEspool d:\spool\spool1.xls
select * from tab1;
select * from tab2;
select * from tab3;
spool off

in the above code, data from tab1 should be spooled into sheet1 and data from should be spooled into sheet2 and so on.. in the same excel.

View 7 Replies View Related

Client Tools :: Spooling Output In Windows

May 12, 2010

I am an IT auditor who has been using Oracle 10g Express to test some audit scripts we have created. I write the output of each query in the script to a separate file because it makes it easier for us to go through. However, I cannot get SQLPlus to spool the output files at all. I do not get any errors from running the script, and I can see the results in the SQLPlus window, but I cannot find where the files are actually spooling to, if they are spooling at all.

I didn't specify a network path for any of the spool files because the scripts need to be as generic as possible so they can run on any Windows or UNIX box. An example of my code is below:

spool Audit_Ver

SELECT * FROM V$VERSION;

spool off

But if I search my harddrive for "Audit_Ver" no such file can be found anywhere.

View 7 Replies View Related

SQL & PL/SQL :: Spooling Data In Pipe Delimited File

Feb 15, 2010

I am trying to Spool the data in pipe delimitted csv file but some of the records going on another line from the same records. Currently some of the data going to next line as below oulined in the 2nd and 3rd line (in bold - |Home & Family) . I have following sql setting in my spool file:

set linesize 4000 pagesize 0 trimspool on feedback off verify off echo off
set define off
spool Stk_hold_Sec_Tsk.csv

I tried increase linesize to 5000 but its not working.
Ex.

PSS:Production Manager|ZS:PsS:PP:PROD_TCODES|P2S: PP - Production Transactions|House & street
PSS:Production Manager|ZC:BW:PsS_RPT_MGR|BW PsS Reports Manager
[b]|House & street[/b]

PsS:Production Manager|ZC:BW:PsS_RPT_USER|BW PsS Reports User
[b]|House & street[/b]

PsS:Master Data (PDMs)|ZS:GEN:GENERAL_USER|GEN: General User|House & street

Data should be like into the file:

PSS:Production Manager|ZS:PsS:PP:PROD_TCODES|P2S: PP - Production Transactions|House & street
PSS:Production Manager|ZC:BW:PsS_RPT_MGR|BW PsS Reports Manager|House & street
PsS:Production Manager|ZC:BW:PsS_RPT_USER|BW PsS Reports User|House & street
PsS:Master Data (PDMs)|ZS:GEN:GENERAL_USER|GEN: General User|House & street

I think it should be something with linesize or pagesize but not sure

View 18 Replies View Related

How To Get Start And End Date From Single Column With Iterative Records

Jul 25, 2013

The problem I am facing analyzing a certain table s trying to get a proper start and end date for a specific field such as TICK_COL, because there are so many other fields are being updated in this table - all using MTC_DATE - this is causing iterations of TICK_COL.

So first step was to just use lead to get the end date for all iterations so I could picture how this might look with a start and end date

CODE A (see below)
FROM_DATE TO_DATE
SKU TICK_COL MTC_DATE LEAD (MTC_DATE)
21524804 RIBG 10101 20080615
21524804 RIBG 20080615 20080625
21524804 RIBG 20080625 20080628
21524804 RIBG 20080628 20080920
21524804 RIWH 20080920 20080923
21524804 RIGR 20080923 20080930
[Code] .......

My first bright idea? I tried using Rank as well, hoping to rank each of this tick_color changes as 1, which works for the exception of when tick_col changes to RIWH or RIGR again.

The ranking function doesn't see the 2nd change to RIWH as entirely unique and assigns it a 2 and 2nd change to RIGR a 3. If I could rank each of those as 1 I could query these results as an in-line view where rank = 1 and do lead to get the start and end date, finished,

CODE B (see below)
21524804 RIBS 20130725 20130725 8
21524804 RIBS 20130327 20130725 7
21524804 RIBS 20130317 20130327 6
21524804 RIBS 20130312 20130317 5
21524804 RIBS 20120813 20130312 4
21524804 RIBS 20100916 20120813 3
21524804 RIBS 20100518 20100916 2
21524804 RIBS 20091120 20100518 1
[Code] ........

I am Expecting to see this below:

21524804 RIBG 10101 20080920
21524804 RIWH 20080920 20080923
21524804 RIGR 20080923 20081031
21524804 RIWH 20081031 20090311
21524804 RIGR 20090311 20091120
21524804 RIBS 20091120 20130725

The code I used to generate the first table was which obviously, does not get me as far as I�d like.

CODE A

SELECT sku_nk,
ticket_type_color,
TO_NUMBER (TO_CHAR (mtc_date, 'YYYYMMDD')) mtc_date,
CASE
WHEN LEAD (TO_NUMBER (TO_CHAR (mtc_date, 'YYYYMMDD')), 1, 0)
[code].......

CODE B

SELECT sku_nk,
ticket_type_color,
TO_NUMBER (TO_CHAR (mtc_date, 'YYYYMMDD')) mtc_date,
CASE
WHEN LEAD (TO_NUMBER (TO_CHAR (mtc_date, 'YYYYMMDD')), 1, 0)
[code].........

View 3 Replies View Related

PL/SQL :: Concatenating Columns (in Multiple Rows) To One Column In Single Row

Nov 28, 2012

column1    column2            column3    column4

12                Mar-21-2005 BDW        blah blah blah
11               Feb-07-2001    ZV            ha ha ha
12                Jan-02-2002   YM           zuck zuck zuckI want a view that has that data like this:

column1    column2         

12                Mar-21-2005 - BDW - blah blah blah; Jan-02-2002 - YM     -      zuck zuck zuck
11               Feb-07-2001    ZV            ha ha haCan you help with SQL ?

I tried to use this Oracle LISTAGG function in the SQL, but got a "string concatenation limit exceeded"

View 3 Replies View Related

Client Tools :: How To Remove Dotted Line While Spooling

Jan 27, 2010

i have a issue that when i spool the sql command i get a doted lines below the heading. how to eliminate the doted line but need the heading.

Previous:
OCCUPACODE OCCUPADESC
-------------------- -----------------------------------
1 BUSINESS
2 SERVICE

Needed :
OCCUPACODE OCCUPADESC

1 BUSINESS
2 SERVICE

View 1 Replies View Related

PL/SQL :: Spooling Data From Remote Server To Local Machine

Feb 6, 2013

I need to spool data from a remote server using putty(sqlplus) to a local machine. There are credentials i need to give before accessing the remote databases and i am able to do it..i tired with the below query but the spool file(csv or txt) is not able to create on local machine.

set colsep ,
set pagesize 120
set trimspool on
set headsep off
set linesize 1000
set numw

spool D: estmyfile.csv
select table_name, tablespace_name from all_tables;
spool off

View 2 Replies View Related

JVM :: Java Print API Shows Different Job Name On Printer Than In Spooling Queue

Sep 20, 2013

I am using Java print API (javax.print package) to send a bunch of documents for printing. Below is the code section that I am using to print documents through java program. When the document is sent for printing, I see the Job name is created properly in the print spooling queue on windows machine. But, when i go to the actual printer, the job name is different on the printer than what i saw in spooling. Since I am printing 100s of documents in batch, it gets very difficult to identify which document did not print, in case of issues.  I also used the java.awt.print package . The java.awt. print. PrinterJob has a method setJobName(String). When i used this package, I got the name appear properly in both places. But I wish to use javax.print with the document name appear on printer queue.    

public void printDocument(File pDoc, PrintService pService, DocFlavor pFlavor)   
throws Exception  {    logMessage(true, "Printing Doc::" + pDoc.getAbsolutePath());   
FileInputStream is = new FileInputStream(pDoc);    
// Create the print job    DocPrintJob job = pService.createPrintJob();    
//Set print request attributes with file name as job  
[code].....

View 0 Replies View Related

Client Tools :: Ignore Sql Prompt And Command Entered While Spooling?

Oct 24, 2011

Is it possible to ignore the sql command prompt and the command entered while spooling. I tried the to set echo off,term off and some more options but it did not work.

Example:

SQL> spool c: est.log
SQL> Prompt "This is a test"
"This is a test"
SQL> spool off;

The spool file has the following

SQL> Prompt "This is a test"
"This is a test"
SQL> spool off;

Is it possible to set some options so that the spool file only contains output.

"This is a test"

View 3 Replies View Related







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