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.
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
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
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
how to read the excel data and insert into tables without using SQL loader. i tried using OLE2 package,but i am getting an non-oracle exception. even i tried using CSV format. but i couldn't make it.
how to import data from Excel sheet (inside server) to oracle DB.
Explanation : I need to automate this work, whenever i get an excel sheets with table values, i need to import the table values in to oracle DB automatically.I need an immediate solution for this.
i need to do a loop on a table and export the data in Excel format (so i need a procedure to do it).
write a java class that build this Excel.. oracle procedure loop around the data and every step my Java class write on Excel file.
So, for this i should initialize my java class for example
public class ExcelExporter{ private String fileName; public ExcelExporter(String fileName){ this.fileName = fileName; } }
So, for this issuse i should call the ExcelExporter costructor from my PL/SQL so i can create an object of it and next i use this to populate my Excel.
How its possible to create Java object from PLSQL ? I've seen on the net all procedure call only Java static methods...
I need to generate a single excel file which each department details in seperate sheets of the excel file, each sheet should ne named with department no (ex :Dept_10, Dept_20,Dept_30) and the main excel should be named as Emp_details.
Header row and total row should be in bold.I have created 3 individual excels thru toad and moved them to single excel and renamed the sheets.
Can we programatically do this thru SQL or PL/SQL, does Oracle provide any build packages for excel.As excel files cannot be uploaded,I am unable to upload the excel file I generated manually.
select to_char(empno) empno,ename,job,mgr,hiredate, nvl(sal,0) sal,nvl(comm,0) comm,nvl(sal,0) + nvl(comm,0) "Total Income" ,deptno from emp where deptno = 10 union all select 'Total',null,null,null,null,sum(nvl(sal,0)),sum(nvl(comm,0)),sum(nvl(sal,0))+sum(nvl(comm,0)),null from emp where deptno = 10;
i want to export excel sheet in database table, so i have converted excel file in .csv file(comma delimated)and made control file, then i started sqlldr by double clicking on it. path is-D:oracleproduct10.2.0client_1BIN
i run this command from cmd-
Microsoft Windows [Version 6.1.7600] Copyright (c) 2009 Microsoft Corporation. All rights reserved. C:UsersNeetesh>sqlldr scott/tiger@localdb control=c:/users/neetesh/scott_data. ctl SQL*Loader: Release 10.2.0.1.0 - Production on Tue Jul 17 17:20:33 2012
[code]....
and i attached the .ctl file. and .csv file is stored on same directory as .ctl file, why oracle couldn't find the .ctl file.
i am accessing a form from server through URL using oracle application server FORMS & REPORT services. and i am trying to export table data into excel sheet on my local machine or the server itself.
i am using the following code on WHEN BUTTON PRESSED
PROCEDURE REFERENCELIST IS BEGIN DECLARE AppID PLS_INTEGER; ConvID PLS_INTEGER; RCount Number := 0; CURSOR c1 IS SELECT MATID, MATCODE ,DESCRIPTION , PARTNO FROM REFERENCETABLE; PROCEDURE INSERTROW (invalue VARCHAR2, incol NUMBER) IS
[code]....
this does not give any error and any result as well. the above code very much fine while i run it on my local laptop.
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
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
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.
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].....
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.
how to automate a data from oracle into excel...i have a table "emp" in oracle database now i need colums of emp ex:firstname ,last name, id from that table into excel.
so i need a script which when you schedule it it should create a excel file in particular postion,i was told we have to crete a directory from sql and using utl_file then we have to write a script and then schedule that.out look in excel should be
My client has asked me to provide production data from a xyz table into excel.The constrain for the same is that he needs the data for a particular month within a specific range of dates.
Is this possible using oracle utilities like SQL developer, plsqldeveloper or toad or any other. If yes i want to know if this is possible without using the same?
i have problem with export data from SQL to excel format my question do i can export directly data from sql to excel or i have to write sql statement in form report and from form i can export it to excel
We have requirement such that whenever stored procedure is executed, their resultant records has to be stored in excel file ( Just like an reports ).No third party tool or reporting tools are used.
is there any option in oracle (Stored procedure or built in packages ) which can create excel file with the resultant records.
