Topic: SQL*Plus and PL/SQL >> Help Required Urgently
|
Disclaimer: The purpose of all dbapool forums including OCP and Other Oracle Certification forums is to help each other with specific issues but not to share dump and copyrighted exam content, materials or intellectual property.
You may review the entire Oracle Certification Program Candidate Agreement online Here.
|
|
|
|
| Title: Help Required Urgently |
| Jolly |
Posted: Jul 17, 2007 12:30:10 AM |
Total Post: 24
Joined: Mar, 2007
|
Hi All,
I want to create a csv file through form by calling sql file. But I am getting an error that it cannot create a spool file. The following the scripts:
1. Through Forms WHEN_BUTTON_PRESSED:
declare
f_user iicats.config_changes.m_user%TYPE;
file_name VARCHAR2(30);
exp_dir varchar2(4) := 'U:\';
CURSOR RTU_BATCH_LIST IS
SELECT RTU_ID
FROM IICATS.RTU
WHERE rtu_id LIKE :CONTROl.rtu_id
ORDER BY 1;
begin
if :control.rtu_id is null then
bell;
message('You must select an rtu');
else
if :control.rtu_id is not null then
file_name := exp_dir||:control.rtu_id||'_help'|| '.csv';
HOST('PLUS33.EXE -S ' || Get_Application_Property(USERNAME) || '/' || Get_Application_Property(PASSWORD) || '@iicats @u:\alarm_help :control.rtu_id'|| ' ' || file_name);
end if;
end if;
end;
2. The SQL File
SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 500
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SPOOL u:\&2
select 'Rtu name,Point Id,Type,Derivation,Alarm Help,Alarm Description,Additional Description,Action No,Action,Despatch No,Despatch,'
from dual
/
(SELECT '"'||IICATS.ANAL_INTEG_INPUT.RTU_ID||'",'||'"'||IICATS.ANAL_INTEG_INPUT.POINT_ID||'",'||'"'||IICATS.ANAL_INTEG_INPUT.TYPE||'",'||'"'||
IICATS.ANAL_INTEG_INPUT.DERIVATION||'",'||'"'||IICATS.STD_ALARM_HELP.ALARM_HELP||'",'||'"'||IICATS.STD_ALARM_HELP.ALARM_DESCRIPT||'",'||'"'||
IICATS.STD_ALARM_HELP.ADDITNL_DESCRIPT||'",'||'"'||IICATS.ACTION.PTY_NO||'",'||'"'||IICATS.ACTION.ACTION||'",'||'"'||
IICATS.DESPATCH.DES_NO||'",'||'"'||IICATS.DESPATCH.DESPATCH||'",'
FROM IICATS.STD_ALARM_HELP,
IICATS.ACTION, IICATS.DESPATCH, IICATS.RTU, IICATS.ANAL_INTEG_INPUT
WHERE (IICATS.ANAL_INTEG_INPUT.RTU_ID NOT LIKE 'TEST%'
AND IICATS.ANAL_INTEG_INPUT.FUTURE='U'
AND (IICATS.ANAL_INTEG_INPUT.HI_4_STATUS='A'
OR IICATS.ANAL_INTEG_INPUT.HI_3_STATUS='A'
OR IICATS.ANAL_INTEG_INPUT.HI_2_STATUS='A'
OR IICATS.ANAL_INTEG_INPUT.HI_1_STATUS='A'
OR IICATS.ANAL_INTEG_INPUT.LO_1_STATUS='A'
OR IICATS.ANAL_INTEG_INPUT.LO_2_STATUS='A'
OR IICATS.ANAL_INTEG_INPUT.LO_3_STATUS='A'
OR IICATS.ANAL_INTEG_INPUT.LO_4_STATUS='A'
OR IICATS.ANAL_INTEG_INPUT.DERIVATION='R')
AND IICATS.ANAL_INTEG_INPUT.RTU_ID NOT LIKE 'TOG%'
AND IICATS.ANAL_INTEG_INPUT.RTU_ID NOT LIKE 'TEST%'
AND IICATS.ANAL_INTEG_INPUT.RTU_ID NOT LIKE '%RTM%'
AND IICATS.ANAL_INTEG_INPUT.GROUP_ID NOT LIKE 'Z%'
AND IICATS.ANAL_INTEG_INPUT.GROUP_ID NOT LIKE '%MAINT%'
AND IICATS.ANAL_INTEG_INPUT.GROUP_ID NOT LIKE 'NET%'
AND IICATS.ANAL_INTEG_INPUT.GROUP_ID NOT LIKE 'NSOOS%'
AND IICATS.ANAL_INTEG_INPUT.RTU_ID LIKE ':control.rtu_id')
AND ((IICATS.ANAL_INTEG_INPUT.ALARM_HELP=IICATS.STD_ALARM_HELP.ALARM_HELP)
AND (IICATS.STD_ALARM_HELP.PTY_NO=IICATS.ACTION.PTY_NO)
AND (IICATS.STD_ALARM_HELP.DESPATCH_NO=IICATS.DESPATCH.DES_NO)
AND (IICATS.RTU.RTU_ID=IICATS.ANAL_INTEG_INPUT.RTU_ID))
UNION
SELECT '"'||IICATS.DIGITAL_INPUT.RTU_ID||'",'||'"'||IICATS.DIGITAL_INPUT.POINT_ID||'",'||'"'||IICATS.DIGITAL_INPUT.TYPE||'",'||'"'||
IICATS.DIGITAL_INPUT.DERIVATION||'",'||'"'||IICATS.STD_ALARM_HELP.ALARM_HELP||'",'||'"'||IICATS.STD_ALARM_HELP.ALARM_DESCRIPT||'",'||'"'||
IICATS.STD_ALARM_HELP.ADDITNL_DESCRIPT||'",'||'"'||IICATS.ACTION.PTY_NO||'",'||'"'||IICATS.ACTION.ACTION||'",'||'"'||
IICATS.DESPATCH.DES_NO||'",'||'"'||IICATS.DESPATCH.DESPATCH||'",'
FROM IICATS.STD_ALARM_HELP,
IICATS.ACTION, IICATS.DESPATCH, IICATS.RTU, IICATS.DIGITAL_INPUT
WHERE ((IICATS.DIGITAL_INPUT.MNEM1_STAT='A'
OR IICATS.DIGITAL_INPUT.MNEM2_STAT='A'
OR IICATS.DIGITAL_INPUT.MNEM3_STAT='A'
OR IICATS.DIGITAL_INPUT.MNEM4_STAT='A')
AND IICATS.DIGITAL_INPUT.FUTURE='U'
AND IICATS.DIGITAL_INPUT.RTU_ID NOT LIKE 'TOG%'
AND IICATS.DIGITAL_INPUT.RTU_ID NOT LIKE 'TEST%'
AND IICATS.DIGITAL_INPUT.RTU_ID NOT LIKE '%RTM%'
AND IICATS.DIGITAL_INPUT.GROUP_ID NOT LIKE 'Z%'
AND IICATS.DIGITAL_INPUT.GROUP_ID NOT LIKE '%MAINT%'
AND IICATS.DIGITAL_INPUT.GROUP_ID NOT LIKE 'NET%'
AND IICATS.DIGITAL_INPUT.GROUP_ID NOT LIKE 'NSOOS%'
AND IICATS.DIGITAL_INPUT.RTU_ID LIKE ':control.rtu_id')
AND ((IICATS.DIGITAL_INPUT.ALARM_HELP=IICATS.STD_ALARM_HELP.ALARM_HELP)
AND (IICATS.STD_ALARM_HELP.PTY_NO=IICATS.ACTION.PTY_NO)
AND (IICATS.STD_ALARM_HELP.DESPATCH_NO=IICATS.DESPATCH.DES_NO)
AND (IICATS.RTU.RTU_ID=IICATS.DIGITAL_INPUT.RTU_ID)))
ORDER BY 1
/
spool off
Now in the sql statement the first select statement is to put the heading.
When I press the button It gives me the error cannot create spool file and it stops after extecting first sql statement.
Pls give me an idea where I am wrong and how to proceed to get this working.
Thanks and regards
Jolly |
|
|
 Nand |
| Posted: Jul 17, 2007 06:36:08 AM |  |
|
Total Post: 22
Joined: Jun, 2007
|
In the SQL File
>> SPOOL u:\&2
make sure this drive exists, or better change it to some other location. Like c:\&2.
Regards
Nand
|
|
|
|
|
| Jolly |
| Posted: Jul 17, 2007 10:27:31 PM | |
|
Total Post: 24
Joined: Mar, 2007
|
Hi Nand,
Thanks for your reply. It worked well. Now I am facing some problem with the same sql statement. I have added couple of union statement so the sql statement looks like
(SELECT '"'||IICATS.ANAL_INTEG_INPUT.RTU_ID||'",'||'"'||IICATS.ANAL_INTEG_INPUT.POINT_ID||'",'||'"'||IICATS.ANAL_INTEG_INPUT.TYPE||'",'||'"'||
IICATS.ANAL_INTEG_INPUT.DERIVATION||'",'||'"'||IICATS.STD_ALARM_HELP.ALARM_HELP||'",'||'"'||IICATS.STD_ALARM_HELP.ALARM_DESCRIPT||'",'||'"'||IICATS.STD_ALARM_HELP.ADDITNL_DESCRIPT||'",'||'"'||IICATS.ACTION.PTY_NO||'",'||'"'||IICATS.ACTION.ACTION||'",'||'"'||IICATS.DESPATCH.DES_NO||'",'||'"'||IICATS.DESPATCH.DESPATCH||'",'
FROM IICATS.STD_ALARM_HELP,
IICATS.ACTION, IICATS.DESPATCH, IICATS.RTU, IICATS.ANAL_INTEG_INPUT
WHERE (IICATS.ANAL_INTEG_INPUT.FUTURE='U'
AND (IICATS.ANAL_INTEG_INPUT.HI_4_STATUS='A'
OR IICATS.ANAL_INTEG_INPUT.HI_3_STATUS='A'
OR IICATS.ANAL_INTEG_INPUT.HI_2_STATUS='A'
OR IICATS.ANAL_INTEG_INPUT.HI_1_STATUS='A'
OR IICATS.ANAL_INTEG_INPUT.LO_1_STATUS='A'
OR IICATS.ANAL_INTEG_INPUT.LO_2_STATUS='A'
OR IICATS.ANAL_INTEG_INPUT.LO_3_STATUS='A'
OR IICATS.ANAL_INTEG_INPUT.LO_4_STATUS='A'
OR IICATS.ANAL_INTEG_INPUT.DERIVATION='R')
AND IICATS.ANAL_INTEG_INPUT.RTU_ID LIKE '&1')
AND ((IICATS.ANAL_INTEG_INPUT.ALARM_HELP=IICATS.STD_ALARM_HELP.ALARM_HELP)
AND (IICATS.STD_ALARM_HELP.PTY_NO=IICATS.ACTION.PTY_NO)
AND (IICATS.STD_ALARM_HELP.DESPATCH_NO=IICATS.DESPATCH.DES_NO))
UNION
SELECT '"'||IICATS.DIGITAL_INPUT.RTU_ID||'",'||'"'||IICATS.DIGITAL_INPUT.POINT_ID||'",'||'"'||IICATS.DIGITAL_INPUT.TYPE||'",'||'"'||
IICATS.DIGITAL_INPUT.DERIVATION||'",'||'"'||IICATS.STD_ALARM_HELP.ALARM_HELP||'",'||'"'||IICATS.STD_ALARM_HELP.ALARM_DESCRIPT||'",'||'"'||IICATS.STD_ALARM_HELP.ADDITNL_DESCRIPT||'",'||'"'||IICATS.ACTION.PTY_NO||'",'||'"'||IICATS.ACTION.ACTION||'",'||'"'||IICATS.DESPATCH.DES_NO||'",'||'"'||IICATS.DESPATCH.DESPATCH||'",'
FROM IICATS.STD_ALARM_HELP,
IICATS.ACTION, IICATS.DESPATCH, IICATS.RTU, IICATS.DIGITAL_INPUT
WHERE ((IICATS.DIGITAL_INPUT.MNEM1_STAT='A'
OR IICATS.DIGITAL_INPUT.MNEM2_STAT='A'
OR IICATS.DIGITAL_INPUT.MNEM3_STAT='A'
OR IICATS.DIGITAL_INPUT.MNEM4_STAT='A'
OR IICATS.DIGITAL_INPUT.DERIVATION='R')
AND IICATS.DIGITAL_INPUT.FUTURE='U'
AND IICATS.DIGITAL_INPUT.RTU_ID LIKE '&1')
AND ((IICATS.DIGITAL_INPUT.ALARM_HELP=IICATS.STD_ALARM_HELP.ALARM_HELP)
AND (IICATS.STD_ALARM_HELP.PTY_NO=IICATS.ACTION.PTY_NO)
AND (IICATS.STD_ALARM_HELP.DESPATCH_NO=IICATS.DESPATCH.DES_NO))
UNION
SELECT '"'||IICATS.ANAL_INTEG_INPUT.RTU_ID||'",'||'"'||IICATS.ANAL_INTEG_INPUT.POINT_ID||'",'||'"'||IICATS.ANAL_INTEG_INPUT.TYPE||'",'||'"'||
IICATS.ANAL_INTEG_INPUT.DERIVATION||'",'||'"'||IICATS.ANAL_INTEG_INPUT.ALARM_HELP||'",'||'"'||" "||'",'||'"'||" "||'",'||'"'||" "||'",'||'"'||" "||'",'||'"'||" "||'",'||'"'||" "||'",'
FROM IICATS.ANAL_INTEG_INPUT
WHERE (IICATS.ANAL_INTEG_INPUT.FUTURE='U'
AND (IICATS.ANAL_INTEG_INPUT.HI_4_STATUS='A'
OR IICATS.ANAL_INTEG_INPUT.HI_3_STATUS='A'
OR IICATS.ANAL_INTEG_INPUT.HI_2_STATUS='A'
OR IICATS.ANAL_INTEG_INPUT.HI_1_STATUS='A'
OR IICATS.ANAL_INTEG_INPUT.LO_1_STATUS='A'
OR IICATS.ANAL_INTEG_INPUT.LO_2_STATUS='A'
OR IICATS.ANAL_INTEG_INPUT.LO_3_STATUS='A'
OR IICATS.ANAL_INTEG_INPUT.LO_4_STATUS='A'
OR IICATS.ANAL_INTEG_INPUT.DERIVATION='R')
AND IICATS.ANAL_INTEG_INPUT.RTU_ID LIKE '&1')
AND IICATS.ANAL_INTEG_INPUT.ALARM_HELP LIKE 'No%')
UNION
SELECT '"'||IICATS.DIGITAL_INPUT.RTU_ID||'",'||'"'||IICATS.DIGITAL_INPUT.POINT_ID||'",'||'"'||IICATS.DIGITAL_INPUT.TYPE||'",'||'"'||
IICATS.DIGITAL_INPUT.DERIVATION||'",'||'"'||IICATS.DIGITAL_INPUT.ALARM_HELP||'",'||'"'||" "||'",'||'"'||" "||'",'||'"'||" "||'",'||'"'||" "||'",'||'"'||" "||'",'||'"'||" "||'",'
FROM IICATS.DIGITAL_INPUT
WHERE ((IICATS.DIGITAL_INPUT.MNEM1_STAT='A'
OR IICATS.DIGITAL_INPUT.MNEM2_STAT='A'
OR IICATS.DIGITAL_INPUT.MNEM3_STAT='A'
OR IICATS.DIGITAL_INPUT.MNEM4_STAT='A'
OR IICATS.DIGITAL_INPUT.DERIVATION='R')
AND IICATS.DIGITAL_INPUT.FUTURE='U'
AND IICATS.DIGITAL_INPUT.RTU_ID LIKE '&1')
AND IICATS.DIGITAL_INPUT.ALARM_HELP LIKE 'No%')))
Now when I execute the above statement I get following error
ERROR at line 61:
ORA-00933: SQL command not properly ended.
Thanks and regards
Jolly
|
|
|
|
|
| Jolly |
| Posted: Jul 18, 2007 12:57:54 AM | |
|
Total Post: 24
Joined: Mar, 2007
|
Hi
I had solved the issues with the sql statement I had earlier. Now could you please help me in this statement
UPDATE ACTION
SET ACTION = REPLACE(ACTION,'"',''')
The above statement should replace all " with '. But when I try to execute it I am getting error. Pls be kind enough to give me correct syntax for this.
Thanks and regards
Jolly
|
|
|
|
|
| Nand |
| Posted: Jul 18, 2007 02:39:10 AM | |
|
Total Post: 22
Joined: Jun, 2007
|
When replacing " with ' ......use ''
This is the correct line.
UPDATE ACTION SET ACTION = REPLACE(ACTION,'"','''')
Regards
Nand
|
|
|
|
|
| Vijay |
| Posted: Jul 18, 2007 06:32:06 AM | |
|
Total Post: 12
Joined: Jul, 2007
|
Dear Jolly/Nand,
In the latest Oracle 10G. This can be handled more easily.
The Update SQL statement:
UPDATE ACTION SET ACTION = REPLACE(ACTION,'"','''')
can now be re-written as:
UPDATE ACTION SET ACTION = REPLACE(ACTION,q'["]',q'[']');
This alternative quoting mechanism is new feature added in the Oracle 10G.
Regards,
Vijay Mahawar
|
|
|
|
|
| Jolly |
| Posted: Jul 18, 2007 06:20:20 PM | |
|
Total Post: 24
Joined: Mar, 2007
|
Hi All,
Thanks for your kind help which is really appreciated.
Thanks and regards
Jolly
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
|
|
|
|
Forum Rules & Description
Who Can Read The Forum? Any registered user or guest
Who Can Post New Topics? Any registered user
Who Can Post Replies? Any registered user
|
| |
Get FREE Magazines
|
Top 10 Forum User
|
| Murtuja Khokhar | 857 |
| Mohammed Taj | 746 |
| positive fanatic | 483 |
| Jayanta Sur | 479 |
| Vigyan Kaushik | 386 |
| Gopu Gopi | 333 |
| Gitesh Trivedi | 322 |
| Vinoth Kumar | 264 |
| neeraj sharma | 258 |
| Ramesh Jois | 246 |
|
|
