I have one table as per the attachment, with id(Primary key),level,name and Srno.Now I want to maintain Sr. if user transfers level 2 of one block (level 1) to another block.is it possible through any oracle function.
View 8 RepliesI have following table.
CREATE TABLE ABC
(
DPT_NUM NUMBER,
LOT_NUM NUMBER,
ASSOCIATED_WITH_LOT NUMBER
);
SET DEFINE OFF;
Insert into ABC
[code]......
Now i run follwoing query and result is
select * from abc
DPT_NUMLOT_NUMASSOCIATED_WITH_LOT
1501
1502501
1509501
1511
1503
1516
1522
1565
1569565
2601
2602
2604602
2607
2508
I need following result
DPT_NUMLOT_NUMASSOCIATED_WITH_LOT Serial_Number
1501 1
1502501 2
1509501 2
1511 3
1503 4
1516 1
1522 2
1565 3
1569565 3
2601 1
2602 2
2604602 2
2607 3
2508 3
Note that serial number must reset after each 4 lot_num againt dpt_num. Not that where lot is associated in associated_with_lot there serial number must be same as serial number is for associated lot.
I have two tables,
CREATE TABLE TBL_ID(
SERIAL NUMBER(3),
ID NUMBER(5));
CREATE TABLE TBL_ID2(
ID NUMBER(5));
[code]...
I want translate id from TBL_ID2 to TBL_ID, and make new serial as max serial + 1 I tried
insert into TBL_ID(serial,id)
select (select max(serial) + 1 from ),id from TBL_ID2
Desired Output after insert
select * from TBL_ID
SERIAL ID
------ --
1 10
2 20
3 50
4 60
5 90
I have follwoing Table
create table abc( prd_cod number,tax_num number, tkt_num number,shd_cod number,cnt_num number,ctn_num number);
Insert into ABC(PRD_COD, TAX_NUM, TKT_NUM, SHD_COD, CNT_NUM, CTN_NUM) Values (1, 1, 1, 1, 1, 1);
Insert into ABC(PRD_COD, TAX_NUM, TKT_NUM, SHD_COD, CNT_NUM, CTN_NUM) Values (1, 1, 1, 1, 2, 1);
Insert into ABC(PRD_COD, TAX_NUM, TKT_NUM, SHD_COD, CNT_NUM, CTN_NUM) Values (1, 1, 1, 2, 1, 1);
[code]...
Now i have following data
PRD_CODTAX_NUMTKT_NUMSHD_CODCNT_NUMCTN_NUM
111111
111121
111211
112111
151211
211111
I need to query above data with a column "Srl_Num". Its serial number and serial should like 'AA1','AB1'. In serial number is last digit 1 is "Ctn_Num" Column Value. First Letter 'A' of serial number is 'A' and second letter of serial number will change. When it reaches till 'Z' Then first letter will change.I mean if serial number is 'AZ1' Then next serial number will be 'BA1','BB1','BC1' and so on.
I need query to show data like
Srl_Num PRD_CODTAX_NUMTKT_NUMSHD_CODCNT_NUMCTN_NUM
AA1 111111
AB1 111121
AC1 111211
AD11 12111
AE1 151211
AF1 211111
How can i print serial no of records in sql query? I know I can achieve this with selecting rownum but it gives wrong data if used with group by like below
select rownum,deptid from emp group by rownum,deptid order by rwnum
how do you generate serial numbers? i have an invoice where i have to add items, so i want the numbering to start from 1 everytime i start a new form and increment by 1 as i i enter a new record. For this i have put my code in when-new-record-instance but problem is if i delete a record and enter a new one, the serial number starts from the nex number.
For ex. if i have 4 items with serial numbers 1, 2, 3, 4 and i delete the item at no. 3 and add a new item , the serial number comes as 5.
proper code to generate the serial numbers?
i want to get serial numbers in oracle forms. i have one data block with 10 rows and a non database display item with corresponding length as that of the original data block .
when ever i am executing the form i want to see the serial number generated in the display item and when i inserted a new item or row it should show the corresponding rownum.
eg:
empno name serialno
5 tom 1
4 tinil 2
6 sofy 3
7 john 4
1 albert 5
2 michel 6
i want to get the above answer using forms.
I need Sequence generate in each row in tabular style form
Like
FA0015
FA0016
FA0017
(I dont want to use sequence because when i delete the row its conceder delete row in sequence )
I have form with master detail relation ship (invoicing form) the detail block is tabular, displaying upto 7 records...
Now my clients wants to show the serial number along with each record while feeding the data (this includes when insert, editing, deleting / clearing records) he wants to see the serial number as in MS access / MS techniques.
I tried to use the system variable to use :system.cursor_record; but this dose not works.(in insert/edit/delete/clear record)
I have created simple data block by wizard and taken tabular layout 10 rows and 6 columns
i want to generate serial number on first column .....
what need to use..?
I do need to mantain a table with the las 13 of a month, for example:
ID Month_ID MONTH_MAT
1 201206 201106
2 201206 201107
3 201206 201108
4 201206 201109
5 201206 201110
[Code]....
Kind of a year-to-date table but this one is 13 months one.
I have been trying to create the code from scratch
I have thought of using a Function:
Query: SELECT TRUNC(ADD_MONTHS(SYSDATE, -13),'MM') FROM dual;
Result: 01-MAY-11
I have a requirement like below
I have 3 tables (Prospect,customer,user) and I have a contact_dtls table .
In prospect table Prospect_id is the primary like this customer_id in customer table and user_id in the user table
in the contact_dtls table I have a column contact_id and other cols.The contact_id can be from prospect_id,customer_id and user_id in other words we can tell the prospect can be a contact or the customer can be a contact and the user also can be a contact also.
Now the question is "is it possible to maintain primary key and foreign key relationship with this design that means the prospect_id,customer_id and user_id would be the foreign keys to the contact_dtls table.
We need to maintain the day 0 value of special character "ÿÿ" for application. If i use the special character directly in sql file , it is getting inserted as junk character "?"
If i use workaround by using chr(255) function i am getting below behavior.
select chr(255) from dual
---------------------------
ÿ
However below query gives null value.
select chr(255)||chr(255) from dual
What is the good practice to maintain RMAN catalog ?
RMAN backup and recovery is fine but to delete old backups it takes a lot of time.
Even if I delete backups manually,RMAN takes a lot of time to delete expired backup command.
What can be done to increase the speed of delete obsolete backup and delete expired backup operations ?
I was considering a solution to maintain a replicated copy of a database in a remote office. However we are using SE One edition of oracle, so native support for dataguard is not available. There definitely should be some scripting solutions for this task, but I can't find any to date.
View 7 Replies View RelatedThere is a scenario: I dropped all objects of 3 users and dropped other 2 users and then I checked the space from dba_segments it reflects decreased space which is perfectly fine. Problem is that when I checked the space of physical datafile on disk it remains same. How can I restore or shrink or regained the space after dropping objects and users to maintain my storage requirements.
View 1 Replies View RelatedI want to conditionally re-arrange the serial. Sample test case is as below.
DROP TABLE t1;
CREATE TABLE t1
(
[Code].....
SQL> Select * from t1 order by no;
NO T
---------- -
1245 R
1246 N
1247 N
1248 R
1249 R
1250 R
1251 N
1252 N
1253 R
1254 R
1255 N
11 rows selected.
Required output is
OLD_NO T NEW_NO
---------- - --------
1245 R 1245
1246 N 1247
1247 N 1251
1248 R 1248
1249 R 1249
1250 R 1250
1251 N 1252
1252 N 1255
1253 R 1253
1254 R 1254
1255 N 1256
Scnario is to add 1 in no. field but no.s having type 'R' will remain unchanged. e.g. Record no. 1247 is changed to 1251 because 1248,1249,1250 have typ 'R'.
I have total 8 procedure to run in parallel . and after that my 9th procedure should run.
below is my job submission procedure
create or replace procedure DURATION_ALARM_WEEKLY as
l_job number ;
begin
dbms_job.submit(l_job,'begin ALARMS_WEEKLY_CALL_OUT ; end;');
dbms_job.submit(l_job,'begin ALARMS_WEEKLY_CALL_IN ; end;');
dbms_job.submit(l_job,'begin ALARMS_WEEKLY_DURATIN_OUT ; end;');
[code].......
what is the syntax I have to do in my FINALE procedure . using DBMS_ALERT.REGISTER , DBMS_ALERT.WAITANY .....?
My management is wanting to know the serial numbers of all the components of our two Exadata machines: one quarter-rack V2 and one half-rack V1
I can use dmidecode to get the appropriate information for comp nodes and storage cells, but not for the Cisco/Voltaire switch, nor the IB switches. I read MOS 1299791.1 and the thought of asking a DOC operative to pull out the label ‘which could damage the switch’ worries me quite a bit.
Is it still true that we are unable to obtain the serial number for the IB switches and the Cisco/Voltaire switches from the CLI of the switches themselves? Sad face.
I'm trying to read some data from external device via COMM Port.
Application is running n server and device is connected localy on my PC.
My device iz DPI605R for purpose of� pressure measuring.
Is it possible to communicate with a serial device (via COM1) with oracle forms / webutil? I am able to communicate in java using RXTXcomm, but I am having problems making a bean that uses it (getting security errors and yes, I have signed the jars).
Basically, I have a scale hooked up to my COM1 port and want to be able to send commands (zero and tare) to it and read the weight from it through a form or java bean.
I want to transfer data from Oracle form 9i to serial port.
Serial port is connected to electric board to show.
Is it Possible to Maintain real Time Backup using RMAN ?
View 39 Replies View RelatedI want to connect a biometric device with my system, through serial port / parallel port. And want to permit only valid users to the system.
View 18 Replies View RelatedBackground: We are migrating a lot of databases from one SAN appliance to another. We are doing this by adding new disks from the new SAN appliance to the existing disk groups, re balancing, removing the old disks from the disk groups, and then re balancing again.
Question: If I execute two ALTER commands with the same power on 2 or more separate disk groups, do both operations start executing right away? Or do they queue up and execute one after another?
I ask because we would like to queue up several re-balances so we don't have DBAs watching status bars all day.
I have to cleanup data from our tables (Production Environment) that contain millions of rows. The question is apart from the solution of the partitioned tables what alternative recommended solution suggests Oracle?
To delete these tables by using a cursor PL/SQL block or to import all the database and in the tables that we want to remove the old rows to use the QUERY option of the data pump utility.
I have used both ways and i have to admit that datapump solution is much much faster than the deletion that suffers from I/O disk.The question again is which method from these two is more reliable and less risky for the health of the database.
I have a sale invoice form with 3 data blocks.
Data block 1- master_blk : For date/customer of sale invoice
Data block 2- detail_blk1 (detail of the master block - For products and qty)
Data block 3- detail_blk2 (detail of DETAIL_BLK1 For entering serial numbers of products)
My requirement is that whatever quantity user enter in data block 2 against each product he must enter equal number of serial numbers of that product in data block 3.
For this I have created on item (cnt_iteml : to count product's serial numbers in block3 ) in data block 2, and on summary item (t_serial_no ) in block3.
Whenever user changes in quantity, cnt_iteml: item is populated with t_serial_no in block3 of that product by following trigger on quantity column.
POST-Change Trigger ( quantity column )
:stock_transactions.cnt_itl:=:serial_numbers.t_serial_no;
Following trigger is written on block level at data block-3 to populate cnt_iteml with t_serial_no.
PRE-RECORD
IF GET_BLOCK_PROPERTY('SERIAL_NUMBERS',STATUS) IN ('CHANGED') THEN
:stock_transactions.cnt_itl:=:serial_numbers.t_serial_no;
END IF;
Above triggers are fulfilling my requirement except following condition.
If user after entering serial numbers in block 3 and without saving goes back to block2 and try to navigate to another record he gets a message asking him to save changes by forms. At this time if user presses no then cnt_itl item is not been populated with t_serial_no item's value.
What I want in above condition is that if user was inserting new record cnt_it item should be populated with 0, so that he shouldn't be able to save this record. And If he was updating then cnt_itl item should be populated with actual no of records in database against that product.
i am using oracle developer 6i report builder i required this type of query
example
if (:page number LIKE '1')
then
srw.set_text_color('darkred');
end if;
return (TRUE);
end;
but page number is not my table database item how can i use builtan page &<pagenumber> use for conditional format.
i want to replace 4 digit number in a given string with the same number incremented by 10000.
That mean in the given sting 1201 should be replace by 11201 (Icremented BY 10000).
Input String:
<query><matchAll>true</matchAll><row><columnId>1201</columnId><dataType>31</dataType><op>Like</op><val>North America - Houston</val></row><row><columnId>1212</columnId><dataType>31</dataType><op>!=</op><val>Agreement Date Mismatch</val></row><row><columnId>1212</columnId><dataType>31</dataType><op>!=</op><val>Facility Type Mismatch</val></row><row><columnId>1224</columnId><dataType>31</dataType><op>Like</op><val>y</val></row></query>
Required output :
<query><matchAll>true</matchAll><row><columnId>11201</columnId><dataType>31</dataType><op>Like</op><val>North America - Houston</val></row><row><columnId>11212</columnId><dataType>31</dataType><op>!=</op><val>Agreement Date Mismatch</val></row><row><columnId>11212</columnId><dataType>31</dataType><op>!=</op><val>Facility Type Mismatch</val></row><row><columnId>11224</columnId><dataType>31</dataType><op>Like</op><val>y</val></row></query>
I have a text field and if the text field has 5 consecutive numbers then I have to extract the number and the previous character from where the 5digit number starting
For example i/p asdfasfsdS251432dasdasd o/p should be S251432