SQL & PL/SQL :: Query To Update Current Value In DBA View To New One

Jul 18, 2012

I am looking to build a query to update a current value in a DBA view to a new one.i.e. updating directories based on the current value:

CREATE OR REPLACE DIRECTORY 'DIRECTORY_NAME' AS 'DIRECTORY_PATH'(substr(directory_path, 1,5) + '/&dbname' {i.e. this is different for every database name }+ 'DIRECTORY_PATH'(string after /xyz/)
WHERE DIRECTORY_NAME in
( select DIRECTORY_NAME
from DBA_DIRECTORIES
WHERE DIRECTORY_PATH
like '/xyz/%'
)

i.e. resulting output should be:

CREATE OR REPLACE DIRECTORY 'ABC' AS '/xyz/DBNAME/abc/def/';

(when the directory previously was 'xyz/abc/def/') i.e. basically inserting the db name into the directory.where DBNAME is a variable more directories are added frequently so therefore this needs to be a dynamic procedure to change the directories in the db.

View 26 Replies


ADVERTISEMENT

PL/SQL :: Materialized View - Update Query Without Drop And Recreate

Aug 24, 2012

i have a little doubt in Materialized view. i created a materialized view and log with the following query,

create table test_mv (a number ,b number )
alter table test_mv add constraint t_pk primary key ( a );
insert into test_mv values (1,2);

[Code]...

Now i want to update the query in the MV as 'Select a from test_mv' . for that i tried with

*'ALTER MATERIALIZED VIEW product_sales_mv AS SELECT a from test_mv;'*

But it throwing error,

Error starting at line 5 in command:

alter  MATERIALIZED VIEW product_sales_mv   AS SELECT  b  from test_mv

Error report:

SQL Error: ORA-00933: SQL command not properly ended +00933. 00000 - "SQL command not properly ended"+

*Cause:+   
*Action:+

i guess i am doing wrong. i want to update it without drop and recreate

View 2 Replies View Related

Where Current Of - For Update Of

Oct 5, 2010

I have an employee table that has a paygrp_id that will be used for my subset of employees. For all the employees that have the paygrp_id = 10212 on the employee table I need to update the workbrain_user table to set the flag wbu_cansee_self to 'N'. The join between the employee table and the workbrain_user table is the emp_id.I get the following error when I run this cursor.

[error]
Error on line 0
DECLARE
CURSOR wbuFlag_cur
IS SELECT e.emp_fullname, e.paygrp_id,wbu.WBU_CANS
[code]...

View 3 Replies View Related

Update Where Current Of

Sep 27, 2010

I have a table I need to update called employee_history. It has almost the exact same values as another table called Employee. The difference being that the employee_history has a start date and end date. So it allows you to have overrides in the future. The employee table is static. So it only allows you to see the current data as of today.

I need to write an insert statement that will pull values from employee table and populate the employee_history table but I also have columns in the employee_history table that requires a next value and a start and end date.

From the employee table I want to select e.emp_id, e.emp_firstname, e.emp_lastname, e.shftpat_id, e.client_id from employee e

I want to INSERT INTO EMPLOYEE_HISTORY (EMHIST_ID, EMPHIST_START_DATE, EMPHIST_END_DATE, EMP_ID, EMP_FIRSTNAME, EMP_LASTNAME, SHFTPAT_ID, CLIENT_ID)

with the VALUES being (SEQ_EMPHIST_ID.nextval, TRUNC(sysdate), 01/01/3000 and the employee data from the employee table)). I bought the book Easy Oracle PL/SQL Programming but I am not finding what I need. It gives an example of a CURSOR FOR UPDATE and a reference to an UPDATE WHERE CURRENT OF.

The examples are straight forward where you take two columns from table one and update a new table with those values, or where you select a subset of employees that need a commission and update the emp table with that commission.

View 1 Replies View Related

Forms :: How To Update Current Row Comparing It With Existing Value

Jul 14, 2013

I have master-detail form I would like to update the emp1 table on the base of current value of dept table deptno and emp table ename .

update emp1
set ename = :emp.ename
where ename =
(select ename from emp e
where e.ename=:emp.ename
and e.deptno=:dept.deptno);

I try the above query but it did not able to update emp1 table.

View 12 Replies View Related

Application Express :: Current Version Of Data In Database Has Changed Since User Initiated Update

Sep 25, 2012

I am experimenting with the 4.2 version of Apex at URL.....

I have created a tabular form (based on a view with an instead of trigger) and have restricted this form to "Update only" mode. Whenever I modify a field value and submit the change I get the error "Current version of data in database has changed since user initiated update process".

View 6 Replies View Related

PL/SQL :: Query By Current Month

Jul 12, 2013

I have an excel spreadsheet that queries an oracle data for some information.  I am trying to get the information shown to only be from the current month.  I have tried my google-fu, but all of the formulas I have found will not work and return a various error of some sort. The rest of the query works great, but I cant figure this one out.   Select*From&& and I guess i need a where statement, but nothing seems to work.Where"My_Table_Name","OrderDate".

View 6 Replies View Related

Query To Find Current Queries Not Using Indexes?

Mar 29, 2012

hint of which DBA views we need to query to find current running queries which are not using indexex?

I suppose v$session is one , what else we need to look to?

View 1 Replies View Related

SQL & PL/SQL :: Query To Get Default Tablespace Of Current User?

Oct 27, 2010

I need query to retrive the default tablespace of the current user, when the user is not a dba, i.e. user doesn't have access to dba_users table.

View 2 Replies View Related

SQL & PL/SQL :: Getting Query For Current Year Data Up To Sysdate?

Jun 19, 2012

I have a table for exampl,

emp sal date
111 200 03-mar-2011
100 200 03-mar-2012
15 200 06-mar-2012
17 200 03-mar-2003
178 200 03-mar-2004
11 200 11-jun-2012
101 200 19-jun-2012

i need sql querry to get current year records upto sysdate.i.e for example this year 2012 so i need all the 2012 year records upto sysdate.

my output like this.....

emp sal date
100 200 03-mar-2012
15 200 06-mar-2012
11 200 11-jun-2012
101 200 19-jun-2012(upto sysdate)

View 2 Replies View Related

SQL & PL/SQL :: Taking Latest Current Balance Using Analytical Query

Jan 22, 2013

I am having a table with 5 lakhs transactions. I want to fetch the last balance for a particular date. So i have have returned a query like below.

SELECT curr_balance
FROM transaction_details
WHERE acct_num = '10'

[Code]...

This has to be executed for incrementing of 12 months to find the last balance for each particular month. But this query is having more cpu cost, 12 times it is taking huge time. how to remodify athe above query to get the results in faster way using analytical query. Whether this can be broken into two part in PL/SQL to achive the performance. ?

View 9 Replies View Related

Forms :: Can't Navigate Out Of Current Block In Enter Query Mode

May 11, 2010

I have a form with multiple tabs, each tab showing one data block. Many of the blocks are standard in enter-query mode. I want to allow the users to navigate through the tabs while the blocks are in enter-query mode.how can i do this?

View 3 Replies View Related

SQL & PL/SQL :: Create View From Dynamic Query (or Function Returning Query)

Dec 5, 2012

I have a dynamic query stored in a function that returns a customized SQL statement depending on the environment it is running in. I would like to create a Materialized View that uses this dynamic query.

View 1 Replies View Related

SQL & PL/SQL :: Update Join View?

May 20, 2010

9i worked fine 11g release2 giving ora-01779

alternative sql for the following :

UPDATE /*+ BYPASS_UJVC */
(
SELECT
c.c1,
c.c2,
c.c3,

[code].....

View 5 Replies View Related

SQL & PL/SQL :: Difference Between Sysdate / Current Date / Current And Local Timestamp

May 30, 2012

What is the difference between the following . In my schema all are giving the same results with some different format

SQL> SELECT sysdate , current_date , current_timestamp , localtimestamp from dual;

SYSDATE CURRENT_DATE CURRENT_TIMESTAMP LOCALTIMESTAMP
----------- ------------ ------------------------------------------------- -------------------------------------------------
5/30/2012 8 5/30/2012 8: 30-MAY-12 08.27.22.037703 AM -04:00 30-MAY-12 08.27.22.037703 AM

View 1 Replies View Related

SQL & PL/SQL :: Verify Current Date Is Greater Than 15th Of Current Month

Sep 22, 2010

I need to verify if the current date is grater than the 15th of the current month. If its grater than the 15th of the current month i need to do an action or if else its lesser than 15th of the current month i need to do an other operation.

View 5 Replies View Related

Oracle 11G - Update Is Very Slow On View?

Dec 7, 2012

I have big trouble with some Update query on Oracle 11G.I have a set of tables (5) of identical structures and a view that consists in an UNION ALL of the 5 tables.None of this table contains more than 20 000 rows.Let's call the view V_INTE_NE. Each of the basic table has a PRIMARY KEY defined on 3 NUMBERS(10,0) -> INTE_REF / NE_REF / INSTANCE.

Now, I get 6 rows in another table and I want to update my view from the data of this small table (let's call it SMALL). This table has the 3 columns INTE_REF / NE_REF / INSTANCE.

When I try to join the two tables :

SELECT * FROM T_INTE_NE T2
WHERE EXISTS ( SELECT 1 FROM SMALL T1 WHERE T2.INTE_REF = T1.INTEREF AND T2.NE_REF = T1.NEREF AND T2.INTE_INST = T1.INSTANCE )

I get the 6 lines in 0.037 seconds

When I try to update the view (I have an INSTEAD OF trigger that does nothing (just return for testing even without modifying anything), I execute the following query :

UPDATE T_INTE_NE T2
SET INTE_STATE = -11 WHERE
EXISTS ( SELECT 1 FROM SMALL T1 WHERE T2.INTE_REF = T1.INTEREF AND T2.NE_REF = T1.NEREF AND T2.INTE_INST = T1.INSTANCE )
The 6 rows are updated (at least TRIGGER is called) in 20 seconds.
However, in the execution plan, I can't see where Oracle takes time to achieve the query :
Plan hash value: 907176690

[code]....

Predicate Information (identified by operation id):

2 - access("T2"."INTE_REF"="T1"."INTEREF" AND "T2"."NE_REF"="T1"."NEREF" AND
"T2"."INTE_INST"="T1"."INSTANCE")

Note- dynamic sampling used for this statement (level=2)

Statistics
-----------------------------------------------------------
3 user calls
0 physical read total bytes
0 physical write total bytes
0 spare statistic 3
0 commit cleanout failures: cannot pin

[code]....

I get exactly the same execution plan (when autotrace is ON).Furthermore, if I try to do the same update on each of the basic tables, I get the rows updated instantaneously.

View 10 Replies View Related

PL/SQL :: Correlated Update (Inline View)

Jan 21, 2013

Can I update inline view like below?

update

(select * from surveys s join answers a on s.survey_id = a.survey_seq_id where month = 201212 and qa = 1 and main_group_id = 55) a,
(select * from surveys s join answers a on s.survey_id = a.survey_seq_id where month = 201212 and qa = 1 and main_group_id = 3) b
where a.survey_id = b.survey_id and substr(a.question_uid , 3) = substr(b.question_uid, 2))

set b.answer = a.answer;

My second question is: The following query is giving error. What can I do?

update answers ans set (ans.answer) = (with a as (select * from surveys s join answers a on s.survey_id = a.survey_seq_id where month = 201212 and qa = 1 and main_group_id = 55),
b as (select * from surveys s join answers a on s.survey_id = a.survey_seq_id where month = 201212 and qa = 1 and main_group_id = 3)
select a.answer from a join b on a.survey_id = b.survey_id and substr(a.question_uid , 3) = substr(b.question_uid, 2) and b.answer_id = ans.answer_id) where ans.main_group_id = 3;

SQL Error: ORA-00904: "ANS"."ANSWER_ID":

View 8 Replies View Related

Replication :: Trigger - After Update On Materialized View?

May 30, 2008

i want to create "on update" database trigger on materialized view which is on replication schema.I would like to update rows in table when update occurs on materialized view on master side.I wrote some code and tried with this:

CREATE OR REPLACE TRIGGER a
after update on A@dirfep.us.oracle.com
FOR EACH ROW
BEGIN

[code]...

View 1 Replies View Related

Error In Creating Update Table Materialized View

Aug 28, 2013

we are trying to create a materialized view (MV) which would access the remote database through db link. Now we need to do update on the local MV so that it should be reflected on the master table.

There is no primary key on this table and we are using "complete refresh" option. since we dont have control over remote database, we are not allowed to create MV log over there.

in this scenario, if i try to create updatetable MV with complete refresh, we are getting below error:

SQL Error: ORA-12013: updatable materialized views must be simple enough to do fast refresh

how to create such MV on this scenario?

our environment is:

Oracle 11.2.0.3

View 1 Replies View Related

PL/SQL :: Update A Base Table Using Updatable Materialized View?

Sep 12, 2013

I am using sqlplus. example to update a base table using a updatable materialized view.

View 17 Replies View Related

SQL & PL/SQL :: Query On View

May 25, 2010

I have created a table "temp" with one column "c_temp_1"

I have created a view "temp_vw" on table "temp".

When I queried the view, I got the table data i.e., column "c_temp_1".

Now, I added another column "c_temp_2" to table "temp"

When I querid the view, I did not got the column "c_temp_2".

But after recreated the view "temp_vw", I got the column "c_temp_2".

My knowledge is view will hold only select statement and will be executed when ever we call the view.

So, when I call the view after adding the second column, I should be able to see second column data as well but why I have to recreate the view?

-- Because of my PC issues, I was not able to paste SQL commands.

View 1 Replies View Related

ListAgg Query Vs View?

Jan 29, 2013

I've generally operated under the assumption that if I have a query that executes properly, I should be able to create a view from that same logic. However, I've had more than one occasion where this seems to NOT be the case.

Today, I have a query using ListAgg that operates just fine when run interactively, but (in SQL Developer v 3.0.04) it gives ORA-24344 when I try to create a view using the exact same statement.

View 9 Replies View Related

SQL & PL/SQL :: How To Implement Inner View Query

Apr 19, 2011

SELECT
DISTINCT CUSTR.TRX_NUMBER trx_number,
CUSTR.TRX_DATE transaction_date,
(
SELECT
MAX(DECODE(fndcatusg.format,'H', st.short_text,NULL,st.short_text, NULL) )
FROM fnd_attachment_functions fndattfn,

[code]....

I have this above query i want to make this query like

SELECT
DISTINCT CUSTR.TRX_NUMBER trx_number,
CUSTR.TRX_DATE transaction_date,
CORRECTED_PROMISE_DATE
FROM
RA_CUSTOMER_TRX_ALL CUSTR

View 2 Replies View Related

SQL & PL/SQL :: View Is A Recompiled Query

Mar 7, 2010

Quote:View is a precompiled sql query.What do you mean by the word precompiled?

Are views useful or beneficial for performanace,

View 7 Replies View Related

SQL & PL/SQL :: Query From The View Returning Zero Records

Feb 27, 2012

Im running the following query in DB - Prod2

select *
from t_act_rwrd_reimb_stay a
where reimbursed_amt < 0
and invoice_date = '01-JAN-1900'
and not exists ( select 'x' from t_act_rwrd_reimb_stay b
where a.GPM_ID_STAY = b.GPM_ID_STAY
and reimbursed_amt > 0 )

The above table t_act_rwrd_reimb_stay --> is from a view on the database Prod2 and this view is pulling the data from the database prod1 via dblink

When I run the same query on Prod1 it returns 3 rows. Is there any reason this is not fetching data on prod2 view? or whatz wrong with the above query

or for example if I run the above query as follows with the dblink "@prod1" it returns the data properly

select *
from t_act_rwrd_reimb_stay@Prod1 a
where reimbursed_amt < 0
and invoice_date = '01-JAN-1900'
and not exists ( select 'x' from t_act_rwrd_reimb_stay@prod1 b
where a.GPM_ID_STAY = b.GPM_ID_STAY
and reimbursed_amt > 0 )

View 10 Replies View Related

SQL & PL/SQL :: Correlated Query And Inline View

Apr 9, 2010

which one is better in performance point of view and why between inline view and correlated query.

View 5 Replies View Related

PL/SQL :: How To Use Materialized View And Its Data From A Query

Nov 15, 2012

I have got a simple materialized view question.

I have a view which is taking long time to get back results and this view is being used in various user queries. I noticed that the SQL of the view access some tables, data of them changes rarely. So it makes sense to retrieve that data in a materialized view and somehow re-use it.

My question is how can I use the materialized view and it's data from a query.

View 2 Replies View Related

SQL & PL/SQL :: Dynamic Query In View For Correct Column Name

Feb 20, 2012

I don't know what column name would there for my new view. so can i use dynamic SQL in View so that I can get correct column name?

View 2 Replies View Related

SQL & PL/SQL :: Update Query Using Join

Jun 26, 2012

How to update single table column using join query

Example:

Update table1 t1,table2 t2
set t1.column2 = 'Y'
where t1.column1 = t2.column1

View 8 Replies View Related







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