Home » SQL & PL/SQL » SQL & PL/SQL » Help creating tab delimited file from sql
Help creating tab delimited file from sql [message #287533] Wed, 12 December 2007 09:37 Go to next message
jordan_dba
Messages: 19
Registered: May 2007
Location: SF, CA
Junior Member


I am trying to create a tab delimited file from a view.. below is my script but I get an ORA-0094: invalid identifier message.

alter session set nls_date_format='mm/dd/yyy HH12:MI AM';
select '\&DATEFMT=MM/DD/YYYY HH12:MI AM' from dual;
select '\&PARAMETERS="PRODUCT NAME"|"UPC"|"SALE PRICE"|AUTHOR|"AMG ID"|"MUSE ID"|"ISBN"|"AVAILABILITY"|"BUY URL"|"RETAILER SKU"|"IMAGE URL"|"MANUFACTURER NAME"|"PRODUCT DESCRIPTION"|"REGULAR PRICE"|"SHIPPING COST"|"RETAILER CATEGORY"|"SHIPPING PROMO TEXT"|"PROMOTIONAL TEXT"|"RETAILER NAME"|"BRAND STATEMENT"|"REGULAR PRICE GBP"|"SALE PRICE GBP"|"REGULAR PRICE JPY"|"SALE PRICE JPY"|"REGULAR PRIC
E EUR"|"SALE PRICE EUR"|"PRODUCT SHORT NAME"|"STRENGTH"|"COUNT"|"FORM"|"SERVING SIZE"|"WARNING"|"NOT CONTAIN"|"DIRECTION"|"INGREDIENTS"' from dual;
select "PRODUCT NAME"||
chr(9)||"UPC"||
chr(9)||"SALE PRICE"||
chr(9)||"AUTHOR"||
chr(9)||"AMG ID"||
chr(9)||"MUSE ID"||
chr(9)||"ISBN"||
chr(9)||"AVAILABILITY"||
chr(9)||"BUY URL"||
chr(9)||"RETAILER SKU"||
chr(9)||"IMAGE URL"||
chr(9)||"MANUFACTURER NAME"||
chr(9)||"PRODUCT DESCRIPTION"||
chr(9)||"REGULAR PRICE"||
chr(9)||"SHIPPING COST"||
chr(9)||"RETAILER CATEGORY"||
chr(9)||"SHIPPING PROMO TEXT"||
chr(9)||"PROMOTIONAL TEXT"||
chr(9)||"RETAILER NAME"||
chr(9)||"BRAND STATEMENT"||
chr(9)||"REGULAR PRICE GBP"||
chr(9)||"SALE PRICE GBP"||
chr(9)||"REGULAR PRICE JPY"||
chr(9)||"SALE PRICE JPY"||
chr(9)||"REGULAR PRICE EUR"||
chr(9)||"SALE PRICE EUR"||
chr(9)||"PRODUCT SHORT NAME"||
chr(9)||"STRENGTH"||
chr(9)||"COUNT"||
chr(9)||"FORM"||
chr(9)||"SERVING SIZE"||
chr(9)||"WARNING"||
chr(9)||"NOT CONTAIN"||
chr(9)||"DIRECTION"||
chr(9)||"INGREDIENTS"
FROM extract_test_data;



chr(9)||"INGREDIENTS"
*
ERROR at line 35:
ORA-00904: "INGREDIENTS": invalid identifier
Re: Help creating tab delimited file from sql [message #287535 is a reply to message #287533] Wed, 12 December 2007 09:39 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
SQL> select "empno" from emp;
select "empno" from emp
       *
ERROR at line 1:
ORA-00904: "empno": invalid identifier


SQL> desc emp;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(4)
.....

SQL> select empno from emp;

     EMPNO
----------
      7369
      7499
      7521
      7566
....


Try different options..

Regards

Raj

P.S : Oops, I missed the basic bit. Anything you enclose in double quotes will be treated as it is in the sense it will be treated with the same case and it should match exactly the same. Also, what i noticed is you have given a column name with spaces in between. Unless and until you have chosen the hard way to create the column something like this

SQL> create table test123 ("abc efg" number);

Table created.

SQL> desc test123;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 abc efg                                            NUMBER

SQL> 


[Updated on: Wed, 12 December 2007 09:42]

Report message to a moderator

Re: Help creating tab delimited file from sql [message #287543 is a reply to message #287535] Wed, 12 December 2007 10:32 Go to previous message
jordan_dba
Messages: 19
Registered: May 2007
Location: SF, CA
Junior Member
Thanks.. that resolved my issue. Columns in quotes had to match exactly as the view.
Previous Topic: sql%rowcount not working as expected. 0 rowcount?
Next Topic: Needs Help about Query
Goto Forum:
  


Current Time: Sat Dec 10 07:10:28 CST 2016

Total time taken to generate the page: 0.09728 seconds