how to put quotes ina .sql file

UNIX for Advanced & Expert Users
1

Hi i am new on Unix..........
I have to write a ddl and for tables and I am using single quotes in the comment for the table and its column...........but when i see on the unix server the ' dont show .

e.g. DROP TABLE DMR_PURGED;

CREATE TABLE DMR_PURGED
(
HEALTH_SERVICE_ID NUMBER(20) NOT NULL,
HEALTH_SERVICE_ITEM_NO NUMBER(2) NOT NULL,
SYSTEM_CD VARCHAR(2) NOT NULL,
PURGED_DT DATE NOT NULL,
REC_CREATE_TIMESTAMP DATE NOT NULL
);

COMMENT ON TABLE DMR_PURGED IS 'This stores purged member claims from PBS';

COMMENT ON COLUMN DMR_PURGED.HEALTH_SERVICE_ID IS 'Health Service ID';

COMMENT ON COLUMN DMR_PURGED.HEALTH_SERVICE_ITEM_NO IS 'Health Service Item Number';

COMMENT ON COLUMN DMR_PURGED.SYSTEM_CD IS 'System Code';

COMMENT ON COLUMN DMR_PURGED.PURGED_DT IS 'Purged Date';

COMMENT ON COLUMN DMR_PURGED.REC_CREATE_TIMESTAMP IS 'Record Create Timestamp';

CREATE OR REPLACE TRIGGER TRG_DMR_PURGED_TS
BEFORE UPDATE OR INSERT
ON DMR_PURGED
FOR EACH ROW
BEGIN

the comment line quotes either dont show or comes out as special character........plz help

2

How are you composing the file?

If you edit the file on the UNIX box with vi does the problem go away?

3

[quote=agarwalniru]
Hi i am new on Unix..........
I have to write a ddl and for tables and I am using single quotes in the comment for the table and its column...........but when i see on the unix server the ' dont show .

Ran this code -

more run.ksh

sqlplus <<ENDIT
$(cat $HOME/.dw_plus_logon.sec)

CREATE TABLE DMR_PURGED
(
HEALTH_SERVICE_ID NUMBER(20) NOT NULL,
HEALTH_SERVICE_ITEM_NO NUMBER(2) NOT NULL,
SYSTEM_CD VARCHAR(2) NOT NULL,
PURGED_DT DATE NOT NULL,
REC_CREATE_TIMESTAMP DATE NOT NULL
);

COMMENT ON TABLE DMR_PURGED IS 'This stores purged member claims from PBS';

COMMENT ON COLUMN DMR_PURGED.HEALTH_SERVICE_ID IS 'Health Service ID';

COMMENT ON COLUMN DMR_PURGED.HEALTH_SERVICE_ITEM_NO IS 'Health Service Item Number';

COMMENT ON COLUMN DMR_PURGED.SYSTEM_CD IS 'System Code';

COMMENT ON COLUMN DMR_PURGED.PURGED_DT IS 'Purged Date';

COMMENT ON COLUMN DMR_PURGED.REC_CREATE_TIMESTAMP IS 'Record Create Timestamp';

quit
ENDIT

-------------------------------------------------------------------------
Looks like it worked - I saw these results -

select * from user_col_comments
where table_name like 'DMR%'

TABLE_NAME COLUMN_NAME COMMENTS

DMR_PURGED HEALTH_SERVICE_ID Health Service ID
DMR_PURGED HEALTH_SERVICE_ITEM_NO Health Service Item Number
DMR_PURGED SYSTEM_CD System Code
DMR_PURGED PURGED_DT Purged Date
DMR_PURGED REC_CREATE_TIMESTAMP Record Create Timestamp
------------------------------------------------------------------

But there may be parameters you need to check - like some of the NLS parameters in Oracle.

4

thats the whole problem......i m trying to edit it via Vi editor on unix and it doesnt let me edit.
so i making the changes ona notepad and FTP on unix....it still gives special characters in place of quotes

5

the DBA got back to me saying that fo rthem it showed special chracters instead of quotes in the comment line........actually i created this doc in notepad an dthen FTP on unix server

6

On the unix machine try:
tr -d '\r' < file.txt > file

7

Agarwalniru,
If you have the text in Notepad, the quick solution is to just
copy and paste it to unix vi.

8

Do you know how to use vi?

What are the files attributes, is it read-only?

How does vi complain?

What about nano, emacs or any other editor you have?

What about a text editor running under "X"?

What languages/code pages/character sets etc is your Windows box using?

9

No i did not know how to use Vi , But i just now figured it out and was able to edit my script .........finally.
But i really appreciate your help!!! thanks!!

10

Pasting in Vi didnt help .......it showed . in place of ' .........so i edited my script in Vi, i did not know how to use Vi , But i just now figured it out
But i really appreciate your help!!! thanks!!