PL/SQL :: Validating Date Without Using Inbuilt Oracle Functions
Jan 2, 2013writing a manual date validation function without using inbuilt oracle functions.I am planning to write this function in PL/SQL.
View 17 Replies
ADVERTISEMENT
SQL & PL/SQL :: Oracle Functions Date Conversion
Jan 20, 2011the data in the column is in below format
---------------------
2/10/2009 9:28:41 PM
mm/dd/yyyy
my requirement is
--------------------
20090210
YYYYMMDD
SQL & PL/SQL :: Date Functions?
Dec 3, 2012Trying to understand how Oracle DATE works and how it differs to TO_DATE in the below manner...
SELECT DATE '2012-10-25' FROM DUAL;
SELECT TO_DATE('2012-10-25','YYYY-MM-DD') FROM DUAL;
I've had a look through the Oracle Docs but can't find much on this. Basically I want to know how safe the first option is and if it does the same as the second. Also, where does DATE get the Format String from (NLS setting?). Can it be employed to compare a literal with a date that has a TIME portion set to something other than 12:00:00?
SQL & PL/SQL :: Query Using Analytical Functions-date Range
Nov 30, 2010I have a table which has the attached data.
Sample data is here
LOGON_DATE NUMBER_OF_LOGINS
11/28/2010 02:00:001
11/28/2010 03:00:001
11/28/2010 04:00:002
11/28/2010 06:00:004
11/28/2010 07:00:002
11/28/2010 08:00:003
11/28/2010 09:00:006
[Code] ........
I am trying to do a report like this.
Date PeakUsersBetween6AMand6PM AVGUsersBetween6AMand6PM PeakUsersBetween6PMand6AM AVGUsersBetween6PMand6AM
Output should be
11/28/2010 25 11 49 27
11/29/2010...
I am using analytical function to do this, It throws an error range cannot be used for dates.
Forms :: Running Oracle Report Through 6i And Validating Between Two Dates
Feb 16, 2010I want to call a report through forms 6i and i want to send a parameter from forms6i to report 6i. in which i want to take records between two dates.
View 26 Replies View RelatedAny Inbuilt Procedure To Mask Table Columns
Aug 11, 2012i never try masking of oracle columns before, and i will want to try masking some columns in oracle database. Just to check if the columns are masked and user using sqlplus or sql developer will not be able to access the columns.
However my application is require to get the values in the back end. Is it possible for my web application to unmask the column values? does my web application requires to call any procedure(create by oracle) in order to unmask the values? I have google and found that oracle can use VPD to mask the columns.Just to check if VPD is a inbuilt procedure by oracle to perform masking? can VPD perform unmasking of values? can it be call by web application?
Application Express :: Inbuilt Log And Audit Tables For 4.1
Sep 5, 2012When we access the Administrator login then we can view various logs like the sql commands that have been recently fired,user list for a workspace,access to each application.Where are these data stored and fetched.Also could we get the inbuilt audit and log table for APEX 4.1 ?
View 2 Replies View RelatedXML DB :: Xpath Functions In Oracle
Aug 24, 2012Is there any function like fn:local-name(.) in oracle XPATH functions. I am new to Oracle & migrating procedure from SQL server to oracle where procedure taken XML script as input.
I need to convert below SQL server select statement to Oracle.
SELECT converseUID,
'Distributed',
cast(ServiceCalls.query('fn:local-name(.)') as varchar(100)),
1,
WIP.ServiceCalls.value('(TotalTime)[1]','int'),
WIP.ServiceCalls.value('(Status)[1]','varchar(100)'),
[code].......
SQL & PL/SQL :: Overriding Pre-defined Functions In Oracle
Apr 11, 2012Can we override the pre defined functions in oracle. I am able to do this in my schema. But if DB server contains more than one schema do I need to write the same procedure in all schemes.
we can call the same procedure with schema name from different schemes also . But is there any way to over ride the actual function provided by oracle (just like over riding the equals method of object class in java).
create or replace function to_number(var varchar2) return number
is
no number := var;
begin
return 99999;
end;
SQL> select to_number(' 234 ') oracle_function , dbo.to_number(234) user_function from dual;
ORACLE_FUNCTION USER_FUNCTION
--------------- -------------
234 99999
PL/SQL :: Pass Statements Using Oracle Semantic Functions In PHP?
Apr 26, 2013I am developing android application connected to Oracle database using PHP, and I use PL/SQL statements with semantic technologies provided in Oracle(specifically, SEM_MATCH, SEM_MODELS, SEM_RULEBASES or something like this).
However, it doesn't work properly...the connection between android and Oracle works well, though. Also, the query statement is implemented well in the sql developer.The whole php code is shown below :
*<?php*
function db_connect()
*{*
*$db = "(DESCRIPTION=(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 203.249.105.20)(PORT = 1521)))(CONNECT_DATA=(SID=orcl)))";*
[code]...
SQL & PL/SQL :: Validating Input Parameter
Aug 11, 2010I'm using Oracle 9i. How can i check for a string if it contains only numbers or letters? Otherwise i should return false.
View 6 Replies View RelatedSQL & PL/SQL :: Fail While Validating Email ID
Jul 3, 2012I am creating function for validating email id :
create or replace function f_is_valid_email(p_email in varchar2) return varchar2 is
begin
if REGEXP_LIKE(p_email, '^[a-zA-Z0-9._%-]+@[a-zA-Z0-9._%-]+.{1}[a-zA-Z]$') then
return 'success';
ELSE
RETURN 'Failure' ;
END IF;
end f_is_valid_email;
But this function fail when
i did
SELECT f_is_valid_email('aaa..ss@AA.com') from dual
or
SELECT f_is_valid_email('aaa.ss@AA..com') from dual
PL/SQL :: Validating XML Against A Schema Registered In DB
Oct 2, 2012I have an XML schema registered in the database (user_xml_schema table has a rows for the schema). I have another table in which XML data is stored in CLOB column.
I want to validate this XML data against the XML Schema [ SCHEMA_URL = 'MY_TEST_SCHEMA' ].
Assign Different Profile For Oracle Database Users According To Their Job Functions
Apr 10, 2011it is a good practise to assign different profile for oracle database users according to their job functions.what could be the resons for that?
View 3 Replies View RelatedPL/SQL :: Writing User Defined Function The Same Way As The Oracle Functions
Sep 26, 2012In one of the interviews , I have attended the guy asked me to write a user defined function which will take the column name and list all the values .
For example
Table Name:Employees
Column Name: Employee_Name
Employee_name
Scott
Ivgun
Jack
Shane
The query should be in this fromat
SELECT col_agg(Employee_name) emp_name from Employees;
The output should be
Emp_names
scott,ivgun,jack,shane
Forms :: 6i / Validating Text Items?
Nov 24, 2010I am working on form 6i.
i need to write validation on text fields like first name,last name,phone no,email id
i have to not allow special char.
Forms :: Validating Item Vehicle_Reg_No
Feb 5, 2011I am currently having trouble validating the item Vehicle_Reg_No..I have to ensure that it starts with a letter and ends with a number. The code below gives me the TRUCK_START but for every other scenario the form throws an unhandled exception.
DECLARE
TRUCK_START VARCHAR2(100);
TRUCK_END VARCHAR2(100);
NUM_ALERT NUMBER;
BEGIN
IF :M_ORDER_ITEMS.VEHICLE_REG_NO IS NOT NULL THEN
truck_start := substr (:M_ORDER_ITEMS.VEHICLE_REG_NO,1,1);
truck_end := substr (:M_ORDER_ITEMS.VEHICLE_REG_NO,-1,1);
truck_start:= to_number(truck_start,0);
truck_end := to_number(truck_end,0);
[code]....
PL/SQL :: Validating Connection From Batch File?
Sep 3, 2012I am trying to create a .bat file where I need to take inputs for below variables
DNAME
UNAME
PSWD
So I have done like this
SET /p DNAME="Enter the Database Name "
SET /p UName="Enter the Username "
SET /p Pswd="Enter the Password "
Now I need to validate whether I am able to establish the connection
sqlpus -s "%UName%/%Pswd%@%DNAME%"
if the above connection is valid one then perform certain action else need to echo user that Access is denied.
(ex: I am trying to say if cond is valid
stmt-1
else
echo message to user. )
Forms :: Validating Field With Data In Database?
Aug 6, 2010I currently have a field in which the user inputs a number, but before this number is allowed to be saved to the database it has to be checked to see if the number is already stored in a different field within the database. For example, if the number is not in the database the user cannot put that number into the field.
I do want it to be user input so I don't want a LOV linked to the appropriate field in the database.
Is below anyway close to the way it should be done? For some reason seq_rec.seq seems to give me 3 and doesn't seem to compare it with each of the values in the database.
DECLARE
cursor seq_curs IS SELECT SEQ FROM ZZJOB;
seq_rec seq_curs%rowtype;
none_existing_seq EXCEPTION;
invalid_seq EXCEPTION;
return_alert NUMBER;
[code]....
Just say the field in my database has numbers: 1 2 3 4 and 5 in the field that i allow the user to input data they can only enter 1 2 3 4 or 5. Hence the check needs to be made with te database field to check if the input number is already in the database.
P.S. they are separate fields for different things, the numbers just need to be in one field before they can be added in the other.
Forms :: Personalization Validating A Multi-Record Block?
Sep 5, 2012I have a problem validating a multi-record block. I need to put the validation after the user clicks save. The custom validation should run through all the records in the multi-record block. But instead of validating all records, it will only validate the record where the cursor is located before the user hits save.
View 1 Replies View RelatedJDeveloper, Java & XML :: Capture Invalid Nodes While Validating XML With Schema
Jan 6, 2011I wonder is there any way to capture all the invalid nodes in the xml when the xml is validated with an xsd (schema).
I am using java's xml validation package to perform validation (javax.xml.validation packages in java 1.5)
Forms :: Created Block For Table On Tabular Style - Validating Item?
Oct 10, 2011I created a block for EMP table on tabular style. if in first line empno entered is 1then i don't want to allow the end user to enter 1 in second line. but when-validate-item is not working because first_record and next_record is not allowed. I need to validate before saving the data into emp table and immediately after empno is entered into empno item.
View 16 Replies View RelatedSQL & PL/SQL :: Display Date Ranges In One Column As Separate Date Periods (start And End Date) In Two?
Jun 1, 2010I'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.
Forms :: Expiry Date To Automatically Show A Date 15 Years After Initial Date
Apr 12, 2010I 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?
PL/SQL :: Difference Between Real Hijri Date With Oracle Hijri Date
Sep 18, 2012In 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;
SQL & PL/SQL :: Min / Max Functions?
Jun 23, 2011I have a table that looks like this...
APPOINTMENT
APPOINTMENT PATIENT DENTIST PROCEDURE AMOUNT
NUMBER NUMBER ID NUMBER DATE BILLED PAYMENT
20000001 3001 27 33100 11-Feb-2000 405.00 405.00
20000005 3988 23 11201 05-Mar-2000 80.00 0.00
[code]...
and i used this to find which dentist had the least appointment
select min(count(dentist_ID)) as "Minimum Number of Appointments" from appointment group by dentist_ID;
and this shows..
Minimum Number of Appointments
------------------------------
2
if there's a way where I can show the dentist's ID that corresponds to this "min number of appointment" i tried doing
select dentist_ID, min(count(dentist_ID)) as "Minimum Number of Appointments" from appointment group by dentist_ID;
but it gives me this error
ERROR at line 1:
ORA-00937: not a single-group group function
SQL & PL/SQL :: Using Character Functions?
May 13, 2010I would like to use character functions (LPAD, INSTR, SUBSTR, etc) to accomplish what i feel should be rather easy. I would like to take the following character strings:
'ADAM ST #3 S_109640'
'ALBANY AVE #5 & #6 S_AL5&6'
'ALBIN RD #10A S_123318'
And make them
'ADAM ST'
'ALBANY AVE'
'ALBIN RD'
As you can see, it probably boils down to using SUBSTR, INSTR, RTRIM, and LENGTH??
SQL & PL/SQL :: Analytic Functions?
Jul 25, 2006analytic functions regarding the ORDER BY part =)
SQL> ed
Wrote file afiedt.buf
1 select *
2 from (select deptno, ename, sal
3 ,dense_rank() over (partition by deptno order by sal desc) rank
4 from emp)
[code]...
why is that i just added ename on the ORDER BY part of the DENSE_RANK and then
SQL> ed
Wrote file afiedt.buf
1 select *
2 from (select deptno, ename, sal
3 ,dense_rank() over (partition by deptno order by sal desc, ename) ran
[code]...
ADAMS and WARD we're removed from the result, why is it? did it rank it as UNIQUE per sal and ename?
SQL & PL/SQL :: Group Functions
Jul 2, 2010count the no: of emp working under each manager? and instead of manager number display the manager name
View 5 Replies View RelatedExample For Conversion Functions
Mar 27, 2012Again i getting confused with conversion function especially Explicit data type conversions. some cases oracle server automatically converts the data to the required type. This is called IMPLICIT CONVERSION. Explicit conversions are done by using the conversion functions.
Oracle Explicit Data Type Conversions are
1 TO_CHAR
2 TO_DATE
3 TO_NUMBER