PL/SQL :: Oracle Date Function

Jun 16, 2013

I have a table with date column (16/06/1996 15:03:59) as value in the displayed format. As I have tried with the below query format I could not able to retrieve the

records. select * from <table> where DATE_INSERT=SYSDATE;

Retrieve the rows for the tables ? 

View 2 Replies


ADVERTISEMENT

PL/SQL :: Function Date

Aug 30, 2012

CREATE OR REPLACE FUNCTION my_to_date (value_in IN VARCHAR2)
RETURN DATE
IS
TYPE mask_t IS TABLE OF VARCHAR2 (30)INDEX BY BINARY_INTEGER;
fmts mask_t;

[Code]....

When i try using this method i am getting error...

SQL> select my_to_date(to_char(07/11/1987),'yyyy') from dual;
select my_to_date(to_char(07/11/1987),'yyyy') from dual
*

ERROR at line 1:ORA-06553: PLS-306: wrong number or types of arguments in call to 'MY_TO_DATE'

what is the error here...What is the solution for this function.....how to execute this function

When i am using ananymous block i am getting this answer:

SQL> declare
2 v date := '07-nov-1987';
3 u date;
4 begin

[Code]...

PL/SQL procedure successfully completed.

View 7 Replies View Related

SQL & PL/SQL :: Date Function?

Nov 22, 2011

i have one table chequedetails in this 4 rows are there that is

date name sno
1-10-2011 b 1
10-10-2011 c 2
25-10-2011 d 3
11-11-2011 e 4

these records are there in my table in this i selected between date it wil display o/p like this

date name sno
1-10-2011 b 1
11-11-2011 e 2

my query
select * from chequedetails where date between '1-10-2011' and '11-11-2011'

View 22 Replies View Related

SQL & PL/SQL :: Date Comparisons In A Function

Dec 8, 2010

I am passing a date parameter to a function. Based on parameter dates comparison the function return me few names.Unfortunately, i am not getting all the names i should.

I suspect this is because the date comparisons are not working properly. Perhaps becausee of the date format. Both paramter and cursor date values come from column type date.Some values are in this format 8/11/2004 and few others in this 7/11/2005 4:40:00 PM..

find_names(p.prospect_id, pro.proposal_id, pro.start_date, pro.stop_date )
CREATE OR REPLACE FUNCTION find_names(p_prospect_id VARCHAR2
,p_proposal_id VARCHAR2
,p_start_dt date
,p_stop_dt date)
RETURN VARCHAR2 IS
v_result VARCHAR2(1000);
v_name VARCHAR2(200);
[code]...

View 8 Replies View Related

SQL & PL/SQL :: Fetch System Date Using Function?

Oct 18, 2010

create or replace function get_date(nn date)
return date as age date;

BEGIN
age := sysdate;
END;

i want to return that value in front end using callable statament..

View 2 Replies View Related

PL/SQL :: Unable To Round Date Function?

Feb 21, 2013

cant i round the date like this? or only sysdate can be rounded or if the date is stored in a variable can only be rounded

select round('21-feb-1999','mon') from dual;

View 2 Replies View Related

SQL & PL/SQL :: Date Error In Execute Immediate Query Through Function?

Jun 25, 2012

I've used a date in execute immediate query in function, but at the time passing the date as input parameter and getting the result i'm getting following error.

CREATE TABLE MIS.TEMP
(
ID NUMBER(8),
STOCKDATE DATE,
STOCKQTY NUMBER(10,2)
);

[code]....

SQL> select getstockqty(1,to_date('31/03/2012','dd/mm/yyyy')) from dual;
select getstockqty(1,to_date('31/03/2012','dd/mm/yyyy')) from dual
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "MIS.GETSTOCKQTY", line 11

View 12 Replies View Related

SQL & PL/SQL :: Function Invoked By Passing Date Parameter

Oct 18, 2011

get rid of the below error

CREATE OR REPLACE FUNCTION fn (
p_salesrep_id IN jtf_rs_salesreps.salesrep_id%TYPE,
p_org_id IN jtf_rs_salesreps.org_id%TYPE,
p_cnf_date IN emcint_ord_headers_all.creation_date%TYPE

[Code]....

Invoking Functions

select fn(-3,293,'1/1/1952'), resource_id from jtf_rs_salesreps
where rownum < 5
ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-382: expression is of wrong type

View 8 Replies View Related

SQL & PL/SQL :: Function To Find Max Last Logout Date For Each Agent Daily

Jun 3, 2010

I have to create a function. I need to find the max last logout date for each agent daily. For example, if an agent logged in for the first time at 9:00 and he logged out at 12:00 and he logged in again in 14:00 and he logged out at 15:00 the time I need my report to show is 15:00. How can I do that?In order to make it easiest for you to understand I am sending you this query:

select
a.login as login2,
To_Char(max(s.endtime), 'dd/MM/yyyy, HH24:MI:SS') as lastLogout
from cti.agent a
inner join cti.agentsessionlog s
on s.agentid = a.agentid and To_Char(s.endtime) != '31-DEC-99 11.59.59.000000 PM'
group by a.login;

This query returns the agent's login and the agent's last logout time. It works fine if I enter a date between but I cannot do that. If a use this query as it is and I try to export a report for 31/5 it shows as lastlogout the logout for 01/06 or 2/06. Is there a function I can use? I have a deadline.

View 7 Replies View Related

SQL & PL/SQL :: Display Date Ranges In One Column As Separate Date Periods (start And End Date) In Two?

Jun 1, 2010

I'm trying to work out how to take a table like this:

IDDate
12502-Feb-07
12516-Mar-07
12523-May-07
12524-May-07
12525-May-07
33302-Jan-09
33303-Jan-09
33304-Jan-09
33317-Mar-09

And display the data like this:

IDPeriodPeriod StartPeriod End
125102-Feb-0702-Feb-07
125216-Mar-0716-Mar-07
125323-May-0725-May-07
333102-Jan-0904-Jan-09
333217-Mar-0917-Mar-09

As you can see, it's split the entries into date ranges. If there is a 'lone' date, the 'period start' and the 'period end' are the same date.

View 13 Replies View Related

Forms :: Expiry Date To Automatically Show A Date 15 Years After Initial Date

Apr 12, 2010

I have a two date fields in my form; valid from date and expiry date.

Currently my valid from date has an inital value property of $$date$$ which automaitcally brings up todays date.

I need my expiry date to automatically show a date 15 years after this date?

View 8 Replies View Related

PL/SQL :: Difference Between Real Hijri Date With Oracle Hijri Date

Sep 18, 2012

In my application I show the Date in Hijri using the NLS setting. but the issue that "i face a deference between the real hijri date with oracle hijri date"

Assume that the real hijri date on 18-SEP-2012 was 02/11/1433 in hijri but in oracle was 03/11/1433

when I execute the following query it shows me "1433/11/03", but the actual Hijri date is "1433/11/02"

select to_char(to_date('20120918','YYYYMMDD'),'YYYY/MM/DD', 'NLS_calendar='''||'ARABIC hijrah') FROM DUAL;

View 8 Replies View Related

PL/SQL :: Lag Function In Oracle

Jul 15, 2012

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?

View 13 Replies View Related

SQL & PL/SQL :: MIN Function In Oracle Sql

Sep 11, 2012

I have written a query which basically retrieves id and created date. IF i put MAX function it is returning id which have max created date. But if i use min function this query is not providing id with min created date,its not returning any rows.

SELECT To_char(OSH.osh_id),
OSH.osh_created
FROM tn_order_status_history osh,
tn_order_status_type ost,
tn_orderline_product op
[code]..........

View 4 Replies View Related

Oracle 9i To 10g To Char Function?

Feb 11, 2009

Well the company i work for has just recently upgraded from Oracle 9i to 10g. We are having various problems with the migration, and with certain code breaking. My question regards this piece of code;

[code]

to_number(to_char('a_date','YYYY'))

[code]

There are various statements like the one above scattered throughout a query i am trying to fix. When run, the query returns an "invalid number ora-01722" error, which i know is caused by the above code.

if this method of converting from date, to character was discontinued from 9i to 10g?it just seems strange as there are a lot of statements like the one above and they must have worked at some point, but now i cant even get one to work on its own.

View 3 Replies View Related

VPD Function With Loop - Oracle 11g

Jul 5, 2012

I have the following function:

CREATE OR REPLACE FUNCTION get_project_id(
schema_p IN VARCHAR2,
table_p IN VARCHAR2)
RETURN VARCHAR2
IS
projects_pred VARCHAR2 (400);
[code].......

I am trying to get the projects a user has from the works_on table (user_id, project_id). The user_id is retrieved from the context projects_ctx. I am getting the error Function created with compilations errors.

View 6 Replies View Related

SQL & PL/SQL :: Can Use Htp Package In Oracle Function

May 14, 2010

can we use htp package in function?

something like

if event_id = p_event_id THEN
htp.tableRowOpen;
htp.tableData(htf.bold('Evnt'), 'RIGHT', cattributes=>'CLASS=bptext');
htp.tableRowClose;
end if;

View 4 Replies View Related

SQL & PL/SQL :: Oracle Analytical Function

Jul 11, 2011

Here is the test-table creation script:

CREATE TABLE TEST1 (AGG_DATE DATE, COL1 NUMBER(9), COL2 NUMBER(9), COL3 NUMBER(9));
Here is the test-data population script:
insert into TEST1 (AGG_DATE, COL1, COL2, COL3)
values (to_date('01-01-2012', 'dd-mm-yyyy'), 1, 1, 1);
[code]....

The problem is when I wrote an analytical query, it is giving the BEGIN_DATE and END_DATE by taking all the partition values together and so instead of the values above, it is creating an answer as follows:

Wrong Dataset

BEGIN_DATEEND_DATECOL1COL2COL3
1/1/20121/8/2012111
1/1/20121/8/2012111
1/1/20121/8/2012111
1/1/20121/8/2012111
1/4/20121/11/2012222
1/4/20121/11/2012222
[code]....

Only the last row is correct. What can I do to get the right answer as I know am falling short? Here is my current query:

SELECT MIN(AGG_DATE) OVER(PARTITION BY COL1, COL2, COL3) BEGIN_DATE,
MAX(AGG_DATE) OVER(PARTITION BY COL1, COL2, COL3) END_DATE,
COL1,
COL2,
COL3
FROM TEST1;

View 6 Replies View Related

SQL & PL/SQL :: Oracle 11g Function Error

Apr 16, 2013

I'm using the following code for a function which is working fine in Oracle 9i, but throwing error like ORA-06512 (Numeric value error) in Oracle 11g.

CREATE OR REPLACE FUNCTION Decrypt(toconvert VARCHAR2) RETURN VARCHAR2 IS
str_original VARCHAR2(30);
str_new VARCHAR2(30);
sub_str VARCHAR2(1);
j NUMBER;
[code]......

View 17 Replies View Related

SQL & PL/SQL :: Oracle Function Working In DB And Not In Another?

Jun 16, 2010

I have an oracle package that i am using to search for a string in a blob entry. I compiled the package and the package body in one environment, it had no errors, when i execute, i get my results.I went ahead and created the same package and function in another environment and it fails by giving me the below error

ORA-06503: PL/SQL: Function returned without value ORA-06512: at "SYSTEM.IMPACTUS_PCODE", line 158 for sysadm

I have used this on other environments often and have never had an issue.

View 7 Replies View Related

Oracle 6i Refresh Function

May 18, 2009

when i press the save button in my application the record will be saved but the value in my "display item" wont be refreshed. when i minimize the form designer and maximize it again, the "display item" is refreshed.

so i am searching for a "refresh" function

SYNCHRONIZE;
redisplay;

these functions doesn't work.

View 3 Replies View Related

Using WITH Statement In Oracle Procedure Or Function

May 24, 2011

Is it possible to use WITH statement in an Oracle procedure or function?

I've had problems compiling a procedure with "WITH" statement.

View 1 Replies View Related

SQL & PL/SQL :: Function Very Slow After Oracle Upgrade

Oct 11, 2012

We have a function that is called in various other PL/SQL packages, and performance has always been very good. On 29th Sept we upgraded our db to 10.2.0.5.0 and since then, a package that calls the function has gone from ~4mins, to ~2.5hrs to run.

In PL/SQL Developer, a simple select that calls the function has gone from ~0.5secs to retrieve the first 100 rows, to ~12secs. I ran a profile of the main package, which highlighted the where the bottleneck was (a fetch from an explicit cursor). Running an explain plan on the cursor SQL doesn't really show up anything untoward.

However, I found that if I subtly changed the cursor SQL, (so that it did the same thing, but was written differently), it fixed the performance problems.

where ade_start_date between cpDate-cpDays and cpDate-1
/*and ade_start_date < cpDate
and ade_start_date >= (cpDate-cpDays)*/

From this, we thought that there may have been a bad cached execution plan which the change of code forced a recalculation of. However, about 2 hours later, the changed code ran slowly again. So a further subtle change was made, which fixed the issue again. Until this morning, when it was running slowly again.

This feels like it is CBO/stats related potentially, but is out of my area of knowledge unfortunately. We have our DBA investigating this, but there may be things I can test to narrow down the possibilities in the meantime.

View 5 Replies View Related

SQL & PL/SQL :: Does Oracle 10g Supports Regexp_Like Function

Jan 6, 2009

I am using REGEXP_LIKE function, but it is giving me error like this.

SQL>select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

5 rows selected.

SQL>select regexp_like(testcol,'^ab[cd]ef$') from test;
select regexp_like(testcol,'^ab[cd]ef$') from test
*
ERROR at line 1:
ORA-00904: "REGEXP_LIKE": invalid identifier

But in Oracle documentation it is given under heading of 10g. Does Oracle 10g supports this function?

View 11 Replies View Related

SQL & PL/SQL :: Finding Dependency Of Function In Oracle

Nov 26, 2012

Is it possible to find the dependency of a Function whether it is used somewhere in the package or procedure or some other functions?

Suppose I have function named get_valid_loc , Now my question is how to find out in which place this function has been used.

View 13 Replies View Related

SQL & PL/SQL :: Left Function Equivalent In Oracle 9i

Dec 22, 2006

Is there is an equivalent to the 'LEFT' Function of SQLServer in Oracle9i?

View 10 Replies View Related

SQL & PL/SQL :: How To Create Identity Function In Oracle

Apr 15, 2010

i have a project that needs to have a identity number in the table.this is the table:

create global temporary table BOOK_INFO
( row_id number(5),
BOOK_TITLE char(20),
BOOK_AUTHOR char(15)
) on commit preserve rows;

i dont know how can i generate row_id, in sybase i can only set the datatype of row_id in IDENTITY and it will automatically generates number and as the transaction ended the row_id will automatically back to 1.

in oracle i tried to create sequence but it didnt back to 1 when the transaction/procedure ended.. Like for example if the procedure ended and the last row_id inserted in the table is 300 the next run of the procedure will start in 301. but i need to start in 1 in every run of the procedure.

Also, i tried to set the variable v_row_id to 0 and it increment by 1 as it insert in the table, but the problem is there are times that in 1 book_title there is more than 1 author. so the v_row_id of the book_title with more than 1 author is all the same.Here is my query:

insert into BOOK_INFO
(select v_row_id, BOOK_TITLE, BOOK_AUTHOR from LIBRARY);

The result is:

ROW_ID BOOK_TITLE BOOK_AUTHOR
1 BOOK_1 AUTHOR_1
1 BOOK_1 AUTHOR_2
1 BOOK_1 AUTHOR_3
1 BOOK_1 AUTHOR_4
2 BOOK_2 AUTHOR_5

View 8 Replies View Related

SQL & PL/SQL :: Output Of Oracle Function Dump

Oct 18, 2010

what the following output of the oracle function "dump" means?

query: select dump(:new.messaging_state) from dual

output:

Typ=2 Len=1: 193

I need this debug output because I want to know which value the program wants to write in the columns messaging_state.

The update fails because of a parent Key violation.

View 2 Replies View Related

Any Built-in Function (HOST) In Oracle

May 30, 2012

saw in one procedure using host function in below manner.

v_status:= host(v_cmd);

But i searched for the host function it is not available.

My question is --Is there any pre defined built-in function "host" in oracle 10g.

View 1 Replies View Related

Oracle Function To Retrieve Records Randomly?

Jun 19, 2013

I need function to pick the record from DB random manner.

For example
, say we have 500 records and input value to the function is 5 means, it should display the records randomly between 1 to 5

If user inputted value is 10 means, it should display one record between 1 to 10.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved