Forum

Posts

RECENTLY INDEXED PROGRAMMING SOLUTIONS OF Oracle


SQL Query question (count dates for each month)  

Hi there,Hope you can help me with the following, i have the following view availble:DD/MM/YYYYENTITY | StartDate | EndDate | CodeA | CodeB | Revenue | CurrencyAZERT | 01/01/2011 | 02/01/2011 | SU | BOLD | 100 | EURAZERT | 28/01/2011 |... Continue Reading
Views : 2644
Monday 31st of December 2012

Help with SQLPLUS script timelapse  

Hi,I need to make a particular query on an Oracle 10g DB via SQLPLUS.I have a table structured this way:NAME - VALUE1 - VALUE2 - DATEThis table is populated every day by a procedure that does a count on 2 other tables that should be identical (they are... Continue Reading
Views : 237
Monday 31st of December 2012

Problem refreshing Materialized View in Oracle SQL Developer  

I have a database schema with a Materialized View (MV) that updates a products table shown on a website. This schema was recently imported to an 11g database from 8i, but since this import I have been unable to refresh my schema?s Materialized View.The MV_PRODUCT_MASTER... Continue Reading
Views : 242
Monday 31st of December 2012

SQL Expert Exam 1Z0-047 (best study materials)  

I hope someone can help me, I am interested in taking this cert: SQL Expert Exam 1Z0-047. Does anyone have a suggestion for the best study materials? Eventually, I would also like to take this exam, 11g Administration I Exam: 1Z0-052. I'll get to that one once I... Continue Reading
Views : 215
Monday 31st of December 2012

PK violation (was "Please look at this. and help me out")  

1. Create the following 3 tables. Choose appropriate data types and create all specifiedprimary and foreign keys.Table: DEPARTMENTSName----------------------------------------- --------DEPARTMENT_ID Primary KeyDEPARTMENT_NAME NOT NULLMANAGER_ID FK... Continue Reading
Views : 247
Monday 31st of December 2012

Help Configuring Rman Utility  

I know people ask all day how do they backup Oracle or help with 'Rman' utility however I'm trying to follow this and it advises me to set the two variables before I use 'rman':1. Create a... Continue Reading
Views : 243
Monday 31st of December 2012

Dynamic fields with custom data type  

I will try to make this simple. I am using an EBS API and it has a datatype called EGO_ITEM_PUB.ITEM_TBL_TYPEIt is essentially an object which has all of the fields for it's base table (which is MTL_SYSTEM_ITEMS_B).When using the API, I do something like this... Continue Reading
Views : 258
Monday 31st of December 2012

Create Table Not Replicating SQL Select Results  

Hey all,This is a very odd one...never encountered this before within SQL DeveloperI have a SQL statement that returns a set of columns...but...when I create table as I get the same columns but with 2 of the columns containing each others data,... Continue Reading
Views : 198
Monday 31st of December 2012

Not returning values  

Hi, I'm trying to do a count on the number of combinations of country/state codes that are invalid (in Australia) for driving licences. Code: Continue Reading
Views : 242
Monday 31st of December 2012

IselfSchooling  

Could someone help me on this matter please. I have registered with iselfschooling and want to access its videos.but i cant seem to find the username and password to their server. Your help will be greatly appreciated. Thank You Continue Reading
Views : 203
Monday 31st of December 2012

SQL Query  

Q1. SELECT TOP 1 salaryFROM (SELECT DISTINCT TOP 6 salary FROM employeeORDER BY salary DESC) aORDER BY salarywhat does this ' a ' means here. ????Q2. How to auto number the rows in a table in... Continue Reading
Views : 185
Monday 31st of December 2012

Scalar select (single row subquery)  

I'm trying to speed this up. 88 seconds is far too long. I need to identify if a student belongs to an ACAD_PLAN that has an ACAD_SUB_PLAN with ACAD_SUBPLAN_TYPE = 'AOP'. AOP is Accelerated Online Program. Trying to modify it into a subquery. emplid exist in PS_ACAD_PLAN and... Continue Reading
Views : 230
Monday 31st of December 2012

Oracle Query Optimization - steps, tools, and resources  

1. For tuning SQL queries, is the following the general approach:- Determine which SQL queries are the most active (V$SQLAREA and V$SQL, stats$sql_summary, dba_hist_sql_summary table)- For the most active:- - Add statistics to the tables to help with the CBO (Cost-Based... Continue Reading
Views : 220
Monday 31st of December 2012

Displaying rows into columns  

I have table having below records.empno ename deptno101 a 10102 b 20103 c 10104 d 20105 e 30Normal Outputdeptno count(*)-----------------10 220 ... Continue Reading
Views : 233
Monday 31st of December 2012

Your personal experience with "1Z0-047" Oracle Database SQL Expert  

HiPlease write your personal experience (and Oracle background) with1Z0-047 and give safe bets on passing exam. I am writing since 3 yearson a weekly basis SQL scripts on *NIX. Failed 1Z0-047 twice, attendedofficial training and studied with all available... Continue Reading
Views : 254
Monday 31st of December 2012

Oracle 11g File watcher issue  

HiHave been reading almost every thread there is on this subject and also tried proposed solutions but still I'm stuck.I have a File watcher created, using a Credential, that normally would trigger a job through the event created.In short this is what we... Continue Reading
Views : 231
Monday 31st of December 2012

View and table with the same name  

Hi,If you have one table called CUST_ACCOUNTS and a view called CUST_ACCOUNTS -- and then you want to select from the view and not the table, how do you do that?Is there a prefix for views or something that I can have in front of the name to specify it's the view I... Continue Reading
Views : 243
Monday 31st of December 2012

SQL query question  

Hi, I've written this code for my pl/sql class and it runs fine. The only problem is that no matter what I input I always get the v_toolow exception which makes me sure my SELECT statement is not right, since I looked in the database and it intentionally put in data that should not trigger... Continue Reading
Views : 179
Monday 31st of December 2012

Collapse a matrix to a single line item  

Hi,I think it would be best to use a simple example to explain my problem. The table below needs to become compressed into to distinct line itemsCustomer_No Hair SP_Code Gender Age Colour Weight1 blonde 123 M null null null1 ... Continue Reading
Views : 240
Monday 31st of December 2012

ISQL*Plus login for SYSDBA  

iSQL*Plus login for SYSDBA Step-1) Export the java path.[harsh@esparkinfo.com]$ export JAVA_HOME=$ORACLE_HOME/jdkStep-2) Change the directory.[harsh@esparkinfo.com]$ cd $ORACLE_HOME/oc4j/j2ee/isqlplus/application-deployments/isqlplus Continue Reading
Views : 162
Monday 31st of December 2012

Help with queries  

please delete this thread,accidentally posted double threads Continue Reading
Views : 240
Monday 31st of December 2012

Anyone able to help  

I am having a slight problem with an sql procedure im trying to create, keep getting an error saying "encountered ;, while expecting the following: if" could anyone help?CREATE OR REPLACE PROCEDURE CustOrders( iCust IN NUMBER)AS CurrentRow... Continue Reading
Views : 247
Monday 31st of December 2012

Role, user one doubt..pls help  

When i query dba_role_privs, one of the GRANTEE there is 'DBA' which is granted several roles & now when i query dba_users there is no 'DBA' value in column USER there....i understand one would say DBA would be a role in dba_role_privs and not an actual user..but if thats the case then why... Continue Reading
Views : 212
Monday 31st of December 2012

Inserting Dates into Table  

My homework requires me to create a booking table for a hotel and I have created the table but I'm having trouble inserting the dates. This is my table: Code: Continue Reading
Views : 234
Monday 31st of December 2012

View - Join displays duplicate rows  

The view below creates, however displays duplicate rows. Why is this may I ask?CREATE OR REPLACE VIEW customer_order_vwAS SELECT a.customer_id, a.name, a.city, a.state, a.zip, a.discount_percent, b.order_id, Continue Reading
Views : 204
Monday 31st of December 2012

Execute Sp  

Basic question how to you execute a stored procedure in ORACLEFor example in SQL SERVER its just EXEC Proc_Name ParameterValuesHow the hell do you do this in oracle i just want to test if my stored procedure works.Many Thanks Continue Reading
Views : 192
Monday 31st of December 2012

Horizontal counting  

I would like to run a query that counts by case_manager, number of distinct app_id's that have a status ='AC' in a select number of programs. All of the fields are in the same table.I want it to look like this:Case Mgr A D YY OYJane 1 3 42 0 Continue Reading
Views : 150
Monday 31st of December 2012

Query to get rows of latest date from each group  

HiI want to get rows by latest date for each group pf rows.say my data is :TYPE1 TYPE2 quantity DATE-ORDERED sweets chocolate 10 05-FEB-2012sweets chocolate 10 04-DEC-2012sweets chocolate 10 ... Continue Reading
Views : 205
Monday 31st of December 2012

Inline pragma before cursor  

Hi,I have a stored proc that calls a user-defined function "fun" like this (greatly simplified): Code: Continue Reading
Views : 220
Monday 31st of December 2012

Executing a Function in Oracle  

Hi,I have created a Package Body and Package Spec for a function to select a username and a password from a table and return the username. The code i have created is this:- CREATE OR REPLACE PACKAGE BODY USER_LOGINASFUNCTION user_select... Continue Reading
Views : 252
Monday 31st of December 2012