SQL & PL/SQL :: Unable To Override Nls_date_format For A Query?
Feb 2, 2011
We have nld_date_format set to dd-mon-rr at database level
While querying I am using to_date('08-SEP-81','dd-MON-yy') which is not giving me the desired results However using the date directly or with to_date('08-SEP-81','dd-MON-rr') is giving correct results
I was expecting using to_date('08-SEP-81','dd-MON-yy') will override the database or instance settings and will return the results.
SQL> set pages 100
SQL> select ename,hiredate from emp;
ENAME HIREDATE
---------- ---------
SMITH 17-DEC-80
ALLEN 20-FEB-81
WARD 22-FEB-81
[code]....
14 rows selected.
SQL> select ename from emp where hiredate>to_date('08-SEP-81','dd-MON-yy');
no rows selected
SQL> select ename from emp where hiredate>'08-SEP-81';
ENAME
----------
MARTIN
SCOTT
KING
[code]....
7 rows selected.
SQL> select value from nls_instance_parameters where parameter='NLS_DATE_FORMAT';
VALUE
SQL> select value from nls_database_parameters where parameter='NLS_DATE_FORMAT';
VALUE
[code]....
7 rows selected.
View 4 Replies
ADVERTISEMENT
Jun 4, 2013
--Create a parent object
CREATE OR REPLACE TYPE PARENTOBJ AS OBJECT
(
FIRST_NAME VARCHAR2(50),
static function GETNAME return varchar2,
MEMBER PROCEDURE getoutput
)
NOT FINAL;
/
----Create the body for parent object type
CREATE OR REPLACE TYPE BODY PARENTOBJ AS
static function GETNAME return VARCHAR2 IS
BEGIN
RETURN 'PARENTOBJ';
END ;
MEMBER PROCEDURE getoutput iS
BEGIN
DBMS_OUTPUT.PUT_LINE( 'Hello world from '||PARENTOBJ.getname );
END ;
END;
/
--Create a child object
CREATE OR REPLACE TYPE CHILDOBJ UNDER PARENTOBJ
(
static function GETNAME return VARCHAR2,
--override the parent method
OVERRIDING MEMBER PROCEDURE getoutput
)
FINAL;
/
----Create the body for child object type
CREATE OR REPLACE TYPE BODY CHILDOBJ AS
static function GETNAME return VARCHAR2 IS
BEGIN
RETURN 'childobj';
END ;
--override the parent method
OVERRIDING MEMBER PROCEDURE getoutput IS
BEGIN
DBMS_OUTPUT.PUT_LINE( 'Hello world from '||CHILDOBJ.getname );
END;
END;
/
The above types are created with no issues. However, when I am trying to inherit the child method and override its parent, I am getting the follwoing error:-
--NOW INHERIT THE PARENT INTO CHILD, AND OVERRIDE THE PARENT METHOD BY CALLING THE CHILD METHOD
DECLARE
OBJ PARENTOBJ;
BEGIN
OBJ := NEW CHILDOBJ(NULL);
DBMS_OUTPUT.PUT_LINE('obj method getoutput() = ' || GETOUTPUT());
END;
/
ORA-06550: line 6, column 56:
PLS-00201: identifier 'GETOUTPUT' must be declared
ORA-06550: line 6, column 3:
PL/SQL: Statement ignored
View 7 Replies
View Related
Feb 24, 2011
I'm issuing an alter session setting NLS_DATE_FORMAT in a C batch process right after the connection takes place. The format I specify is YYYYMMDDHH24MISS, this is the format used all over the process. In my development environment this works perfectly, but I've had problems in other environments.
CASE A development environment: The process works fine, $NLS_LANG and $NLS_DATE_FORMAT environment variables are not set.
CASE B Test envirnonment 1: The process failed.$NLS_LANG=American_America.WE8ISO8859P1 $NLS_DATE_FORMAT environment variable is not set.For some reason the $NLS_LANG variable seems to have more weight than the alter session command.. why? The process works fine after setting $NLS_DATE_FORMAT to the desired format.
CASE C Test envirnonment 2: The process failed. $NLS_LANG and $NLS_DATE_FORMAT environment variables are not set. Can't get it to work here. why?
View 1 Replies
View Related
Oct 16, 2012
I have created a form based on a table and I want to override the processing which inserts/updates the data in the database. I have figured out how to create a new process but I am having trouble referencing the form field data.
If I have a filed call P40_USERNAME how do I reference this in the insert/update statement in the custom process.
View 1 Replies
View Related
Oct 29, 2010
I have installed oracle 10g express edition, and created 2 users
1. DBA
2. HR -- is the normal application developement user.
When i issue the following query from DBA user, it works.
select * from dba_tab_cols; -- works fine for DBA user
select * from dba_tab_cols; -- doesnot work for HR user - invalid table name
When i try to issue the previage from DBA user like,
grant select on DBA_TAB_COLS to hr; -- DBA user says like, insufficient privileges.
View 6 Replies
View Related
May 1, 2011
User complaints fetching data from table A is timing-out ( as per application standards). I decided to collect latest statistics(dbms_stats) on index used by query which is running for more than 12 hours.not.It is partition table and got close to 10 millions records.
1) I checked dba_waiters - no rows
2) I checked v$locked_object/v$locks - no rows
3) I'm unable to run explain-plan for any criteria on that table
4) I'm unable to open table data-tab in Toad/ Sql developer.
5) I'm unable to query a single partition; no error but it keeps on running.
View 8 Replies
View Related
May 13, 2010
I am getting the same error on a form I am setting up under version 10.1.2 Form Builder. But when I press Shift-F1 I get a Getting Started with Internet Explorer popup. I am running the form under OAS. I have a parent/child relationship between 2 tables that I query on the form.
A copy of the fmb file is attached to access it.
View 11 Replies
View Related
Jan 24, 2012
When i run the below mentioned query(similar query)on TOAD. It will run without any errors and i can see "PL/SQL Procedure successfully completed" in the task bar.....
However i am unable to see the output for the query.
print the the query output on TOAD ....
**** Sample Query Starts *****
DECLARE
i PLS_INTEGER;
BEGIN
SELECT NVL(i, 93)
INTO i
FROM DUAL;
--print i;
--dbms_output.put_line('i1: ' || i);
END;
**** Sample Query Ends*****
**** Actual Query Starts *****
-----------------------------------------------------------------
DECLARE
UnxDate number(6);
MyResult number(6);
Todaysdate date := TO_DATE('17-01-2012 00:00:00','dd-mm-yyyy hh24:mi:ss');
BEGIN
SELECT ROUND (Todaysdate - TO_DATE('01-jan-1970','dd-mon-yyyy') ) INTO UnxDate FROM dual;
[code]......
View 9 Replies
View Related
Dec 8, 2005
I have inherited a query that union alls 2 select statements, I added a further field to one of the select statements ( a date field). However I need to add another dummy field to the 2nd select statement so the union query marries up I have tried to do this by simply adding a
select
'date_on'
to add a field called date on populated by 'date_on' (the name of the column in the first query)
however when I run the union query i get the error Ora-01790 expression must have same datatype as corresponding expression.
View 6 Replies
View Related
Dec 5, 2012
I have a dynamic query stored in a function that returns a customized SQL statement depending on the environment it is running in. I would like to create a Materialized View that uses this dynamic query.
View 1 Replies
View Related
Apr 26, 2013
I have data in a table and another in XML file,I used SQL query to retrive the data placed on the table, and link this query with XML query that retrieves the data stored in the xml file. The data stored in the table and xml file sharing a key field, but the xml contents are less than what in the table.I want to show only the data shared between the two queries, how can I do that?
e.g.:
Table emp:
e_id | e_name | e_sal
023 | John | 6000
143 | Tom | 9000
876 | Chi | 4000
987 | Alen | 7800
XML File
<e_id>
143
876
So, I want the output to be:
e_id | e_name | e_sal | e_fee
143 | Tom | 9000 | 300
876 | Chi | 4000 | 100
View 2 Replies
View Related
Aug 7, 2009
I am looking to simplify the below query,
DELETE FROM A WHERE A1 IN (SELECT ID FROM B WHERE BID=0) OR A2 IN (SELECT ID FROM B WHERE BID=0)
Since both the inner queries are same,I want to extract out to a local variable and then use it.
Say,
Array var = SELECT ID FROM B WHERE BID=0;
And then ,
DELETE FROM A WHERE A1 IN (var) OR A2 IN (var)
How to do this using SQLPLUS?
View 8 Replies
View Related
Jun 19, 2012
I have the following four tables with the following structures Table A
ColA1 ColA2 ColA3 ColA4 ColA5 AA 100 CC DD EE
Table B
ColB1 ColB2 ColB3 ColB4 ColB5 AA 100 40452 A9 CDE
when these two tables were joined like the following:
Select colA1,ColA2, ColA3, ColA4, ColB3,ColB4, ColB5 from table A Left outer join (select ColB3, ColB4, ColB5 from table B where colB3 = (select max(colB3) from table B ) on (colA1 = colB1 and ColA2 = col B2)
Now i have to join the next table C with table B
Table C structure is
ColD1 ColD2 ColD3 Desc1 A9 Executive Desc1 A7 Engineer
I have the common column such as ColD2 and colB4 to get the Col D3
how do i join the existing query + join between table b and table c?
View 4 Replies
View Related
Jul 17, 2011
how to achieve F11(Query mode) and Execute Query in Oracle Forms?
View 1 Replies
View Related
Apr 6, 2010
I have a query that is pulling back more rows when I use the dblink than when I hit the linked database directly.
For example:
select
x,y,z
from
mytable@dblink
returns 788,324 rows
while
select
x,y,z
from
mytable
returns 712,102 rows
It's the exact same query, with the only difference being the dblink. It's not pulling the data into a cursor or array, it's a simple, straightforward query on a remote database.
View 10 Replies
View Related
Mar 10, 2012
Is there a technique to getting a Top-N query to work as a sub-select in a larger query -or- is there another way to generate Top-N like results that works as a sub-select?
Background:
We have a large query that is being used to build an export from a legacy HR system to a new one. Amount the data needed in the export is the employees primary phone number.
The legacy HR system allows multiple phone numbers to be stored in a simple table structure:
SELECT emp_id, phone_type, phone_number
FROM employee_phones
emp_idphone_typephone_number
------- --------------- -------------------
46021CELL2222222222
46021HOME1111111111
46021WORK3333333333
The new HR system does allow for multiple phone numbers, however they need a primary phone number identified and stored with the employee master information. (Subsequent phone numbers get stored in alternate table.)
From a business perspective, we have decided that if they have a HOME phone in the legacy system that should be the primary in the new system, if no HOME phone, then WORK, if no WORK then CELL.
That can be represented as:
SELECT *
FROM employee_people_phones
WHERE emp_id = '46021'
ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')
emp_idphone_typephone_number
------- --------------- -------------------
46021HOME1111111111
46021WORK2222222222
46021CELL3333333333
Or similarly with Top N concept:
SELECT *
FROM (SELECT *
FROM employee_people_phones
WHERE emp_id = '46021'
ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')) results
WHERE ROWNUM = 1
emp_idphone_typephone_number
------- --------------- -------------------
46021HOME1111111111
Or really what I want in my export:
SELECT phone_number
FROM (SELECT phone_number
FROM employee_people_phones
WHERE emp_id = '46021'
ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')) results
WHERE ROWNUM = 1
phone_number
-------------------
1111111111
However, when the Top-N query is added as a sub-select in a larger query using the employee id from the larger query (WHERE emp_id = export.emp_id), it fails saying that �export.emp_id� is not a valid id.
(SELECT phone_number
FROM (SELECT phone_number
FROM employee_people_phones
WHERE emp_id = export.emp_id
ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')) results
WHERE ROWNUM = 1)
1.Any way around this? Is it possible to put a Top-N (with a WHERE clause using data from the main query) in a sub-select?
2.Any alternatives (other than Top-N) to delivering a ROWNUM=1 result with a �custom� ORDER BY statement?
Other Notes: Yes, we know we could do two queries in the data conversion first deliver the bulk data to the target table, and then update with the phone numbers. However, for multiple reasons, that is less than desirable.
View 3 Replies
View Related
Sep 19, 2010
I am having a Select query(below Query1) and I want to use one column(sum(col4)) from this Select query to be displayed in another Select query(Query 2). how to display this.
Query 1 :-
select a.col1,a.col2,b.col3,sum(b.col4)
from tab a, tab b
where a.key1=b.key1 and a.key2=b.key2
group by a.col1,a.col2,b.col3
Query 2 :-
select a.col1,a.col2,b.col3,sum(b.col6)
from tab a, tab b
where a.key1=b.key1 and a.key2=b.key2
group by a.col1,a.col2,b.col3,b.col5
View 4 Replies
View Related
Oct 31, 2010
I'm attempting to upgrade a 9.2.0.7 to 10.2.0.? (haven't run the patch set yet so it's at 10.2.0.1) I'm not sure if the listener is correct to proceed.
The platform is Windows x323 (server 2003)
>>I'm not an Oracle DBA, I stumble thru it in administering PTC's Windchill vault..
I am trying to run the Oracle DBUA and the documentation for the Upgrade Assistant instructs me to install in an OraHome that is separate from the existing. Done.
The Upgrading Guide then States:
"Run the Oracle Net Configuration Assistant
If you are upgrading from Oracle9i and a listener was not configured in the Oracle9i repository, run Oracle Net Configuration Assistant to configure the listening protocol address and service information for the new Oracle Database 10g database, including a listener.ora file. A version 10 listener is required for an Oracle Database 10g database. Previous versions of the listener are not supported for use with an Oracle Database 10g database. However, it is possible to use a version 10 listener with previous versions of the Oracle database."
Oracle Net Services Administration Guide, �10 states:
"Note:
A version 10 listener is required for an Oracle Database 10g database. Previous versions of the listener are not supported for use with an Oracle Database 10g database. However, it is possible to use a version 10 listener with previous versions of the Oracle database."
Here's what I've got: The Installation using the Universal installer for 10g ran w/o complaint, reported success. The installation type was Advanced>Custom per the PTC Windchill documentation. After the initial installation, the Oracle Net Configuration Assistant did not start automatically, as the documentation stated it would. (The documentation from PTC on Oracle is skimpy at best)
I launched the Network Config Assistant from the start menu in the new OraHome10g. When I tried to create the listener, TCP on port 1521, it told me this was already in use by another program. I could configure it, but until the conflict was resolved....
When I open the Net Manager, the path in the header bar of the frame is to OraHome92\network\Admin....YET, when I run HELP>About in the Net Manager it reports "Oracle Net Manager; Version 10.2.0.0.0 Production"
I had stopped all Oracle services when I ran the UI to install 10g. I have also since removed the service for the 9i TNSListener from windoze register (and restarted). Right now, there is no that shows running in Services.
??Am I actually OK to go ahead w/ the patch set, then run the Pre-Upgrade Information Tool then the Oracle DBUA?
Do I actually have a 10g listener setup?
But, the listener.ora, tnsnames.ora etc are all in the OraHom92/network/admin folder and nothing but the samples show in the OraHome10g/network/admin. Will the DBUA migrate these or should I manually transplant them into the OraHome10g directory?
View 1 Replies
View Related
Apr 2, 2010
i used UTL_SMTP to send email. it works fine and send email. but when i used UTL_MAIL, its throwing below error
Error at line 1
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at "SYS.UTL_SMTP", line 20
ORA-06512: at "SYS.UTL_SMTP", line 96
ORA-06512: at "SYS.UTL_SMTP", line 138
ORA-06512: at "SYS.UTL_MAIL", line 395
ORA-06512: at "SYS.UTL_MAIL", line 608
ORA-06512: at line 2;
i ran below
begin
utl_mail.send('abc.def@xyz.com',
'abc.def@xyz.com','Testing UTL_MAIL Option','blah blah blah');
end;
if utl_smtp is working, means SMTP server is configured properly. then why am i not able to get email using UTL_MAIL ?
utl_mail hav public grant.
also i smtp_out_server has value in v$parameter :
select * from v$parameter where name = 'smtp_out_server';
View 7 Replies
View Related
Jan 15, 2013
i am not able to mount my database created today. I uninstalled the RDBS software and all peoplesoft database content to start from a fresh again. But now i am not able to mount my DB. I am working on 11g. The error is -
SQL> startup
ORACLE instance started.
Total System Global Area 744910848 bytes
Fixed Size 1374696 bytes
Variable Size 226493976 bytes
Database Buffers 511705088 bytes
Redo Buffers 5337088 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Process ID: 2192
Session ID: 5 Serial number: 1
SQL>
View 11 Replies
View Related
Mar 12, 2013
I hv no problem connecting oracle at localhost. but when connecting using another pc on the same LAN, the sqldeveloper returned an error saying
IO Error: The Network Adapter could not establish the connection on the linux where oracle installed, iptables is disable, but firewalld is enabled, so I did a
'firewall-cmd permanent zone=home --add-port=1521/tcp'
yet the remote pc still prompt the same IO error. what should I do to have oracle accept connection from the LAN and WAN ( I hv setup virtual server on router)
View 12 Replies
View Related
Mar 18, 2009
Whenever I try to create this trigger, it asks for values for :OLD and :NEW for some reason, and then when I attempt to put those in, it just says
Unable to bind ":OLD"
Unable to bind ":NEW"
CREATE OR REPLACE TRIGGER audit_grade_change
BEFORE UPDATE OF final_letter_grade ON enrollments
FOR EACH ROW
BEGIN
UPDATE grade_change_history SET old_final_grade=:old.final_letter_grade, new_final_grade=:new.final_letter_grade;
end;
am I using :OLD and :NEW incorrectly?
View 1 Replies
View Related
Mar 1, 2011
I am unable to generate a fk in the following script..
DROP TABLE PD1 ;
CREATE TABLE PD1 (APP_CODE NUMBER,APP_DTLS VARCHAR2(10),
CONSTRAINT APP_CODE_PK PRIMARY KEY (APP_CODE)
);
DROP TABLE PD2 ;
CREATE TABLE PD2 (LAND_CODE NUMBER,PVT_LAND VARCHAR2(10),
GOV_LAND VARCHAR2(10),OWND_LAND VARCHAR2(10),LEASED_LAND VARCHAR2(10),
CONSTRAINT LAND_CODE_PK PRIMARY KEY (LAND_CODE)--,
-- CONSTRAINT APP_CODE_FK FOREIGN KEY (APP_CODE) REFERENCES PD1(APP_CODE)
[code]....
View 2 Replies
View Related
Mar 22, 2011
why am i getting this?
SQL> SELECT TABLE_NAME,owner from dba_TABLES where TABLE_NAME in ('BSNL_ACCOUNT_DATA','BSNL_SERVICE_DATA','BSNL_PKG_DETAILS');
TABLE_NAME OWNER
------------------------------ ------------------------------
BSNL_ACCOUNT_DATA BPREAD
BSNL_SERVICE_DATA BPREAD
but the table exists
SQL> select count(*) from BPREAD.BSNL_PKG_DETAILS;
COUNT(*)
----------
3704
SQL> select TABLE_NAME,owner from dba_TABLES where TABLE_NAME='BSNL_PKG_DETAILS';
no rows selected
why is it showing no rows even if the table exists
View 3 Replies
View Related
Nov 15, 2010
I have 25G free in tablespaces. but while inserting the table i am getting error.
ORA-01688: unable to extend table CIRCMANAGER.TBK_NEW_ORDER_SUMMARY partition HA20044Q by 512 in tablespace ts_neworder
We need to change any parameter?
View 4 Replies
View Related
Jul 5, 2012
I facing the below issue while enable the audit on table.
SQL> audit delete on test;
audit delete on test
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01400: cannot insert NULL into ("SYS"."AUDIT_DDL"."DICT_OBJ_TYPE")
ORA-06512: at line 2
===
Related Information
===
SQL> select version from v$instance;
VERSION
-----------------
11.2.0.2.0
SQL> show parameter audit;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /orasw/product/11.2.0
/rdbms/audit
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string DB
SQL> SELECT TABLESPACE_NAME,owner from dba_tables where table_name='AUD$';
TABLESPACE_NAME OWNER
------------------------------ ------------------------------
AUDIT_DATA SYS
View 4 Replies
View Related
Dec 20, 2011
Am trying to extract data from oracle onto flatfile(.txt), am using UTL_FILE but, NULLs in oracle tables are getting converted into space and if i try loading into table it is getting loaded as space,
View 1 Replies
View Related
Nov 13, 2006
I am unable to select only record for 07Nov06. if i use between i get records e.g. :07 November ,2006-0941
Select
TO_CHAR(session_START_time,'dd month,yyyy-hh24mi') LOG_IN_TIME,
TO_CHAR(session_END_time,'dd month,yyyy-hh24mi') LOG_OUT_TIME
from SESSION_LOG
where SESSION_LOG.SESSION_START_TIME
between '06Nov06' AND '07Nov06';
View 2 Replies
View Related
Jun 21, 2012
When we tried to query a data from the sql server we are getting the below generic error message i
SQL> select * from all_users@testing
2 /
select * from all_users@testlink1
*
ERROR at line 1:
ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: preceding 2 lines from TESTLINK1
below are the details setup / configuration done by us:
os :windows 2008 server.
database "oracle standard edition
Command Line input /output:
C:\Users\aorenstein>C:\oracle\product\11.2.0\db_1\bin\dg4odbc
Oracle Corporation --- WEDNESDAY JUN 20 2012 10:20:54.308
Heterogeneous Agent Release 11.2.0.1.0 - 64bit Production Built with
Oracle Database Gateway for ODBC
[code]....
View 1 Replies
View Related
Aug 4, 2010
In oracle 11i, unable to insert rows using insert statement. unable to retrieve data using select statement.
View 5 Replies
View Related
