SQL & PL/SQL :: Can't Use PUT_LINE As This Will Put NEW_LINE At End Of Line
Sep 30, 2009
I'm writing following block,
Begin
str := '...'
....
f1 := utl_file.Fopen('EXT_REP_DIR',b,'W',32767);
utl_file.Put(f1,str);
Loop
....
utl_file.Put(f1,str);
End Loop;
utl_file.Fclose(f1);
End
There would be around 100000 records and I want everything in 1 line(i.e no line breaks). I'm getting error ORA-29285, WRITE_ERROR...By using PUT_LINE, the size of the file is for 10000 records is 3035542. But, I can not use PUT_LINE as this will put NEW_LINE at end of line.
View 4 Replies
ADVERTISEMENT
Jan 18, 2011
I want to extract data from a table and write into a txt file using UTL_FILE utility.I have written the below query. I am able to see query is returning data.But it is not creating file and writing data into it.
CREATE OR REPLACE DIRECTORY DOC_PATH AS '/apps/orarpt/SJDEV/utl'
CREATE OR REPLACE PROCEDURE xxcfi_outbound_test (errbuf OUT VARCHAR2,
retcode OUT VARCHAR2)
AS
CURSOR emp_cur
[code].....
View 1 Replies
View Related
Feb 28, 2013
I am executing a plsql procedure and trying to increase buffer size to display all characters, procedure is given below:
create or replace procedure prc_p(prm_t1 in VARCHAR2,
prm_t2 in VARCHAR2,
prm_tab in varchar2
)
AUTHID CURRENT_USER
as
str_sql VARCHAR2 (4000);
[code]..........
View 1 Replies
View Related
Feb 13, 2013
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
We are processing spatial data from another source to display in our GIS environment. The data is a set of multi lines. The gtype is 2006. A typical geometry looks like:
SDO_GTYPE 2006
SDO_SRID 31370
SDO_POINT.X NULL
SDO_POINT.Y NULL
SDO_POINT.Z NULL
SDO_ELEM_INFO (1,2,1, 7,2,1)
SDO_ORDINATES (105094.84, 195084.96,
105094.54, 195080.22,
105094.84, 195084.96,
105094.84, 195084.96,
105094.68, 195082.47 )
Now, this is not an actual multiline... it's just encoded as a multi line, but if you look at the coordinates you'll see that the end point of the first line is the same as the beginning of the second line (105094.84, 195084.96).
A better way to encode this geometry would be:
SDO_GTYPE 2002 <---
SDO_SRID 31370
SDO_POINT.X NULL
SDO_POINT.Y NULL
SDO_POINT.Z NULL
SDO_ELEM_INFO (1,2,1)
SDO_ORDINATES (105094.84, 195084.96,
105094.54, 195080.22,
105094.84, 195084.96, <---
105094.68, 195082.47 )
Is there a standard function in Oracle that does this conversion for me? Or do I have to write my own :)
View 4 Replies
View Related
Jul 18, 2012
I have a table
Line_num Amount
1 10
2 20
3 30
create table Test (line_num number, amount number);
insert into test values(1,10);
insert into test values(2,20);
insert into test values(3,30);
I need to get the line number which has a maximum amount.
View 2 Replies
View Related
Jul 31, 2012
how to get more lines into one line per customer.
For instance:
CREATE TABLE XXX_MAPE_CC
(
accounting_month_key number,
customer_key VARCHAR2(18 BYTE),
total_amount VARCHAR2(29 BYTE)
)
insert into XXX_MAPE_CC
values (201205, 313774201, '15,03')
[code]...
And I would like get every total_amount to individual columns by accounting_month_key
--------------------------------------------------------------------------
customer_key month_201205 month_201206 month_201207
----------------------------------------------------------------------------
313774201 15,03 10,03 13,10
----------------------------------------------------------------------------
View 3 Replies
View Related
Jul 28, 2010
I tried writing a decode statement but it doesn't work as expected. The data I'm working has 4 different brand names with an associated code. I tried using decode to get the 4 brands to be on one line like this: Description, Inventory_Item, Product_Line, Product_Type, Brand_1, Brand_2, Brand_3, Brand_4..I still get 4 lines with the Brands displayed like a stair steps.
What do I have to do to get the data on one line? My code is as follows:
SELECT sgk_invfg_organization_items.description,
sgk_invfg_organization_items.inventory_item,
sgk_invfg_organization_items.product_line,
sgk_invfg_organization_items.product_type,
Decode(sgk_item_master_launch_v.sgk_model_item, 20,
sgk_item_master_launch_v.sgk_model_number) brand_1,
Decode (sgk_item_master_launch_v.sgk_model_item, 30,
[code]...
View 5 Replies
View Related
Jul 25, 2012
I am trying to get a line of best fit for a set of data in oracle
TERM Count
TERM_080178
TERM_080258
TERM_08030
TERM_080482
TERM_08050
TERM_08060
TERM_080732
TERM_08080
TERM_090192
[code]...
View 4 Replies
View Related
Dec 5, 2011
I want run sql connection and command in one line , something like this ...
sqlplus "/as sysdba" 'select * from dual'
is it possible to do it in one command ?
View 3 Replies
View Related
Aug 29, 2011
I have a select that return the query like this:
EMPCODEMPCONNOMEEMPCONFONEEMPCONEMAI
434Ronaldo 11 25411414compras@gralha.br
434Ronaldo 11 21454117compras2@gralha.br
434Thiago 13 25418745thiago.alcarin@br.com.br
so,I need the query result in just one line...
EMPCODEMPCONNOMEEMPCONFONEEMPCONEMAI EMPCODEMPCONNOMEEMPCONFONEEMPCONEMAI
434Ronaldo 11 25411414compras@gralha.br 434Ronaldo 11 21454117compras2@gralha.br
I saw some exemples of "pivot query" but I still looking for a solution.
View 10 Replies
View Related
Mar 4, 2011
I am having trouble putting all 3 of these decodes together on one line. I would like my output to return like this:
ytd_state_tax ytd_ss_tax ytd_medicare_tax
============= ========== ================
182.29 163.28 56.37
if deduct_type=6012 return sum(pdt1.deduct_amount) YTD_MEDICARE
else
if deduct_type=6020 return sum(pdt1.deduct_amount) YTD_STATE_TAX
else
if deduct_type=6010 return sum(pdt1.deduct_amount) YTD_SOCIAL_SEC
[Code]....
View 7 Replies
View Related
Sep 16, 2011
I am getting the below error when executed the query below in production database.The database version of both the database is 10.2.0.4
ORA-01008: not all variables bound
ORA-02063: preceding line from DBLINK.PROD.NIC.CMS
select count(*)
from order_tbl@DBLINK.PROD.NIC.CMS a
group by a.col1
having count(distinct a.col2) > 1
the same sql is working fine in dev environment.Is this a bug?
View 12 Replies
View Related
Nov 27, 2009
My query is suppose to capture the student's number, date of birth, and number of students which that students mentors. I am suppose to show only the 5 oldest students
SELECT s.std_num, s.birth_date, COUNT(s2.mentor_num)
From Student s left outer join Student s2
on s2.mentor_num=s.std_num
GROUP BY s.std_num, s.birth_date
ORDER BY s.birth_date ASC;
This function works but the problem is in displaying the five records because whenever I apply the rownum<=5 function the counter returns incorrect results. I tried writing another query to fix it but I could not dispay the counter anymore
SELECT ROWNUM as SENIOR, s.std_num, s.birth_date, COUNT(s2.mentor_num)
FROM (SELECT s1.std_num, s1.birth_date, COUNT
(e2.super_id)
From Student s left outer join Student s2
on s2.mentor_num=s1.std_num
GROUP BY s1.std_num, s1.birth_date
ORDER BY s1.birth_date ASC) S
WHERE row_num<=5;
This code only works if I remove the count in the first line but I need to display the number in my result.
View 2 Replies
View Related
May 22, 2011
CREATE TABLE LOCATION(
Locid NUMBER (5) LOCATION_LOCID_PRIMARY KEY,
Bldg_Code VARCHAR2 (10) NOT NULL,
Room VARCHAR2 (6) NOT NULL,
Capacity NUMBER (5));
CREATE TABLE FALCULTY(
[code]...
View 6 Replies
View Related
Mar 10, 2012
I'm working on my database homework, and I came across an error that I cannot seem to fix. I'm getting a missing right parenthesis error on line 7, but I seem to have all my parenthesis, endless I'm overlooking one.
DROP TABLE RoomDM CASCADE CONSTRAINTS PURGE;
1 CREATE TABLE RoomDM (
2 rNo VARCHAR(4),
3 hNo NUMBER(3),
4 type CHAR(6) NOT NULL,
5 price NUMBER(3,2) NOT NULL,
6 CONSTRAINT RoomDM_PK PRIMARY KEY (rNo, hNo),
7 CONSTRAINT RoomDM_hNo_FK FOREGIN KEY (hNo)
8 REFERENCES HotelDM (hNo) ON DELETE CASCADE,
9 CONSTRAINT RoomDM_type_CK CHECK(type IN(single, Double, Family))
10 );
The error looks like this:
CONSTRAINT RoomDM_hNo_FK FOREGIN KEY (hNo)
* ERROR at line 7:
ORA-00907: missing right parenthesis
View 2 Replies
View Related
Mar 21, 2012
I am running Oracle 9 and using sql loader to import text file into table. Can sql loader skips the record which blank line or carriage return? Do I need to set up with options?
View 1 Replies
View Related
Sep 18, 2010
I have a table test_test
Name Mark
------- ------
ABC 10
DEF 10
GHI 10
JKL 20
MNO 20
PQR 30
The result should be
mark count names
10 3 ABC,DEF,GHI
20 2 JKL,MNO
30 1 PQR
View 3 Replies
View Related
Feb 18, 2013
I have some Oracle Databases and sometimes for me is easier to access to the databases with SQLPLUS instead SQLDEVELOPER or TOAD.
But i can't reach to a good configuration of command line prompt to do queries against the database, normaly if it returns a lot o rows and columns it is unreadable.
View 5 Replies
View Related
Sep 2, 2010
Replacing a new line character in clob column i Oracle 10g.I have tried replacing the new line using regexp_replace,replace and translate. I have also tried using lob_relace procedures.
View 2 Replies
View Related
May 19, 2010
I have this query:
select distinct event_number from events_total WHERE event_id = 16395493
minus
select distinct event_number from event_details_ford WHERE event_id = 16395493
result of which is :
6L2Z-7861693-AAC
6L2Z-7862187-CAC
i want to put this in dynamic sql where clause :
where event_number in ('6L2Z-7861693-AAC','6L2Z-7862187-CAC'). and if the result of the query is only one number, then where event_number in (6L2Z-7861693-AAC) result of the query can be NULL also. but i think i can use IF condition for "SELECT ..WHERE event_number in " query
so how can i put the results of query in one line, so that i can use it in where clause.
View 12 Replies
View Related
Feb 8, 2010
I am trying to use utl_tcp to connect, through TCP/IP, to a 3rd party program PC Miler. When I use telnet, PC Miler works without any issue. However, when I use utl_tcp, beginning from the second PC Miler API call, when I do a utl_tcp.get_line to get the result, the first line that return is always blank (with 1 white space). Here is my program.
---------------------------------------------------
DECLARE
c utl_tcp.connection;
ret_val PLS_INTEGER;
v_data VARCHAR2(4000);
BEGIN
c := utl_tcp.open_connection('mars', 8145);
[code]....
Note that the 1st and 3rd API call is the same and should give me the same results. The 2nd API is supposed to give me 1 line back. At the end of each output, the server will end will the word "Ready". I use that to indicate when to exit the loop.
If I disconnect and reconnect after each API call, each output result will be correct because each API call will become the "1st" call since connection - like this program:
DECLARE
c utl_tcp.connection;
ret_val PLS_INTEGER;
v_data VARCHAR2(4000);
BEGIN
c := utl_tcp.open_connection('mars', 8145);
[code]....
If there is something wrong with PC Miler, why would it work when I telnet through a Windows Command prompt? If it is not PC Miler and utl_tcp, what else can be wrong?
View 8 Replies
View Related
Feb 14, 2005
My Table "demo" has a column "Combined_attributes". I want to update this column with some value having a new line character in it.
View 3 Replies
View Related
Jun 11, 2012
I have a column in a table say ename in emp table, which allows values with newlines and carriage return.
I need to find all the values (i.e ename's) which are having newline chars or carriage return.
View 4 Replies
View Related
Jul 1, 2013
I need to reset the line number for each header values.
create table header_table(header_value varchar2(100));
create table line_table(header_value varchar2(100), line_number number);
insert into header_table values('ALAOF');
[Code]....
But My line table output looks like below
LINE TABLE:
header value line_number
ALAOF 1
ALAOF 2
ALAOF 3
[Code].....
View 6 Replies
View Related
Apr 13, 2013
declare
type idstab is table of employees.employee_id%type;
type nametab is table of employees.last_name%type;
ids idstab;
[code]...
View 4 Replies
View Related
Jan 10, 2012
I've created a Java class in my Oracle DB that calls a Visual Basic program to convert a XLS file into a CSV file in order to load it into an external table. The problem that I have is that when I call the Visual Basic program from the Java class, nothing happens. I had the same problem with a Python program, and I thought that the problem was from Python, but now with Visual Basic the problem remains, both aren't executed.
The strange thing is that when I call the same Java class outside Oracle, directly from a command line, it executes both Python and Visual Basic programs.
Here is the Java class defined in Oracle:
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "OSCommand" AS
import java.io.*;
public class OSCommand{
public static void Run(){
try
{
[code]....
And here is the procedure that calls the Java class:
create or replace
procedure run_os_command
as language java
name 'OSCommand.Run()';
View 1 Replies
View Related
Jul 1, 2013
I would like to read specific line in the file in the util file.
View 1 Replies
View Related
Nov 19, 2010
I'm trying to write my own application ( in Delphi) which should be work similar to the Oracle SQL Developer. I received the body of the selected stored procedure by select * from all_users where type='PROCEDURE' and NAME='name_of_the_selected_proc'
Next, I put the body of the procedure into richedit component, make necessary changes, then put the corrected body into the variable ( varchar2) which is the input parameter of my stored procedure PW_DO_IT
create or replace PROCEDURE PW_DO_IT(P_SQL in varchar2)
as
begin
EXECUTE IMMEDIATE P_SQL;
end;
Everything works fine , the only problem is that I don't know how to get the line in which simulated error occured and its details. The only thing I get is the ORA-24344: success with compilation error
View 4 Replies
View Related
Feb 21, 2008
i have a problem with oracle reports 6i. the output has 1000s of lines. in my output first to 999th line is fine..bt then 1000th line is blank and again the data continues to be shown from 1001th line till 2000. and again i see a blank. how can i get rid of these blank lines very 1000th line.
View 3 Replies
View Related
Jun 12, 2013
There is a problem with name field like ex:
ENTER(line break) between 'HHD DDD PRIVATE' and 'LTD.
how to put a check in code to avoid these type of scenarios.
View 4 Replies
View Related
