SQL & PL/SQL :: Result Come As Number Like To Change Varchar

Jun 28, 2011

I have procedure that is used by trigger but I define it as Varchar2 it's result show number.

change my PLSQL to use as varchar,

If trigger fired, the procedure make this query

select CB_SMS_FLAG, CB_SMS_DT from TM_SFS_CUST_04 where CUST_NUM = 01071234151;

but I want this query

select CB_SMS_FLAG, CB_SMS_DT from TM_SFS_CUST_04 where CUST_NUM ='01071234151';
because CUST_NUM is VARCHAR2(12)

so it can use index appropriately.

how can I change my PLSQL this sentence,

v_sel_sql1 := 'select CB_SMS_FLAG, CB_SMS_DT from ' || pTBL_NM ||' where CUST_NUM = ' || pCUST_NUM;

Here my code

CREATE OR REPLACE PROCEDURE ORAASFS.P_TM_SFS_CB_SMS_CNT_upd_104(
gKIND VARCHAR2,
pCUST_NUM VARCHAR2,
pSMS_CNT NUMBER,
pSMS_CLC VARCHAR2,
pRD_FLAG VARCHAR2,

[Code]....

View 4 Replies


ADVERTISEMENT

SQL & PL/SQL :: Using Varchar Parameter In Queries With Number Column Can Result In Performance Degrade

Jul 4, 2013

I would like to know if using varchar parameter in sql queries with number column can result in performance degrade.

Ex: Procedure testa ( myparam varchar) is
begin
select col1 into var1 from table where colno = myparam;
end;

Here col no is a number column and myparam is varchar. I feel its better to change the parameter to number.

View 7 Replies View Related

SQL & PL/SQL :: Inherit From Varchar 2 / Number

Mar 22, 2012

I see you can build an address object consisting of street name and number, city and so on, and use this in CREATE TABLE, so all tables use the same fields for addresses. And moreover you can add check functions to prevent street numbers to be negative for instance in the constructor. You can even use inheritance. I like that.

However, all teaching on this subject I found on the Internet starts with structs (points consisting of x and y, persons consisting of name and address, etc.). Then they go to inheritance and show how to build an employee who is just a person plus an employee number. They never start with simple types.

What I would like to do would be rather to start with basic types such as number or varchar2 and inherit from them. An EAN is a VARCHAR2 holding only digits where the last digit is a check sum that must be matched. An item number may be a VARCHAR2 matching 'NNN-AAAA'. A simple string may be a VARCHAR2 where no functional characters such as carriage return, etc. occur.

I would then write my create table statement thus:

CREATE TABLE item (itemno T_ITEMNO, ean T_EAN, itemname T_SIMPLESTRING); and inserts like INSERT INTO item values('123--BCD', '1234567890abc', 'toy') would fail with exceptions like 'item numbers must be formatted NNN-AAAA' or '1234567890abc is not a valid EAN'.

However this seems not possible:

create or replace type T_EAN under varchar2
(
constructor function T_EAN(p_string in varchar2) return self as result
);

fails with "PLS-00580: supertype must be an object type".

Rather than creating completely new types, I would start with extending existing types, but this seems not possible. Is that really impossible or am I using the wrong approach?

View 4 Replies View Related

SQL & PL/SQL :: Varchar Not Printing Zero Front Of Number?

Feb 18, 2013

Today we faced one strange issue Varchar doesnot print the zero front of number

eg

SQL> select agent_code from table_name where AGENT_CODE = 0061;
AGENT_CODE
----------
61
61

where in my database agent_code columns as value 0061 and 061 for both it shows same value.

View 9 Replies View Related

SQL & PL/SQL :: Query Two Columns With Datatypes As Number And Varchar

Jul 17, 2012

Have table with two columns with datatypes as number and varchar and the values in A column like 1,2,3 AND B column values like a,b,c. Now need to display data in a single column as 1,a,2,b,3,c.

View 4 Replies View Related

PL/SQL :: String And Result Number

Jul 9, 2012

Im trying to generate this link on my result [URL].....

materialId and materialFolderId are the results of my query.. Im not sure how to combine my results and string on my query..

View 14 Replies View Related

SQL & PL/SQL :: Displaying N Number Of Rows From Large Result Set

Nov 29, 2012

I have a query that returns 11 Million rows but not all of them can be displayed in SQLDeveloper or DBVisualizer because of limited memory or other type of issues. I need to copy the entire result set to excel for further calculations.

Is there any way that i can select N number of rows out of my actual result set.

For example:
a) A result set contains 10 Million rows in total.
b) I want to display first 5 Million rows by executing a query
c) Then I want to display the remaining 5 Million rows by executing the query again with any parameter changes.

So all I want is to extract the rows of my actual result set in two or more executions, depending on the number of rows.

View 3 Replies View Related

SQL & PL/SQL :: Different Number Of Result Sets While Adding Further Columns In Select Clause

May 4, 2010

The below sql is giving different number of result sets while adding further columns in select clause.i.e After adding the columns 4,5,6 in the below query its giving different number of result set.In this case the result set count would be 5.

Before adding the columns 4,5,6,the result set count was 11.

SELECT PAYMENT_METHOD_MAP.NETTINGGROUP_ID,
PAYMENT_METHOD_MAP.CREDITPAYMENTMETHOD_CD,
PAYMENT_METHOD_MAP.DEBITPAYMENTMETHOD_CD,
PAYMENT_METHOD_MAP.AGENT_ID,
SETTLEMENT.NETTINGGROUP_ID,
SETTLEMENT.SETTLEMENTDATE
[code]....

View 8 Replies View Related

Reports & Discoverer :: SQL Union All - Query Block Has Incorrect Number Of Result Columns

Feb 1, 2012

I have the following Union All query. It throws the following error in SQL plus

ERROR at line 27: ORA-01789: query block has incorrect number of result columns

After doing some google for the above error it suggests there are incorrect number of columns in the Union All query.I could not figure out the exact location well SQl Plus says error is on line 27 at the first opening bracket like

(Select distinct c.contact_code

Following is the SQL query

Select
tbl_contact.contact_code,
contact_title
||'.'||contact_name contact_name,
contact_address,
[Code] ......

View 1 Replies View Related

How To Change Listener Port Number

Sep 16, 2013

ENV: Oracle 10gR2, RHEL 64bit on ASM (10gR2): 

Current listener port is 1521. I have been asked to change it to 10526

I tried changing the port number in the listener.ora file and then restarting the listener but the I start getting "ORA-12541: TNS: no listener" error. If I change it back to 1521 and restart the listener, I am able to connect to the database. What else do I need to do to change the port #? After I change the port and restart, here is what I tried to test: $ sqlplus user/password@sid...ERROR:ORA-12541: no listener Enter user-name: When I rollback the port# change, I am able to login. 

View 18 Replies View Related

Reports & Discoverer :: Page Number Change

Dec 3, 2011

During the Report Development for the Invoice Print the Page Number is required to display for the each Page like 1 of 3.The Report have the parameter of From Invoice Number and To Invoice Number. The Page display the Current page and Total Page. How to Split the Invoice Number wise page Number in the Report.

Presently

Invoice No Page No.
14001 1 of 4
14002 2 of 4
14002 3 of 4
14003 4 of 4

Requirement

Invoice No Page No.
14001 1 of 1
14002 1 of 2
14002 2 of 2
14003 1 of 1

View 2 Replies View Related

Server Administration :: Change Port Number?

Feb 6, 2013

When I created a new oracle database the port number was 1521 (default), but I want to change it to 1522.

View 4 Replies View Related

How To Change Port Number Of Email Alert Notification

Nov 24, 2010

I want to configure the Email Alert Notification in OEM.

In OEM --> Setup --> Notifications Methods --> I am able to see a provision to enter SMTP Address, but there is no port number.

Due to some security policies, my company has opened only Port Number : 9925 for SMTP and blocked Port number : 25.

When I click on "TEST MAIL SERVERS" button, it gives me a error that :

"Test failed with message : Could not connect to SMTP Host , Port No. 25" .

Now I want to change this port number 25 to 9925.

View 5 Replies View Related

Forms :: Dynamically Change Number Of Records Displayed?

Mar 31, 2011

I am currently on a project which has a requirement of changing the number of records dynamically. Oracle Forms version 10g.

View 2 Replies View Related

Reports & Discoverer :: Change Number Of Column Display According To The Condition

Apr 18, 2012

Recently I am facing a problem while working with Oracle reports 2.5.

My requirement is:
there is a report in which there are 5 columns right now. Now what i want is, whenever a condition will satisfy , a new column should be also display in that report otherwise it must be stay as it is.

Previously : a b c d
Now if a=1 : a b z c d
else
a b c d
where a,b,c,d,z are columns.

View 1 Replies View Related

Networking And Gateways :: Change Default Port Number Of Listener - Instance Registration

Feb 19, 2013

i am trying to chnage the default port number of listener but facing problems in instance registration. i did as follows:

1. stop the listener as lsnrctl stop (default listener).
2. chnage the listener.ora and tnsnames.ora with desire port numbers (1526) as follows:-
[code]
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =

[code]...

but i didnt get the reason of aforsaid error and my instance is not registering

View 5 Replies View Related

SQL & PL/SQL :: Compare Varchar With Date

Feb 26, 2012

I have one command compare varchar type with date type by Pl/SQL.

date_entry gave values:" 2012/01/06 22:28:24",

now i want to convert it to date and compare with sysdate.

Select * From qvsystem
Where to_char(to_date(date_entry, 'rrrr-mm-dd hh24:mi:ss'), 'dd-mm-rrrr') = to_char(Sysdate, 'dd-mm-rrrr');
----
note:date_en2012/01/06 22:28:24

after I executed that command It's appear one message .

View 7 Replies View Related

SQL & PL/SQL :: Convert Long To Varchar

Aug 30, 2012

display or get values stored in a long column.i tried the below code, but myvar is shown as null. am i missing something here or is there a better apporoach than this ?. There are actually 16 rows returned for this query but with empty values for 'high_value' column which is critical for me. How can i do this long to varchar convertion ?

SET SERVEROUTPUT ON
DECLARE
my_var long;
BEGIN
for x in ( SELECT high_value
FROM all_tab_partitions
WHERE table_name = 'SALES_DISCOUNT'
AND table_owner = 'CED12'
ORDER BY partition_position DESC )
[code]....

View 4 Replies View Related

SQL & PL/SQL :: Creation Of Sequence To Varchar

Dec 7, 2011

how to create sequence to varchar and how see that sequence

View 2 Replies View Related

SQL & PL/SQL :: Getting Highest Value From Varchar Column

Feb 12, 2011

I've a large table on which I applying aggregate and group by functions to the the results.

Here are two of the columns in my table:

Name ==== Score
John ==== 200*
Zohaib ==== 299
Ali ==== 0*
John ==== 200
Maria ==== 150*
Ali ==== 0
Maria ==== absent
John ==== absent

Here astrick (*) means with distinction....

The "score" column is a varchar column I want to run a query on this table to show the highest score for each student and the output should be like this:

Name ==== Score
Zohaib ==== 299
John ==== 200*
Maria ==== 150*
Ali ==== 0*

Important note:

1. if there is a tie between two highest scores like for a student, incase of john 200 was made twice, but the score with asterick (*) will be the "maximum" becuase it is with distinction so the output should also show the the highest score with asterick.

2. the output should show the the 2nd column (score) in desc order of highest score by each student...again incase of a tie, the one with astrick will come first in the result..

I know with just mere numbers, that is pretty easy but in this case it is a varchar column and also i need the astrick along with the highest score.

I want the simplest and shortest query if possible to achieve this result

I hope I've been able to clearly explain my requirment. I am using 10G.

View 1 Replies View Related

SQL & PL/SQL :: Difference Between Varchar And Varchar2?

Aug 31, 2012

What is the exact difference between varchar and varchar2?

As i know only the length is the one difference.Apart from this length,what are all the differences?

View 3 Replies View Related

SQL & PL/SQL :: Storing Unicode In Varchar

Feb 3, 2011

I have a varchar field in a table. When I store the following string, some undefined character is storing.

String to be stored: Test String
String actually stored: Test ¿ string

Please note that here '' is not Hyphen. It is a special character that msword has. when you copy this and paste in a word document you find the difference.

I have listed the character set for CHAR and NCHAR from NLS_DATABASE_PARAMETERS

NLS_CHARACTERSET - WE8ISO8859P1
NLS_NCHAR_CHARACTERSET - AL16UTF16

View 12 Replies View Related

SQL Statement With Varchar Datatype Size

Dec 21, 2010

The below table and functions are examples, but issue is like this.

i have one issue while forming below sql , Application will form this query at run time wih required inputs
it is failing because the application will not support the clob datatype.

Table : T_E (similar to EMP table structure)

This table has data similar to emp but bulk data around 10k records.

Query formed

select empno,ename, get_employees(deptno)
from t_e;

This sql query this is failing when function return varchar2 string more than 4000 size.Because in sql query size should not exceed 4000 for varchar2 data type , function return size can be upto 20000

Function which
CREATE OR REPLACE FUNCTION get_employees (p_deptno in emp.deptno%TYPE)
RETURN varchar2
IS
l_text varchar2(32767) := NULL;
BEGIN
FOR cur_rec IN (SELECT ename FROM t_e WHERE deptno = p_deptno) LOOP
l_text := l_text || ',' || cur_rec.ename;
END LOOP;
RETURN LTRIM(l_text, ',');
END;

I can try this with clob but application doesn't support that..so i tried with using clob in above function , after that while returning to application i want to conver the clob to char and return the entire string

select empno,ename, to_char(substring(get_employees(deptno),1,4000)) -- first 4000 characters
, to_char(substring(get_employees(deptno),4000,8000)) -- next 4000 characters
from t_e;

But this is failing how to return the entire string in above sql

View 1 Replies View Related

SQL & PL/SQL :: Finding MAX And MIN Value From Varchar Datatype Field

Mar 13, 2013

How to get max and min value from a varchar2 datatype column?

CREATE TABLE TEST
(
WEIGHT VARCHAR2(20)
);
INSERT INTO TEST VALUES('100');
INSERT INTO TEST VALUES('120');
INSERT INTO TEST VALUES('113');
INSERT INTO TEST VALUES('145');
INSERT INTO TEST VALUES('204');
INSERT INTO TEST VALUES('130');

I've to find the max and min weight from this data.

View 2 Replies View Related

SQL & PL/SQL :: Varchar 2 Interact Server With Client

Nov 14, 2011

We have server oracle 11 , running on UNIX . we have table with a lot of columns - varchar2(4000)

My question

when data comes from server to client? what actually comes for column varchar2(4000) ?

for example value of columns 'abc'

does it mean that from server to client will be sent buffer with size 3 char ?

or

it will be sent buffer 4000 characters and one more value shows how big is variable inside buffer ( for this case it will be 3 )

View 5 Replies View Related

SQL & PL/SQL :: Split Varchar Field Into 2 Fields?

Mar 22, 2010

I need to separate 1 field into 2 fields. The source is varchar2 and is like:

[VOUCHER]
CGJ0000617
CG0001442
CGJ0001444
CMOV0000200
CXAR00000001
CXAR00000002

Result should seperate numeric value from characters so that the result would be:

[VOUCHER_char] [VOUCHER_num]
CGJ 0000617
CG 0001442
CGJ 0001444
CMOV 0000200
CXAR 00000001
CXAR 00000002

I need one or two SQL statement.

View 9 Replies View Related

SQL & PL/SQL :: Converting Varchar To Clob Within SQL Union?

Feb 14, 2013

Why does oracle not allow the following query

select to_clob(1) from dual
union
select wm_concat(sysdate) from dual;

wm_concat returns a clob. To make both queries in the union have the same type columns i convert the column in the first query to a clob but Oracle gives me an [1]: ORA-00932: inconsistent datatypes: expected - got CLOB error even though both are returning a clob value.

Each of the queries work individually and both return a clob value.

View 4 Replies View Related

Copy Data From One Field (varchar) To Another Within A Table

Oct 5, 2010

i have a table COLOR having alphanumeric fields i.e Color Description & Color Ref. i want to copy the values from Color Description to Color Ref.

what will the select statement for this?

View 9 Replies View Related

Built-in Function To Covert CURSOR To VARCHAR?

Feb 13, 2007

know if there's a built-in function to covert an Oracle CURSOR to VARCHAR? Or how about a XMLType to VARCHAR?

View 1 Replies View Related

SQL & PL/SQL :: Get Numeric Values From A Column Of Varchar Data?

Aug 16, 2013

I have a table and data like mentioned below.

create table emp( ename varchar2(20));
insert into emp values ('122');
insert into emp values ('abc');
insert into emp values ('0.2');
insert into emp values ('0-5');
insert into emp values ('25-30');

SQL> Select * from emp;
| ENAME |
-------------------
| 122 |
| abc |
| 0.2 |
| 0-5 |
| 25-30 |

I am running the code

SQL>select regexp_substr(ename , '^[[:digit:]]+.[[:digit:]]+$|^[[:digit:]]+$')
from emp;

AFTER RUNNING I AM GETTING THIS

| REGEXP_SUBSTR(ENAME,'^[[:DIGIT:]]+.[[:DIGIT:]]+$|^[[:DIGIT:]]+$') |
---------------------------------------------------------------------
| 122 |
| (null) |
| 0.2 |
| 0-5 |
| 25-30 |
| (null) |

Why it's not excluding '0-5' and '25-30', how I should write code to exclude this and Is there is any function in oracle to check for numeric in column and print.

View 4 Replies View Related







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