SQL & PL/SQL :: ASCII Function In UTF-8 Database?
Dec 2, 2010
I want to get NUMBER value from RAW bytes in PL/SQL...For example, single-byte value 0xED in RAW variable should became 237 in NUMBER variable.
We used to use:ascii(utl_raw.cast_to_varchar2(utl_raw.substr(p_data,i,1))), but this seems working in DBs with single-byte character sets only, and now I am on DB with AL32UTF8 character set...
I would be happy if the following function returns 237, not 0:
function mmm (
r RAW -- ed (237 in decimal)
) return NUMBER
is
n NUMBER;
begin
[code]....
View 4 Replies
ADVERTISEMENT
May 16, 2013
why the REPLACE function is not replacing. I assume it has something to do with the ASCII value being zero.
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0
Connected as aggs@AGGSTEST
SQL>
SQL> SELECT str,
2 e9,
3 REPLACE(str, '%E9', e9) replace,
4 regexp_replace(str, '%E9', e9) regexp_replace,
5 utl_url.unescape(str, 'UTF8') utl_url,
6 ascii(e9) ascii
7 FROM (SELECT 'Soir%E9e' str,
8 chr(to_number('E9', 'xx')) e9
9 FROM dual);
STR E9 REPLACE REGEXP_REPLACE UTL_URL ASCII
-------- --- ------- -------------- ------- -----
Soir%E9e é Soire Soirée Soirée 0
View 5 Replies
View Related
Mar 24, 2011
I have simple program .It reads binary file and writes all , what reads into another file
WHILE my_lenght_help <= my_lenght LOOP
UTL_FILE.GET_RAW ( g_read_file , my_bufer_read ,200 );
UTL_FILE.PUT_RAW ( g_write_file , my_bufer_read );
my_lenght_help:= my_lenght_help + 200;
END LOOP;
my question. How I can change "my_bufer_read" between UTL_FILE.GET_RAW and UTL_FILE.PUT_RAW to make writable file ascii
View 3 Replies
View Related
Jan 27, 2011
I have requirement to convert the extended ascii to character. Is there any function available .
View 9 Replies
View Related
Sep 9, 2013
In field APPLICATIONNAME are non ASCII characters. Howto delete them?for example
old value:
René
new value:
Ren
table
CREATE TABLE MIDDLEWARE_CONT
(
APPLICATIONNAME VARCHAR2(120 BYTE),
PRODUCTNAME VARCHAR2(70 BYTE),
HOSTNAME VARCHAR2(60 BYTE),
[code]....
View 14 Replies
View Related
May 20, 2011
I can't think of a clue how to get the desired results.
I have table column with varchar2 type. I need to convert the strings in this column to ascii values. Now I know Oracle function ascii() converts a character to ascii values. But it just converts the first character of the string and ignore the others. I need to convert the whole string to ascii values. For example, if I need to convert the string "USA", the result should be '858365' (as in U decimal value is 85, S decimal value is 83 and A decimal is 65).
I think I need to use instr() function and also PL/SQL to pass all these strings through a cursor and then use a loop but I'm still confused about it.
View 5 Replies
View Related
Jun 8, 2011
I'm looking for a way to insert tabs (ASCII 009) in SQL output, to make the output file Excel-ready without manual intervention.
Here's what I need :
SQL> set output myfile.xls;
SQL> select article,TAB,count(*) from sales group by article having count(*) > 0;
Instead of TAB I can insert a dollar sign, but then I have to replace it using a file editor, which is a manual operation. I have been looking for an OpenVMS application which can replace all dollar signs by tabs in a given file, with no luck.
I created a DCL procedure (simplified from GSR.COM which I found on the internet) that reads every record in the output file and substitutes dollar signs by tabs, but it doesn't work with tabs, only with "normal" characters.
Even if I use the OpenVMS editor to insert ASCII 009 tab characters in the SQL statement (using the GOLD 009 GOLD SPEC INS key combination), SQL apparently automatically substitutes tabs by full stop signs in the output :
select article,' ',date from werknemers limit to 2 rows;
ARTICLE DATE
TROUSERS . 12-28-1974
SHIRTS . 10-08-1973
2 rows selected
I can use SQL to create a CSV file (comma separated values) which can be opened by Excel :
"trousers",20
"shirts",30
But the user has to open this file from within Excel and make a number of choices (separator, text delimiter, header row) before (s)he gets to see the desired columnar spreadsheet. That's not automation : I need the generated SQL output file to be double-clickable so that it opens as a columnar spreadsheet immediately.
View 19 Replies
View Related
Sep 19, 2013
I have a table like mentioned below
create table test1( test_no number, test_description varchar2(100));
insert into test1 values (1,'ABC£¥');
insert into test1 values (2,'BCD£¥');
Now I am selecting from the above table and the expected rest it should have shown is as shown below
TEST1
-----------------------
TEST_NO TEST_DESCRIPTION
1 ABC£¥
2 BCD£¥
But instead of showing the extended ascii characters, it is showing some different characters as shown below
select * from test1;
TEST1
-----------------------
TEST_NO TEST_DESCRIPTION
1 ABCLY
2 BCDLY
I have an requirement where i need to fetch the exact ascii characters for £ and ¥ instead of L and Y respectively.
View 1 Replies
View Related
Apr 10, 2013
My application has several update-able forms. And I build a single staging table to store all these records.
The user wants a button on the last form and by clicking on this button they want to export the file(updated records from all the forms) in ASCII format.
if its do-able or not in apex since I haven't come across this kind of request earlier and didn't find the same in this forum?
Versions: 10g DB, Apex 4.1.
View 6 Replies
View Related
Jan 10, 2012
I am using SQL Loader to load data from text file to DB. non-ASCII characters present in the text file is not uploaded correctly to DB.
Sample Data
test data üindex
Data in DB
test data ?index
View 1 Replies
View Related
Sep 20, 2012
I am using oracle database 9iR2 (9.2.0.8) on windows 2003 server.
Is there any method to convert following output in to ASCII format ??
select ename from scott.emp;
ENAME
--------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
View 6 Replies
View Related
Jul 20, 2010
i have to upload a file in database, the column seperator is ASCII CHARACTER 29, i dont know how to specify that in loader file?
how to write this - fields terminated by ' ' in sql file, which i am calling from a batch file.
i have attached the text file which i am trying to upload, here field seperator is ASCII CHARACTER 29 and record seperator is NEW LINE CHARACTER.
View 3 Replies
View Related
Dec 19, 2011
I have following procedure
CREATE OR REPLACE PROCEDURE peter."GET_RANDOMDATE"
AS
V_CNTR number(5);
V_PKDATE VARCHAR2(20);
BEGIN
[code].....
My objective is to insert the random date value returned by datamask.get_random_date database function into peter.date_ random table.
I need the exact syntax to compile this procedure.
View 3 Replies
View Related
Apr 23, 2013
I created a function and what I'm trying to do with this function is add tax to a particular order in my database. I want it to multiply the quantity and cost of the order then add tax and then insert it into the corresponding column.
for the tax I'm using this function:
Create or replace function tax(v_value in float) return float is
Begin
Return(v_value*.07);
End tax;
select PRODUCT_ID,TOTAL_COST, tax('TOTAL_COST') from PRODUCT;
I get an error saying TOTAL_COST is an invalid identifier.
This is my table structure:
SQL> select *
2 FROM user_cons_columns
3 WHERE table_name = 'CUSTOMER';
OWNER CONSTRAINT_NAME
[code]....
I want the total cost to be inserted into the total cost column.how I work this out through either a function or procedure or both?
View 6 Replies
View Related
Jun 19, 2012
in a 9.2.0 db I create a db link but does not function :
CREATE DATABASE LINK "ONEDB"
CONNECT TO "user1" IDENTIFIED BY "****" USING 'ONEDB';
SQL> select * from dual@"ONEDB";
select * from dual@"ONEDB"
*
ERROR : ORA-12154: TNS:could not resolve service nameBut on the same server I can connect to 11g in sqlplus :
Z:>sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Mar. Juin 19 10:13:45 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> connect user1@ONEDB
password :
Connected.Of cours the other DB links toward 9.2.0 databases work well.
View 4 Replies
View Related
Mar 8, 2007
I'm trying to insert a character from the extended ascii character set. Specifically, there's a company that has an accented e (�) in the name. Right now, the company name doesn't have the e at all, accent or no accent. So I'm trying to do an update, something like
update table1 set company_name='blah�" where company='blah'
It runs, but doesn't do the update. Even when I try to forcefully do an insert (instead of an update) I get nowhere; the accented is simply dropped. So the basic question is, how do you insert extended ascii characters into oracle?
View 3 Replies
View Related
Feb 18, 2011
I'm trying to create a function that simply returns the current database name (e.g: select db_unique_name FROM v$database ) from a function but when I compile it comes up with :
Error(9,5): PL/SQL: SQL Statement ignored
Error(9,44): PL/SQL: ORA-00942: table or view does not exist
I am compiling and running this as the SYSTEM user and I do think that I need to set privledges/roles, etc to allow this (since I have read that using synonyms in functions/procedures requires permissions...but I cannot seem to find anything that tells me exactly what role/priveledge I need to grant/allow to let this happen.
View 5 Replies
View Related
Dec 12, 2012
Can a Oracle Function return Images? I have been getting and able to read lot of solutions but still unconfirmed. As in certain forums it has been mentioned that the difference between procedure and function is that. With procedures you are able to return images but not with function.
View 1 Replies
View Related
Feb 24, 2011
I have 2 databases:
Database A
Database B
In Database A I have a series of config tables and stored procedures/functions In Database B I have a lot of tables.
I would like to execute my stored procedures and all associated functions in database A on my data in database B.
Now I've figured out that creating a database link enables me to do SQL selects on data in both A and B...but how do I run SP/Funcs ? I've read something about packages but not sure if I'm heading in the right direction.
Do I need to create a simple synonym ? Can I use the existing DB link ? or is there a special way of calling them, or...
I like the A/B database set up since I can keep battle tested code in one location and have it work across multiple dbs...saves me having to create everything in every database.
View 2 Replies
View Related
Apr 9, 2012
Why Oracle allowing the Built-in function names as column name in data base table
Ex :
create table to_char
(to_number number(8) , to_char char(20) , to_date date ,substr varchar2(20))
View 3 Replies
View Related
Jul 17, 2012
how the DB handles record locking when an aggregate function is called? For instance:
...
select count(*)
into v_count
from x;
...Is there a lock maintained on table x for the duration of the transaction so no rows can be inserted or deleted?
View 4 Replies
View Related
Apr 9, 2013
There are 2 Oracle databases with pseudo names Remote and Local. I have a function in Remote called FUS.F_Return_10 which simply returns 10 for testing purposes, where FUS is a schema name. In Local I want to create a procedure that will call the above function. Here's the PL/SQL:
CREATE OR REPLACE PROCEDURE TEST
(
V_COUNT OUT NUMBER
)
AS
V_FOO NUMBER(2,0);
BEGIN
[Code]...
There's a Public Database Link called PER_ACC in Local. When I try to create this procedure I get: Encountered symbol "@" when expecting one of the following: .(*%&................
where my mistake is?
View 7 Replies
View Related
May 22, 2013
I have created a function for knowing the status of link which connect me with remote database.function is as follows
---------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION get_status_db_link (p_db_link_in VARCHAR2)
RETURN NUMBER AS
rows NUMBER;
v_code NUMBER;
v_errm VARCHAR2(64);
[code]....
Now functin should returns 1 when link is up and it should return 0 when link down but sometime when link is down it continues showing executing it hang my pc.
View 1 Replies
View Related
Sep 29, 2010
How to export the procedure,views,function and packages in a database, by using Export commmand.
View 2 Replies
View Related
Nov 17, 2011
I have created two types and a list of the first type:
create type type1;
/
create type type1_list as table of ref type1;
/
create type type2;
/
I have now just created the two types as follows:
create type type1 as object(
id# number
);
/
create type type2 as object(
attribute1 type1_list,
MEMBER FUNCTION function1 RETURN NUMBER
);
/
Ok, I've created the tables (I don't know if it's necessary to point out my problem)
CREATE TABLE type1_table OF type1;
/
CREATE TABLE type2_table OF type2
NESTED TABLE attribute1 STORE AS nested_type1_list_table;
/
And what I wanted to do now is to implement the member function1 and check something of the attributes of type1 in the list of attribute1... And that's where my question occurs, how does it work, I can't figure it out. I tried something like this:
Quote:
create or replace
TYPE BODY type2 AS
MEMBER FUNCTION function1 RETURN NUMBER AS
[Code]....
But I don't get the right way, it doesn't work
View 4 Replies
View Related
Jun 15, 2012
Is anyway to create function based index for group function columns.
For example
select max(timestamp),min(age),averge(sal).... ... .. from tab;
View 5 Replies
View Related
Feb 4, 2013
I have the following C code:
class Factorial {
public:
int getVal (int a);
};
[code]....
/When I am trying to execute this function always get the ORA-06521. I changed the data types - but nothing changed.
Just in case, listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
[code]....
View 6 Replies
View Related
Mar 11, 2010
What is the Difference between a Stand Alone Function/Procedure & a Function/Procedure declared in a Package.
View 2 Replies
View Related
Jun 10, 2010
What is advantage of Deterministic function over normal function?
What is the diff B/W Deterministic function and normal function and also give me a example in which scenario we use Deterministic function?
View 4 Replies
View Related
Apr 4, 2007
i have a query where i am using the max function to find the most recent record. What i want to do is use that query as part of an insert statement into a different table, however, i don't want to insert the column that i used the max function on. Is there anyway to use the max function without having the column it is being used on showing in the results?
View 1 Replies
View Related
