SQL & PL/SQL :: Getting MONTHS_BETWEEN Function Clarification?
Apr 18, 2013
Need clarification about months_between function ..
C:>sqlplus/nolog
SQL*Plus: Release 10.2.0.3.0 - Production on Thu Apr 18 13:20:43 2013
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
SQL> conn scott@orcl
Enter password:
Connected.
SQL> select months_between('28-feb-2013','28-jan-2013') from dual;
[code]....
when date is 29 or 30 jan, then result is less than 1, but when date is 28 or 31 jan then result is 1, why? if 28 is 1 then why 29 or 30 is less than 1?
I need a clarification on oracle WITH CLAUSE. When the query is build using oracle WITH clause and under lying table data gets changed, does oracle update data in temprorary table as well.I have the below query
WITH channel_summary AS (SELECT channels.channel_desc, SUM(amount_sold) AS channel_total FROM sales, channels WHERE sales.channel_id = channels.channel_id GROUP BY channels.channel_desc)
when i fire the query on it SELECT channel_desc, channel_total FROM channel_summary WHERE channel_total > (SELECT SUM(channel_total) * 1/3 FROM channel_summary);
this will give always same result or it will change the records as and when the tables goes under changes in case of DML operation.
In regard to the Oracle 4GB limit, what exactly is this limit? Is this the total size of the datafiles of the user defined tablespaces (excluding SYSTEM and UNDO) so if, for example, we have a 2.8Gb datafile, can we only add another 1.2Gb datafile.
or is it the total size of extents/segments in the user defined tablespace i.e. if we have a 2.8Gb datafile but the database data comes to only 1.7Gb, can we add another tablespace with a new 2.8Gb datafile to allow for another 1.7Gb schema.
As I got APEX_PLSQL_JOB is used JUST to run a process only ONCE in the background.So, in case I want to send an email by the end of each month or a reminder email I have to use dbms_scheduler.create_job.
I am in need of a clarification regarding the file size growth in Physical Standby Database.Say we have 150 GB size of LIVE DB and we have created the same as in STDBY(150 GB).Will there be a growth in STDBY DB datafiles with respect to the LIVE DBOR only the logs are applied currently to the STDBY and the changes will only be reflecting when there is a switchover or failure scenario??
i have a query where i am using the max function to find the most recent record. What i want to do is use that query as part of an insert statement into a different table, however, i don't want to insert the column that i used the max function on. Is there anyway to use the max function without having the column it is being used on showing in the results?
I am creating an stored function which has to do some inserts in the meanwhile, and return after all the work has done, an UDT (2 or 3 columns of NUMBER datatype).
With this scenario I have an problem. The DML operations are not supported by and "SELECT * FROM Table(MyProc(args))". I have to use this "SELECT * FROM Table(MyProc(args))" because I need to pass the stored function results directly to an dataset.
Using a Stored Procedure it gives no errors, but the arguments must be passed like OUT params, and it is not what I want.
My question is: Is there any other way to get a result (UDT) of an Stored Function (that makes Inserts) into a DataSet?
I've been tasked to parse tags from a string that look like the following:
{Date + XXX}
where XXX represents a numeric value. I have to replace this, including the brace characters with
SYSDATE + XXX
which will ultimately calculate SYSDATE plus the number of days suggested by XXX. The problem is that I am trying to use regexp_replace to achieve this goal but since XXX is completely arbitrary, I cannot search for it as a fixed value. So, ultimately, I would like to use a regular expression that ignores the numeric part of my search and only replaces the starting brace, the "Date + " part and the ending brace, leaving the numeric portion intact. I was trying to do something like the following
I'm posting below test case in which I'm not able to understand output for LAST_VALUE function. I'm expecting maximum value for the salary in a department. Because I'm partitioning by department and ordering a partition as assending so being last value it should give me maximum value within a partition i.e. department in this case.
There is an 'emp' table with a column name as 'mgr' with datatype 'number'. following is the detailed description of the table:
SQL> desc emp;
Name Null? Type ----------------------------------------- -------- --------------------------- EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2)
Now when I run the 'select mgr from emp e;' query the output which I get is:
7902 7698 7698 7839 7698 7839 7839 7566
7698 7788 7698 7566 7782
Note: One value in between here is null, the required to me is that I want to print a character value 'President' in place of null .
I have two tables where I have to find the record for Max value of the column sap_pkid for every sap_id as in given table create script. This script is giving correct value but looking for a better way so that when data increses it doesn't hit the performance.
way where max can be avoided or a more tuned query .
I am creating a function to sum five numbers (less 1). Is it possible to have an array of numbers in an SQL function, and how would this be implemented?
Here is the screenshot of my output (I cannot embed links until 5 posts!): flic.kr/p/eaSHBP
CREATE OR REPLACE FUNCTION sumfivenumbers ( n1 NUMBER, n2 NUMBER, n3 NUMBER, n4 NUMBER, n5 NUMBER) RETURN NUMBER IS Sumnums NUMBER; BEGIN SELECT SUM(n1+n2+n3+n4+n5-1) INTO Sumnums FROM DUAL; DBMS_OUTPUT.PUT_LINE(Sumnums); RETURN 1; END sumfivenumbers; / SELECT sumfivenumbers(5,5,5,5,5) AS "Five Numbers less 1" FROM DUAL;
I am trying to create a function that when called will add the salary and commission a certain way to return an employee's annual salary.Here's my code
create or replace function Get_Annual_Comp (Sal in number, Commission in number) return number as [code]...
When I run the query, I get the proper rows return; however, my function does no calculation. If I input random numbers, I get the proper value returned. What I want is for my function to return the salary and commission of the employee specified in my select's where clause to be calculated as an annual salary.
Any way to write a function to parse through a clob and extract certain values to insert into a table. I've written the following and it compiles but it doesn't work.
create or replace function all_fields (type_field VARchar2, domain_field VARchar2) return VARchar2 as typefield VARchar2(100) :=type_field; domainfield VARchar2(100) :=domain_field;
I am using lag function to display values like below:
order details date starttime ----------------- -------- -------------- main order 1 07/10/12 06:00am line 1 07/10/12 06:21am line 2 07/10/12 06:31am main order 2 07/11/12 07:00am line 1 07/11/12 07:01am line 2 07/11/12 07:02am
the data displays correctly when i use lag function except that the line 1 details are never getting displayed ie first line under every order does not get displayed? is using lag function in this case correct?
explain the flow what exactly is the below function do in detail.
FUNCTION Get_RmtUsr_rec_FUNC (p_MsgType IN VARCHAR2) RETURN RemoteUser_Rec_Type;FUNCTION Get_RmtUsr_rec_FUNC ( p_MsgType IN VARCHAR2) RETURN RemoteUser_Rec_Type IS
