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

Convert Oracle Sqlplus Output Into ASCII Format?

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

Inserting Character From Extended ASCII Character Set?

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

SQL & PL/SQL :: Stop Inserting Data Into A Table Before It Inserting Using Trigger?

Jul 26, 2011

shall we stop inserting data into a table before it inserting using Trigger?

View 3 Replies View Related

Application Express :: Hide / Display Tabs?

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

Application Express :: Submitting A Page Via Tabs

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

Application Express :: 4.0 - IR Saved Reports As Tabs?

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

SQL & PL/SQL :: Converting Binary To Ascii

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

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 View Related

SQL & PL/SQL :: Extended Ascii To Character

Jan 27, 2011

I have requirement to convert the extended ascii to character. Is there any function available .

View 9 Replies View Related

SQL & PL/SQL :: Delete Non-ASCII Characters?

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

SQL & PL/SQL :: How To Convert String To Ascii

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

Hiding Graphics (text / Frames) When Switching Between Tabs?

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

Oracle Report That Should Display / Two Tabs On A Same Spreadsheet In Excel

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

Forms :: Load Data From Excel With Multiple Tabs Using DDE?

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

Application Express :: Open Lists In New Tabs / Pages?

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

SQL & PL/SQL :: Why Replace Function Is Not Replacing - ASCII Value Being Zero

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

Client Tools :: Toad Schema Browser Multi Tabs

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

Application Express :: How To Create Tabs With Drop Down Submenu Items

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

SQL & PL/SQL :: How To Find Extended ASCII Characters From A String

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

Application Express :: Export To ASCII File?

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

Server Utilities :: SQL Loader Is Not Uploading Non-ASCII Characters?

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

Application Express :: Convert Tabs In The 2nd Level To Drop-Down Menu As App Builder Theme

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

SQL & PL/SQL :: How To Print The Multiple Output Statements In Single Output

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

Server Utilities :: How To Process ASCII Character 29 While Loading Data With SQL Loader

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

SQL & PL/SQL :: Splitting 1 Output Row Into 3 Output Rows

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

Inserting In A View

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

Inserting Dates Into Table

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

Inserting Values From A Sequence?

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

Oracle SQL Plus Inserting Into A New Field?

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







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