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?
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?
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]....
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.
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 :)
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.
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.
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
----------------------------------------------------------------------------
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]...
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]...
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 ?
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.
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.
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]....
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.
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]...
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
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 RelatedI 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
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.
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 RelatedI 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.
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?
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 RelatedI 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.
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].....
declare
type idstab is table of employees.employee_id%type;
type nametab is table of employees.last_name%type;
ids idstab;
[code]...
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()';
I would like to read specific line in the file in the util file.
View 1 Replies View RelatedI'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