Forms :: Calculation In Timing With Varchar2 Datatype

Jul 16, 2011

I'm facing a problem where I create a form and I want to feed time in "HH:MI" format, Firstly I choose the "Date" datatype, but during the feeding of time i.e "00:30", means if I want to feed only mints, then it doesn't accept, at the other hand if I feed "01:30" means mints with hr then it accepts.

To get rid of this problem I changed datatype "Date" to "Varchar2".

I create 3 columns on my form "TTL_WORKING_TIME" - "TTL_RUN_TIME" = "BRK_DWN_TIME". If I get any value in "BRK_DWN_TIME" column thn it has to be distribute in 3 or 4 reason of B/D, means if I get 00:30 mints brkdwn thn 00:15 mints for "reason1" and 00:15 mints for "reason2".

How I make calculation where I use "Varchar2" datatype.

View 1 Replies


ADVERTISEMENT

SQL & PL/SQL :: Passing IN Parameter Of Datatype Varchar2

Feb 16, 2010

I have a stored proc which takes IN parameter of datatype varchar2.When I am trying to run the proc it is throwing error that "input buffer too small".The datatype assigned to IN parameter is of varchar2(200) but actually the length of the parameter passed is around 500 characters.the way to increase the length of Input parameter to 500 characters??

View 16 Replies View Related

SQL & PL/SQL :: How Does Operator Works For Varchar2 Datatype Columns

Nov 22, 2011

I have a table called Student and a column as name now if i write a Query

select * from student where name < 'BRIAN D'

How does the comparison will be done.

View 3 Replies View Related

SQL & PL/SQL :: Change Column Datatype From CLOB To Varchar2

Jun 22, 2012

I have to change the datatype of a column from CLOB to varchar2, without changing the order of the columns. The table has no data.
I could find any other way other than dropping the CLOB columns and then adding new columns with varchar2 datatype. But this changes the order of the columns in the table.

View 4 Replies View Related

SQL & PL/SQL :: Write A Function That Receive As Parameter A Datatype Varchar2?

Jul 18, 2012

here is the situation, I have to write a function that receive as parameter a datatype varchar2 and validate if the letter is less than "A" or greater than "Z", it must print do not correct, or correct depending on the situation, no problem so far, the only problem I've got is: If the symbol is ";" is valid, but i do not know how to write it, It does not depend on the PL/SQl languaje but depends on the logic itself.

DECLARE
v_nombre varchar2(20):='john;%';
vv varchar2(1);
begin
for i in 1..length(v_nombre) loop

[code]...

View 2 Replies View Related

PL/SQL :: Automatic Rounding Off Number Datatype While Converted To VARCHAR2

May 29, 2013

I am facing issue related to Number data while it is being converted to Varchar2, it is automatically getting rounded off after 32 decimal place.My database version is "Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production".

1) create table test18 ( col1 varchar2(10), val Number);
create table succeeded.

2) insert into test18 values ('First', -347026.6408499652467480885711448714129679); -- After decimal 34 digits
1 rows inserted

insert into test18 values ('Second', -347026.64084996524674808857114487141296); -- After decimal 32 digits
1 rows inserted

3) select * from test18;

COL1 VAL
---------- ----------------------
First -347026.6408499652467480885711448714129679
Second -347026.64084996524674808857114487141296

4) As per the requirement, all the columns would need to be concatenated as a single string along with comma delimiter

select col1 || ',' || val as record_string
from test18;

RECORD_STRING
---------------------------------------------------
First,-347026.64084996524674808857114487141297
Second,-347026.64084996524674808857114487141296

"First" string got rounded off to 97 (last 2 digits) instead of 9679 but for "Second" record it holds the actual value.Only thing which I could figure out while the number is getting type casted to String, it is getting rounded off to 32 decimal place.throw off some light on it and provide the solution how the original record can be kept intact without rounding off.

Expected Output_
RECORD_STRING
---------------------------------------------------
First,-347026.6408499652467480885711448714129679
Second,-347026.64084996524674808857114487141296

View 10 Replies View Related

Table Partitioning - Range On Datatype Number (21, 7) And Varchar2

Nov 15, 2013

Database Version : DB : Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionOS : HP-UX nduhi18 B.11.31 U ia64 1022072414 unlimited-user licenseAPP : SAP - ERP I have to RANGE partition on UPDATED_ON  or PROFILE  either one table which is having below

structure :   Name                Null?    Type
-------------------- -------- --------------------------------
MANDT                NOT NULL VARCHAR2(9) MR_ID                NOT NULL VARCHAR2(60) PROFILE              NOT NULL VARCHAR2(54) REGISTER_ID          NOT NULL VARCHAR2(30) INTERVAL_DATE        NOT NULL VARCHAR2(24) AGGR_CONSUMPTION     NOT NULL NUMBER(21,6) MDM_VERS_NO          NOT NULL VARCHAR2(9) MDP_UPDATE_DATE      NOT NULL VARCHAR2(24) MDP_UPDATE_TIME      NOT NULL VARCHAR2(18) NMI_CONFIG           NOT NULL VARCHAR2(120) NMI_CONFIG_FLAG      NOT NULL VARCHAR2(3) MDM_DATA_STRM_ID     NOT NULL VARCHAR2(6) NSRD                 NOT NULL VARCHAR2

[Code]....

 As per my knowledge, RANGE is better suited for DATE or NUMBER. and INTERVAL partition is possible on DATE or NUMBEr . Column PROFILEIts is of VARCHAR2 datatype. I know still I can partition as Oracle internally convert varchar2 to number while inserting data. But INTERVAL is not possible.  How to RANGE partition on PROFILE ? Column CREATED_ON :It is of NUMBER with decimal

View 0 Replies View Related

Forms :: Calculation When Add Numbers

Feb 5, 2011

I am developing a form where I need to add Numbers.In fact we have a bag of Cones that contain 24 cones.In normal calculation when I add numbers for example

5.24 Plus 5.24 it will give the result 10.48

I Need the appropriate method to calculate if I add these two numbers it should give the result 5.24 Plus 5.24 the result should be 12

View 7 Replies View Related

Forms :: Time Calculation

Oct 20, 2011

Currently I am working on payroll system where I have to calculate employees working hours/late coming hours/early going hours against its roster which is defined in the beginning of every month/week.

In roster form user define shift of every employee like

Code Name Shift
7 Saad Nafees A - 09:00 17:00
492 Muhammad Nasir Shahzad B - 17:00 01:00
243 Muhammad Tahir C - 01:00 09:00

Roster table structure

code varchar2
name varchar2
Shift date
Remarks varchar2

shift table structure

code varchar2
timein date
timeout date
latetime date
Hdaytime date

Oracle stores both date and time information in date data type, suppose today user change shift timings from 17:00 to 17:30 or user change timein/timeout in attendance form then oracle will store current date with user define timings.

Now this is the main problem which I am facing because whenever you calculate difference between timein and timeout or compare with its roster then output comes wrong because oracle returns total no of hours whenever you minus two dates.

View 11 Replies View Related

Forms :: Calculation Total In Application?

Mar 16, 2011

I'm getting trouble in getting the sum of all the product's prize present in the form fields of invoice form. For example, I user bought 2 products so I want to get there prize sum in the total box. I first want that I've QUANTITY, DISCOUNT and SELLING PRIZE.

If I multiply Quantity with Selling Prize then I gets the result in Product and Quantity Total Box. However, Now I want to add discount option in it. Means if user is providing 10% discount to his/her customer then Quantity * Selling Prize - Discount% becomes a result in product's solution final prize box.

Here is the pic..

Look...Now here..I've added 22 in quantity.. now if I'll insert 10% in discount then the total will comes in Amount Display Box.

I'm currently using this coding on on Prize Box..
:SP_PRODUCT_PTOTAL := NVL(:SP_PRODUCT_QUANTITY,0) * NVL(:SP_PRODUCT_PRIZE,0) * NVL(:SP_PRODUCT_DST,0) / 100;

View 19 Replies View Related

Forms :: Calculation Mode With Conditions

Sep 24, 2013

I have 2 fields f1,f2

I define f1 a summary in calculation mode

I want to sum f1 where f2 = 5

View 3 Replies View Related

Forms :: Getting Calculation Result From 2 Text Boxes?

Mar 8, 2011

I'm creating an on demand software. I have 3 text boxes on my form

1st text box will carry Product's Actual Price
2nd text Box will carry Product's Selling Price
3rd text Box will carry Profit..

3rd one is display item..this will carry "selling price - actual price" and then result into 3rd box.. that would be profit..

my query is select nvl(:PL_PRODUCT_SELL_PRICE, 0) - nvl(:PL_PRODUCT_ACT_PRICE, 0) INTO :PL_PRODUCT_PROFIT FROM SBM_PRODUCT_LIST;

how can I move Proceed? I've currently applied this trigger as POST-query on form and POST-CHANGE on :PL_PRODUCT_PROFIT

View 3 Replies View Related

Forms :: How To Display Runtime Calculation In Cursor

May 18, 2010

How To Display Runtime Calculation in cursor for example in loop i define xyx number then add + 1 in xyz and move its value to block item until loop valid but in display no value shown but if i apply message(xyz) then it also shown the answer is correct but with message style i click every time in message but i want that with some delay form automatically show the value of xyz in block field.

View 3 Replies View Related

Forms :: Days Hours Min And Seconds Calculation

Nov 5, 2010

I am able to run this query in Sql Prompt.

DEFINE Today = TO_DATE('03.12.2004:10:34:24','DD.MM.YYYY:HH24:MI:SS')

SELECT TO_CHAR(hiredate,'DD.MM.YYYY:HH24:MI:SS') "Hiredate",
TO_CHAR(&Today,'DD.MM.YYYY:HH24:MI:SS') "Today",
trunc(86400*(&Today-hiredate))-60*(trunc((86400*(&&Today-hiredate))/60)) "Sec",

[Code]...

It gives proper / desired results. I want to use it on my Form, how can it be done?

View 3 Replies View Related

VARCHAR2 (100) Changes Column To VARCHAR2 (100 Byte)

Oct 24, 2007

i am fairly new in the oracle arena, but what would cause a statement such as

ALTER TABLE TEST_TABLE MODIFY text_field1 varchar2(100) DEFAULT 'testval' NULL

to change a column's type from VARCHAR2(100) to VARCHAR2(100 byte)? i found a few mentions of the 100 byte concept online but nothing that jumped out at me.

View 2 Replies View Related

PL/SQL :: Conversion Of BLOB Datatype To CLOB Datatype

Aug 17, 2012

I would need to convert the column datatype from BLOB to CLOB. currently in the table, the BLOB column has the data. the requirement is to convert this column from BLOB to CLOB datatype.

How to convert from BLOB datatype to CLOB datatype ?

View 10 Replies View Related

SQL & PL/SQL :: Scheduling Same Job With Different Timing?

May 24, 2013

IS it possible TO schedule same JOB WITH different timings.

I need only one SCHEDULER which should run ON saturday AT 6 pm ,AND monday AT 9 pm.

CREATE TABLE dept1 AS SELECT * FROM dept;
CREATE OR REPLACE
procedure update_record is
BEGIN
insert into dept1 values(10,'xxx','xxx');
END;

[code].....

View 7 Replies View Related

PL/SQL :: Get IN Time Between Timing

Aug 28, 2012

I have the following query

  Select ei.emp_code,
  TO_CHAR(ei.intime, 'dd/mm/rr') "IN Date" ,
  --TO_CHAR(ei.intime, 'hh24:mi:ss') IN_Time,
  MIN(TO_CHAR(ei.intime, 'hh24:mi:ss')) IN_Time,
  TO_CHAR(ei.intime, 'FMDAY') IN_DAY
 
[Code]..... 

no I want to modify this query, I mean I want to get data between time 09:15 to 09:12 In time.

View 3 Replies View Related

How To Set Timing On For Every SELECT Statement

Aug 23, 2008

how to set the timing on for all SELECT statements in a procudeure...and i want that should come as a output for very SELECT statement slong with my original output.

View 6 Replies View Related

Log Shipping Arrival Timing

Apr 17, 2012

Just want to know about archive log shipping time from production to physical standby. How can i know the timing that ARCHIVE LOG takes to reach from production to standby server.

I think we can get this through ALERT log but m not sure.

View 1 Replies View Related

Server Administration :: Tracking DBMS Job Timing

Jul 20, 2010

I have a database job to ANALYZE TABLES,, it takes more than 40 hours. I want to know exactly how much time does it take..

My job has started on 19th Jul at 02:32 hrs.. What is the way to know the exact time when the job ends.

View 4 Replies View Related

SQL & PL/SQL :: Unable To Query Table - Fetching Data Timing Out?

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

Forms :: Error In Concat A Clob With Char Datatype

Nov 2, 2011

How could we concat a clob with a char datatype.

declare
a clob;
b varchar2;
c clob;
begin
c:=a||b;
end;

When execute above code in form runtime, I get error ora-32767.

"ORA-29287: invalid maximum line size Cause: An invalid maximum line size value was specified.
Action: Correct the maximum line size to be in the range [1, 32767]."

View 3 Replies View Related

Forms :: ORA-03115 / Unsupported Network Datatype Or Representation

Dec 6, 2010

We are using 10G Forms and Reports against a 8I database (DB1) which has a link into another 8i Database (DB2).

DB2 has been upgraded to 10G. The application works fine but when I try to compile the forms in form builder I get an "ORA-03115, unsupported network datatype or representation." error.

The error occurs where the code in the form references the link directly. Other code where we created views to hide the link name works fine. BTW Same code works fine in SQL Plus 10G & Toad.

I have tried changing the type of variables & different columns but that did not work.

View 1 Replies View Related

Server Administration :: DBMS_SERVER_ALERT Tablespace Full Alert Timing?

Oct 20, 2013

Tablespace usage alerts are checked every ten minutes. You can request a check every minute when you set the threshold, and confirm that this has been set:

orclz> execute DBMS_SERVER_ALERT.SET_THRESHOLD(-
> metrics_id => DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL,-
> warning_operator => DBMS_SERVER_ALERT.OPERATOR_GT,-
> warning_value => '50',-
> critical_operator => DBMS_SERVER_ALERT.OPERATOR_GT,-
> critical_value => '75',-

[code]....

orclz>but you will still have to wait up to ten minutes for the alert to be raised. know whether this frequency can be changed? And why this particular alert behaves differently from all the others? This is the bahaviour in all releases since server alerts arrived.

View 2 Replies View Related

SQL & PL/SQL :: Gratuity Calculation?

Mar 16, 2012

i have attached the .csv file for gratuity calculation.

In the given criteria you can see if number of years 1 then he can get 1 salary if years 1.5 then 2 salary and so on also we need to take care about the leap year.

View 2 Replies View Related

SQL & PL/SQL :: For Ratio Calculation?

Sep 14, 2011

,i had given the sample data below

create table ex (sno number,ename varchar2(10),job_code char(4),sal number);
insert into ex values(101,'John','Java',21000,'IT');
insert into ex values(102,'Michel','BI',25000,'IT');
insert into ex values(103,'Johny','Java',30000,'IT');

[code]...

My expected output is attached in a text file

View 12 Replies View Related

SQL & PL/SQL :: Field Name Based On Calculation

Apr 19, 2010

I want to display Week No as the heading.So, I have these:

define week_no=number
column week_col new_value week_no
select to_number(to_char(sysdate, 'ww')) week_col from dual;

I have tested it and it is working as the way I expected: select 'to display week no as column' "Week &week_no" from dual;

The output:
Week 16
----------------------------
to display week no as column ...So how do I display Week 15, Week 17 etc based on the calculation of &week_no e.g. &week_no+1?

View 19 Replies View Related

SQL & PL/SQL :: Calculation On A Time Field?

Jul 5, 2012

I have time fields that have been converted from a 12hr clock to a 24hr clock and these fields hold only the time...no date.

I need to be able to determine the difference between column A and B eg.

Column A: Column B:
11:00 14:00

I can remove the ':' by using the replace command, but as I need to run on a actual 24hr clock I am not sure how to tackle the calculation as it is not the full oracle datetime format.

View 8 Replies View Related

SQL & PL/SQL :: Cumulative Calculation In Oracle?

Feb 24, 2011

Is it possible to calculate cumulatively in oracle sql queries, i.e. using the results of the last row for calculating the values in the current row?For example I want my query to return the following:

Month col1 _ col2 _ col3
jan 1 _ 100 _ 100 * 1
feb 2 _ 200 _ 100 * 2 + 200
mar 3 _ 300 _ 400 * 3 + 300
apr 4 _ 400 _ 1500 * 4 + 400
may 5 _ 500 _ 6400 * 5 + 500

In col3 above, for feb I want to use the result returned for jan ((100 * 1)*2+200), for mar I am using the result returned for feb((100 * 2 + 200) * 3 + 300) and so on.

i.e. I want to use the previous value of column3 to derive the current value of column3. Like using the LAG function but on the analytically derived column itself.

View 16 Replies View Related







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