SQL & PL/SQL :: Inserting Tabs (ASCII 009) In Output
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
ADVERTISEMENT
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
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
Jul 26, 2011
shall we stop inserting data into a table before it inserting using Trigger?
View 3 Replies
View Related
Jun 13, 2012
In my application, I have SSO authentication. However, I also want to restrict access to certain pages (tabs). What would be the best approach for this? Secondly, How can we hide/display tabs in the application?
I am on 4.1/11g
View 5 Replies
View Related
Aug 13, 2012
Isn't it the case that clicking on a tab submits the current page, using the tab name as the request? I'm getting it to work some of the time but not always. In particular, it seems to work if the execution point is "On submission before computation" but not when the execution point is "After submission".
Here is what I did: I created a new application with two pages, each of which has a tab. Page 2 has nothing in it. Page 1 has an item, a button and two PL/SQL processes. Process 1 is unconditional, and sets the item to 10. Process 2 is conditional on the tab named "T_PAGE2", and increments the item by 5.
If both processes execute "on submission before computation" then everything works correctly:
-- If I click on the button, then the item is 10, indicating that only process 1 fired.
-- If I click on the tab to page 2, then the item is 15, indicating that both processes fired.
Now suppose I change them so that the processes execute "after submit". Then the button still works, but the tab doesn't:
-- If I click on the button, then the item is 10, as it should be.
-- If I reset the item value to 0 and click on the tab to page 2, then the item doesn't change, indicating that neither process fired.
Note that I have no computations or validations in my application. My only conclusion is that when you submit via a tab, there is some sort of internal validation occurring that keeps the processes from firing.
View 3 Replies
View Related
May 7, 2013
I am using APEX 4.0 and have a task of implementing the old tabs (older versions of APEX) for the IRR when they are saved. My users are not a fan of the select list when the IRRs are saved. I have located a site [URL] .....
that has this implemented but the tabs are not displaying within my application on apex.oracle.com, only the saved report names with no spacing. getting the tabs to display and/or possibly adding a dash (-) between each saved report name?
I added a Report Region as a 'PL/SQL Dynamic Content' and added code below in the Source. Am I missing anything to get the tabs to display like the example?
DECLARE
CURSOR cur_saved_ir (p_app_id IN NUMBER, p_page_id IN NUMBER, p_user IN VARCHAR2)
IS
SELECT '<span id="'||REPORT_ID||'" onclick="gReport.pull('''||REPORT_ID||''')">'||
[code]...
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
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
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
Apr 16, 2009
I have a tabbed canvas within Oracle Forms 5. On one tab I have several frames and text items. (Only they arent called items, but are called graphics which lie underneath the canvas.)
I have tried using the ...
SET_ITEM_PROPERTY(item_name, property, value)
...statement to hide the graphics, but it doesn't work.
What function would I have to use in order to hide graphics?
View 1 Replies
View Related
Jun 25, 2013
I have a report that displays Supplier Requirements (Gross) for the specified number of weeks in 10g. This Supplier Requirements Report (SRR) is displayed in excel format when the job is done through concurrent program. I have a requirement of "displaying gross requirements and netted requirements on a same spreadsheet in two different tabs when I run a concurrent program" where now only gross is displayed.
View 1 Replies
View Related
Jun 7, 2010
Is it possible that we can load the data from excel to forms with multiple tabs using DDE? I tried doing it manually, but is there a programatic way that we can do it?
View 1 Replies
View Related
Jun 5, 2013
We have created a lists (shared objects) with a couple of links which is displayed on all application pages. It is easy to maintain. My question is that the links open in the same page. I was hoping we could use something like a "Target=_New"..I guess the only other way would be to manually code this as a HTML region in each page using <a href> as the last resort!
View 3 Replies
View Related
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
Jan 6, 2010
We are playing with Toad 9.7 and seems like the right tools for Trade. The schema browser on left hand side I am keeping on Multi Tab type.The one problem is that when I select Table or Constraint from there, the tabs rearrange and is quite annoying to find another tab say View again since it has changed its location.
Is there any way to keep the TABS fixed to their location so when you select no changing of their position occur.
View 3 Replies
View Related
Dec 20, 2012
how to create tabs with drop down submenu items like the ones we have under products tab in the link below.
[URL]
View 7 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
Nov 17, 2012
Given:
- Apex 4.2
- Application Theme: Bluejay-22
- Page Template Class: Two Level Tabs
Required:
- Convert 2nd Level Tabs of (Bluejay-22 Theme) to be Drop-Down Menus as in (Application Builder Theme)
- Where can I find documentation for app_AppMenuMultiOpenBottom3
Current Situation
- Only the first level tabs are converted to match the (Application Builder Theme).
- The Drop-Down menus of the second level tabs are not working
Steps tried
1) link the following CSS's into the Header section of the Page Template of (Bluejay-22)
<link rel="stylesheet" href="/i/css/apex_builder.min.css?v=4.2.0.00.27" type="text/css" />
<link rel="stylesheet" href="/i/css/apex_ui.min.css?v=4.2.0.00.27" type="text/css" />
<link rel="stylesheet" href="/i/css/apex_ui_builder_home.css" type="text/css" />2) link the following Scripts into the Header section of the Page Template
<script type="text/javascript">
[code].....
View 16 Replies
View Related
Jul 1, 2013
DECLARE
JOBSFILE UTL_FILE.FILE_TYPE;
-- TAKE ALL JOB TITLES FROM JOBS
CURSOR JOBSCUR IS
SELECT *
-- DDOCNAME,DDOCTITLE,DSECURITYGROUP,DDOCAUTHOR,DDOCTYPE,DINDATE,PRIMARYFILE,EXTRACTIONDATE,BATCH_ID
FROM TARGET_UCM ;
[code].......
this is my plsql here to print table values i am using many utl_file.put_line statements is there any way to print all table values in a single utl_file.put_line.
View 2 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
Sep 25, 2013
Currently I have a requirement where I need to create 2 more output rows using each result row.
In my requirement I am populating charges table with types of charges, on each line item of charges, I need to apply 2 types of taxes and populate it along with the charge line item. I will be storing charges in table charges and the 2 taxes to be applied in taxes table respectively. For each row of charges, i need to apply these 2 taxes present in taxes table resulting in 3 rows output.
--Create tables charges
create table charges
(
charge_type varchar2(10) ,
charge number
);
[Code]....
My expected output should be like below:
Item_type amount
-------------------- ----------
charge1 100
Charge1_tax1 10
Charge1_tax2 20
charge2 200
Charge2_tax1 20
Charge2_tax2 40
how I can achieve the expected output using a single sql query
View 6 Replies
View Related
Aug 13, 2010
I have a view ( from many tables) , an error view in wich i monitor errors that appear in my project. This view should be empt always but whenever a error occurs the view shows me this. I wanna put a trigger or something like that on that view to send me an e-mail whenever a line is inserted in that view. ( I don't wanna use a job for that to make a count because this will affect the entire database).
View 3 Replies
View Related
Mar 10, 2012
My homework requires me to create a booking table for a hotel and I have created the table but I'm having trouble inserting the dates.
This is my table:
DROP TABLE BookingDM CASCADE CONSTRAINTS PURGE;
CREATE TABLE BookingDM (
hNo NUMBER(3),
gNo NUMBER(5),
dFrom DATE NOT NULL,
[code]......
This is the first set I'm attempting to insert
hNo = 148
gNo = 11169
dFrom = 09/03/2009
dTo = 09/10/2009
rNo = 202
This is my attempt to insert the set:
SQL> INSERT INTO BookingDM VALUES('148', '11169', '09/03/2009', '09/10/2009', '202');
INSERT INTO BookingDM VALUES('148', '11169', '09/03/2009', '09/10/2009', '202')
*
ERROR at line 1:
ORA-01843: not a valid month
I need my dates to be in the format "MM/DD/YYYY".
View 4 Replies
View Related
Jun 10, 2011
I created a Table with a single column varchar2.. in which I wanted to insert value like 'BBBBAB1'... till 'BBBBAB100'
Created a sequence starting with 1...
and inserted single row, and multiple rows using loop also; by using below code -
insert into Trans SELECT CONCAT('BBBBAB', Trans1.NEXTVAL) from dual;
but whenever I see the values they are not as required ... 'BBBBAB1' but one character 'B' is missing, and the values populating are 'BBBAB1'.. 'BBBAB100'
View 1 Replies
View Related
May 29, 2007
Ive added a new field to a table The new field is called Release_confirmation. How do I add the same value to this field for all rows ie. confirm.
So Id basically be setting Release_confirmation to confirm for all existing rows.
Should I use update or insert?
View 2 Replies
View Related