Sysdate as char instead of date

I have a query. i have a config table AB where a row is marked as "sysdate-360" col1 ||col2 AB || sysdate-360 BC || sysdate -2 When i write a procedure to get date value from the config table AB i used. v_date varchar(20); cursor c1 ...
more »

2017-02-19 21:02 (3) Answers

Materialized View refresh interval granularity

I've run into something I don't understand with materialized view refresh-times and hoped someone could explain what I'm missing. I'm on 12cR1. When I define a materialized view with a refresh interval of N seconds, the view compiles and the unde...
more »

2017-02-19 20:02 (0) Answers

oracle XML to Database table

I have XML like this . I am unable to parse it into a table <REQMST> <ROW> <REQ_ID>668 </REQ_ID> <RequestDetails> <REQ_DTL_ID>845</REQ_DTL_ID> <INTERFACE_REFNUM>1</INTE...
more »

2017-02-19 13:02 (1) Answers

Oracle query - Displaying data grouped yearly

I am trying to display data grouped by 12 months period based on the min start date. If FTE was changed during the 12 month period, it will be listed with corresponding start and end date. Test data is provided as part of [Data grouped by date in or...
more »

2017-02-19 09:02 (1) Answers

Execute Immediate on DDL Statements

I executed the following code in Oracle Database but faced with an error of the view name does not exist. Can anyone look into it ? Declare Stmt varchar2(2000); Var number; Begin Stmt:='create or replace view emp_dept_v as select * from emp'; Execu...
more »

2017-02-19 09:02 (2) Answers

Trying to create table using execute immediate

i am trying to create a table using below code. set serveroutput on; DECLARE cursor c1 is select '''create table demo1 (demo varchar2(100))''' c2 from dual; testvar c1%rowtype; BEGIN open c1; fetch c1 into testvar; close c1; execute immediate test...
more »

2017-02-18 11:02 (1) Answers

Data grouped in 12 month period

I have 2 tables Create table t ( employee varchar2(10), job varchar2(5), start_date date, end_date date, amt number ); With the following data insert into t values (1111,1,19-May-2008,20-May-2008,7); insert into t values (1111,1,...
more »

2017-02-18 09:02 (0) Answers

Selecting arbitrary value as max

I have two tables, STUDENTS and LOG. The LOG table may have 0 or more records in it per student. The records in the LOG table are categorized by column SUBTYPE. Values for SUBTYPE are: 'H', 'L1', 'L2', 'L3', 'M', 'P1', 'P2', and 'Px'. I am trying t...
more »

2017-02-17 22:02 (2) Answers

Oracle join a table using a variable field

I'm trying to get the rent rate for which a cage is to be charged with for each month. Here's the logic behind how to get it: If the rate is defined in the cage table use that table's foreign key to link to the rate table. If the rate is defined in...
more »

2017-02-17 17:02 (2) Answers

REGEXP_SUBSTR with negative position

I have a query to treat comma as delimiter in a string. I want to substr the last occurrence. example: string: a, b, c, d string: e, f, g, h, i, j select regexp_substr(string, '[^,]+', -1, 1) from dual; should return: d and j but the ...
more »

2017-02-17 16:02 (4) Answers

MSSQL Data type conversion

I have a pair of databases (one mssql and one oracle), ran by different teams. Some data are now being synchronized regularily by a stored procedure in the mssql table. This stored procedure is calling a very large MERGE [mssqltable].[Mytable] as s...
more »

2017-02-17 16:02 (2) Answers

Spliting join of multiple tables in PL-SQL

I need to create a function that returns a sysref cursor pointing to some result set. I need to join a global temporary table with several other tables, the basic aproach would look like this: Open sysrefcur for select * from (sel...
more »

2017-02-17 16:02 (2) Answers

ORACLE SQL: Pause Execution until a user input

I have a Oracle SQL script like this: Display some values from a table want to pause here until I can review the values from the table in above step, and hit a button to keep on executing. If the value looks good to me, I want to break the SQL scri...
more »

2017-02-17 14:02 (1) Answers

Full outer join with "case when" and subquery

I have a full outer join query with case when and sub query in oracle. What I am trying to accomplish is aggregating current year data and previous year data from the same table in order to compare them. However my FULL OUTER JOIN is acting as an inn...
more »

2017-02-17 14:02 (1) Answers

How does a simple CTE work?

Say, I have a short query. with test_cte as( select * from table1 where conditions1 ) select * from table2 inner join test_cte on conditions2 where conditions3 Am I right to assume that the query first goes through test_cte to filter the rows base...
more »

2017-02-17 11:02 (2) Answers

Oracle XML extract path

I have been trying to extract data from xml using oracle. This is my xml: <?xml version="1.0" encoding="UTF-8"?> <?xfa generator="XFA2_4" APIVersion="3.6.12227.0"?> <xdp:xdp xmlns:xdp=""> <config xmlns...
more »

2017-02-17 11:02 (3) Answers

Fetch Out of sequence <ora-01002> Rollback

Today i encountered "FETCH OUT OF SEQUENCE" ORA-01002. I have done a lot of research and i came to understand that it is quite common when we either do . Try to fetch when we have already closed the cursor. For update and commit. My requirement...
more »

2017-02-17 10:02 (2) Answers

SQL Column ambiguously defined error

I have to write a query that tells me the celebrities that have both starred in a movie and released an album: Select CELEB From StarredIn INTERSECT Select CELEB From Releases; Then from these celebs, I have to count the number of movies and album...
more »

2017-02-17 06:02 (2) Answers

timestamp wth timezone vs django

My Oracle DB has a table with a column of type TIMESTAMP WITH TIMEZONE. The Django model converts it to datetime but loses the original TZ info and assumes UTC. DB: 01-JAN-17 AM -05:00 Django:2017-01-01 00:10:01.809000+00:00 USE...
more »

2017-02-17 06:02 (1) Answers

SQL filter LEFT TABLE before left join

I have read a number of posts from SO and I understand the differences between filtering in the where clause and on clause. But most of those examples are filtering on the RIGHT table (when using left join). If I have a query such as below: select *...
more »

2017-02-17 04:02 (3) Answers

Splitting time range based on activities in sql

I need a Oracle sql to show the following output given the sample input. Basically, an employee is schedule for a 9 hour shift. I need to split up the activities during the day to separate records. Especially the general activity of Cash. I need to ...
more »

2017-02-16 19:02 (3) Answers

Oracle Sql developer "IN" statement for long list

how do i write a query where the results match a set of invoice numbers? I am using oracle sql developer. I would create a new table and use a join as a filter but I don't have privileges to create a table. also, the list includes about 2000 invoice ...
more »

2017-02-16 16:02 (0) Answers