i have a problem in renaming the column of a table and it throws a strange error-"ORA-14155: missing PARTITION or SUBPARTITION keyword " ,but i have used correct syntax which is that " alter table temp rename id to num; " where is the problem.
View 2 Replies
I want to be able to name a column created from my query.
Query is:
select A.OrigRef, A.DisplayName, A.ExtCode, count(CalcId)
from OrigRefView A, CalcView B where A.OrigRef = B.NewRef and A.OrigRef like 'AB%'
group by A.OrigRef, A.DisplayName, A.ExtCode
order by A.SusRef
and it returns the Count in "column4" Is there a way I can get the query to output a different column name without creating a whole new table (i.e. not by creating a new table for my query output and then running a new procedure at the end to rename the column...)?
i.e. I want "column4" to read "CountofCalId"
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"
Is there any way to rename a column in a table A, so that it is changed wherever it is used, like all the procedures that uses this column, or other tables that has reference to this table A?
I would like to try to rename a newly created dB. Referring to the topic posted on your website[URL]... had some clarification before executing the dbrename.sql mentioned there:
After modifying the the init.ora and tnsnames.ora, do I have to rename all OLD, existing directory path (for controlfiles, datafiles, etc.) with the new-- meaning I don't have to create another directory for it's 'NEW' name?
I have another question to ask on packages..
I have a dictionary under the schema
OWNERSYS
DIRECTORY_NAMEUTL
DIRECTORY_PATHc:oracleoradataspmap1utl
I have cretaed an external table to read data to a table from a csv file placed in "c:oracleoradataspmap1utl". The csv file name is say "pildata.csv"
I have cretaed a package to read data from the exteranl table and to insert it into a table.
INSERT INTO M_PILEINT SELECT
A.AREA AS "AREA",
A.SUB_FAC_DESC AS "SUB_FAC_DESC",
A.SCOPE_DETAIL AS "SCOPE_DETAIL",
A.MTO_ISSUE_DATE AS "MTO_ISSUE_DATE",
A.MTO_TAKE_BY AS "MTO_TAKE_BY",
A.COMMODITY_CODE AS "SECTION"
A.PILE_NAME AS "PILE_NAME"
FROM M_EXE_PILE A
(where M_EXE_PILE A is the external table which is reading from pildata.csv)
The package runs good and data is populated to M_PILEINT .Is there a way , I can rename the csv file (say to pildata_logxxxx.csv.. something like that) from within the package.Whenever the package is run , it will copy the data from exteranl table and renames the csv file to something else..?
I've 2 schemas namely department, hrNow department schema is having one table EMP . And a view is created on that table with the name R_EMPSchema Name :
departmentTable Name : EMPView Name : R_EMPGRANT SELECT ON R_EMP TO HR ; In HR schema a synonym has been created on view R_EMP with the name R_EMP .CREATE OR REPLACE SYNONYM hr.R_EMP for department.R_EMPSchema Name : hr Synonym Name: R_EMP
Now I want to rename the view name R_EMP to V_EMP from HR schema only So, I tried the folloiwng syntax in HR schema (All the operations should be done from this schema only )[code] RENAME department.R_EMP to department.V_EMP ;
Error: ORA-01765: specifying table's owner name is not allowed[/code] How to change the syntax to make the statement work from HR schema .
How can we Rename or Relocate the SYSTEM datafile?
View 3 Replies View RelatedWe are planning to change the dbname from appr to apdr. Its a DR (Standby Database).
a. NID option will work for this? - DB in mount state (Standby db)
b. Is that possible to change the Instance name also?
c. Any Prerequsities and postrequirments for this change? (ie,Data Broker, communication with Primary and Standby etc?
Oracle 10g, OS - Sun Solaris
While renaming the datafile.
1) ALTER TABLESPACE .... OFFLINE;
2) CHANGING THE DATAFILE NAME IN OS LEVEL;
3) ALTER TABLESPACE .... RENAME DATAFILE 'FILE_OLD' TO 'FILE_NEW';
4) ALTER TABLESPACE .... ONLINE;
In the above steps, I HAD FORGOTTON TO FOLLOW THE 2ND STEP.
At the step of 3rd, i got error message, now i am not able to change the name in OS level Also.
LSNRCTL> status
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
Start Date 09-NOV-2012 19:55:06
Uptime 0 days 0 hr. 24 min. 26 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:oracleproduct11.2.0dbhome_1
etworkadminlistener.ora
Listener Log File c:oraclediag nslsnrTESTQ51listeneralertlog.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=NDCORCKSDQ51.global.loc)(PORT=1521)))
Services Summary...
Service "testdb" has 1 instance(s).
Instance "ksdtest", status READY, has 1 handler(s) for this service...
Service "testdbXDB" has 1 instance(s).
Instance "testdb", status READY, has 1 handler(s) for this service...
The command completed successfully
i am trying to rename the logfile
"c:oraclediag nslsnrTESTQ51listeneralertlog.xml" to "c:oraclediag nslsnrTESTQ51listeneralert estdb.log"
so far i tried
LSNRCTL> set log_file testdb.log
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
TNS-01251: Cannot set trace/log directory under ADR
LSNRCTL>
When a user is renamed in Active Directory, they can no longer connect to the Oracle DB thru OS authentication. There is no OID/DIP integration.
sqlnet.ora
SQLNET.AUTHENTICATION_SERVICES = (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, HOSTNAME)
NAMES.DEFAULT_DOMAIN = cal.com.br
create user "CALRENATOH" IDENTIFIED EXTERNALLY
GRANT CREATE SESSION TO "CALRENATOH"
AD User CALRENATOH can connect to DB as 'sqlplus /'
But after renaming AD User CALRENATOH to CALRENATOH1 and dropping DB user CALRENATOH and creating DB user CALRENATOH1 drop user "CALRENATOH"; create user "CALRENATOH1" IDENTIFIED EXTERNALLY;
Now OS authentication 'sqlplus /' fails 'ORA-01017: invalid username/password; logon denied'..Once I recreate the DB user with old AD user name 'CALRENATOH', OS authentication succeeds. create user "CALRENATOH" IDENTIFIED EXTERNALLY;
C:Windowssystem32>set username
USERNAME=RENATOH1
C:Windowssystem32>sqlplus /@rmlab001
SQL*Plus: Release 11.1.0.6.0 - Production on Tue Jul 3 15:16:46 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning and OLAP options
Why the Database is still looking for old AD user name? Does Oracle cache information about OS authenticated users?
Provide automated shell script for renaming oracle DB 10gR2. Now it is a daily manual job for us.
View 4 Replies View RelatedI have one hirarchical query which return the parent to child hirarch level data. it has 11 level child data. i want to create column based on number of child in hirarchy. though i know it is 11 but it can change also.Is there any way i can create the column dynamically
ORG_UNITCOST_CENTERORG_UNIT_NAMEPARENT_ORG_UNITLLSYS_CONNECT_BY_PATH(ORG_UNIT,'/')
500171960000022000Managing Director - LUL500169965/00000001/50000001/50017588/50016996/50017196
500018370000021241FSO500171966/00000001/50000001/50017588/50016996/50017196/50001837
502894940000021241Knowledge Management500018377/00000001/50000001/50017588/50016996/50017196/50001837/50289494
508014980000021241Finance500018377/00000001/50000001/50017588/50016996/50017196/50001837/50801498
On my APEX page i have region which has sql query as source and it displays as HTML table the query result to the user.
I want to display addinonal column with a hyperlink inside, and that hyperlink would have CGI/URL-parameters which contains the other values of the HTML row.
So, let's say my APEX region queryes columns as "select c1, c2, c3, c4 ..." and displays out values "V1, V2, V3, V4" then i want to have addional output column with such hyperlink:
a href="f?p=100:7:13467554876288::NO::c1,c2,c3,c4:v1,v2,v3,v4">My link column with CGI-parameters</aHow can i create such hyperlink?
The overall idea is that the link would forward to a page which loads those values "v1,v2,v3,v4" into form fields and user can proceed from there.
---
"Application Express 4.2.1.00.08"
I receive the error,
REP-1241 Circular column dependency originating with column 'cenvat_opening'
I have the following return statement for a column total_credit;
function total_creditFormula return Number is
begin
Return (return nvl(:cf_cenvat_closing ,0) - nvl(:cf_duty_on_goods_ed,0);
end;
Now i am using this total_creditFormula for the error formula column 'cenvat_opening'
function cenvat_openingFormula return Number is
f_num_opn number(14,2);
begin
SELECT NVL(Amount,0) INTO F_NUM_OPN
[code]...
here the formula column 'total_credit' has the following return statements;
RETURN Nvl(:cenvat_opening,0) + Nvl(:cenvat_credit_manu,0)
so how can i use this formula column? is there any other option to return the value in the same column.
I'm trying to do a pivot query in oracle to get the years from a column and make a separate column for each. I found an example of the code to use on the internet and i changed it for my own tables but i'm getting errors. Namely a "FROM keyword not where expected" error at the beginning of the 'avg(...' statements.
I have copied the code used in
select stud_id, 2006, 2007, 2008, 2009
from (
select stud_id,
avg(case when year=2006 then ((present/poss)*100) else null end) 2006,
avg(case when year=2007 then ((present/poss)*100) else null end) 2007,
avg(case when year=2008 then ((present/poss)*100) else null end) 2008,
avg(case when year=2009 then ((present/poss)*100) else null end) 2009
from attendance.vw_all_attendance_perc
group by stud_id
);
i have two questions.
(1) how can i fill some value in a table column based on some existing column value automatically without user intervention. my actual problem is i have 'expiry date' column and 'status'. the 'status' column should get filled automatically based on the current system date. ex: if expiry date is '25-Apr-2011' and current date is '14-May-2011', then status should be filled as 'EXPIRED'
(2)hOw can i build 'select' query in a report (report 6i) so that it will show me list of items 'EXPIRED' or 'NOT EXPIRED' or both expired and not expired separately in a single report based on user choice. 'EXPIRED' & 'NOT EXPIRED' can be taken from the above question no. 1.
This is what i want to do:
I have a basic report looking like this:
<Column name>
value 1
value 2
value 3
.
.
.
value n
Since the column is small it can fit in a page more then one time. I know how to make it print more then once: i switch the repeating frame to print down and across and i modify the frame that contains it so the horizontal elasticity is variable.
After these changes my report looks like this:
<Column name>
value 1-----------------------value n+1-----------------------value m+1
value 2-----------------------value n+2-----------------------value m+2
value 3-----------------------value n+3-----------------------...
.------------------------------- .
.------------------------------- .
.------------------------------- .
value n-----------------------value m
What i want is my report to look like this:
<Column name>-------<Column name>------------<Column name>
value 1-----------------------value n+1-----------------------value m+1
value 2-----------------------value n+2-----------------------value m+2
value 3-----------------------value n+3-----------------------...
.------------------------------- .
.------------------------------- .
.------------------------------- .
value n-----------------------value m
I have written the code for sql loader, I have given termination of file is | , i have given five columns name in sequence...My Requirement is that i want to mapped 100th column from Csv file after 5th column how to do it.
OPTIONS (ERRORS=100000000)
LOAD DATA
CHARACTERSET UTF8
INTO TABLE "TARGET_STG_AMC"
TRUNCATE
[code]....
We are using Oracle 11g with Apex 3.2 on AIX. We are reporting data from customer satisfaction surveys. I'm using the following sql to create my report
<code>select * from(
select month,'Overall Satisfaction' as q_group, 1 as srt,Overall Satisfaction,site,
case when count(*) < 31 then '*' else
round((sum(ttos)/count(*))*100,0)||'/'|round((sum(bfos)/count(*))*100,0)||'/'||count(*) end ospct
from v_XXX_report a,(select distinct month_dt month from v_XXX_report) b
where Overall_Satisfaction is not null and year_dt = 2012
[Code]....
The problem is that site is not allways present and sometimes I have other variables in addition to site. This creates a situation where the month columns will not allways appear at the same column number. For example, When I run this query as is then the "JAN" column is Col3 (first column is a break, col2 is not shown). When I run this query without site then "JAN" is the second column. I would like to create column links for the "JAN" - "DEC" columns but not for any other columns.
Another issue - in the column link creation screen I can create up to 3 variables that I can pass to the next page. Since my query is a pivot I'm uncertain how to pass the column heading or the row value (for col2)
ie
Overall Satisfaction JAN FEB MAR APR MAY ...
Overall Satisfaction 12/12/200 12/12/210 12/12/220 12/12/230 12/12/240...
Recommend 12/12/200 12/12/210 12/12/220 12/12/230 12/12/240...
etc.
So if I clicked on the values at Recommend:FEB how can I get "Recommend" and "FEB" into variables that I can use on the next page? I've tried #column_name#, #month#, #q_name# and #APEX_APPLICATION.G_F10# but no luck.
I want to pass multiple column values of a row in an interactive report page to hidden items in another page through column link. And I did it successfully. However, I found I need to pass more than 3 columns of a row in this report, while a column link only permits me to pass 3 column value at most. Is there anyway that I can pass more column values to hidden items in another page?
View 3 Replies View RelatedCREATE TABLE TYPE
(
c1_type VARCHAR2 (10),
c2_type VARCHAR2 (10),
c3_type VARCHAR2 (10),
c4_type VARCHAR2 (10),
c5_type VARCHAR2 (10),
c6_type VARCHAR2 (10),
[code]......
actual output of the below query, but i want to display in different way
select * from type;
C1_TYPE C2_TYPE C3_TYPE C4_TYPE C5_TYPE C6_TYPE C7_TYPE C8_TYPE C9_TYPE
Region_D Region_E Region_F Region_D Region_E Region_D Region_M Region_D Region_E
The expected output should be like this below, how to write a query or which built in function used to get the below result,
Region_D
Region_D
Region_D
Region_D
Region_E
Region_E
Region_E
Region_F
Region_M
I have a table where i have description column which free text column, the data in description column is seperated and i want to corvert 1 row data in multiple rows dependeing on the number of words.
eg
id description
78664 Pumps Alarm from CAMS RTU154
In the above example this column has 5 word so i want data in 5 rows like below
78664 Pumps
78664 Alarm
78664 from
78664 CAMS
78664 RTU154
This column data can be varied from 1 to any number of words.
I am trying to create a trigger which changes the content of one column based on the information within another column.
For Example, if the 'ITEMQUANTITY' field drops below 1 then I want the STATUS column to say 'Out Of Stock'.
is there some performance/access difference between a bitmap index on a number column and char(1) column? Both columns are not null with a default value.My application has a querie like this:
select ass.column20, ass.column30from table_a pucinner join table_b asson ass.column1 = puc.column1where pc.column_char = 'S'and ass.column_char02 = 'P'
If I create a bitmap index on column "column_char", the access plan is not changed. But changing the column datatype to number(1) and obviously the values, the index is accessed and the cost decreases.This table has 4.000.000 rows. Oracle 11.2.0.2SO
Is there a way to alter an existing virtual column to set as a real column without dropping it and recreate (i have index on it, and i don't want to recreate them).
View 2 Replies View RelatedI have a partitioned table with ~50 million rows that was setup with a number(10) instead of a date column. All the data in the table is ALWATS in this format YYYYMMDD
CREATE TABLE T1.monthly
(
SEQ_NUM NUMBER(10) NOT NULL,
DAY_DK NUMBER(10) NOT NULL
)
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
[code]........
some sample data
SEQ_NUM DAY_DK
---------- ----------
990 20121225
991 20121225
992 20121225
993 20121225
994 20121225
995 20121225
996 20121225
997 20121225
998 20121225
999 20121225
When I use the exchange partition method the parition is able to move the data from "monthly" table to "mth" table.
desc t1.mth; ### my temorary table
Name Null? Type
----------------------------------------- -------- ----------------------------
SEQ_NUM NUMBER(10)
DAY_DK NUMBER(10)
Than when I try to alter my temp table "mth". I get an error table must be empty to change column types.
alter table n546830.mth modify (DAY_DK date);
Next I tried making my temporary table "mth" a date column. When I an the exchange partition command I get the following error:
alter table t1.monthly exchange partition DEC_2012
with table t1.mth without validation;
alter table n546830.monthly exchange partition DEC_2012 with table n546830.mth without validation
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
Method I can use to convert a number(10) to date column and keep the information in a table. Note, I don't care about HH:MM:SS as I never had that information to begin with and would be happy to set that part of the date column to all zeroes "00:00:00"
if one of the columns is given as
ABC varchar2(10)
can we tell anything about the size of the data in bytes that this column going to hold.
I used this query:
SELECT to_char(return_date_time,'dd/mon/yyyy hh24:mi:ss') Test,DECODE ('I', 'I', DECODE (1, NULL, NULL, 0, NULL, SYSDATE + 1)) Days,DECODE ('I', 'I', SYSDATE + 1) Day1FROM fm_curr_locnWHERE patient_id = 'DU00002765' output: TestDays Day126/oct/2013 00:00:00 26-OCT-13 10/26/2013 3:06:59 PM
I want to know the difference between Days column and Day1 column and why the days column didn't show the time .
I have a INSERT query which is happening with a SELECT query.
===================================================
INSERT INTO tbl_fact_effort_lvl_data ( ............... )
SELECT ria.report_id,report_status: :,
((SELECT lov_num_val
FROM tbl_reference_data
WHERE lov_type = 'FREQUENCY' ) * (SELECT SUM(pph_task)
FROM tbl_ri_process
WHERE report_id = ria.report_id )) TOT_YEARLY_PROD_HOURS ,TOT_YEARLY_PROD_HOURS * tf.fac_value TOT_FACT_DATA,location_id
FROM tbl_fact tf LEFT JOIN ......... ;
====================================================
So, here I want to use column alias TOT_YEARLY_PROD_HOURS as another column to derive another column value TOT_FACT_DATA.
