SQL & PL/SQL :: Sysdate Procedure If Statement

Dec 2, 2011

I'm working on a project and I can't figure out the procedure I will need to use. I've got a sysdate field in my "Calls" table which generates a call date + time, however I need to insert a severity level of the call after a certain amount of time,

e.g. after 1 hour level turns from level 4 to level 3 and so forth until reaching level 1 after x amount of time. I know I'd need to put in an else if statement within this procedure I just can't work out how to do it with the sysdate field that has also been created.

A bit of info about the table, table name = calls, column 1 = date_time and column 2 is called severity_level

View 32 Replies


ADVERTISEMENT

Writing IF Statement Using SYSDATE?

Apr 22, 2011

I have an employee table. I Have to get the data of all employees in such a way that. If today I run the Query,then i have to get the data of all employees working between december 1st of previous year(current year-1 i.e., december 1st 2010.) till today(april 21st). If the query run date is in the month of december(example december 15th) then the query should get the data from december 1st of current year(december 1st 2011) to December 15th. I wrote the if statement some how its not working. I want to make use of this If or Case Statement as the start date of the employee_timestamp. Is this possible here or not.

select * from employee
where
employee.employee_timestamp > (select to_date(to_char(concat('12-01-', extract(YEAR FROM sysdate)-1)),'MM/DD/YYYY') as Startdate From DUAL)
and
employee.employee_timestamp < (SELECT SYSDATE FROM DUAL).

[code]....

View 1 Replies View Related

SQL & PL/SQL :: Can A Procedure Be Used In Update Statement

Aug 2, 2011

PROCEDURE split_name (
full_name IN VARCHAR2,
name1 OUT VARCHAR2,
name2 OUT VARCHAR2,
name3 OUT VARCHAR2,
name4 OUT VARCHAR2,
name5 OUT VARCHAR2
)

the above is the procedure declaration. i want to use this function in updating the records in a table. this procedure is working fine in forms and it is splitting the names perfectly and i dont know how to use this in update statement as it is not returning anything like functions do.

View 4 Replies View Related

SQL & PL/SQL :: Cannot Use Oracle Procedure In Statement

Mar 11, 2010

Why we can not use the Oracle Procedure in an SQL Statement?

View 12 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 :: Single Select Statement Instead Of Procedure?

Oct 2, 2010

presenting the case as follows:There are two dates with time components like

26-sep-2010 13:00 and 29-sep-2010 19:00

In between these dates hours between 20.00 to 05.00 (night hours)need to be considered and In these night hours less than three hours can be ignored and between 3 to 9 hours should be treated as 1. Need the number of one's in between the dates.

Procedure, cursor solution is not needed and need single select statement.

View 5 Replies View Related

SQL & PL/SQL :: How To Make Procedure From Select Statement

Feb 8, 2011

i do have below query in oracle db just joining two views,

select docview.firstname as "First Name", docview.lastname as "Last Name", docview.mrn as "MRN", docview.physician as "Physician", docview.surgicalcong "Surgical Consent Missing", docview.admissionnotemissing "Admin Assessment Missing",
(case (docview.doctype) when 135 then 'Doctor Chart Checklist' else 'DS Chart Checklist' end) as doctypename,
docview.hpmissing "History & Physical Missing", docview.nursingassessment "Nursing Admission/Assessment",

[Code] ........


how can i make a procedure with below two steps,

1) load the result of above query to some temp table / or even a fixed table (which ever easier)
2) list all data from the temp table / fixed table created in first step

so in the final expecting result is

execute myprocnamehere -- will result same as running above query

View 3 Replies View Related

SQL & PL/SQL :: Cursor In DML Statement Inside A Procedure

Sep 12, 2011

I have a DML Statement inside a procedure and i use a cursor variable to get the values checked as below . I have attached my procedure not completely but the declaration part and the DML statement part.

The issue is my procedure is not inserting the records at all. It selects the values and then inserts accoringly but its not selecting because of the cursor reference R_LOC.LOCATION_GID.

when i hard code the value in the DML statemnt for the R_LOC.LOCATION_GID, the rows are inserted as expected. So i guess the way the procedure executes the value is not correct.

Modifying my select part which uses cursor variable R_LOC.LOCATION_GID under Insert statement.

select d.servprov_gid, d.depot_gid, replace(d.appointment_time,'':'') appmt_time, d.'||v_day_to_use||' DayUsed
from tesco_fresh_templates t, tesco_fresh_templates_d d, location_refnum r
where t.set_id= d.set_id
and d.depot_gid=r.location_gid
AND D.SERVPROV_GID=''R_LOC.LOCATION_GID''
and r.location_refnum_qual_gid=''TESCO.IVS SCHEDULING''
and (r.location_refnum_value=''YES'' or r.location_refnum_value=''Y'')
and t.default_set=''Y''

View 21 Replies View Related

PL/SQL :: Differences Between Calling SQL Statement / Procedure?

Apr 22, 2013

I am planing to write the web-application which use Oracle DB 11g.I would like to understand what are the differences (specially, performance issues, steps of execution, optimizer possibilities) between calling SQL statements and PL/SQL procedures/functions. Which approach is more appropriate, and why?

Examples:
a) WebApp->Call("select * from employees where department_id = ?", 10) ;

b) WebApp->Call("? := mypackage.get_emp(?)", refCursor, 10);

create package mypackage is
function get_emp(dep_id in number) return sys_refcursor is
   begin
    open cur for select * from employees where department_id = dep_id;   
end;
end;==========================================
Requirements: High-concurrency, 100+ db sessions, DB will not be used for business-logic.

View 10 Replies View Related

Store Procedure In View With Select Statement

Aug 31, 2012

Need a trigger in view with select statement that means

CREATE OR REPLACE VIEW TEST_VIEW AS SELECT * FROM TEST_TABLE;
CREATE OR REPLACE TRIGGER TEST_VIEW_TRG1
INSTEAD OF DELETE ON TEST_VIEW DECLARE
BEGIN
Dbms_Output.Put_Line('STATEMENT TRIGGER.');
END;

i wanted to use select statement instead of delete.How can i get that

View 1 Replies View Related

SQL & PL/SQL :: Procedure Containing Select Statement To Fill Page Items

Oct 24, 2011

I would like to fill items :P200_A and :P200_B and so on with the result of a SELECT which depends on the different values of many select lists.

E.G. :P200_list_alpha with the list of values
STATIC:less than 10;less,equal than 10;equal,above 10;above,indifferent;indiff

:P200_list_beta with the list of values
STATIC:active;active,passiv;passiv,excluded;excluded

How do I write the select statement ? I think it has to be executed in an anonymous PLSQL Procedure (after submit).

What is a convenient way to write the select statement ? I could imagine to use lots of IF , ELSIF, ELSE statements and in each branch (here 12 ) the whole/complet SELECT statement is written.

How to solve this problem in an elegant way ? In my opinion the CASE statement could be useful, but how to use it in the WHERE clause with this nested conditions ?

View 1 Replies View Related

SQL & PL/SQL :: Merge Statement In Procedure - Internal Error Code

Feb 9, 2011

I have a merge statement in one procedure , this job runs on daily . It is running successfully since Jan-19-2010 and giving the below error now.

ERROR at line 1:
ORA-00600: internal error code, arguments: [kcbgcur_9], [4224596], [1],
[4294967250], [2], [], [], []

It is giving error in the beginning of the MERGE statement.

View 11 Replies View Related

Client Tools :: Error / ORA-00900 / Invalid SQL Statement After Executing Procedure

Jan 3, 2011

CREATE OR REPLACE PROCEDURE test
IS
CURSOR cusers IS SELECT user_name, user_date FROM users;
uname users.user_name%TYPE;
udate users.user_date%TYPE;
BEGIN
OPEN cusers;

[code].....

When I try to execute this procedure I get following error:

ORA-00900: invalid SQL statement

Compilation of procedure is successful.

SQL code for creating the USERS table is here:

CREATE TABLE "USERS"
("USER_ID" NUMBER(10,0) NOT NULL ENABLE,
"USER_NAME" VARCHAR2(50) NOT NULL ENABLE,
"USER_EMAIL" VARCHAR2(50) NOT NULL ENABLE,
"USER_PASS" VARCHAR2(50) NOT NULL ENABLE,
"USER_DATE" DATE NOT NULL ENABLE,
CONSTRAINT "USERS_PK" PRIMARY KEY ("USER_ID") ENABLE
)

View 3 Replies View Related

SQL & PL/SQL :: Sysdate In Where Clause

Apr 22, 2010

I am not strong in SQL but can write easy SQL for data extraction but seem to not understand how to correctly use sysdate in a where clause.

Case: I have to create an alert that will email my IT dept once a person is terminated in Oracle HR. The alert will only run once a day with all terminations specified in the alert. Setting up the alert is not the issue, but rather the SQL code I want to use.

The alert will run everyday at CoB 17:30. Now, in my query, how do i specify that the results should be only for the current day? The problem however, is that I also retrieve Person Type which should show as Ex-Employee, but this is only shown the day after the actual termination has been done, because the employee is still active on the date of termination.

View 13 Replies View Related

SQL & PL/SQL :: Default Value Is Sysdate

Nov 4, 2011

During the Table Creation if it possible to Use the SYSDATE is Default Value for a Column.

View 3 Replies View Related

SQL & PL/SQL :: Difference Between Sysdate And Current_date

Apr 15, 2010

What is the difference between the values of sysdate and current_date ? On querying the database I got the results as sysdate = the date and time for the database server location and current_date = my local system date

If however, i change my local system date - it still shows the correct date.Myunderstanding was that current_date uses the dbtimezone.But..

SQL> select sysdate,current_date from dual;
SYSDATE CURRENT_DATE
-------------------- --------------------
15-APR-2010 06:06:14 15-APR-2010 18:36:15
SQL> select dbtimezone from dual;
DBTIME
------
+00:00
SQL>

View 9 Replies View Related

SQL & PL/SQL :: Sysdate Between Two Date Columns?

Feb 26, 2013

I need to fetch Data from a table X where current date(Sysdate) lies between the datecolumns Active_From and Active_To.

Active_From and Active_To are date columns.

Create table X(
ID number,
Active_From date,
Active_To date
)

Insert into X values (1, sysdate-3,sysdate + 3);
Insert into X values (1, sysdate-2,sysdate + 3);
Insert into X values (1, sysdate-3,sysdate +3);

View 5 Replies View Related

PL/SQL :: Sysdate Produces 00:00 Time

Apr 11, 2013

I have a stored procedure that stores a record containing a date field.

The syntax is

insert into audit_log values (lv_sequence, sysdate, REC_TYPE, p_pln_id, OPER_UPDATE, log_message);

This works except that the date stored in the record has a time of 00:00. This does not work well for an audit log.

How can I store a date that includes a time?

An interesting fact is...I used this same command in SQL Developer to store a record and the date stored did have a time???

View 7 Replies View Related

SQL & PL/SQL :: Adding Number In Sysdate

Apr 26, 2013

Can we add 10000 days in SYSDATE using + operator ?

Quote: This I can test it but I don't have database access now that's why I am asking this question

View 5 Replies View Related

SQL & PL/SQL :: Use Trunc Function Along With SYSDATE

Mar 12, 2011

The reason to use the the trunc function along with the SYSDATE.

E.g. :- TRUNC(SYSDATE).

Why we need to set the current date into midnight? Where we can use this method?

View 3 Replies View Related

SQL & PL/SQL :: Taking Months Off Sysdate?

Feb 25, 2010

I have a table called transaction_dw and I need to select all records that have an account balance that has been below 0 in the past 6 months. initial query I tried was:

select account_balance, timestamp
from transaction_dw
where account_balance < 0
and timestamp between sysdate and sysdate - 6;

but this is only taking 6 days off the sysdate rather than months, how I can get it to take off 6 months?

View 3 Replies View Related

SQL & PL/SQL :: Select Sysdate From Dual

Aug 30, 2012

when 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

View 11 Replies View Related

PL/SQL :: Select Time From Sysdate

Sep 20, 2013

I have to concatenate a date(not sysdate) with the system time and store it in a column having datatype as DATE... So I tried like this....

SELECT TO_CHAR (C_DATE, 'DD-MON-YYYY')      || ' '    || TO_CHAR (SYSDATE, 'HH:MI:SS PM')    FROM   DUAL; 

But while inserting the output of the above query in my table it throws error like this... ORA-01830: date format picture ends before converting entire input string Is there any possibility to achieve this.

View 6 Replies View Related

SQL & PL/SQL :: Returning Sysdate Even Run On Sunday

Apr 4, 2010

I have following code. It is always returning sysdate even run on Sunday.

declare
vDate date;
begin
if to_char(sysdate,'Day')='Sunday' THEN
vDate := sysdate-3;

[Code]...

if there is something missing or wrong.

View 2 Replies View Related

Sysdate Showing Wrong Timezone?

May 24, 2012

I have one issue My server is in france and it is in french timezone but when I query for sysdate it returns US time.

In '/etc/sysconfig/clock/'
Zone= europe/paris
UTC= true

echo $TZ variable is returning nothing.

sysdate = us time
systimestamp= us time
current_timestamp = french time
current_date = french time
dbtimezone= europe/warsove, sessiontimezone=+2.00( which is also europe timezone offset)
tz_offset(dbtimezone)=+2.00, tz_offset(sessiontimezone)= +2.00 i.e europe
os timezone= europe/paris.

This command "./emctl config agent getTZ" is also returning timezone as europe/paris

Also in "emd.properties" file "agentTZRegion" parameter is set to europe/paris

Oracle version= 11.2.0

Now I don't understand why this sysdate and systimestamp is returning "US time zone" while everything else is returning french time zone.

View 9 Replies View Related

Compare A Date In One Of Tables To Sysdate?

Mar 5, 2012

i wanted to compare a date in one of my tables to sysdate. I have a table reservation and a field in it is Date Reserved From, i wanted to compare this to sysdate and returned the results

View 2 Replies View Related

Changing Values Of Sysdate In Session Only?

Feb 19, 2007

Is there a way of changing the values of sysdate in a session only?

not the formate the actual values

is if the date was 31/12/2003 and i wanted to change it to 31/05/2003

could this be done

View 4 Replies View Related

Forms :: Set Initial Value As Sysdate Plus 12 Month?

Apr 10, 2010

how can I set initial value as sysdate plus 12 month????

View 3 Replies View Related

Forms :: Cannot Display Sysdate In Oracle

Mar 8, 2013

when i open my oracle form on my live application server to display sysdate its does not show me.but when i open this same form on my test application server its shows my current sysdate time.

View 1 Replies View Related

Where Value Of Systimestamp / Sysdate Comes From And Dbms-scheduler

Mar 27, 2013

I saw bunch of other posts but I could find the post that exactly explaining about where the value returned as systimestamp / sysdate comes from or impacted Here is my situation I have an access to this db (let me call db A) and when I access it, I get following result. I don’t have full access to this db so I cannot experiment a lot here.

SYSTIMESTAMP CURRENT_TIMESTAMP LOCALTIMESTAMP DBTIMEZONE SESSIONTIMEZONE
--------------------------------------------------------- --------------------------------------------------------- ------------------------------------------------ --------------------- ----------------------------
27-MAR-13 02.31.55.041411 AM +00:00 26-MAR-13 07.31.55.041416 PM -07:00 26-MAR-13 07.31.55.041416 PM +00:00 -07:00

I’m in PST timezone.I have my db which I have full access as well as its host.I can make result like db A on my db if I started up db and its listener while TZ environment variable equal to UTC.Now I saw in other post that someone was trying to retrieve systimestamp value in a job executed via dbms_scheduler.run_job.

So I did that in two ways. 1 with use_current_session = true and 2 is false for the same.On my db, results are the same (both returns time in UTC) but on db A, I got UTC time when use_current_session = true and PST when use_current_session = false.

So questions are:
What could be the difference in setup between my db and db A?
Is there a query, logfile, or anything I should check to find out what can be the difference?

I tried to find the cause with my db and I could see the same result as db A which is to see UTC time if use_current_session = true and PST time if use_current_session = false by bringing up the db listener after I set TZ environment variable equal to PST8PDT. However this causes systimestamp from sqlplus session become also a PST time.

The reason I’m playing around with the setup and checking systimestamp value is because we are facing the situation where everywhere except pl/sql job submitted by enterprise scheduler service is pointing wrong timezone (PST instead of UTC)

View 0 Replies View Related







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