SQL & PL/SQL :: Cursor Not Fetching Special Character

Feb 5, 2013

This is the cursor which fetches from View uk_case_recommends_vw and this view calls the table ukcc_case_casenames below is the query

cursor cur_case_name_search is
select a.*,DENSE_RANK() OVER (ORDER BY sc desc) rank
from uk_case_recommends_vw a
where rownum < 2 + 3;
SELECT
/*+first_rows(4) index(u ukcc_case_casename_idx)*/
persistentid,
casename,
FROM ukcc_case_casenames

Here in this table my case name may be with special character or normal .

For Ex ('R V Wilson') - With Special character
('R V Wilson') - Without Special character

Actually my cursor is not fetching with special character ..neither it is not mentioning as no data found. Find the piece of code below.. I am not getting values after opening the cursor.

open cur_case_name_search;
loop
fetch cur_case_name_search into v_case_name_row;
exit when cur_case_name_search%notfound or
v_case_name_row.rank > v_matchingvalue or
[code]......

View 10 Replies


ADVERTISEMENT

SQL & PL/SQL :: How To Insert Special Character / ORA-00911 / Invalid Character

May 25, 2010

We have Oracle 10g and user trying to Insert following in one of our table field and getting error:

'REVOLUCIÃ"N Historical Corruptions Agenda'

Getting follwoing error:

ORA-00911: invalid character
create table Employee
(Emp_ID Varchar2 (10),
Company_Name Varchar2 (40)
)
Insert into Employee
values ('Emp1', 'REVOLUCIÃ"N Historical Corruptions Agenda');

View 5 Replies View Related

SQL & PL/SQL :: Fetching Records From Cursor

Jun 21, 2012

I have created one global temporary table in which I inserted 2 rows.

I am fetching the rows by using following cursor :

declare
cursor c1 is
select TTD_TRV_MODE
from global_tra_trv_dtl;
v_trv_mode varchar2(10);
Begin
open c1;

[code]....

But instead of 2 rows , 3 rows are getting fetched.

View 6 Replies View Related

SQL & PL/SQL :: Cursor Not Fetching Values

Apr 21, 2011

oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"

I have a cursor in my procedure. When I OPEN, FETCH, it doesnt give me any values. But instead of cursor,if I use the sql , used in the same cursor, then i am getting the value.

DECLARE
l_vin CLM_MAIN.vin%TYPE;
l_part CLM_MAIN.vin%TYPE;
l_clm_id CLM_MAIN.vin%TYPE;

[Code]....

Clm_main has a UNIQUE constrains, of VIN, PART. So when i OPEN the cursor, its not finding clm_id , eventhough it exist in the table. so it takes it as claims_cur%NOTFOUND, an tries to INSERT in the table. But since the record with that VIN and PART already exists, it throws exception that ORA-00001: unique constraint (CLM_MAIN_UK) violated.

View 25 Replies View Related

Forms :: Fetching Value From Dynamic Cursor?

Dec 13, 2011

I have used a dynamic cursor for fetching value from different tables hence table name assigned dynamically during run time but i face an error ORA-06562 type of argument must match type of column and bind variable with error ORA-06212 But i frequently check the table structure and Declared variable there is no mismatch between them.

Code----------

Declare
C_YEAR number(38);
C_LOC VARCHAR2(200);
C_INVNO VARCHAR2(200);
C_INVDT DATE;
C_CTRT VARCHAR2(200);
C_GLCD VARCHAR2(200);

[code]....

View 2 Replies View Related

SQL & PL/SQL :: Special Character In String

Feb 23, 2011

My requiremnt is that a field must contain on Alpha number value, no specail character allowed, how can we sure by query that string which is going to commit is alphanumeric.

View 2 Replies View Related

How To See The Exact Special Character

Mar 18, 2013

I am using Release 10.2.0.1.0 version of oracle. I am getting a special character, but i suspect it as any other language character.

Its appearing in my 'TOAD editor/Sql prompt editor' as 'A?'. but when i am selecting it from the table using below query, its giving 'No rows Returned'.

select id from tab1 where id like 'A%';

How can i be able to see the exact character or which editor will enable me to see the character?

I think sql developer GUI might be able to show the same but i dont have sql developer with me.

View 1 Replies View Related

Forms :: Select Not Fetching Data Within A Cursor

Feb 8, 2011

SELECT
contgrp_num,
cpgrp_desc,
strategy_id,
perftyp_cd,
stiertyp_id_calcbase,
stiertyp_id,
cpgrp_flg_aggr,
pgrptyp_cd

[Code]..

The above query is not returning any value. But, when I put this within a begin-end block, its fetching data.

why its not returning any data without the begin-end block.

The entire code has an outer begin-end block within which the cursor is also defined.

View 1 Replies View Related

SQL & PL/SQL :: Identify / Count Records In Ref Cursor Without Actually Fetching?

Jan 22, 2013

/* Formatted on 22/01/2013 19:32:50 */
CREATE OR REPLACE PROCEDURE test_rdm_miles (
p_ref_cursor OUT SYS_REFCURSOR
p_success NUMBER)
IS
BEGIN
OPEN p_ref_cursor FOR
SELECT 5168 mem_uid,

[code]....

I have a Procedure with out parameters as a REF CURSOR and response message as p_success.This ref cursor will be returned to the calling service. Is there a way in oracle by which we can identify whether the Ref cursor holds data without actually fetching it. Since if i choose to fetch the data, i will lose one row when i return the ref cursor back to the calling service Or else is there way i can retrieve the row i lose during fetch.

Other alternative what have been suggested is create an object type ,fetch the ref cursor values in object type. Then i can use the ref cursor to return the data by table casting.

one more solution is

OPEN
FETCH
CLOSE
OPEN (AGAIN) { this will lead to redundancy)

View 7 Replies View Related

Insert Special Character In Database?

May 5, 2008

I am trying to insert data into table through SQL.

However my table has a column by name "User Name". Now I have a user name " x,yz" (see there is comma b/w x and y).

how do I escape and insert this data(x,yz) into the table.I need data as it is in DB for my application.

View 1 Replies View Related

PL/SQL - Generate Update DML With Special Character?

Dec 1, 2010

I have a pl/sql question about interpreting special characters in pl/sql. I am generating an update statement and get an invalid character when the customer name has an ampersand in it (&). I�m now generating the update statement to include the customer name cut up with concatenation characters surrounding the ampersand, for example �CSCP-C�||�&�||�E�. However, pl/sql is still not executing the update.

Here is the code.

SET PAGES 2000;
SET LINES 200;
SET SERVEROUTPUT ON;
SET VERIFY OFF;
spool update_table.log ;

[code]....

The RESULTS follow. Of course, I can copy this statement and execute in sqlplus without a problem.

---------------------------------------------
- UNKNOWN ERROR CONDITION
SQL = UPDATE mytable SET CUSTOMER_CORE = ('DSCP-C' || '&' || 'E') WHERE REC_ID = 170795889
;
---------------------------------------------
PL/SQL procedure successfully completed.
SQL>

View 1 Replies View Related

SQL & PL/SQL :: Drop Table With Special Character?

Jul 15, 2011

I have created a table with space by mistake.how to drop that..?

QL> select * from cat where table_name like 'OM_DW_RTNORD%';

TABLE_NAME TABLE_TYPE
------------------------------ -----------
OM_DW_RTNORD? TABLE
OM_DW_RTNORD TABLE

[code]....

no rows selected

SQL> drop table OM_DW_RTNORD? purge;
drop table OM_DW_RTNORD? purge
*
ERROR at line 1:ORA-00911: invalid character

View 3 Replies View Related

SQL & PL/SQL :: Special Character Not Allowed In Column Value?

Aug 8, 2012

I have a table stud_mst In which i have following columns STUD_NAME , ROLL_NO , COURSE

Table values are for e.g

STUD_NAME , ROLL_NO , COURSE
Harshita 1323 BSC
Atul 1734 BCOM
L'Pradhan 3739 BA

Still today user can enter any special character value in stud_name like L'Pradhan . I want to put a restriction on stud_name so that user can enter only alphabetical value into stud_name column.

View 6 Replies View Related

SQL & PL/SQL :: How To Store Special Character In Database

Aug 19, 2010

i want to store special characters in database. i have tried following but not succeed. how to store and retrieve it. Herewith i'm posting my try.I want to store and retrieve following character.

SQL> select nchr(181) from dual;

N
-
µ

SQL> create table temp (uom varchar2(10));

Table created.

SQL> insert into temp values(nchr(181));

1 row created.

SQL> commit;

Commit complete.

SQL> select * from temp;

UOM
----------
?

here i'm getting the '?' but i want 'µ'.

View 2 Replies View Related

SQL & PL/SQL :: Special Character Interpreted Differently?

Feb 21, 2011

I have to update 'SAINT BARTHÉLEMY' in the database . when I update the same from sql prompt Its getting updated correctly .

But when we want to see the same from Toad/Sql developer its interpreting this character in different way .

I tried e.g.

select dump( 'SAINT BARTHÉLEMY') from dual;
select dump( 'É') from dual;
Returns me
DUMP('SAINTBARTHÉLEMY')
---------------------------------------------------------------
Typ=96 Len=16: 83,65,73,78,84,32,66,65,82,84,72,144,76,69,77,89
DUMP('É')

[code]....

Where I should fix to bring both in sync.

View 2 Replies View Related

SQL & PL/SQL :: Escape Special Character Word

Jan 7, 2011

E1 Table Data

Job_id
Sa_Clerk
Sa_Manager
Sahil

select job_id from E1 where instr(job_id,'_')>0;

returns output

Sa_Clerk
Sa_Manager

same as

select job_id from E1 where job_id like 'Sa_\%' escape '';

resturns

Sa_Clerk
Sa_Manager

Same Result , Then What is exact need of Escape

View 4 Replies View Related

SQL & PL/SQL :: Maintain Day 0 Value Of Special Character ÿÿ For Application

Aug 29, 2012

We need to maintain the day 0 value of special character "ÿÿ" for application. If i use the special character directly in sql file , it is getting inserted as junk character "?"

If i use workaround by using chr(255) function i am getting below behavior.

select chr(255) from dual
---------------------------
ÿ

However below query gives null value.

select chr(255)||chr(255) from dual

View 5 Replies View Related

SQL & PL/SQL :: Different Special Character Display Oracle 10 Versus 11g?

Sep 17, 2012

A database containing inventory data has been migrated from Oracle 10g to Oracle 11g. I have access to both the Oracle 10g and Oracle 11g database on different client computers. Both databases use the same character set, WE9MSWIN1252 (query shown below). However the results from the sql SELECT show incorrectly displayed characters. I would like the "1/2" character and degree character to show in the text. The ASCIISTR function shows that the underlying ascii is the same in the two copies of the databases.

Is there a setting that needs to be changed in Oracle 11g so that the saved special characters in the database show correctly (as in Oracle 10).

Query of database character set

SQL> Select value from SYS.NLS_DATABASE_PARAMETERS where PARAMETER = 'NLS_CHARACTERSET'
WE8MSWIN1252

Under Oracle 11g, this is a query on DSI using SQLPLUS 11.2.0.1.0.

SQL> select description from part where id = '57234';

DESCRIPTION
----------------------------------------
KL BRKT PLN 22╜░ ANGLE (AMER BOT RAIL)
SQL> select asciistr(description) from part where id='57234';
ASCIISTR(DESCRIPTION)
--------------------------------------------------------------------------------

[code].....

View 6 Replies View Related

Server Utilities :: Getting Special Character While Loading?

Sep 19, 2013

I have a file which i am loading , the last column is being loaded with special character which looks like sqare shape character for all the rows.How can i prevent it to load as it is not present in the file

data in file

Department|2|Tranport for London|Rail & Underground|ER|ER|ER|ER|ER|ER|555555555|Owner 8|8

the data once loaded for the last column on which is for above example last column value 8 will be loaded with 8 and square share character.

View 5 Replies View Related

SQL & PL/SQL :: Special Character Conversion / Translate / Replace

Nov 3, 2010

I have requirement to cleanup special character of field. The data which is retrieved from the field and written to text file.

So Here

Ã- should be replaced by i
HÃ-­re - I need value Hire(actual value) from the filed.
í- this has written into file instead of i

In database i have the following

NLS_NCHAR_CHARACTERSET - AL16UTF16
NLS_CHARACTERSET - UTF8

I tried this

select convert('HÃ-re','UTF8') from dual
------------------------
Output: H??re

View 6 Replies View Related

JDeveloper, Java & XML :: How To Use Sql To Bypass Xml Special Character

Jun 20, 2012

"SELECT xmltype(RCATT).extract('/JASON/RCA/SUB_PROBLEM_TYPE').getStringVal() as SPT,
xmltype(RCATT).extract('/JASON/RCA/SUB_ROOT_CAUSE').getStringVal() as SRC,
xmltype(RCATTT).extract('/JASON/RCA/ELABORATION').getStringVal() as ELA,
xmltype(RCATT).extract('/JASON/RCA/PREVENTION').getStringVal() as PRE
FROM table"

Above is my select statement

some explanation: the above select statement is to select the child node inside the xml content. But the child note contains some special character "<>" that illegally in xml.

Below is my xml content for the node of sub_problem_type

<JASON>
<RCA>
<SUB_PROBLEM_TYPE>
For <FS_PLS_WAIT>, it is appeared in both valid and invalid password entered case but in requirement, only valid case has stated the display of <FS_PLS_WAIT>. This is the legacy radio behavior.
</SUB_PROBLEM_TYPE>
</RCA>
</JASOn>

The result will generate the following issue.

ORA-31011: XML parsing failed ORA-19202: Error occurred in XML processing LPX-00225: end-element tag "ELABORATION" does not match start-element tag "FS_PLS_WAIT" Error at line 10 ORA-06512: at "SYS.XMLTYPE", line 301 ORA-06512: at line 1

I wish to know where can i by pass the checking for special character.

View 1 Replies View Related

PL/SQL :: LENGTH (String) Ignores Special Character %?

Oct 10, 2012

why the below is not the same?

select * from textsearch where
LENGTH(TRIM(BOTH ' ' FROM Text)) < 1
--0 Rows

select * from textsearch where Text='%';
--12 Rows

View 1 Replies View Related

SQL & PL/SQL :: Handling Special Character (&) In Oracle Stored Procedure?

Apr 23, 2013

How to handle special charater '&' in stored procedure.

Following is a test code I want to Implement

CREATE OR REPLACE PROCEDURE SP_Test(QueryID NUMBER,DateFirst DATE,DateLast DATE) IS
BEGIN
INSERT
INTO TempTable(QueryID,LineID,data,Datetime)
SELECT
1,6,Emp,startdate FROM salary

[code]....

The code above resuts into INVALID state of stored procedure and cannot be executed.

View 8 Replies View Related

SQL & PL/SQL :: Convert Data Back To Special Character Values?

Nov 4, 2011

Enterprise Edition Release 10.2.0.5.0

SQL> SELECT * FROM NLS_DATABASE_PARAMETERS where parameter = 'NLS_CHARACTERSET';
PARAMETER VALUE
------------------------------ ----------------------------------
NLS_CHARACTERSET AL32UTF8
SQL>

There is table (VIN_TEMP) in my company database containing following records. It seems like this table should contain some greek language special chracter values instead of this weird data.

SQL> select * from vin_temp;
ATTR
--------------------------------------------------------------------------------
���9999
���9998
���9997
���9997

[code]....

Client are reporting these records as invalid and requesting us to fix. As i investigated i found out, this table was created and loaded few year back. Client sent us one time files which we loaded into this table. I was able to find the code which was actually used to load this table, but unfortunately i was not able to find the raw files where we load this data from...

It seems like Previous Developer specified character set "UTF8" statement, in his sql loader script, to load this data. It seem those file contain some Greek language special character data which was not support by "UTF8" charater set and result in creating those invalid data. My Job is to fix these invalid records and convert them back to its original values which were present in the raw file. I tried to contact client and see if i can find out the raw files but no luck. I tried to use convert function as mention to convert this data from "UTF8" to our current character set format but no luck.

SQL> SELECT attr, dump(attr), convert(attr,'UTF8', 'AL32UTF8') from vin_temp;
ATTR DUMP(ATTR) CONVERT(ATTR,'UTF8','AL32UTF8'
------------------ ---------------------------------------------------------------- -----------------------------
���9999 Typ=1 Len=13: 239,191,189,239,191,189,239,191,189,57,57,57,57 ���9999
���9998 Typ=1 Len=13: 239,191,189,239,191,189,239,191,189,57,57,57,56 ���9998
���9997 Typ=1 Len=13: 239,191,189,239,191,189,239,191,189,57,57,57,55 ���9997
���9997 Typ=1 Len=13: 239,191,189,239,191,189,239,191,189,57,57,57,55 ���9997

[code]....

Here is the script to create table with those type of invalid records.

create table VIN_TEMP
(
attr VARCHAR2(50 CHAR)
);
insert into VIN_TEMP (attr) values ('���9999');
insert into VIN_TEMP (attr) values ('���9998');

[code]....

View 1 Replies View Related

Globalization :: SQL Loader Loads Special Character Incorrectly

May 31, 2013

We are trying to load some xml files via

sqlldr user/pswd@xe control='C:xmlldr.ctl' data='listoffiles.dat'

where xmlldr.ctl is the following:

load data
CHARACTERSET WE8ISO8859P1
replace
into table LOADER_CTG_PROTOCOL
(

xmlfile lobfile(nct_code) terminated by eof,
nct_code char(15) "substr(:nct_code,1,11)",
created_dt sysdate
)

I was playing with different CHARACTERSET, but some special characters e.g. "greater than or equal" do not get loaded/displayed correctly in the database. Also tried changing NLS_LANG registry key and following some advices in the Oracle doc

[URL].......

View 1 Replies View Related

SQL & PL/SQL :: Handling Trademark Special Character During Data Loading In Oracle 11g

Aug 6, 2012

we have a Oracle 11g database, but it does not support UTF8 character set. Now we have received a file which contains records with Trade Mark symbol (special character). Now when we are trying to load the record, it is getting loaded with a sign "?". As a result when we try to display record in web application, the application is not able to show the records properly.

View 5 Replies View Related

JDeveloper, Java & XML :: Parsing With Special Character As Part Of Data

Sep 10, 2013

I'm using following select to parse XML as table ( Oracle 11.2.0.3.0 EE Linux and Windows)

SELECT a.id, a.name, a.description
FROM ( XMLTABLE('//ROW'
PASSING (SELECT xmltype('<ROWSET>
<ROW><ID>1111</ID><Name>Name1</Name><DESCRIPTION>AAA</DESCRIPTION></ROW>
[code]........

However, when DESCRIPTION contains "<" as part of it - I'm receiving an error:

SELECT a.id, a.name, a.description
FROM ( XMLTABLE('//ROW'
PASSING (SELECT xmltype('<ROWSET>
<ROW><ID>1111</ID><Name>Name1</Name><DESCRIPTION>AAA</DESCRIPTION></ROW>
[code]........

Quote:ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00231: Invalid character 61 ("=") found in Name or Nmtoken

I'm receiving a XML from some external source and can not change it.

View 8 Replies View Related

Export/Import/SQL Loader :: How To Validate Special Character After Migration

Mar 26, 2013

We have migrated the database from Oracle 10.1.0.3 to 10.2.0.5 by using conventional export and import. (OS - HP.Uinx).,After the migration some characters (like é or à or è) have not been migrated correctly.

how to validate this characters after migration?

Note:

All NLS parameters are same in both source and target database

View 8 Replies View Related

Client Tools :: PL SQL Developer Program Changing Special Character

Mar 29, 2010

I dont know why, but my PL SQL Developer keeps changing special character as "ã", or "ç", to "?". And As I use these letters because of my language, I need a way to solve this thing.

View 6 Replies View Related

Server Administration :: How To Know Character Set As Single Character Or Multi Character

Sep 26, 2013

how do we know database character set is either single character set or multi character set?

While changing character-set from AL32UTF8 to WE8MSWIN1252 got "ORA-12712: new character set must be a superset of old character set".

Below are steps taken to resolve the issue -

ALTER DATABASE CHARACTER SET WE8MSWIN1252;

i got this error: ORA-12712: new character set must be a superset of old character set

below are the commands executed by me:

SQL> SHUTDOWN IMMEDIATE;
SQL> CONNECT SYS/password AS SYSDBA;
SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE WE8MSWIN1252;
SQL> SHUTDOWN;
SQL> STARTUP;
SQL> QUIT;

And its working...

I have not done it in proper order. Neither have done ccsscan. Still, no user reported any issues. Do my changes truncated the data?

View 11 Replies View Related







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