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.
I have a string in this format '2011/06/01 00:00:00'. How do I convert this to date format.I tried to_date,to_char and they give errors invalid number & literal does not math format string. I don't have much control on the string since that is the way is comes from the application.
I have a column which has has all numeric values 3 digits to 7 digits in length but all in string format, I want to use seomthing other than to_number function.
Would like to use within the ETL tool, where to_number function isn't getting recognized.
i have a column 'name' in which value is 'Shailesh Negi',i have to insert 'shailesh' into'first name' column and 'Negi' into 'last name' column respectively.
What I want to do is, translate '10100010' into the string '(Bananas,Apples,Kiwi)' using SQL-only. As a bonus it would be nice if the string is completely empty if all flags are '0', so no '()'.
We have a forms application in Forms 10g. We scan a document and gets it to a image item in the Form. Now, we need to pass this image to a 3rd party application (document mgt. system, ). They have given us a web-service for this. We have to convert the image to a BASE64 string and then invoke the web-service method and input it.
The, 3rd party application document management system will also send images as BASE64 strings through the web-service. We need to convert them back to a blob and put to an image item in the form so that it can be viewed by the end-user.
So, how can we convert a image item in form (as BLOB) to a base64 string and also do the reverse???
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
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.
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?
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?
I'm facing some problem even after using INSTR function in Oracle.The problem is I have written the logic in the PL/SQL block which appends all the values fetched in a loop on the basis of whether the string is present or not.
For ex:
The first value fetched from the select query first is ABCDEFG which gets appended to a variable The next value fetched is AB even this has to be appended to the variable since this exactly doesn't match with ABCDEFG. The next value fetched is BCDEF even this has to be appended to the variable since this exactly doesn't match with ABCDEFG. The third Value fetched is ABCDEFG this will not get appended presently according to the logic which is correct.
writing that piece of code to append the value fetched which doesn't exactly match with the existing string
How to get 'MEAL' string? The length of the string can be various. Means, 'MEAL' can be 'INFLIGHT'. So, i cant use the substr. Is there a function that can recognize the pipeline? so that i can remove all the string before the pipeline and after the pipeline to get the string between the pipeline?
I wrote the one query which is return the following output..
Query:
SELECT RMC_N_ID,NVL(DISCOUNT_N_ID,NULL) AS DISCOUNT_N_ID,NVL(NULL,'') AS LOADINGS_N_ID,NVL(NULL,'') AS WARCOMB_N_ID,NVL(NULL,'') AS ENDORSECOMB_N_ID FROM UWRMC_DISCOUNT WHERE RMC_N_ID =224 AND DISCOUNT_N_ID IS NOT NULL UNION SELECT RMC_N_ID,NVL(NULL,'') AS DISCOUNT_N_ID,NVL(LOADING_N_ID,NULL) AS LOADING_N_ID ,NVL(NULL,'') AS WARCOMB_N_ID,NVL(NULL,'') AS ENDORSECOMB_N_ID FROM UWRMC_LOADING WHERE RMC_N_ID =224 UNION SELECT RMC_N_ID,NVL(NULL,'') AS DISCOUNT_N_ID,NVL(NULL,NULL) AS LOADING_N_ID ,WARCOMB_N_ID,NVL(NULL,'') AS ENDORSECOMB_N_ID FROM UWRMC_WARCOMB WHERE RMC_N_ID =224 UNION SELECT RMC_N_ID,NVL(NULL,'') AS DISCOUNT_N_ID,NVL(NULL,NULL) AS LOADING_N_ID ,NVL(NULL,'') AS WARCOMB_N_ID, ENDORSECOMB_N_ID FROM UWRMC_ENDORSECOMB WHERE RMC_N_ID =224
my data looks like:- Ename Job salary ----- ----- ------- A manager 10000 B clerk 60000 C assistant 30000 A manager 50000 B clerk 20000 D accountant 40000
I want the result while select all data should look like:-
A B C D manager clerk assistant accountant 10000 60000 30000 40000 50000 20000
