SQL & PL/SQL :: All DML Operation Can Be Done In DUAL Table?
Apr 29, 2010What are all the DML operation can be done in DUAL table.?
View 5 Replies
ADVERTISEMENT
Add A Column To Dual Table
May 17, 2012i'v a problem regarding dual table,i logged to oracle as sys as sysdba and i add a column to dual table and then want to delete that added column but oracle gives "ORA-12988: cannot drop column from table owned by SYS"
i'm not able to drop any user oracle gives "ORA-00604: error occurred at recursive SQL level 1" "ORA-01031: insufficient privileges"
another error:: SP2-0575: Use of Oracle SQL feature not in SQL92 Entry Level oracle gives error when i want to switch user all problems starts when i add that column to dual table
Dual Table Has Only One Column?
Oct 29, 2010why the dual table has only column?
View 3 Replies View RelatedPL/SQL :: DDL And DML Commands On Dual Table
Feb 8, 2013Can we perform DDL & DML Commands on dual table????
If yes then how??
PL/SQL :: DUAL Table In Subquery Of UPDATE
Apr 26, 2013Should the following code work? I feel like I have done this before, but I'm getting a "must be a subquery" error now.
update TABLE1
set (FIELD1, FIELD2) =
values(select 'blah','blah' from DUAL)
where PK_FIELD=12345;
If this is invalid, is there another way to hardcode a series of values like this?
SQL & PL/SQL :: Creating List Of Month From Dual Table?
Nov 3, 2011creating sql query for producing result as per below from dual table.
SELECT to_char( sysdate,'MON-RRRR')
FROM dual
where sysdate between '01-APR-2011' and '31-MAR-2012'
order by 1 desc
from above query the result is
NOV-2011
but i need the result as per below
APR-2011
MAY-2011
JUN-2011
JUL-2011
AUG-2011
SEP-2011
OCT-2011
NOV-2011 CURRENT MONTH
SQL & PL/SQL :: How To Undo A Drop Table Operation
Feb 24, 2013How do I perform Undo a drop table operation?
View 12 Replies View RelatedServer Administration :: Flashback Table Operation For The Sys User
Jun 7, 2010Flashback table operations are not supported for the SYS user . I have found the above line in one of the article.
I don't know why the SYS user can not able to flash back the table.
Server Administration :: ORA-30967 - Operation Directly On Path Table
Sep 30, 2013While doing scanning of character set, error occurred in between.
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
Current database character set is AL32UTF8.
Enter new database character set name: > WE8MSWIN1252
Enter array fetch buffer size: 1024000 >
Enter number of scan processes to utilize(1..64): 1 >
Enumerating tables to scan...
. process 1 scanning REINS.RI_TD_EXECUTION_LOG[AAASwXAAAAABwkAAAA]
. process 1 scanning INS.SERVICE_TRANSACTION_LOG[AAAS5hAAAAADWiAAAA]
. process 1 scanning REINS.RI_TD_VOUCHER_DETAILS[AAASx+AAAAAC6KAAAA]
. process 1 scanning REINS.RI_TT_VOUCHER_DETAILS[AAAS0OAAAAAAMiAAAA]
. process 1 scanning CONFSYS.DML_AUDIT_LOG_FOR_DESIGNER[AAASnwAAAAAAsIAAAA]
. process 1 scanning REINS.RI_TD_TRANSACTION_LOG[AAASx0AAAAACsoAAAA]
[code]...
SQL & PL/SQL :: Matching Dates Using Dual?
Jul 7, 2010What I'm trying to do is make it so that it returns 'MATCH' when I pass a date that matches a date every two weeks starting Jan 01. Like Jan 01, Jan 15, Jan 29, Feb 12, etc. would return as MATCH Jan 02, Jan 03, etc. would return as NO_MATCH
The part in bold is what I'm having trouble figuring out.
select nvl(
(select 'MATCH'
from dual
where 'date' = '2 week intervals starting Jan 01'
), 'NO_MATCH') from dual
SQL & PL/SQL :: Select Sysdate From Dual
Aug 30, 2012when i run this nls qusery i got this error
E:oracleproduct10.2.0db_1BIN>sqlplus
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Aug 30 11:45:59 2012
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Enter user-name: sys as sysdba
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select value from v$nls_parameters where parameter= 'NLS_DATE_FORMAT';
VALUE
----------------------------------------------------------------
DD-MON-RR
SQL> select sysdate from dual;
SYSDATE
----------------
30-????? -12
SQL & PL/SQL :: UNION While Selecting From Dual?
Sep 19, 2013I would like to SELECT these 3 hardcoded titles from DUAL, and have a blank line under each, on the output in this order from the SQL. But the result does not end up that way
SQL> set heading off;
1 select '#ENCODING WINDOWS-1252' from dual
2 union
3 select ' ' from dual
4 union
5 select 'Language Section EN-US' from dual
6 union
7 select ' ' from dual
8 union
9* select 'Catalog Section Title Date Source' from dual
SQL> /
#ENCODING WINDOWS-1252
Catalog Section Title Date Source
Language Section EN-US
- - - - - - - - - - - - - - - - -
Desired Output:
#ENCODING WINDOWS-1252
Language Section EN-US
Catalog Section Title Date Source
SQL & PL/SQL :: Select From Dual Versus Equals Operator?
Mar 14, 2011I have a package function which is wrapped and I cannot see the code.The package function raises an user-defined exception when :
SELECT ABC.*
FROM ABC
WHERE ABC.A = PACK.FUNC(ABC.B,ABC.C)
But it does not raise any exception and the query works absolutely fine generating desired results when :
SELECT ABC.*
FROM ABC
WHERE ABC.A = (SELECT PACK.FUNC(ABC.B,ABC.C) FROM DUAL)
PL/SQL :: Query A Remote DUAL Causes ORA-01729 Or ORA-02019
Jul 6, 2012I have ORACLE XE 11g installed on two machines and have been trying my luck to get my local db to query/insert/update a table from the remote db.
I have created the public database link and querying the dba_db_links shows that the links is successfully created (see the bottom of the post). I have also created synonyms for my remote table and queries like select name from jforum_forums succeed, with jforum_forums being a public synonym referring to user.jforum_forums@corona.magic.ubc.ca.
However when I try to insert a row into the remote table, there is a query to the remote DUAL to fetch the last generated ID for a given table similar to the following: SELECT jforum_forums_seq.currval FROM DUAL. Statements of such fail and they either throw an ORA-02019: connection description for remote database not found or ORA-01729: database link name expected. I tried including the database link with the name of the table making it like SELECT jforum_forums_seq.currval FROM DUAL@corona.magic.ubc.ca but that didn't work either.
PS, I should mention that global_names are set to true in both databases and that my database link has the same name as the global_name of the remote database.
OWNER
------------------------------
DB_LINK
--------------------------------------------------------------------------------
USERNAME
------------------------------
HOST
--------------------------------------------------------------------------------
CREATED
[code]...
SQL & PL/SQL :: Using Sequence.NEXTVAL From DUAL Versus In INSERT Statement?
Apr 8, 2013I am trying to understand the difference between using sequence.NEXTVAL from DUAL as against using it direclty in an INSERT statment.
--Sequence Creation
CREATE SEQUENCE SEQ_ID START WITH 1 MINVALUE 1 NOCYCLE CACHE 500 NOORDER;
--Table1 Creation
Create table TABLEA (COL1 number, COL2 varchar2(10),
constraint COL1_PL primary key (COL1));
--Table2 Creation
Create table TABLEB(COL3 number);
alter table TABLEB add constraint COL1_FK foreign key(COL3) references TABLEA(COL1);
-- Option1 - Using sequence.NEXTVAL from DUAL
DECLARE
v_seq_num NUMBER;
BEGIN
SELECT SEQ_ID.NEXTVAL INTO v_seq_num FROM DUAL;
INSERT INTO TABLEA (COL1, COL2) VALUES (v_seq_num, 'test');
INSERT INTO TABLEB (COL3) VALUES (v_seq_num);
END;
-- Option2 - Using sequence.NEXTVAL in INSERT USING RETURNING INTO clause
DECLARE
v_seq_num NUMBER;
BEGIN
INSERT INTO TABLEA (COL1, COL2) VALUES (SEQ_ID.NEXTVAL, 'test') RETURNING COL1 INTO v_seq_num;
INSERT INTO TABLEB (COL3) VALUES (v_seq_num);
END;
TimesTen In-Memory :: Execute Statement SELECT CURRENT_DATE FROM DUAL On 11.2.2?
Sep 24, 2013We are trying to execute a statement SELECT CURRENT_DATE FROM DUAL on Timesten 11.2.2 . It throws error unknown referenced column error. Command> select current_date from dual; 2211:
Referenced column CURRENT_DATE not foundThe command failed. But the following doc shows the support.
TimesTen PL/SQL Support: Reference Summary CURRENT_DATE function
Returns the current date in the session time zone. YIn TimesTen this returns the current date in UTC (universal time). TimesTen does not support local time zones.
Forms :: Dual Monitor Forms With Alerts / Messages
Apr 18, 2013We have forms 11gr2 on win xp 32bit , weblogic linux server 64bit. We are converting 6i to 11g. Our system has many forms that span on 2 monitors. 1280x768 monitors, so some forms are 2560x768.
When alerts/messages pop up on the screen , they are positioned in the middle of both screens (guessing Oracle by default calculates width =2560/2, height =768/2).
Possible options:
1- is there a way to tell oracle to open alerts/messages a X Y coordinates? Maybe a config file on the server? Already file a Sr with Oracle tech support.
2- tried creating a form to do our messages. but this is flawed since when you call the new form to display the message, then the focus on current form is lost. when the message form is closed (then trigger when_window_activate fires)...this would be hell to try and control all our messages (we may have 20-30 on each big form. We have a total of 118 forms. Lots of messages with Y/N/Cancel options.
3- Maybe create a place holder for messages on all forms (bottom of screen)...thats the best idea we have so far.
4- Is there a way to do something with a java beans? We already have some knowledge with beans since we use image beans and pdf beans.
Merge Operation By ROW-ID?
Sep 8, 2011 run down of the implications of MERGE by ROWID in such a fashion:
CODE MERGE
INTO XXWT_AP_ACCRUALS_RECEIPT_F EXT
USING (
SELECT PO_DISTRIBUTION_ID,
[code]...
Can this lead to an "Unstable Set of Rows?". Is it possible for the ROWID's to change during the execution of this statement - meaning certain ROWIDs identified in the SELECT will not actually be updated when it comes to the MERGE operation?
Basically, is it sound practice to use ROWID to merge on - in cases where you dont have a WHEN NOT MATCHED condition?
Distributed Operation Not Supported
Jun 12, 2013I 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.
SQL & PL/SQL :: Like Operation For Multiple Values
Jun 12, 2013I have a source view where I have some invalid records and those should be found based on codes present in another table.
For eg. from source the records come like
****************SIINNSFDFD****FDFDF2******8
**********TABLE****************FDFSFSSFASFAS********
and if my reference table has values
SIINNSFDFD
TABLE
then these values are present as substring in the particular column in the source view. So I need to flag those records. For every record, I need to check whether all the values present in the reference table matches or not. If it matches then it should be flagged.
I can use in operator as we are not checking for the exact match and we are checking whether that value is present anywhere in that column record.
Looping results in performance issue. We can use PL/SQL for this. As the source view is put into a ETL internal file.
SQL & PL/SQL :: Invalid File Operation
May 5, 2010i have created an file in my local system (sys name : System1). i have created a directory as below
SQL>CREATE DIRECTORY test_dir AS '\System1TEMP';
I gave rights as
SQL>GRANT ALL ON DIRECTORY TEST_DIR TO PUBLIC;
DECLARE
fileHandler UTL_FILE.FILE_TYPE;
BEGIN
IF utl_file.is_open(fileHandler) THEN
utl_file.fclose_all;
END IF;
[code]......
but while executing the above procedure, Invalid File Operation error occurs.
SQL & PL/SQL :: Values Before / After The Operation Is Performed
Dec 2, 2011I want to create one sql script with the followings-
1.Show the values before the operation is performed
2.Display the values after the operation is performed.
How it can be done in a proper sql script format.
TNS-12535 - Operation Timed Out?
Apr 26, 2013Frequently getting below error in alert log file. to sort out this issue.
Database: Active standby database
OS: Windows Server
Db Version: 11.2.0.2
Error:
***********************************************************************
Fatal NI connect error 12170.
VERSION INFORMATION:
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
Oracle Bequeath NT Protocol Adapter for 64-bit Windows: Version 11.2.0.2.0 - Production
[code]...
PL/SQL :: Condition In Count Operation
Jul 25, 2012I have a car table with the following attribute sale_code,model_car etc. the format of sale_code is year/number example when a client buy a car(model_car=mercedes) her code is 2012/0125.
I want to calculate the number of cars that were sold in the year of 2011 grouped by the model_car attribute.
select count (sale_code),model_car
from car
where sale_code like '2011%'
when I tested this query i received an error.
My question is :
how to resolve the issue when we use an attribute in where and we use the same attribute in count operation ?
Insert Operation Very Slow While Import?
Feb 20, 2011I am working on an SAP application migration project using Oracle 10.2.0.2 database. We are migrating the application from Windows to Solaris.
During the process we are facing problem with very slow insert operation on a particular table.The server's capacity is very good and so no resource bottleneck.
The table contains around 2,70,000 rows and inserting at around 100 rows per 10 seconds.
The table contains following data types.
SQL> desc SAPDATDB.CAF_GP_VALDEF;
Name Null? Type
----------------------------------------- -------- ----------------------------
VAL_UUID NOT NULL NVARCHAR2(34)
VAL_GUID NOT NULL NUMBER(10)
VAL_CLOB NCLOB
Forms :: Refresh Datablock After DML Operation
May 19, 2010How do you refresh a datablock after DML operation(s) - INSERT, UPDATE or DELETE? I know "EXECUTE_QUERY" retrieves records, but what trigger do I use to automatically refresh the datablock after these operations - my form only has 1 datablock.
View 3 Replies View RelatedSQL & PL/SQL :: ORA-02064 - Distributed Operation Not Supported?
Apr 20, 2011When 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.
Server Administration :: ASM Rebalance Operation Never End
Sep 19, 2011I had added a new disk into disk group DATA1 with rebalance power of 5, it ran as estimated for about 2 days long, I can see the estimated time down to zero but the process never end.
The command used:
SQL> alter diskgroup DATA1 add disk '<path>' rebalance power 5;
Below is the disk status:
SQL> select group_number, disk_number, total_mb, free_mb, mount_status, header_status, state
from v$asm_disk where group_number=1 order by disk_number;
GROUP_NUMBER DISK_NUMBER TOTAL_MB FREE_MB MOUNT_S HEADER_STATU STATE
------------ ----------- ---------- ---------- ------- ------------ --------
1 0 1191626 314236 CACHED MEMBER NORMAL
1 1 1191626 314230 CACHED MEMBER NORMAL
1 2 1191626 314232 CACHED MEMBER NORMAL
1 3 1191602 314229 CACHED MEMBER NORMAL
1 4 1191626 314210 CACHED MEMBER NORMAL
1 5 1191626 314218 CACHED MEMBER NORMAL
1 6 1191626 314218 CACHED MEMBER NORMAL
1 7 1191602 314223 CACHED MEMBER NORMAL
1 8 1191626 314216 CACHED MEMBER NORMAL
1 9 1191626 314202 CACHED MEMBER NORMAL
1 10 1191626 314230 CACHED MEMBER NORMAL
[code]....
I've checked and follow the status for a few days more, it still running with EST_MINUTES=0. I think it was hang and never end.During the next few weeks, I've manually restarted the rebalance process by modifying its power to 6, 8, 10
SQL> alter diskgroup DATA1 rebalance power 10;
After every restart, the diskgroup rebalance again but has the same issue, never end.Here is the content in the alert log
NOTE: starting rebalance of group 1/0x9d2529a0 (DATA1) at power 5
Starting background process ARB0
Fri Sep 16 12:01:11 2011
ARB0 started with pid=15, OS id=21066
Starting background process ARB1
Starting background process ARB2
Fri Sep 16 12:01:11 2011
[code]....
As you can see, the rebalance process started 3 days ago, hanging till now
SQL> select group_number, operation, state, power, est_minutes from v$asm_operation;
GROUP_NUMBER OPERA STAT POWER EST_MINUTES
------------ ----- ---- ---------- -----------
1 REBAL RUN 5 0
SQL & PL/SQL :: Performing TYPE 2 Operation Using Merge In 10g
Mar 11, 2010I have a type to table where only few columns are SCD 2. Like address, city, zip.I wrote a code using cursor. But now the requirement is to replace the cursor .how to perform type 2 using Merge in oracle 10g or any better way to perform it without using cursor. Look the code which i have attached with cursor.
CREATE OR REPLACE PROCEDURE Proc_cp_jci_contract_dim_hist
IS
l_fun_ret_value NUMBER;
v_src_id NUMBER;
rec_jci_contract_dim jci_contract_dim%ROWTYPE;
rec_jci_contract_dim_hist jci_contract_dim_hist%ROWTYPE;
err_row VARCHAR2(4000);
ind NUMBER(1) := 1;
[code]....
SQL & PL/SQL :: ORA-29283 - Invalid File Operation?
Dec 29, 2012We are using the below procedure to generate the files on the DB server.
SQL>
SQL> create or replace procedure write_to_file(p_dir varchar2,
2 p_file varchar2,
3 p_mode varchar2,
4 p_clob clob) as
5 l_output utl_file.file_type;
6 l_amt number default 32767;
7 l_offset number default 1;
8 l_length number default dbms_lob.getlength(p_clob);
9 new_clob clob;
10 BEGIN
11
12 l_output := utl_file.fopen(p_dir, p_file, p_mode, 32767);
13 while (l_offset < l_length) loop
14 new_clob:= SUBSTR(p_clob,l_offset,l_amt);
15 utl_file.put_line(l_output, new_clob,true);
16 l_offset := l_offset + dbms_lob.getlength(new_clob);
17 end loop;
18 utl_file.new_line(l_output);
19 utl_file.fclose(l_output);
20 end write_to_file;
21 /
Procedure created
SQL>
This works fine we call this from PL/SQL developer tool. However, when this procedure is called from Java JDBC, it is giving error
java.sql.SQLException: ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at "SYS.OBP_UPGRADE_UTIL", line 2816
ORA-06512: at "SYS.OBP_UPGRADE_UTIL", line 3029
ORA-06512: at line 1
DB schema used for both are same (SYS) Verified the directory on the DB server This Procedure called from package with AUTHID CURRENT_USER option.