I want to create procedure who should run for both montly as well as yearly report for monthly report i will pass Year and Month as argument for year i will pass Year only
for annual report i a counting all the user, new entry for item, new enty for customer, new enty for admin for montly report i am counting new item, new group.
we have 4 node rac cluster. Node 4 crashed. All the services on node 4 moved to node1.how can I evenly distribute the services instead all services going to node1 ?
for example: i have 10 services on node 4. All went to node 1.i want 3 on node1, 3 on node2, 4 node3 .
how to reduce quantity from one table due to other table for example .i have a purchase a shampoo in 20 quantity and then sale this shampo 2 quantity...so i want now there should be 18 quantity of shampoo in purchase table automatically
Purchase tableSale tabel Product IdSale id Product Nameproduct name QuantityQuantity
Now, i want to count quantity follow slip_no and merchandise.
Example: Merchandise :2501B002CA have 8 slip with quantity is 12 and 5 slip with quantity is 16. Merchandise: 2501B001CA have 6 slip with quantity is 12 and 7 slip with quantity is 16.
I want to write select query to get sum of quantity for each type of item ,in the table I have itemnum,itemtype and quantity ,type have value(A,B,C,....)
the result will be like that ex:
item.No A B C D ....
10 50 60 80 20
--- 50 will be sum(quantity) for item type A
how can I do that, I mean how can I retrieve many column from the same field?
i am trying to create a update trigger that allows any quantity reduction of 5 units and if the reduction is more than 5 units it blocks it and just reduces it by 5.here are two scenarios
SQL Update statement where the new Quantity value being smaller than 5 is allowed by the trigger.
-- old qty 20 update purchase_order set quantity = 18 where po_no = 'PO11'; -- new qty 18
SQL Update statement where the new Quantity value being larger than 5 is only reduced by 5 by the trigger.
--old qty 25 update purchase_order set quantity = 19 where po_no = 'PO15'; -- new qty 20
i just have the basic trigger code, but i think it is the math that i am not getting (i was never good at math)
create or replace trigger purchase_quantity_updt before update of quantity on purchase_order for each row when (new.quantity < old.quantity ) begin -- not sure what to put here end;
I am using the dblink to merge the data. I am using the following merge statement.
merge into APP_USER.USR_NEW_RIGHTS@NEW_RIGHTS t Using (select 'test' GRANTEE,'TESTxxx'ROLE from dual ) s on (t.GRANTEE = s.GRANTEE and t.ROLE = s.ROLE) when not matched then insert (ID,GRANTEE,ROLE,XRIGHT,COMPANY,OWNER,TABLENAME) values ('','test','TESTxxx',null, null, null, null);
I know that I have to set a commit and it's working when I insert information's with a normal insert statement via database link, but it seems that merging doesn't work.
We found out an error from alert log of our Oracle 10.2.0.5 DB : ==================================== .. Wed Jan 30 16:45:01 EAT 2013 DISTRIB TRAN bea1.67AA54355C4A74ECDEE0 is local tran 6.42.332492 (hex=06.2a.512cc) insert pending prepared tran, scn=8151148567799 (hex=769.d6509cf7) Wed Jan 30 16:45:02 EAT 2013 Errors in file /oradata/sfapdb/bdump/sfapdb_reco_2739.trc: ORA-24756: transaction does not exist Wed Jan 30 16:45:02 EAT 2013 Errors in file /oradata/sfapdb/bdump/sfapdb_reco_2739.trc: ORA-24756: transaction does not exist .. ====================================
There is no useful information from the trace log as shown below: ==================================== Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production With the Partitioning, Data Mining and Real Application Testing options ORACLE_HOME = /ap/oracle10 System name:HP-UX Node name:scvap2 Release:B.11.23 Version:U Machine:9000/800 Instance name: sfapdb Redo thread mounted by this instance: 1 Oracle process number: 18 Unix process pid: 2739, image: oracle@scvap2 (RECO)
*** SERVICE NAME:(SYS$BACKGROUND) 2013-01-30 16:45:01.941 *** SESSION ID:(1749.1) 2013-01-30 16:45:01.941 *** 2013-01-30 16:45:01.940 ERROR, tran=6.42.332492, ose=0: ORA-24756: transaction does not exist *** 2013-01-30 16:45:02.059 ERROR, tran=6.42.332492, session#=1, ose=0: ORA-24756: transaction does not exist ====================================
I also found out there are some records (trans_id = "6.42.332492") in SYS.PENDING_TRANS$/ SYS.PENDING_SESSION$/dba_2pc_pending with "prepare" status.
This transaction is launched from a Weblogic Server via JDBC. Since it is abnormal so I have no choice to force commit/purge this transaction. Is that a bug of Oracle DB ? or Weblogic coding problem ?
Query in format Query@DB_Link is sometimes dropping transactions. Move it to a package on target dababase "B" called from DATABASE "A" via package.procedure(varaibles);
Is that all I need to do to get the transaction to always go? I tried a commit in the package body, with repeated attempts if it fails. Btu when I use commit, the package doesn't work. (Compiles fine) Why would the commit fail?
My goal is to make that the transaction succeeds.Code for what I tried below. the COMMIT is commented out - if I uncomment it, the transaction fails. I have done this before calling an oracle SP from SQLServer and it worked
Comments:
The loop is to avoid an endless cycle..The concept is to perform the update, commit it, if the commit fails, rollback and try again.I'm not sure if I even need a loop, but I don't know if the unresolved transaction would get fixed by the remote DB, or dropped.
When I try to call a database procedure written in Oracle 8.1.7.4.0 with OUT parameter and COMMIT statement from my Oracle 10g environment, I am getting error like "ORA-02064: distributed operation not supported".
I cannot omit OUT/COMMIT statement from the procedure because it is also updating another table from called procedure. I have tried some solutions from my end, but it is not working and same error generating. Like:
1) Moved the update statement with COMMIT statement to another procedure and calling that procedure from main called procedure 2) Creating a job to run the newly created procedure and submit the job from called procedureetc.
I have a scenario In which I have say 4 AQ in which I will post the message.Also i have say 2 database.I am planning to create an MDB which will poll on these AQ's, so whenever I post message the MDB will read it and perform a specific action.I believe I can create only one MDB per queue, if it is so then I have to create 8 MDB.
As there 2 datasource and 4 MDB. Is there any other way to handle this I mean without creating 8 MDB as the Data sources can increase to 10 to 20 so the number of MDB will be 20 to 40.I guess this will affect the application performance. Can I make some changes in application so that only few MDB's are required?
I am on Oracle 11.2.0.3 on Linux. In my production database, I am getting this alert, in the alert log:
--the below two lines are from alert log. Error 604 trapped in 2PC on transaction 50.73.546578. Cleaning up.
--query from a data dictionary view SQL> select count(*) from DBA_2PC_PENDING; COUNT(*) ---------- 1
When I query the production database I see that there is one row in the DBA_2PC_PENDING view. But I dont' know how to identify which are the databases that are involved in this distributed transaction. That is my first issue - how to identify which are the two databases that are involved in the distributed transaction?
I want to make sure I am describing correctly what happens in a query where there is distributed database access and it is participating in a NESTED LOOPS JOIN. Below is an example query, the query plan output, and the remote SQL information for such a case. Of particular note are line#4 (NESTED LOOPS) and line#11 (REMOTE TABLE_0002).
What I want to know is more detail on how this NESTED LOOPS JOIN handles the remote operation. For example, for each row that comes out of line#5 and is thus going into the NESTED LOOPS JOIN operation @line#4, does the database jump across the network to do the remote loopkup? Thus if there are 1 million rows, does that mean 1 million network hops? Does batchsize play a role? For example, if the database batches in groups of 100 then does that mean 10 thousand network hops?
I think each row that comes out of line#5 means a network hop to the remote database. But I do not know for a fact.I have done some abbreviating in the plan in an attempt to make it fit on the page (line#7 TA = TABLE ACCESS).
I have read almost all docs about distributed transaction on tahihi.oracle.com website,But I can find a statment about this:
Can Oracle always guarrantee the data consistent in a distributed transaction?
For example,there is a distributed transaction on node a,node b and nod c.node b and node c informed node a they were prepared,so node a committed,and inform node b and node c commit.then node b committed and feedback,but network on node c broken at this point,So node a can't not get feedback from node c,but node a and node b has been committed, so what will Oracle do in this condition?
If node c rollback the data on local node, consistent in this distributed transaction was failed ,yes?
[/b]select distinct t.f_month_number from time_dim t where f_date between (select start_date from employee where emp_id = 111 ) and (select add_months(start_Date,12) from employee where emp_id = 111)[b]
but when i add
select distinct t.f_month_number,p.start_date,round(replace(p.total_sal,',','')/12,2) as Monthly_sal from time_dim t, employee p where t.f_date = p.start_date and f_date between (select start_date from employee where emp_id = 111 ) and (select add_months(start_Date,12) from employee where emp_id = 111)
I want to display all months (JAN till DEC) in 1st column and their counts in 2nd column but as there are no records in JAN, FEB and march, so they are not coming up.
Get JAN, FEB etc with counts as 0?I know it can be done with NVL function but I don't know where to put it. I'm using oracle 10g on Windows XP. [code]SELECT '',
CASE WHEN b.mon= '01' THEN 'JAN' WHEN b.mon= '02' THEN 'FEB' WHEN b.mon= '03' THEN 'MAR' WHEN b.mon= '04' THEN 'APR' WHEN b.mon= '05' THEN 'MAY' WHEN b.mon= '06' THEN 'JUN' WHEN b.mon= '07' THEN 'JUL' WHEN b.mon= '08' THEN 'AUG' WHEN b.mon= '09' THEN 'SEP' WHEN b.mon= '10' THEN 'OCT' WHEN b.mon= '11' THEN 'NOV' WHEN b.mon= '12' THEN 'DEC' END Months, count(*) cnt FROM (SELECT con.fst_name first_name, con.last_name, usr.login USER_ID, app.appl_src_cd Registration_Source,to_date(usr.created,'DD-MON-YY') Created_Date, TO_CHAR(usr.created,'MM') mon from siebel.s_user usr, siebel.s_contact con, siebel.S_PER_PRTNRAPPL app where con.par_row_id = usr.row_id and app.row_id = con.row_id and app.appl_src_cd = 'Siebel eService' AND TO_CHAR(usr.created,'YYYY') = :P415_YEAR ) bgroup by b.monorder by 3 desc[/code]
Right now getting output as....MONTHSCNTAPR4818JUL4543JUN4295MAY4190AUG541MAR20 What Jan, Feb, March etc in it too with count as 0.
I have table data in which we are entering Visitor's information.We need a monthly report with the count .There are some months which dont have data .So Names of month are not coming .Can i Print Month Name with 0 visitor.
Sample Data
Quote:ID Name Visit_date 1 ABC 01/02/2011 2 DEF 03/04/2011
im using query
Quote:SELECT count(ID) Nos ,TO_CHAR(visit_date,'MONTH') Month FROM DELEGATE_DETAILS WHERE visit_date BETWEEN '01/02/2011' AND '01/04/2011' GROUP BY TO_CHAR(ARR_DATE,'MONTH') ORDER BY TO_CHAR(ARR_DATE,'MONTH'); Output Quote:Nos Month 1 February 1 April
Desired Output:- Quote:Nos Month 1 February 0 March 1 April
I have the following in my select statement and I am getting the days like 1130, 50, 60 etc
round(MAX (TO_DATE ('31-OCT-2011 23:59:59','dd-mon-yyyy hh24:mi:ss') - a.tx_dtime),0) DAYS
Now I want this to convert these days into months like 1.10 this denotes (one month 10 days) 1.25 this denotes (one month 25 days) 2.05 this denotes (two months 5 days)
I know I am using old Oracle but what to do my company is not spending to upgrade.
A non-zero payment indicates that the account was open on that date. A zero payment indicates that the account is closed and assumes that it was open since Jan-01.
AAA: code 00 was open in February, so it was open for 11 months (Feb-Dec) code 01 was open in August, so it was open for 5 months (Aug-Dec) So the result should be 11
BBB: 00 - closed in February, existed for 1 month 01 - open in August, existed for 5 months Result should be 6.
CCC: 00 - 11 months(Feb to Dec) 01 - 7 months (Jan to Aug) Result: 12 months
Need to get: AAA 11 BBB 6 CCC 12
Here is the source table:
SELECT 'AAA' SSN, '00' CODE, '01-FEB-89' RECORD_DATE, 50 Payment from dual union all SELECT 'AAA' SSN, '01' CODE, '01-AUG-89' RECORD_DATE, 50 Payment from dual union all SELECT 'BBB' SSN, '00' CODE, '01-FEB-89' RECORD_DATE, 0 Payment from dual union all SELECT 'BBB' SSN, '01' CODE, '01-AUG-89' RECORD_DATE, 50 Payment from dual union all SELECT 'CCC' SSN, '00' CODE, '01-FEB-89' RECORD_DATE, 50 Payment from dual union all SELECT 'CCC' SSN, '01' CODE, '01-AUG-89' RECORD_DATE, 0 Payment from dual union all SELECT 'DDD' SSN, '00' CODE, '01-OCT-89' RECORD_DATE, 0 Payment from dual union all SELECT 'DDD' SSN, '01' CODE, '01-AUG-89' RECORD_DATE, 50 Payment from dual
