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
Hi all, Need some help with a query.  I'm trying to create a stored procedure that is sort of like a Customer, Order, Order, Details.  In my situation the tables are different but nevertheless, I want to grab all the fields from the  Parent, Child, a [More]
I have a summer 2012 mac book pro running Mac OSX 10.7.4 (this is my first mac product, I bought it like a month ago so I know much). I am intersted in being able to boot Windows 7 though Apple Boot Camp. Windows 7 professional is avialable to me for [More]
Get free stuff from http://www.points2shop.com/?ref=Brit very easily!. Points2shop is a website that allows you to earn points, which you can redeem for ANY prize of your choice on Amazon. They give you 250 points to begin with (equivalent to $2.50) [More]
Can photos be transferred back and forth from/to iPhoto and an external drive? If so, how?When in iPhoto for iOS you can share your photo to iTunes and then connect your iPad to your computer and use the file sharing window to move the photo to any c [More]
Hello, I've looked at the other postings about this but I'm still not having any luck....and I'm not really sure who to call for tech support. I can connect my laptop to the Hotspot just fine. The desktop computer is another story. I bought a Mediali [More]
I've created a newsletter in Pages'06 using the Club newsletter template. After making some modifications,I wanted to swap the positions of pages 3 and 4 but when I select page 3 in the thumbnails on the left, pages 2,3,4 and 5 are all selected. If I [More]
Is there an airplay icon in the iPhoto app for the iPhone 4s and ipad2, I cannot seem to be able to find on.  Is the only way to stream to the apple tv by mirroring the device? Is there a plan to add AirPlay feature to the iPhoto app?Double press the [More]
Hi. R12.1.3 Does AR offer any alternative to processing AR payments other than Lockbox?  Is there an API or some other mechanism? Thanks.Hi, Sure, you can try using AR public APIs. Here are some examples, depending on what you want to do with your re [More]
I'd like to have a go at producing an APP with adobe cc where to start and where to find good low level tutorials I have no pre existing knowledge just finished a DW tutor guided websites and have used fireworks and indesign again to a low knowledge [More]
I've registered the XML Schema, created the table based on the schema and inserted values from the table. I'm having trouble in extracting the values from the table. Here are the steps that I've done. Thanks for the help in advance. BEGIN DBMS_XMLSCH [More]