SQL & PL/SQL :: ORA-02063 - Preceding Line From?

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


ADVERTISEMENT

SQL & PL/SQL :: Range Between X Preceding And Current Row?

Aug 26, 2011

I have a table and want to calculate the stdev of a parameter over a rolling window (past 250 records, sorted by ID):
...
stddev(parameter) over (order by ID range between 250 preceding and current row) as SD
...

I don't get any error, but if I calculate the same in excel (=STDEV(250 row range)), it seems that this code does not the same. Is there something wrong with the 250 row rolling window?

View 4 Replies View Related

PL/SQL :: Distinct Preceding Value Required

Sep 12, 2012

Following on from this answered thread: (xmlagg(xmlelement) - Distinct Values Required..Whereby the last poster recommended the use of

RTRIM (
XMLAGG (
XMLELEMENT (
E,
XMLATTRIBUTES (segment1|| ',' AS "Seg")
)
ORDER BY segment1 ASC
).EXTRACT ('./E[not(@Seg = preceding-sibling::E/@Seg)]/@Seg'),
','
)

To get a distinct list of elements. Works great, but I actually need to use the concept of "distinct immediately preceding".

Sample data:

create table xml_test
(emp_number number, seq number)

insert into xml_test values ('12345',1);
insert into xml_test values ('23456',2);
insert into xml_test values ('44323',3);
insert into xml_test values ('12345',4);
[code]....

View 7 Replies View Related

SQL & PL/SQL :: Extract Numerical Value Preceding Percentage Sign

Jan 17, 2012

I have two values:

'AA:10%:1'
'AC:P35:20%'

I need to extract the numerical value preceding the '%' sign. for first row 10 should be extracted and for the second 20.

View 2 Replies View Related

Spatial :: Convert Multi-line To Simple Line

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

SQL & PL/SQL :: Hierarchical Query - Connect NON-NULL Rows To Preceding NULL Row

Aug 29, 2012

I have the following query:

select col_1,col_9 from
book_temp b
where b.col_1 is not null
order by to_number(b.col_16)
;

What I want to add is the following:

COL_9
=====
NULL
A
B
NULL
C
D
E
F
NULL
G

I need to connect the NON-NULL rows to the preceding NULL row.

View 15 Replies View Related

PL/SQL :: Get Line With Max Value

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

SQL & PL/SQL :: Get More Lines Into One Line?

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

SQL & PL/SQL :: Decode On One Line

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

SQL & PL/SQL :: Line Of Best Fit / Linear Regression

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

SQL & PL/SQL :: Run Command And Connection In One Line

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

SQL & PL/SQL :: Query Result In One Line

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

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 View Related

SQL & PL/SQL :: How To Combine 3 Decodes Together On One Line

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

Remove Count In First Line

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

Missing Right Parenthesis All In Line 2?

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

Missing Right Parenthesis Error On Line 7

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

Loader Skip Blank Line?

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

Displaying Result In Single Line

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

Sqlplus And MS Windows Command Line

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

SQL & PL/SQL :: Replace New Line Character In Clob

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

SQL & PL/SQL :: Results Of Query To Be Displayed In One Line

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

SQL & PL/SQL :: UTL_TCP Returning Blank Line?

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

SQL & PL/SQL :: Update Statement With NEW LINE Character?

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

SQL & PL/SQL :: Search New Line Values In Column?

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

PL/SQL :: How To Reset Line Number Value For Each Header

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

PL/SQL :: Getting Error ORA-6502 - Numeric Or Value At Line 11?

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

Execute Command Line Program Within Java?

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

Reading Specific Line In Util File

Jul 1, 2013

I would like to read specific line in the file in the util file.

View 1 Replies View Related

How To Get Line And Error Code From EXECUTE IMMEDIATE P_SQL

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







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