Link to home
Start Free TrialLog in
Avatar of Sathish David  Kumar N
Sathish David Kumar NFlag for India

asked on

why my pl/sql program not running

what is wrong in the code

declare
cursor c1 is
select  '1' "a" ,'2' "b" ,'3' "c"  from dual ;
begin
for i in  c1
loop
--open c1;
dbms_output.put_line(i.a);
--close c1;
end loop;

end;


its giving error like

                       *
ERROR at line 8:
ORA-06550: line 8, column 24:
PLS-00103: Encountered the symbol "a" when expecting one of the following:
<an identifier> <a double-quoted delimited-identifier> delete
exists prior
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

double quotes force case sensitivity.

try:

...
select  '1' a ,'2' b ,'3' c  from dual ;
...

or

...
dbms_output.put_line(i."a");
...
Avatar of Sathish David  Kumar N

ASKER

both or giving error
Make one change or the other.  Not both.
ya 2 nd one works....

how can use multiple for loop

eg:
for i in  c1 1..2 like that

 i need for loop excute 2 time
declare
cursor c1 is
select  '1' "a" ,'2' "b" ,'3' "c"  from dual WHERE ROWNUM<3;
begin
for i in 1..2 c1
loop
--open c1;
dbms_output.put_line(i."a");
--close c1;
end loop;

end;
>>ya 2 nd one works....

You really should not use double quotes around object names in Oracle.  It will get you into trouble later.

Personally I would have gone with the first option.

>> i need for loop excute 2 time

No, you cannot do that.

Why do you need to loop through the records twice?

>>select  '1' "a" ,'2' "b" ,'3' "c"  from dual WHERE ROWNUM<3;

There is only one row in DUAL so ROWNUM<3 does nothing.


What real problem are you trying to solve?
i need to  fetching data  twise for writing in xml
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
<student>
<studentname>
<mark>
<sub1>10</sub1>
<sub1>20</sub1>
<sub1>20</sub1>
</mark>
</studentname>
<studentname>
<mark>
<sub1>10</sub1>
<sub1>20</sub1>
<sub1>20</sub1>
</mark>
</studentname>
</student>
This my sample xml file i need to genrate i get only one row mark i need to hard code student name how to do that !!

<student>
<studentname>Sathish</studentname>
<mark>
<sub1>10</sub1>
<sub1>20</sub1>
<sub1>20</sub1>
</mark>
<studentname>kumar</studentname>
<mark>
<sub1>10</sub1>
<sub1>20</sub1>
<sub1>20</sub1>
</mark>
</student>
DECLARE
v_filename      VARCHAR2 (255);
V_XML_CONTENT   VARCHAR2 (4000);
v_filehandle    UTL_FILE.file_type;
CURSOR C1 IS
SELECT '2000' "v1",'01014337048' "v2" ,'087' "v3", 'SGD' "v4",'IT11031204202838' "V5",'120420' "v6" FROM EMPLOYEES
WHERE ROWNUM < 3;
begin

   v_filename := TO_CHAR (SYSDATE, 'YYYYMMDD_HH24MI') || '.xml';
   v_filehandle := UTL_FILE.fopen ('F:\sekar', v_filename, 'W');
for i in 1..2
loop
v_xml_content := v_xml_content || '<stuentname>' || CHR (10);
v_xml_content := v_xml_content || '<mark>' || CHR (10);
v_xml_content :=v_xml_content  || '<sub1>' || CHR (10);
v_xml_content := v_xml_content || '<sub2>' || CHR (10);
UTL_FILE.put_line (v_filehandle, v_xml_content);
UTL_FILE.fclose (v_filehandle);
END LOOP;
end;
any update/??
I see you have asked a new question.  I can no longer post here or it becomes a duplicate.

I suggest you close this since it is about why the pl/SQL will not run and that has been answered.
any update
Did you read my last post?

Also, posts in a question are only available to Experts that have already posted.  Posting asking for updates does nothing to help.