Sitemap

Amicuk Programming Answers

Select data fields with delimiter

2015-10-11   Views:0

Advertisement

hi, im new to oracle. I'm working on unix oracle 11g. I need to know the way to fetch records with delimiters. I'd worked with informix before, and thr i use UNLOAD TO <filename> SELECT.... Is there any equivalent one in oracle. I just need the data

hi,
im new to oracle. I'm working on unix oracle 11g. I need to know the way to fetch records with delimiters. I'd worked with informix before, and thr i use UNLOAD TO <filename> SELECT.... Is there any equivalent one in oracle. I just need the data, nt even the column names.
Format required-
data1|data2|data3
data11|data22|data33
Pls help.

The replay answer
Advertisement
The above answer will give data you can spool to a file in SQL*Plus.
If you want to write the data to a file from within PL/SQL, and you need something a little more generic to use for different queries, you can use something along these lines...
As sys user:
CREATE OR REPLACE DIRECTORY TEST_DIR AS '\tmp\myfiles'
GRANT READ, WRITE ON DIRECTORY TEST_DIR TO myuser
/As myuser:
CREATE OR REPLACE PROCEDURE run_query(p_sql IN VARCHAR2
                                     ,p_dir IN VARCHAR2
                                     ,p_header_file IN VARCHAR2
                                     ,p_data_file IN VARCHAR2 := NULL) IS
  v_finaltxt  VARCHAR2(4000);
  v_v_val     VARCHAR2(4000);
  v_n_val     NUMBER;
  v_d_val     DATE;
  v_ret       NUMBER;
  c           NUMBER;
  d           NUMBER;
  col_cnt     INTEGER;
  f           BOOLEAN;
  rec_tab     DBMS_SQL.DESC_TAB;
  col_num     NUMBER;
  v_fh        UTL_FILE.FILE_TYPE;
  v_samefile  BOOLEAN := (NVL(p_data_file,p_header_file) = p_header_file);
BEGIN
  c := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(c, p_sql, DBMS_SQL.NATIVE);
  d := DBMS_SQL.EXECUTE(c);
  DBMS_SQL.DESCRIBE_COLUMNS(c, col_cnt, rec_tab);
  FOR j in 1..col_cnt
  LOOP
    CASE rec_tab(j).col_type
      WHEN 1 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,2000);
      WHEN 2 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_n_val);
      WHEN 12 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_d_val);
    ELSE
      DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,2000);
    END CASE;
  END LOOP;
  -- This part outputs the HEADER
  v_fh := UTL_FILE.FOPEN(upper(p_dir),p_header_file,'w',32767);
  FOR j in 1..col_cnt
  LOOP
    v_finaltxt := ltrim(v_finaltxt||','||lower(rec_tab(j).col_name),',');
  END LOOP;
  --  DBMS_OUTPUT.PUT_LINE(v_finaltxt);
  UTL_FILE.PUT_LINE(v_fh, v_finaltxt);
  IF NOT v_samefile THEN
    UTL_FILE.FCLOSE(v_fh);
  END IF;
  -- This part outputs the DATA
  IF NOT v_samefile THEN
    v_fh := UTL_FILE.FOPEN(upper(p_dir),p_data_file,'w',32767);
  END IF;
  LOOP
    v_ret := DBMS_SQL.FETCH_ROWS(c);
    EXIT WHEN v_ret = 0;
    v_finaltxt := NULL;
    FOR j in 1..col_cnt
    LOOP
      CASE rec_tab(j).col_type
        WHEN 1 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_v_val);
                    v_finaltxt := ltrim(v_finaltxt||',"'||v_v_val||'"',',');
        WHEN 2 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_n_val);
                    v_finaltxt := ltrim(v_finaltxt||','||v_n_val,',');
        WHEN 12 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_d_val);
                    v_finaltxt := ltrim(v_finaltxt||','||to_char(v_d_val,'DD/MM/YYYY HH24:MI:SS'),',');
      ELSE
        v_finaltxt := ltrim(v_finaltxt||',"'||v_v_val||'"',',');
      END CASE;
    END LOOP;
  --  DBMS_OUTPUT.PUT_LINE(v_finaltxt);
    UTL_FILE.PUT_LINE(v_fh, v_finaltxt);
  END LOOP;
  UTL_FILE.FCLOSE(v_fh);
  DBMS_SQL.CLOSE_CURSOR(c);
END;This allows for the header row and the data to be written to seperate files if required.
e.g.
SQL> exec run_query('select * from emp','TEST_DIR','output.txt');
PL/SQL procedure successfully completed.Output.txt file contains:
empno,ename,job,mgr,hiredate,sal,comm,deptno
7369,"SMITH","CLERK",7902,17/12/1980 00:00:00,800,,20
7499,"ALLEN","SALESMAN",7698,20/02/1981 00:00:00,1600,300,30
7521,"WARD","SALESMAN",7698,22/02/1981 00:00:00,1250,500,30
7566,"JONES","MANAGER",7839,02/04/1981 00:00:00,2975,,20
7654,"MARTIN","SALESMAN",7698,28/09/1981 00:00:00,1250,1400,30
7698,"BLAKE","MANAGER",7839,01/05/1981 00:00:00,2850,,30
7782,"CLARK","MANAGER",7839,09/06/1981 00:00:00,2450,,10
7788,"SCOTT","ANALYST",7566,19/04/1987 00:00:00,3000,,20
7839,"KING","PRESIDENT",,17/11/1981 00:00:00,5000,,10
7844,"TURNER","SALESMAN",7698,08/09/1981 00:00:00,1500,0,30
7876,"ADAMS","CLERK",7788,23/05/1987 00:00:00,1100,,20
7900,"JAMES","CLERK",7698,03/12/1981 00:00:00,950,,30
7902,"FORD","ANALYST",7566,03/12/1981 00:00:00,3000,,20
7934,"MILLER","CLERK",7782,23/01/1982 00:00:00,1300,,10The procedure allows for the header and data to go to seperate files if required. Just specifying the "header" filename will put the header and data in the one file.
Adapt to output different datatypes and styles are required.

Go to See the other 13 answers

Select data fields with delimiter

Category:DefaultRelease time:2015-10-11Views:130

hi, im new to oracle. I'm working on unix oracle 11g. I need to know the way to fetch records with delimiters. I'd worked with informix before, and thr i use UNLOAD TO <filename> SELECT.... Is there any equivalent one in oracle. I just need the data[More]

Multi select date field

Category:DefaultRelease time:-0001-11-30Views:130

Hi, Is there any idea how to combine multi select DATE field in the check-in screen? Thanks, eran.Dates are stored using a DATE field in the database in a database date format. As such, multiple values can not be stored in the same field, only single[More]

Error to select data field of same length and same type.

Category:DefaultRelease time:-0001-11-30Views:130

Hi, I have to select data from VBRK , VBRP and then from table CE4UCA1_ACCT based on the condition AKTBO   IN SO_AKTBO           AND PAOBJNR = LT_VBRK_VBRP-POSNR  AND PASUBNR IN SO_RKBNR           AND PRCTR   IN SO_PRCTR. So while coding it gave a sy[More]

Selecting Date fields from selectOneChoice box

Category:DefaultRelease time:-0001-11-30Views:130

Hi all i am having the below problem. i have a SelctOneChioce Box which has a list of TimeStampObject from a View object. i am getting a below error when i selct any values from the drop down list and submit the page. *Cannot convert 2011-08-03 00:00[More]

Visio 2010 - Shape Data Fields Are Not Saving

Category:DefaultRelease time:2015-10-11Views:130

I am having a problem with a number of shape data fields which are not saving data that has been entered into it, whether by entering it through the "shape data" or "define shape data" dialog boxes, or by editing the "shape sheet&[More]

Selection date in Delivery creation

Category:DefaultRelease time:-0001-11-30Views:130

Hello, If the schedule line is not confirmed in sales order (due to zero atp stock i think ) and if I try to create a delivery, system will give a response that " no schedule lines are due for delivery  with a yellow question mark against the message[More]

Select-options for date field.

Category:DefaultRelease time:2015-10-11Views:130

Hi all, i need to give select options for Date field.How can i give that. Thanks & Regards Ravi.Hi Ravi, Use the Component WDR_SELECT_OPTIONS to include select options in Web Dynpro ABAP. Follow these steps: 1. In your Component , "Used Componet&[More]

Date field not updated when select on F4 value on editable ALV Grid

Category:DefaultRelease time:2015-10-11Views:130

Can some one look into this to see what was wrong. I have a report that display fields extracted from a ZTable and display on an ALV Editable Grid. The data are displayed as read only mode for these fields: Field A -   type char20 and have a search h[More]

How to select data from a table using a date field in the where condition?

Category:DefaultRelease time:2015-10-11Views:130

How to select data from a table using a date field in the where condition? For eg: data itab like equk occurs 0 with header line. select * from equk into table itab where werks = 'C001'                                                   and bdatu = '3[More]

In AP invoice i need to make "Terms date " field to calculate the date automatically when i select the payment terms

Category:DefaultRelease time:2015-10-11Views:130

In AP invoice i need to make "Terms date " field to calculate the date automatically when i select the payment terms. How can i do this?Hi, You can select the Payment Term at PO Header Level. When you book an invoice, that Payment Term will auto[More]

Date field on Selection Screen

Category:DefaultRelease time:2015-10-11Views:130

Hi All, I need to create Date field (From and To) in the selection screen. The Date format should appear as YYYY-MM-DD. I have defined this requirement as below. select-options: s_date for sy-datum no-extension default sy-datum. Due to above line, da[More]

Hot
I bought windows 8 .1 machine from factory. Then I had local shop install 8.1 on larger ssd. I just performed latest bios update and now machine will not boot. I am sure this is uefi related but I am not knowledgable in that area. The boot parameter [More]
How to you return an app if you don't like after downloading it?You can try contacting Apple Support for iTunes Store at the link below: https://expresslane.apple.comRead other 3 answers [More]
I am new to the mac world and have been finding it interesting... I just started playing around with iwork 09 and when i export to a word document it will only allow me to convert as a word 97 document. I am losing tons of info doing this. Is there a [More]
I just downloaded 300 pictures from a CD.  The pictures only show up in the "last import", they don't show up in "photos", or "events".  In addition, when I go to Costco site to upload photos, none of my 2012 photos are showi [More]
Hi I'm trying to create an OD replica but it won't stick and I get the following in my log file Aug 8 16:14:15 racer slapd[4763]: @(#) $OpenLDAP: slapd 2.2.19 $\n Aug 8 16:14:15 racer slapd[4763]: bdbbackinitialize: Sleepycat Software: Berkeley DB 4. [More]
I successfully installed a Panasonic BL-C131A Network Camera on my network and can view it on my iPhone or over an internet connection in Safari or Firefox. The only trouble is that I cannot hear the sound output while streaming video from the camera [More]
Hi,     I want to know the field name and Table Name  for Employee Qualification i found the table P0022-AUSBI .Here shows only number like 43,44,49,50. How can i get the text for particular number and also which table i want to link it.Can you help [More]
I am trying to start the text off the page and scroll onto the page; however, I cannot figure it out. I followed the SourceSansPro file and copied the exact layout, but on my website the text does not scroll onto the screen.  Any help?Sorry, it's the [More]
Hi I'm exporting my book from InDesign CC 2014 to fixed layout epub, but some of the text gets squished together (or spaces seem to be left out) I have removed overrides and tried different fonts to no avail. Any ideas? Andy Grogan - An Accidental En [More]
http://www.oracle.com/technetwork/database/express-edition/downloads/index.html Interesting that the production XE license agreement says: "You must accept the OTN License Agreement for Oracle Database Express Edition 11g Release 2 to download this s [More]