I've been trying to write some code to add a column if it does not exist as the code will be run numerous times and will be parameterized in other software to run across multiple tables.
Here is what I have made so far:
DECLARE
COLEXISTS integer;
vCmdStr varchar2(4000);
[Code]....
I can't figure out where the invalid character is.
how do I add a column to an existing table which when rows are added to the table this column will have the same default value?
alter table thetable add (new_column_name varchar2(100) default bbubu);
I had created a table which have 100s' of entries in it.
create table reg_user
(
USERNAME VARCHAR2(50),
PASSWORD VARCHAR2(20)
)
***
[Code]..
1. In this table, how to update the Nth row, how can I do it
2. Now I need to add USERID in this table,which will get value from 1 to max no. of rows. I do not want to drop the table and again re create it adding USERID or update each row manually. Is there any other way to add USERID and have IDs from 1 to max IDs.
I want to add column to table which has huge amount of data and fill with data from another table. What is the best way to do it? Is it faster to use CTAS instead of ALTER TABLE ADD COLUMN?
View 2 Replies View RelatedI want to add some new columns in an existing table that has over 10 millions rows, but not at the end of existing columns, my requirement is such i want new columns between existing existing columns. Is it possible.....
View 16 Replies View Relatedi have 2 data block (maintenance & maintenanceparts) in one form and i will like to add the values of the attribute "LineCost" in the maintenanceparts datablock and put the sum into 'Totalcost' attribute in the maintenance datablock.
I tried use the code
"begin
select sum(maintenanceparts.LineCost)into :maintenance.totalcost
from MaintenanceParts;
end;"
But it ended up adding all the records that was save in that attribute and putting it in totalcost. I am new to oracle
below query.
1) How to add a new column to the existing table's particular position, instead of atlast.
2) I created a table without mentioned the datatype size as below Create table dummy (name char, age number). Then what is the default size will be allocated for those column's?
we received a design advise to add columns to track the update and delete done on each row in our tables:
- DELETED_DATE_TIME
- DELETED_BY
- UPDATE_DATE_TIME
- UPDATED_BY
In our architecture, the application can only access functions/procedures to access/modify data. Each function logs the action, the executed sql statement, oracle error, user terminal, and the user into a unified log table by using v$ tables to create a general log function that is called after execution or error.
The only advantage is that it will be easier to know the delete and last update information faster versus space and design modification.
I have two tables.
First one is
STAFF
======
STAFFNUM NAME
======== ====
1 A
2 B
3 C
SUBJECT
=======
SUBCODE SUBNAME LECTURER
======= ======= ========
A1 ABC 1
A2 EFG 2
A3 HIJ 1
I did the following query
SELECT STAFF.STAFFNUM, STAFF.NAME, SUBJECT.LECTURER
FROM STAFF,SUBJECT
WHERE STAFF.STAFFNUM NOT IN SUBJECT.LECTURER
It will show me..
B and C is not teaching A1.
A and C is not Teaching A2.
B and C is not teaching A3.
This is not what I want.
What I want is to show who is not teaching any subjects.So the expected result is only C coming out.
I am creating an index in program and then drop the index at the end of the program. Some times due to some problem if the index could not be dropped and the user again runs the program then we get the error
ORA-01408 Index already exist on the column.
how I can get away with this error or how I can check whether the index with the same columns exits prior to creating an index.
Is there any command like
Create or replace index.....
I have oracle table with a column of datatype CLOB. In the clob column having the manipulation statements (update, insert) as records.
How to execute the statements exists in a clob column as records?
my table structure and one record is
CREATE TABLE ACTN_CFG_T
(
OBJ_ID NUMBER(32) NOT NULL,
ACTN_ID NUMBER(38) NOT NULL,
ACTN_SEQ NUMBER(2) NOT NULL,
RU_DEF CLOB NOT NULL
[code]....
I want to execute the update statement when i call the obj_id. I used the follwing procedure but error.
1 DECLARE
2 V_ABC CLOB;
3 BEGIN
4 SELECT RU_DEF INTO V_ABC FROM ACTN_CFG_T WHERE OBJ_ID = 1625;
5 EXECUTE IMMEDIATE V_ABC;
6 END;
error is...
ORA-06550: line 5, column 20:
PLS-00382: expression is of wrong type
ORA-06550: line 5, column 2:
PL/SQL: Statement ignored
Oracle 11g r2, APEX 4.1.1.00.23.
I have some classic reports.
I go to Report Attributes, then I click Add Column Link in the "Tasks" right menu, it adds me a column link, I just add some text for the link and a page to go to. Then I run the report and I get :
report error: ORA-01403: no data foundTested with several classic reports on multiple pages.
Debug mode shows me :
0.43816 0.00240 ...Execute Statement: select distinct [...] order by 3,11 ,4
0.44056 0.00162 print column headings
0.44218 0.04816 rows loop: 25 row(s)
0.49037 0.00141 report error: ORA-01403: aucune donnée trouvée
0.49175 0.00078 Computation point: After Box BodyWhen I run the query in my favorite tool, I get expected results.
Did I missed something ?
I am having the following table and values.
A | B
=====
1 | 1
2 | 2
3 | 3
4 | 4
5 | 5
I need an output in following
A | B | C
=========
1 | 1 |
2 | 2 | 3 --> 1+2
3 | 3 | 6 --> 3+3
4 | 4 | 10 --> 6+4
5 | 5 | 15 --> 10+5
I mean add the first row of column b with second row of column ==> this output need to show in column c
again this output need to add with third row of column b .......
any sql commands / keywords for this
I have a table with zip codes and their plus four values. For ex: zip code of 10000, which has corresponding plus four values of 001, 002, 003, and 008, 009, 010. The issue is just that--a zip code can have sequential plus four values, and then it will skip several potential plus four values, and then start again. I would like to assign a low plus 4 value and high plus four value to a zip code, keeping in mind that the plus four values are not always sequential. So, it would be similar to this:
zip plus4 low plus4 high
10000 001 003
10000 008 010
I have DB on cluster and it set to active/active. ASM disk currently using raw device and connect to SAN storage. So i want to add new ASM disk on current environment.
Does oracle have this doc? What i can see from their said just show adding new ASM to new installation. Does it same as adding new ASM disk to current system? Does it success on implementation?
select bill_no from bill_mst is showing 25 records.
select bill_no from bill_mst order by 1 is showing 5 records.
I am trying to run an SQL query which refers/joins around 10 tables.In my case I would want to add an if-else or case condition in the WHERE clause of select query
For example
select a.column1, b.column1, c.column1, d.column1
from a, b, c, d
WHERE a.column2 = b.column2
AND (if a.column3 = 10 then ( I want 2 conditions to be
AND b.column2 = c.column2) added
else after WHERE clause as AND...)
AND b.column2 = d.column2
)
AND d.column2 = a.column2
I have created the below trigger in Oracle to ensure that a value in a table remains at 180 but I need to restrict this to one row in this table. Is there a way to use an IF statement or a where clause of some type for a particular row in that table? The Row is called ADMIN. Triggers weren't covered in my course so only know a few basic statements.
CREATE OR REPLACE
TRIGGER DAYSAGO_trg
BEFORE UPDATE ON days_ago
FOR EACH ROW
BEGIN
:NEW.days_ago := 180;
END;
When I added a new node to RAC (existng 2 windows server 2008 R2 nodes), using the command,
CODEaddNode -silent
"CLUSTER_NEW_NODES={node3}" "CLUSTER_NEW_PRIVATE_NODE_NAMES={node3}"
"CLUSTER_NEW_VIRTUAL_HOSTNAMES={node3-vip}"
the output was "The cluster node addition <path> was successful".
But when I tried the following command,
CODEcluvfy stage -post nodeadd -node3
I got an error stating
QUOTE "ERROR: Shared resources check for node addition failed
The following node is not in cluster: node3
Post-check for node addition was unsuccessful on all the nodes."
When I checked the inventory logs, I found the following entry
QUOTE SEVERE:
oracle.sysman.oii.oiix.OiixException: The Oracle home '<path>' could not be updated as it does not exist.
at oracle.sysman.oii.oiic.OiicBaseInventoryApp.getOracleHomeInfo(OiicBaseInventoryApp.java:645)
at oracle.sysman.oii.oiic.OiicUpdateNodeList.doOperation(OiicUpdateNodeList.java:206)
at oracle.sysman.oii.oiic.OiicBaseInventoryApp.main_helper(OiicBaseInventoryApp.java:790)
at oracle.sysman.oii.oiic.OiicUpdateNodeList.main(OiicUpdateNodeList.java:399)
Can we add 10000 days in SYSDATE using + operator ?
Quote: This I can test it but I don't have database access now that's why I am asking this question
view the below select statement..why it's adding extra zero's...
select to_timestamp('2001-05-22 12:00:18.600','YYYY-MM-DD HH:MI:SS.ff3AM') from dual
output: 5/22/2001 12:00:18.600000000 PM ---why it's adding extra zeors's
my output should be as " 5/22/2001 12:00:18.600 PM"
I created a table but I want to add the Unique check to it as I forgot to apply it to the table when I created it.Is it possible to make the field Unique after having created the table or do I have to drop the table and re-create it?
View 3 Replies View Relatedcolumn name: extra_hour
datatype: varchar2(5)
extra_hour
----------
01:30
01:30
00:00
02:45
and so on...
Problem: I want to add up all the given value and display it in a select statment, that is the output should be:
Output:
XXXXXX
----------
05:45
Ive got a look up table and i want it to display text instead of just the ID number, how would i do that? so for example if
1 = Hello
2 = Bye
it would display "Hello" instead of "1"
I checked and found we have disk that is assigned with 0 disk GROUP_NUMBER. What does that mean ? how to check if disk T1_ASM05 is been part of any disk group or not.?
SQL> select GROUP_NUMBER,NAME from v$asm_diskgroup;
GROUP_NUMBER NAME
------------ ------------------------------
1 DATA
2 FRA
SQL>
SQL> select GROUP_NUMBER,name,PATH from v$asm_disk;
GROUP_NUMBER NAME PATH
------------ ------------------------------ -------------------------
1 T1_ASM01 ORCL:T1_ASM01
2 T1_ASM02 ORCL:T1_ASM02
0 ORCL:T2_BACKUP01
0 ORCL:T1_ASM04
0 ORCL:T1_ASM03
0 ORCL:T1_ASM05
6 rows selected.
SQL> !
/home/oracle > /etc/init.d/oracleasm listdisks
T1_ASM01
T1_ASM02
T1_ASM03
T1_ASM04
T1_ASM05
T2_BACKUP01
/home/oracle >
I want to have sum of Time Stamp Values in a Table. i.e. Group by Query
example : Select Name, Sum(Timestamp column) from Emp group by name;
OR
I have Time values in character column of a table. i.e. Table TT column t1 char(5) values '03:02'
Above mentioned values in more than one line, i want to get the sum of all the values and it should be a time value.
I am trying to use a CASE statement
CASE WHEN (EP.ORDER_NUMBER IS NOT NULL AND ATD.IS_DISCONNECT_CREDIT = 1 ) THEN
.........
......
END
In my case when I club two conditions after WHEN in case statement , everytime the first condtion is satisfied (i.e EP.ORDER_NUMBER IS NOT NULL) it is entering into the loop.
Irrespective of using AND or OR after the first condition I am getting the same results.
I want to enter the CASE statement only when both the conditions (1st and 2nd )are satisfied
i just want to add at the end of each area_cd the total number of asset.. what do you think i should add in my query????
View 32 Replies View RelatedI was given this query: SELECT TZ_OFFSET('EST'),dbTIMEZONE,SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL;
My coworker also said this:
Using this statement as a starting point:
SELECT TZ_OFFSET('EST'),dbTIMEZONE,SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL;
but I need
for
select -5 from dual;
to return
America/NewYork or EST
I have this query
select EAG.AUDIT_NUMBER Audit_Nbr,
EAG.AUDITEE_NAME Grantee_Name ,
EAG.EIN Grantee_EIN_IRS,
EAG.AUDIT_ISSUE_DATE Audit_Issue_Date,
MAX(AUDT.derive_audit_progress_status(EAG.SYS_AUDIT_ID )) Audit_Clesed_Date,
EAG.OIG_DUE_DATE Six_Month_Due_Date,
[code].....
I want o add additional column to this
I added this sql
SELECT CASE
WHEN currentstep.step_id IN (100)
THEN currentstep.start_date
ELSE (SELECT start_date
FROM audt.os_historystep
WHERE ID =
[code].....
here is the completed query
select EAG.AUDIT_NUMBER Audit_Nbr,
(
SELECT CASE
WHEN currentstep.step_id IN (100)
THEN currentstep.start_date
ELSE (SELECT start_date
FROM audt.os_historystep
[code].....
when I try select from this query I get ORA-00904: "DATE1": invalid identifier.
how to add the new column in group by clause.