PL/SQL :: Function That Returns The Check Digit Of 9-digit Number

Jan 17, 2013

I would like a pl/sql function that returns the check digit of a 9-digit number?

Look at

[UR]......

For example number 109409714 (9 digits) should do check digit. I want validation of check digit, & want to return the check digit.

View 8 Replies


ADVERTISEMENT

SQL & PL/SQL :: Function To Accept 4 Digit Number Which Should Not Be Repeated?

Aug 15, 2012

i nee a function which accepts 4 digit number and in four digit number the number should not be repeated. i want all the number in the output.

ex:1234
2367
1262(is not valid)

View 20 Replies View Related

SQL & PL/SQL :: Check Wether Digit Is Numeric Or Character

Dec 4, 2004

is there in pl/sql a function to check wether a digit is numeric or character like we have in c 'is_numeric() ' or is_digit() functions�

View 6 Replies View Related

Fix Number Of Digit After Decimal Point?

Mar 21, 2007

I have a small prob. I want an amount to be always 2 decimal places. I've used the Round function - Round(amount,2). the problem is that if the amount is only to 1 dp like 1.4. the above function will return 1.4. I want it to appear like 1.40

View 2 Replies View Related

SQL & PL/SQL :: Replacing 4 Digit Number In A Given String With The Same Number Incremented By 10000?

Jun 17, 2010

i want to replace 4 digit number in a given string with the same number incremented by 10000.

That mean in the given sting 1201 should be replace by 11201 (Icremented BY 10000).

Input String:

<query><matchAll>true</matchAll><row><columnId>1201</columnId><dataType>31</dataType><op>Like</op><val>North America - Houston</val></row><row><columnId>1212</columnId><dataType>31</dataType><op>!=</op><val>Agreement Date Mismatch</val></row><row><columnId>1212</columnId><dataType>31</dataType><op>!=</op><val>Facility Type Mismatch</val></row><row><columnId>1224</columnId><dataType>31</dataType><op>Like</op><val>y</val></row></query>

Required output :

<query><matchAll>true</matchAll><row><columnId>11201</columnId><dataType>31</dataType><op>Like</op><val>North America - Houston</val></row><row><columnId>11212</columnId><dataType>31</dataType><op>!=</op><val>Agreement Date Mismatch</val></row><row><columnId>11212</columnId><dataType>31</dataType><op>!=</op><val>Facility Type Mismatch</val></row><row><columnId>11224</columnId><dataType>31</dataType><op>Like</op><val>y</val></row></query>

View 7 Replies View Related

SQL & PL/SQL :: Extract Number And Previous Character From Where 5 Digit Number Starting

Oct 21, 2011

I have a text field and if the text field has 5 consecutive numbers then I have to extract the number and the previous character from where the 5digit number starting

For example i/p asdfasfsdS251432dasdasd o/p should be S251432

View 10 Replies View Related

Generate Random And Unique 6 Digit Number In Oracle

Sep 29, 2008

I need to generate random and unique 6 digit number in Oracle. I need to insert these numbers into a table. I tried using DBMS_RANDOM package, which generates random 6 digit numbers, but fails to generate UNIQUE numbers.

View 3 Replies View Related

PL/SQL :: Round Function Output Depend On 2nd Digit After Decimal Point

Oct 19, 2012

I have a question on Round Function

Round(###.###,1)

Will the round function output depend on 2nd digit after decimal point also or not?

View 6 Replies View Related

SQL & PL/SQL :: REGEXP Digit Extraction?

Oct 25, 2011

I am trying to write a pattern that will return multiple numbers when it finds the string "My String" in a Text such as

Text String:
My String 5 Test Need a good regExp 34 to test multiple 12334 occurrences of the same 566 search string My String 3 hopefully this time My String 7 with three of these'

Expected Output:
5
3
7

I managed to write Regular expression that returns only for the first match as shown below.

select TO_NUMBER(REGEXP_SUBSTR(REGEXP_SUBSTR (vSearchText1, ('My String' d+')), 'd+'))

View 30 Replies View Related

Forms :: Show Increasing Digit 1 To 100 In Text Field After Pressing One By One

Aug 26, 2013

the code for show increasing digit 1 to 100 in a text field after pressing a button 100 times?

View 26 Replies View Related

How To Change Decimal Symbol And Digit Grouping Symbol

Jun 19, 2011

Currently , my system using , as digit grouping symbol and . as decimal symbol in Oracle forms and report. Is there any way to reverse that to the whole system?

View 3 Replies View Related

SQL & PL/SQL :: Function Returns Searched String?

Nov 6, 2010

I just think about write a function with gives me a searched string from table.

select col1, col2 from the_table

col1 col2
--------------------------
SMS yes
Melodies no
Java build
...

And I would like to make a function or something like that where input parameter is from col1 and result is col2. For instance:

select the_function('SMS') from dual;
Result: yes

how to write it?

View 13 Replies View Related

SQL & PL/SQL :: Change Value That Function USER Returns

Nov 25, 2011

I have an existing database with several hundred tables and triggers on the tables in the schema S1. Most of the triggers are for storing audit information USER and SYSDATE in the respective columns.

We have built procedures what will accept incoming XML parameters from a BPMS application on the intranet which will have information about which table to update, which columns to update, what values to update and what the O/S user id is. The O/S user ids are mapped to oracle user ids in a specific table. Therefore when we receive the request we can easily figure out the oracle user id. The application always connects to the database using a specified user id S2.

The trouble is, the existing tables have triggers which read :NEW.userid := USER;and whenever a DML is fired from the procedure, the function USER always returns S2, since that is the user id the BPMS application connects to and therefore the audit columns do not capture the correct information as required.

The impact of changing all the existing triggers/procedures will be a mammoth task including regression testing and will certainly not be approved by the project sponsor.

override the value that USER returns? Using session contexts, etc? If so, we can possibly set it at the point of connect and have to do nothing else.

View 18 Replies View Related

SQL & PL/SQL :: Function That Returns Source Tables From A Query

Oct 16, 2013

I am interested if there maybe exists any function that would return all source tables that are present in the given sql. For example function('select 'abc' from table_1, table2') would return a list containing 'table_1' and 'table_2'.

View 14 Replies View Related

SQL & PL/SQL :: Passing Hierarchy As Input To Function That Returns XML?

Aug 3, 2012

I have an requirement to create an function which takes table or hierarchy of tables as input and returns xml output in hierarchy. Below given is the Tables hierarchy.

AAAA
----AAA
----BBB
----CCC
-------CC1
-------CC2
-------CC3

[code]....

Requirement: Initially input was table name and using table as the root node output should generate xml of all the records of child tables.But now requirement is to give the flexibility to user to select what hierarchy he needs i.e he may select AAAA, CCC and in the nodes C1,C2,C3 and C4 if he doesn't want C3 then that node should not be shown in output.

I have created Hierarchy table having 3 columns SI.No, ParentNode and ChildNode and entered the above hierarchy relation.

1. What is the best to way (design)to pass input parameter for the function.

2. How to generate hierarchy in xml using DBMS_XMLGEN

View 12 Replies View Related

PL/SQL :: Sample Function Returns Many Times Of Expected Value

Nov 30, 2012

The SAMPLE clause in the select statement works well in most cases, but we found in some instances the result is way off - between 200% to 700% discrepancy has been observed.

For example, we have thee tables with the following results:

Table1: 495,365,317 rows (20 cols, unique primary key present), SAMPLE ( 0.002018712182064212 ) returns 41,499 (about four times off - we expected about 10,000)
Table2: 3,350,864,539 rows ( 5 cols, unique primary key present), SAMPLE ( 0.00029843044634040336 ) returns 9,835 (this is good as it is close to 10,000)
Table3: 6,974,724,543 rows ( 5 cols, no unique primary key present), SAMPLE ( 0.00014337483779250091 ) returns 58,789 (about six times off - we expected about 10,000)

The tables got billions of rows, and that is why we want to do sampling. The sample percentage rate is computed to return about 10,000 rows in all three tables.On Table3, we ran the sampling three times in one occasion, and we got "58,570", "24,575" and "24,561"

I expected +/- 20% of variance, but 200% to 700% seems to be way too much.Once again, I stress that it does work well in most cases (another 3.4 billion table and numerous smaller tables we tested were well within +/- 5 percent of the target).I noted the presence of a primary key above because I read an article saying that the SAMPLE function relies on the existence of a primary key (which does not quite explain the examples above).Is this kind of spread something we should expect or is it a bug? Is the sampling rate too small for such large tables?

View 2 Replies View Related

PL/SQL :: Two Consecutive  Numbers Are Transposed Function Returns True

Jul 6, 2012

oracle version 11gr2.In the below sample data column a,b if there is Two (2) consecutive numbers are transposed in column B from A it should return string 'true'.

with t as
(
select '123456789' a ,'123476581' b from dual
union all
select '123456789' ,'123465789' from dual
union all
select '332211' ,'332121' from dual
union all
[code]....

View 3 Replies View Related

PL/SQL :: Oracle 11g Table Function Returns No Records On First Call?

Jul 13, 2012

On a Oracle 11g R2 I've a table function ( PIPELINED ) returning rows selected from a table.The first time the function is selected, in a session ( I've tried to disconnect and log in again ), it returns no rows.I've tried to log the call using DBMS_OUTPUT and from what I see the select on the table function returns no rows and no output is printed. So I presume Oracle is not calling the function.

The same function on a similar environment ( same db versions, patches and database structure ) works fine. The second environment is a production environment so it has more memory and some other settings enabled.

View 6 Replies View Related

SQL & PL/SQL :: Function That Returns A Table Type Inside A Package Body

Jul 26, 2011

CREATE OR REPLACE TYPE TEST_OBJ_TYPE IS OBJECT
(
TEST_ID NUMBER(9),
TEST_DESC VARCHAR(30)
)
/
CREATE OR REPLACE TYPE TEST_TABTYPE AS TABLE OF TEST_OBJ_TYPE
/
[code]....

I need to include the above function in a plsql package. How I can declare a object type and table type in a pks file? the syntax to include the above code in a .pks and .pkb file?

I got this code snippet online when I was looking for function that returns a table type. what exactly that Exception block does? delete the table when there is an exception, otherwise return the table type?

View 10 Replies View Related

Client Tools :: Debug Function Which Returns Table Of Records?

Apr 24, 2012

I want to debug a function which returns the table of records. When I try to add the parameters and run the debug it gives the error as

PLS-00653: aggregate/table functions are not allowed in PL/SQL scope

if there is any way to debug the function which returns table of records.

View 3 Replies View Related

PL/SQL :: Oracle Function That Returns Current Fiscal Year In Format

Mar 14, 2013

Is there an oracle function that returns the current fiscal year in format (2012/2013)?

View 9 Replies View Related

Backup & Recovery :: Exact Fetch Returns More Than Requested Number Of Row

Aug 24, 2011

I have perform a failover and encounters the following problem.

ORA-01422: exact fetch returns more than requested number of rows

initially primary db: chicago
physical standby db: boston

after failover

primary db:boston
physical standby: NA

after recreating physical standby

primary db:boston
physical standby: chicago

over at chicago (new standby)

log_archive_dest_3=E:chicago_arc
over at boston (new primary)
log_archive_dest_3=E:oston_arc

over at boston when I connect to both boston as target db and recovery catalog

RMAN> list archivelog like 'E:CHICAGO_ARC\%';

List of Archived Log Copies

Key Thrd Seq S Low Time Name
------- ---- ------- - --------- ----
252690 1 10 A 22-AUG-11 E:CHICAGO_ARC10_759842424_1.ARC

over at chicago when I connect to both chicago as target db and recovery catalog

RMAN> list archivelog like 'E:CHICAGO_ARC\%';

List of Archived Log Copies

Key Thrd Seq S Low Time Name
------- ---- ------- - --------- ----
252690 1 10 A 22-AUG-11 E:CHICAGO_ARC10_759842424_1.ARC

however at E:chicago_arc when I do a os check

C:Documents and SettingsAdministrator>dir /w E:chicago_arc

Volume in drive E is New Volume
Volume Serial Number is C874-DD6D

Directory of E:chicago_arc

[.] [..] 100_759410562_1.ARC
101_759410562_1.ARC 102_759410562_1.ARC 103_759410562_1.ARC

[Code]....

26 File(s) 9,077,989 bytes
2 Dir(s) 2,650,337,280 bytes free

there's more files than indicated by rman

also at both chicago and boston

I have the following error:

RMAN> debug on;

RMAN-03036: Debugging set to level=9, types=ALL

RMAN> report obsolete;

RMAN-06524: RMAN retention policy will be applied to the command
RMAN-06511: RMAN retention policy is set to redundancy 1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of report command at 08/24/2011 23:12:15
RMAN-06004: ORACLE error from recovery catalog database: ORA-01422: exact fetch returns more than requested number of rows

RMAN> debug off;

Debugging turned off

here's the trace files I logged

DBGRCVMAN: EXITING listBackup with exception: ORA-01422: exact fetch returns more than requested number of rows
DBGMISC: krmicomp: error 6004 signalled during compilation [23:12:15.358]
DBGMISC: ENTERED krmkmrsr [23:12:15.358]

[Code]...

How should I resolve this issue?

View 5 Replies View Related

Forms :: ORA-01422 / Exact Fetch Returns More Than Requested Number Of Rows

Aug 18, 2010

ORA-01422: exact fetch returns more than requested number of rows.

I receive this error because i tried to introduce the below coding in a post query of the block.

begin
select supplier_cd into :fin_ex_rev_head.vendor_code
from fin_ex_rev_receipts
where receipt_date
between :keyblock.receipt_date

[code].....

View 10 Replies View Related

SQL & PL/SQL :: Date Check On A Number Column

May 16, 2011

I have a column defined as Number( 8 ) which is supposed to have date values. I would like to check if all the rows in that table have valid dates. We could use to_date(coulmn_name, 'YYYYMMDD') and catch the rownums for error conditions using pl/sql. I would like to know if we could just do it using sql only and return the row numbers for those that are invalid dates?

View 5 Replies View Related

SQL & PL/SQL :: Function To Check Length And Build A String?

Dec 18, 2012

I have created a VIEW with a column containing user-defined aggregated values(stores the output in varchar2 data type). Some of the records exceed the maximum limit, for example varchar2(4000). I was suggested a PL/SQL function to check the length and build a string if necessary..

CREATE OR REPLACE
FUNCTION SUBURL(col1 IN VARCHAR2, col2 IN varchar2)
RETURN varchar2

[Code].....

Here, I got the following error,

Error(4,25): PLS-00103: Encountered the symbol "," when expecting one of the following:
:= . ( @ % ; not null range default character

View 14 Replies View Related

SQL & PL/SQL :: Check Constraint To Disallow Number In Varchar2 Column

Apr 21, 2010

I have table customer which contains a column CUSTOMER_FIRST_NAME

CUSTOMER_FIRST_NAME VARCHAR2(50)

What will be sql statement to add a constraint on the CUSTOMER_FIRST_NAME column of the CUSTOMERS table so that the value inserted in the column does not have numbers ?

View 34 Replies View Related

PL/SQL :: Regular Expression To Check Number With At Least One Decimal Point?

Nov 18, 2013

I would like to use the REGEX_LIKE to check a number with up to two digits and at least one decimal point: Ex.10.11.1112 This is what I have so far.

if regexp_like(v_expr, '^(d{0,2})+(.[0-9]{1})?$') t

View 3 Replies View Related

SQL & PL/SQL :: Function That Multiply Number When There Is More Than 3 Decimal Point?

Mar 20, 2010

how to create a function that will multiply the number when the number has more than 3 decimal point.

Example.
123.012 - multiply by 10
123.0123 - multiply by 100
123.01234 - miltiply by 1000

Column A is the result of the number being multiplied according to decimal point.
Column B contains the multiplier used in column A.

View 10 Replies View Related

(number Precision Too Large) Using POWER (n / M) Function?

Feb 14, 2012

POWER(47.3616, 27.1261) returns: 2.80403309600359E45 which causes "number precision too large" error in my variable which is defined as NUMBER(20,20).

I've tried ROUNDing and TRUNCing the product, but still get the same long output. The only thing that seems to work is SUBSTR.

Is that the ONLY way to deal with this? It seems like there would be a better way or is there another type I should use these super long decimal results?

SQL> SELECT POWER(47.3616, 27.1261) AS Exp_Val
2 FROM dual;
EXP_VAL
----------
2.8040E+45
SQL>

[code].....

View 2 Replies View Related

Forms :: Put One Check Box To Check All The Check Boxes?

Jul 30, 2011

I want to put one check box to check all the check boxes.how can i do this?

View 5 Replies View Related







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