Binding works in VB. See the code below where there two methods - one using command object (binding) and another using dynamic sql. You will see that the command object results in 2 hard parses, whereas dynamic sql results in 100 *hard* parses. If you are testing over and over, remember to change the 'i = x to y' with new values of x & y because the old values will be in the oracle cache.

This works only with the Oracle provider and not with MSDAORA. The best solution of course is to move to a SP.


Results from program :
Command object Stats
parse count (total) 205
parse count (hard) 2
execute count 104
Dynamic SQL Stats
parse count (total) 205
parse count (hard) 100
execute count 104



... and the code:
Private Sub Form_Load()

Dim Cn As New ADODB.Connection
Dim rs As Recordset
Dim sSQL As String
Dim cmd As Command
Dim statsSQL As String

statsSQL = "select a.name name, b.value " & _
"from v$statname a, v$mystat b " & _
"Where a.statistic# = b.statistic# " & _
"and a.name in " & _
" ('parse count (total)', " & _
" 'parse count (hard)', " & _
" 'execute count') "


Cn.Open "Provider=OraOLEDB.Oracle.1;Data source=oradb_sh;User ID=scott;Password=tiger;"

sSQL = "select * from emp where empno = ?"

For i = 6001 To 6100
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = Cn
cmd.CommandText = sSQL
cmd.CommandType = adCmdText
cmd.Parameters.Append cmd.CreateParameter(, adVarNumeric, adParamInput, , i)

Set rs = cmd.Execute
If rs.EOF And rs.BOF Then
' ensure recordset is parsed
End If
rs.Close
Set rs = Nothing: Set cmd = Nothing
Next i

Set rs = Cn.Execute(statsSQL)

Debug.Print "Command object Stats"
Do While Not rs.EOF
Debug.Print rs(0), rs(1)
rs.MoveNext
Loop
rs.Close
Set Cn = Nothing


Cn.Open "Provider=OraOLEDB.Oracle.1;Data source=oradb_sh;User ID=scott;Password=tiger;"

For i = 6101 To 6200
sSQL = "select * from emp where empno = " & i
Set rs = Cn.Execute(sSQL)
If rs.EOF And rs.BOF Then
' ensure recordset is parsed
End If
Set rs = Nothing
Next i


Debug.Print "Dynamic SQL Stats"
Set rs = Cn.Execute(statsSQL)

Do While Not rs.EOF
Debug.Print rs(0), rs(1)
rs.MoveNext
Loop
rs.Close

End Sub

"OraOLEDB validates and fetches the metadata only for SELECT SQL statements. "

This statement is killing us.

It is in the "Oracle Provider for OLE DB Developer's Guide" version 9.2.0.4.8 (latest and greatest), Chapter 2 - Features of OraOLEDB under the section "Preparing Commands".

We have a VB application that utilizes the ADO.Recordset.save method and passing it the "adPersistXML" parameter to generate XML from the database.

This application was originally written to interface with SQL Server and we are converting it to Oracle.

When executing a procedure from VB, the METADATA about the tables and columns involved in the selection of the rowset *is not returned*!
If you simply embed a SQL statement in the VB code, then the adPersistXML parameter retrieves the metadata properly, and generates the XML just fine. Here is an outtake of the XML generated:

<s:AttributeType name='EMPLOYEE_ID' rs:number='1' rs:writeunknown='true' rs:basetable='EMPLOYEES' rs:basecolumn='EMPLOYEE_ID'>
<s:datatype dt:type='number' rs:dbtype='numeric' dt:maxLength='19' rs:scale='0' rs:precision='6' rs:fixedlength='true'
rs:maybenull='false'/>

The stored procedure execution simply leaved out the rs:basetable= and rs:basecolumn=.

I have exhausted google groups, MS technet, and Metalink knowledgebases.

I am submitting this question to metalink Oracle OLE DB provider forum, and a TAR, because it is urgent that we figure this out.

I know we can do this lots of different ways, but this is the way it is written to work, and finding this answer would be the easiest path to success. We are investigating generating the XML directly from Oracle, (which we know we can do, but we are also shaping this data using the msdatashape provider, and that makes it more difficult to duplicate exactly)(We are looking at the XDK to see if this is feasible)

I have also tried MDAC 2.8 MSDAORA, and Oracle's latest ODBC drivers. Maybe I am missing a setting from one of these drivers that magically returns the metadata!?

Of course PLSQLRSet = TRUE in the connection object. Remember, the procedure returns the data fine to VB, its just that the metadata is gone!

Please help.

I opened a tar, and the support individual thinks it may be a bug.

Thanks for reading it anyway!

Sorry for jumping in on this...

Just curious about 2 things:

- Why would it be a bug if it is functioning as documented?

- What do you expect to see as far as metadata for a stored procedure? If you drop down to OCI and do a describe on a stored procedure, you will get info like number of parameters, names of parameters, types of parameters, etc... is that what you are expecting to receive via OleDB? Doing a describe on a stored procedure will not tell you what tables/views it uses, etc...

Anyway, just curious,

- Mark

...the ref cursor is not being "described"

as in dbms_sql.parse/dbms_sql.describe_columns I guess?

Anyway, I was surprised that Oracle support may consider it a bug since it appears to be functioning as documented (unless I am totally misreading the documentation or problem)...

- Mark

Here is *the* response from Oracle support regarding this issue.
**********************************************************
Called customer back. Explained that the full information being returned was actually due to a typo in the code on this end. When this was corrected, the basetable and basecolumn information was NOT returned, even when using strongly typed cursors.

Read to him the response from development when this issue was posed to them:

"Oracle REF Cursors do not provide the base table name and base column name information. This is more of a OCI/PLSQL limitation rather than OLE DB provider limitation and that's the reason you see the same behavior with Microsoft as well as Oracle provider." -- As well as ODBC which isn't mentioned.

Customer's concern: they need the base table and base column metadata. They send this information to their thin client application, where's it's manipulated and then used to build insert statements. These insert statements cannot be built without the table and column information.
**********************************************************

If you read my previous post carefully, you will understand what we are doing.

We are trying to pass a result set from Oracle to a calling VB program. You must use REF CURSORS to accomplish this, yes? The VB program uses the "ADORecordset.save" method with a passed of "adPersistXML", such that the result set *returned in the form of a REF CURSOR from an Oracle stored procedure* is changed into XML automagically into a predefined format (That is already coded for). That generated XML lacks base table name and base column names.

If you embed a straight SQL statement into your VB code, the meta-data we are looking for appears.

And you understand that *no one* wants to embed SQL in their VB code. So - since REF CURSORS are the only option of returning result sets to a VB program, and this meta-data is not returned we are in a bit of a quagmire because of the amount of unexpected work before us, and the limited amount of time we have to perform it.

It seems to me that REF CURSORs should indeed return this information. I'm an Oracle bigot, but this is a bummer.

Of course, it could be argued that we should be using java or some other technology to do this work, but we really don't have a choice as this application is already written, and we are just converting it to use Oracle.

The painful thing here is I've been an Oracle DBA for over 6 years now (I know that's not that long, but it's long enough). Coming to this project, everyone is a SQL Server bigot. I've shown them some of the lackings of SQL Server compared to Oracle, but of course, they'll have nothing of them. And so I'm constantly hearing "Oracle stinks", "I'm so furstrated", "WHAaaa Whaaaaaa WHaaaaaaaaaaa". And now, when I need Oracle software to come through for me to put and end to these whining baby cries.. . . .alas. No meta-data from REF CURSORS.

lol

Work Rules!

Because of the above, we are now going to generate our XML right out of Oracle and pass it through the system.

We've tried using the Oracle supplied packages to get the XML with the schema, but found the following:

DBMS_XMLGEN currently only allows:
dtdOrSchema IN number := NONE

and though the Supplied package pdf says right above DBMS_XMLQUERY, not to use it if you can help it, we tried it with metaType = SCHEMA, but the base table names and base column names don't come out.

Here is some sample code from our try with DBMS_XMLQUERY, as DBMS_XMLGEN doesn't return schemas yet.


Sample code:

CREATE OR REPLACE
FUNCTION simplexml
RETURN CLOB
IS
BEGIN

return DBMS_XMLQUERY.GETXML( ' SELECT * FROM emp where rownum < 2', dbms_xmlquery.schema );

END;
/

select simplexml from dual;


So what do we do if we want schema information to be generated also?

Any help would be more than appriciated.

I have continued my Oracle TAR on the matter, but thought this audience could benefit.

Thanks for the opportunity Tom.

I asked Sean Dillon, our local XML technologist, to take a look at this and here's what he had to say:
-----------

Hi Justin,

When I do this on 9.2.0.1.0 and above it works swimmingly.  That being said, let the support analyst working your TAR know that this test case produces an accurate result on 9.2.0.1, so maybe it's a db version issue.  My results:

SQL> !cat test.sql

create or replace function simplexml
  return clob
is
begin
  return dbms_xmlquery.getxml('select * from scott.emp where rownum < 2',
                              dbms_xmlquery.schema );
end;
/

select simplexml from dual
/

SQL> @test
Function created.

SIMPLEXML
--------------------------------------------------------------------------------
<?xml version = '1.0'?>
<DOCUMENT xmlns:xsd="

   <xsd:schema xmlns:xsd="

      <xsd:element name="ROWSET">
         <xsd:complexType>
            <xsd:sequence>
               <xsd:element name="ROW" minOccurs="0" maxOccurs="unbounded">
                  <xsd:complexType>
                     <xsd:sequence>
                        <xsd:element name="EMPNO" type="xsd:integer" minOccurs="0"/>
                        <xsd:element name="ENAME" type="xsd:string" nullable="true" minOccurs="0"/>
                        <xsd:element name="JOB" type="xsd:string" nullable="true" minOccurs="0"/>
                        <xsd:element name="MGR" type="xsd:integer" nullable="true" minOccurs="0"/>
                        <xsd:element name="HIREDATE" type="xsd:string" nullable="true" minOccurs="0"/>
                        <xsd:element name="SAL" type="xsd:float" nullable="true" minOccurs="0"/>
                        <xsd:element name="COMM" type="xsd:float" nullable="true" minOccurs="0"/>
                        <xsd:element name="DEPTNO" type="xsd:integer" nullable="true" minOccurs="0"/>
                     </xsd:sequence>
                     <xsd:attribute name="num" type="xsd:integer"/>
                  </xsd:complexType>
               </xsd:element>
            </xsd:sequence>
         </xsd:complexType>
      </xsd:element>
   </xsd:schema>
   <ROWSET xmlns:xsi="

xsi:noNamesp
aceSchemaLocation="#/DOCUMENT/xsd:schema[not(@targetNamespace)]">
      <ROW num="1">
         <EMPNO>7369</EMPNO>
         <ENAME>SMITH</ENAME>
         <JOB>CLERK</JOB>
         <MGR>7902</MGR>
         <HIREDATE>12/17/1980 0:0:0</HIREDATE>
         <SAL>800</SAL>
         <DEPTNO>20</DEPTNO>
      </ROW>
   </ROWSET>
</DOCUMENT>

Hope that helps!

_smd_ 

Justin,

Thanks for all the info... Though I can do nothing to help you it was nonetheless interesting to read through it all. I understand what you are wanting to do and why, I was just surprised that something that appeared to me to be working as documented (i.e. only with select statements - no stored procedures for you!) would be labeled a bug by support. As you have no doubt seen there are several similar posts on MetaLink (including your own thread) on this very topic.

Best of luck from a fellow Oracle bigot!

- Mark

I have found a good way to be much more clear in my question. When I mentioned schema information, I guess I meant "extended schema information".

Thanks very much for your response though!

I have changed the example a bit to express a more real world situation. A normal complex query will probably use column aliases in it's construction, so I added some to show what happens.

Here is the code:

***********************************************************
justin@DEV> set lines 150
justin@DEV> CREATE OR REPLACE
2 FUNCTION simplexml
3 RETURN CLOB
4 IS
5 BEGIN
6
7 return DBMS_XMLQUERY.GETXML( ' SELECT empno "emp.empno"
8 , ename "e.ename"
9 , job "e.job"
10 , mgr "e.mgr"
11 , hiredate "e.hiredate"
12 , sal "e.sal"
13 , comm "e.comm"
14 , deptno "e.deptno"
15 FROM emp where rownum < 2'
16 , dbms_xmlquery.schema );
17
18 END;
19 /

Function created.

Elapsed: 00:00:00.00
justin@DEV>
justin@DEV> select simplexml from dual;

SIMPLEXML
======================================================================================================================================================
<?xml version = '1.0'?>
<DOCUMENT xmlns:xsd="</code> http://www.w3.org/2000/10/XMLSchema" >

   <xsd:schema xmlns:xsd="

http://www.w3.org/2000/10/XMLSchema" >

      <xsd:element name="ROWSET">
         <xsd:complexType>
            <xsd:sequence>
               <xsd:element name="ROW" minOccurs="0" maxOccurs="unbounded">
                  <xsd:complexType>
                     <xsd:sequence>
                        <xsd:element name="e.empno" type="xsd:integer" minOccurs="0"/>
                        <xsd:element name="e.ename" type="xsd:string" nullable="true" minOccurs="0"/>
                        <xsd:element name="e.job" type="xsd:string" nullable="true" minOccurs="0"/>
                        <xsd:element name="e.mgr" type="xsd:integer" nullable="true" minOccurs="0"/>
                        <xsd:element name="e.hiredate" type="xsd:string" nullable="true" minOccurs="0"/>
                        <xsd:element name="e.sal" type="xsd:float" nullable="true" minOccurs="0"/>
                        <xsd:element name="e.comm" type="xsd:float" nullable="true" minOccurs="0"/>
                        <xsd:element name="e.deptno" type="xsd:integer" nullable="true" minOccurs="0"/>
                     </xsd:sequence>
                     <xsd:attribute name="num" type="xsd:integer"/>
                  </xsd:complexType>
               </xsd:element>
            </xsd:sequence>
         </xsd:complexType>
      </xsd:element>
   </xsd:schema>
   <ROWSET xmlns:xsi="

http://www.w3.org/2000/10/XMLSchema-instance"

xsi:noNamespaceSchemaLocation="#/DOCUMENT/xsd:schema[not(@targetNamespace)]">
      <ROW num="1">
         <e.empno>7369</e.empno>
         <e.ename>SMITH</e.ename>
         <e.job>CLERK</e.job>
         <e.mgr>7902</e.mgr>
         <e.hiredate>12/17/1980 0:0:0</e.hiredate>
         <e.sal>800</e.sal>
         <e.deptno>20</e.deptno>
      </ROW>
   </ROWSET>
</DOCUMENT>

***********************************************************

2 things are problems with this output in our situation.

1) The *base table names* and *base column names* are not included in the output.

We need the base table names and base column names in the XML attributes because we persist this XML file to a thin-client for them to manipulate. When they manipulate it, we build insert or update statements from the XML to execute against the database, and as such, we must know the base table names, and base column names each of these elements have come from.

Furthermore, we tried using our full table names as aliases so that we could simply get the base table name and base column name from the <name=> attribute, but we have run into Oracle's 30 character identifier limit, which makes that impossible for us.

2) The hiredate column is being expressed as type="xsd:string". 


So, the more clear questions are:

1) How do we get base table names and base column names in the output? Like this:

<?xml version = '1.0'?>
<DOCUMENT xmlns:xsd="

http://www.w3.org/2000/10/XMLSchema" >

   <xsd:schema xmlns:xsd="

http://www.w3.org/2000/10/XMLSchema" >

      <xsd:element name="ROWSET">
         <xsd:complexType>
            <xsd:sequence>
               <xsd:element name="ROW" minOccurs="0" maxOccurs="unbounded">
                  <xsd:complexType>
                     <xsd:sequence>
                        <xsd:element name="e.empno" type="xsd:integer" basetable='EMP' basecolumn='EMPNO' minOccurs="0"/>
                        <xsd:element name="e.ename" type="xsd:string" basetable='EMP' basecolumn='ENAME' nullable="true" minOccurs="0"/>
                        <xsd:element name="e.job" type="xsd:string" basetable='EMP' basecolumn='JOB' nullable="true" minOccurs="0"/>
                        <xsd:element name="e.mgr" type="xsd:integer" basetable='EMP' basecolumn='MGR' nullable="true" minOccurs="0"/>
                        <xsd:element name="e.hiredate" type="xsd:date" basetable='EMP' basecolumn='HIREDATE' nullable="true" minOccurs="0"/>
                        <xsd:element name="e.sal" type="xsd:float" basetable='EMP' basecolumn='SAL' nullable="true" minOccurs="0"/>
                        <xsd:element name="e.comm" type="xsd:float" basetable='EMP' basecolumn='COMM' nullable="true" minOccurs="0"/>
                        <xsd:element name="e.deptno" type="xsd:integer" basetable='EMP' basecolumn='DEPTNO' nullable="true" minOccurs="0"/>
                     </xsd:sequence>
                     <xsd:attribute name="num" type="xsd:integer"/>
                  </xsd:complexType>
               </xsd:element>
            </xsd:sequence>
         </xsd:complexType>
      </xsd:element>
   </xsd:schema>
   <ROWSET xmlns:xsi="

http://www.w3.org/2000/10/XMLSchema-instance" <code>xsi:noNamespaceSchemaLocation="#/DOCUMENT/xsd:schema[not(@targetNamespace)]">
<ROW num="1">
<e.empno>7369</e.empno>
<e.ename>SMITH</e.ename>
<e.job>CLERK</e.job>
<e.mgr>7902</e.mgr>
<e.hiredate>12/17/1980 0:0:0</emp.hiredate>
<e.sal>800</e.sal>
<e.deptno>20</e.deptno>
</ROW>
</ROWSET>
</DOCUMENT>

2) And why is a date being shown as a *string* and not a date field? Seems like that should come out as a Date.

as "xmlns:xsd"),
         xmlelement ("xsd:schema",
           xmlattributes ('

as "xmlns:xsd"),
           xmlelement ("xsd:element",
             xmlattributes ('ROWSET' as "name"),
             xmlelement ("xsd:sequence",
               xmlelement ("xsd:element",
                 xmlattributes ('ROW' as "name",
                                '0' as "minOccurs",
                                'unbounded' as "maxOccurs"),
                 xmlelement ("xsd:complexType",
                   xmlelement ("xsd:sequence",
                     xmlelement ("xsd:element",
                       xmlattributes ('EMPNO' as "name",
                                      'xsd:integer' as "type",
                                      'SCOTT.EMP' as "baseTable",
                                      'EMPNO' as "baseColumn",
                                      '0' as "minOccurs")
                     ),
                     xmlelement ("xsd:element",
                       xmlattributes ('ENAME' as "ename",
                                      'xsd:string' as "type",
                                      'true' as "nullable",
                                      'SCOTT.EMP' as "baseTable",
                                      'ENAME' as "baseColumn",
                                      '0' as "minOccurs")
                     ),
                     xmlelement ("xsd:element",
                       xmlattributes ('JOB' as "ename",
                                      'xsd:string' as "type",
                                      'true' as "nullable",
                                      'SCOTT.EMP' as "baseTable",
                                      'JOB' as "baseColumn",
                                      '0' as "minOccurs")
                     )
                   )
                 )
               )
             )
           )
         ),
         xmlelement ("ROWSET",
           xmlagg (
             xmlelement ("ROW",
               xmlforest (
                 empno as "EMPNO",
                 ename as "ENAME",
                 job as "JOB"
               )
             )
           )
         )
       ) as "DOC"
  from emp
/

...which yields...

<DOCUMENT xmlns:xsd="

<xsd:schema xmlns:xsd="

That has opened my eyes.

Thanks very much for the effort!

Sean,

I have been on a long road with XML generation out of Oracle.

We were using DBMS_XMLQuery to return our XML. The reason we were using it was because it seems to be the only option for returning XML *with* schema information. I was ordered to benchmark Oracle's generation of XML vs. using the ADO parameter called adPersistXML (which generates XML from a result set returned to VB) and found that the generation of XML using DBMS_XMLQuery was significantly slower than if we embed our SQL statements in our VB code.
Like orders of magnitudes slower, and then on top of that, we had to apply XSL to create our much needed basetable name and basecolumn name information.

Your above response to my dilemma was much appreciated, and you mention I could wrap the above select in some code to automate it.

I tried this attempt at placing your select into a stored proc, and it bombs with an ORA-600... not good.

justin@DEV> var x varchar2

justin@DEV> create or replace procedure getvendorlist(p_ans OUT varchar2)
2
3 as
4 v_sql varchar2(10000);
5 begin
6
7 v_sql:='
8 select xmlelement ("DOCUMENT",
9 xmlattributes (''</code> http://www.w3.org/2000/10/XMLSchema''

as "xmlns:xsd"),
 10           xmlelement ("xsd:schema",
 11             xmlattributes (''

http://www.w3.org/2000/10/XMLSchema''

as "xmlns:xsd"),
 12             xmlelement ("xsd:element",
 13               xmlattributes (''ROWSET'' as "name"),
 14               xmlelement ("xsd:sequence",
 15                 xmlelement ("xsd:element",
 16                   xmlattributes (''ROW'' as "name",
 17                                  ''0'' as "minOccurs",
 18                                  ''unbounded'' as "maxOccurs"),
 19                   xmlelement ("xsd:complexType",
 20                     xmlelement ("xsd:sequence",
 21                       xmlelement ("xsd:element",
 22                         xmlattributes (''EMPNO'' as "name",
 23                                        ''xsd:integer'' as "type",
 24                                        ''SCOTT.EMP'' as "baseTable",
 25                                        ''EMPNO'' as "baseColumn",
 26                                        ''0'' as "minOccurs")
 27                       ),
 28                       xmlelement ("xsd:element",
 29                         xmlattributes (''ENAME'' as "ename",
 30                                        ''xsd:string'' as "type",
 31                                        ''true'' as "nullable",
 32                                        ''SCOTT.EMP'' as "baseTable",
 33                                        ''ENAME'' as "baseColumn",
 34                                        ''0'' as "minOccurs")
 35                       ),
 36                       xmlelement ("xsd:element",
 37                         xmlattributes (''JOB'' as "ename",
 38                                        ''xsd:string'' as "type",
 39                                        ''true'' as "nullable",
 40                                        ''SCOTT.EMP'' as "baseTable",
 41                                        ''JOB'' as "baseColumn",
 42                                        ''0'' as "minOccurs")
 43                       )
 44                     )
 45                   )
 46                 )
 47               )
 48             )
 49           ),
 50           xmlelement ("ROWSET",
 51             xmlagg (
 52               xmlelement ("ROW",
 53                 xmlforest (
 54                   empno as "EMPNO",
 55                   ename as "ENAME",
 56                   job as "JOB"
 57                 )
 58               )
 59             )
 60           )
 61         ) as "DOC"
 62    from emp';
 63
 64  execute immediate(v_sql) into p_ans;
 65
 66  end getvendorlist;
 67  /

Procedure created.

justin@DEV> exec getvendorlist(:x)
BEGIN getvendorlist(:x); END;

*
ERROR at line 1:
ORA-00600: internal error code, arguments: [koksghds1], [], [], [], [], [], [], []
ORA-06512: at "JUSTIN.GETVENDORLIST", line 64
ORA-06512: at line 1


That's a little messy for my taste. 

I have written this code as a function also, and it bombs the same way.

select into a clob for the XSQL and that didn't work either.

How would you write a procedure to return this data.

I should mention I did get it working by returning a REF CURSOR, but I don't really think that would be the best way to return XML. I would think it should be returned as a CLOB.

Here is the REF CURSOR one that works:

create or replace procedure getvendorlist(p_rc1 OUT sys_refcursor)

as

begin

open p_rc1 for 
select xmlelement ("DOCUMENT",
         xmlattributes ('

http://www.w3.org/2000/10/XMLSchema'

as "xmlns:xsd"),
         xmlelement ("xsd:schema",
           xmlattributes ('

http://www.w3.org/2000/10/XMLSchema'

as "xmlns:xsd"),
           xmlelement ("xsd:element",
             xmlattributes ('ROWSET' as "name"),
             xmlelement ("xsd:sequence",
               xmlelement ("xsd:element",
                 xmlattributes ('ROW' as "name",
                                '0' as "minOccurs",
                                'unbounded' as "maxOccurs"),
                 xmlelement ("xsd:complexType",
                   xmlelement ("xsd:sequence",
                     xmlelement ("xsd:element",
                       xmlattributes ('EMPNO' as "name",
                                      'xsd:integer' as "type",
                                      'SCOTT.EMP' as "baseTable",
                                      'EMPNO' as "baseColumn",
                                      '0' as "minOccurs")
                     ),
                     xmlelement ("xsd:element",
                       xmlattributes ('ENAME' as "ename",
                                      'xsd:string' as "type",
                                      'true' as "nullable",
                                      'SCOTT.EMP' as "baseTable",
                                      'ENAME' as "baseColumn",
                                      '0' as "minOccurs")
                     ),
                     xmlelement ("xsd:element",
                       xmlattributes ('JOB' as "ename",
                                      'xsd:string' as "type",
                                      'true' as "nullable",
                                      'SCOTT.EMP' as "baseTable",
                                      'JOB' as "baseColumn",
                                      '0' as "minOccurs")
                     )
                   )
                 )
               )
             )
           )
         ),
         xmlelement ("ROWSET",
           xmlagg (
             xmlelement ("ROW",
               xmlforest (
                 empno as "EMPNO",
                 ename as "ENAME",
                 job as "JOB"
               )
             )
           )
         )
       ) as "DOC"
  from emp;

end getvendorlist;
/


justin@DEV> var x refcursor
justin@DEV> exec getvendorlist(:x)

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
justin@DEV> print x

DOC
====================================================================================================================================
<DOCUMENT xmlns:xsd="

http://www.w3.org/2000/10/XMLSchema" ><xsd:schema

xmlns:xsd="

http://www.w3.org/2000/10/XMLSchema" ><xsd:element <code>n
ame="ROWSET"><xsd:sequence><xsd:element name="ROW" minOccurs="0" maxOccurs="unbounded"><xsd:complexType><xsd:sequence><xsd:element n
ame="EMPNO" type="xsd:integer" baseTable="SCOTT.EMP" baseColumn="EMPNO" minOccurs="0"></xsd:element><xsd:element ename="ENAME" type=
"xsd:string" nullable="true" baseTable="SCOTT.EMP" baseColumn="ENAME" minOccurs="0"></xsd:element><xsd:element ename="JOB" type="xsd
:string" nullable="true" baseTable="SCOTT.EMP" baseColumn="JOB" minOccurs="0"></xsd:element></xsd:sequence></xsd:complexType></xsd:e
lement></xsd:sequence></xsd:element></xsd:schema><ROWSET><ROW><EMPNO>7369</EMPNO><ENAME>SMITH</ENAME><JOB>CLERK</JOB></ROW><ROW><EMP
NO>7499</EMPNO><ENAME>ALLEN</ENAME><JOB>SALESMAN</JOB></ROW><ROW><EMPNO>7521</EMPNO><ENAME>WARD</ENAME><JOB>SALESMAN</JOB></ROW><ROW
><EMPNO>7566</EMPNO><ENAME>JONES</ENAME><JOB>MANAGER</JOB></ROW><ROW><EMPNO>7654</EMPNO><ENAME>MARTIN</ENAME><JOB>SALESMAN</JOB></RO
W><ROW><EMPNO>7698</EMPNO><ENAME>BLAKE</ENAME><JOB>MANAGER</JOB></ROW><ROW><EMPNO>7782</EMPNO><ENAME>CLARK</ENAME><JOB>MANAGER</JOB>
</ROW><ROW><EMPNO>7788</EMPNO><ENAME>SCOTT</ENAME><JOB>ANALYST</JOB></ROW><ROW><EMPNO>7839</EMPNO><ENAME>KING</ENAME><JOB>PRESIDENT<
/JOB></ROW><ROW><EMPNO>7844</EMPNO><ENAME>TURNER</ENAME><JOB>SALESMAN</JOB></ROW><ROW><EMPNO>7876</EMPNO><ENAME>ADAMS</ENAME><JOB>CL
ERK</JOB></ROW><ROW><EMPNO>7900</EMPNO><ENAME>JAMES</ENAME><JOB>CLERK</JOB></ROW><ROW><EMPNO>7902</EMPNO><ENAME>FORD</ENAME><JOB>ANA
LYST</JOB></ROW><ROW><EMPNO>7934</EMPNO><ENAME>MILLER</ENAME><JOB>CLERK</JOB></ROW></ROWSET></DOCUMENT>



Please help, and thanks very much for it.

I concurr the ora-600 is nasty -- however, it is happening cause of a serious conversion error.

I took your query and:

ops$tkyte@ORA9IR2> create or replace view v
  2  as
  3  select xmlelement ("DOCUMENT",
...
 56         ) as "DOC"
 57    from emp;
 
View created.

ops$tkyte@ORA9IR2> desc v
 Name                                Null?    Type
 ----------------------------------- -------- ------------------------
 DOC                                          SYS.XMLTYPE
 


See -- it returns an XMLTYPE, not a varchar2 -- sooooo, quickie fixie:

ops$tkyte@ORA9IR2> create or replace procedure getvendorlist(p_ans OUT varchar2)
  2  as
  3          v_sql long;
  4          l_ans sys.xmltype;
  5  begin
  6
  7  v_sql:='
  8  select xmlelement ("DOCUMENT",
....
 64    execute immediate(v_sql) into l_ans;
 65    p_ans := l_ans.getStringVal();
 66
 67  end getvendorlist;
 68  /
 
Procedure created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> variable x varchar2(4000)
ops$tkyte@ORA9IR2> exec getvendorlist( :x )
 
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> print x
 
X
-----------------------------------------------------
<DOCUMENT xmlns:xsd="

but you'll probably want to return a clob, not a varchar2, since the bloated XML stuff will quickly exceed 32k.....

 

That taught me something.

Thanks much. I'll get to trying it.

r_clob := l_ans.getClobVal();


That rules. Thanks so much for your guidance.

Sometimes I have no idea what I would do without the people who make up this site. (Where do I send donations? ;)

Ok Tom,

Now that I have this working, I need to sell it to my team as a palatable solution. The reason the solution is ugly right now, is because all these SQLX tags are wrapped around our actual SQL statement, rendering it nearly unreadable. Once one understands what is happening in the select, I agree that it isn't hard to learn to make modifications to the statement, but I/they would much rather see just a plain old select statement in my initially called procedure, and pass the plain old select statement as a literal string to a conversion procedure which would parse the literal, and spit out the SQLX syntax with my SQL statement embedded in it.

SO... The question is, how would you parse any given select statement and pull out column names and table names. Oracle really only has two functions that I know of to parse text, instr and substr.

In other words I want to:

*****************
PROC getvendorlist
..
v_sql := 'select t093_id from t093_vendor';

parse_query(v_sql);

execute immediate(v_returned_SQLX_stmt) into l_ans;

******************
proc or function parse_query

find the from
find each column name
find each alias name
etc..
spit out the SQLX to the calling proc

*****************



How would you do this?



Keep in mind, the reason I am here is because of performance. These SQLX statements return instantly as benchmarked with TKPROF. So if you know this type of text parse processing would be slow, then that would end my search for this solution.


I am thinking that the parsing procedure/function will be affected by the complexity of the literal passed to it, but if you could show me how to do a simple one, maybe I can figure out the more complex ones.

Thanks very much for your time

I would much rather it be an industrial strength, and re-usable program, as you seem to mean by the "code generator".

I can't invision what you mean, so by all means, please show me.

Keep in mind the performance issue. Our times right now fall into 3 categories.

1) Embedding SQL in VB, and letting ADO generate XML for our vendor list query. -- .18-.30 seconds
2) Using DBMS_XMLQuery to generate the XML for our vendor list query. -- .49 seconds
3) Using SQLX to generate the XML for some our vendor list query. -- .16 seconds


Thank you so much for your help.

So you're saying you would just convert the SQL statements to SQLX statements during development, and put the SQLX statements in your procs?

My leader is still giving me grief about SQLX being so ugly.

So... We are looking at changing the way our application uses XML.

We need to know the best/fastest way to get XML out of Oracle, and the best/fastest way read XML into a DOM or SAXX parser and process it.

What package/utility would you use to do this *really fast*. There are so many Oracle XML packages that's it's almost overwhelmingly confusing as to which one to use.

It seems that DBMS_XMLDOM doesn't have a straight up "LOAD" procedure that allows you to read in your XML from the buffer?? We found PLSXML's DBDOM, and it seems to have it, but it's refered to as a "utility" and that seems odd/disconcerting to me because it isn't installed by default during installation.

To brief you on our situation, we are passing an XML "island" to the web client, and they have the opportunity to make changes to the XML island locally on their machine. Then the XML island is pushed back to the app/web server, then on to the DB server. We want to elliminate pushing the schema inline with every set of XML and just push the data (which reduces the size dramtically for small datasets). We will be writing separate select, insert and update PL/SQL procedures and passing the XML to the proper one based on what was done to the XML.

How would *you* do this - front to back?

I need to know what package/procedure you would use to get the XML out of Oracle blazingly fast, and then what package/procedure you would use to read the XML in via a buffer, parse it and process it blazingly fast.

dbms_xmlgen.getxml?
dbms_xmldom.load???

Thanks much for your help!

 and go to "Documentation" (an icon at the top of the screen).  Once you're there, go to the documentation library for 9i or 10g, and you can read ALL ABOUT Oracle's approach to generating XML, for storing XML and for manipulationg XML.  Here's a quick overview with my take:

Generation XML:
- SQLX.  This is the BEST way for you to generate XML, as it's the most natural way to generate a result.  A QUERY!  What's nice about these?  They generate query plans and they use the cost-based optimizer and they use indexed reads, etc.  This isn't to say any of the others don't but SQLX is Oracle's latest and greatest mechanism for generating XML.
- DBMS_XMLQUERY.  This is another way to query the database to ask for an XML result set, but the options you have for customizing the way Oracle generates the final document aren't quite as good as SQLX's options.

Storing/Processing XML:
- PLSXML's DBDOM.  This is a utility written some time ago to parse XML documents in PL/SQL.  It's not supported, and is considerably dated.  Use at your own risk.
- DBMS_XMLSAVE.  PL/SQL supplied package for taking an XML document and storing it into relational tables and/or objects.  It's good, as it removes a lot of the work from the programmers hands but the XML has to be in a particular "canonical" format which must match the storage architecture of your tables and/or objects.  All in all, this works but isn't quite XML DB.
- XML DB.  Using XML DB in 9iR2, there are MANY options you have for storing and interacting with your XML.  There's structured storage, where the database automatically breaks down your XML document based on your XML Schema, there's unstructured storage which places the entire XML document into a CLOB column in the XML DB schema.  There's the XML Repository which is yet another way to interface w/ the databases's XML technology.

There are more, but it sounds like you need to do some reading up on what's available and how Oracle's toolset maps into your requirements.

To answer your question at the bottom...  Use XML DB.  You upload an XML document via FTP, WebDAV or "SQL".  The XML document is then stored in a CLOB column or a set of object-relational tables & structures for further processing/queries.  When you need the XML document, you pull it out via FTP, HTTP, WebDAV or SQL.  Oracle is then responsible for reconstructing the XML document for you behind the scenes.

XML DB is the best way to go.  Period.  But of course, you'd need Oracle9i Release2. 

For more information on XML DB, search AskTom for XML DB and XMLType.  You can view some articles written by various XML technologists in Oracle Magazine at 

I had a lenghty response written, but I think I would have been bored to read it.

I'll get right to the point. I will check into XMLDB some more, but I don't think we can use that option for our application.

A question I have now is, can you use multiset inside of SQLX?

Could you give an example with emp,dept?

Thanks much!

Dear Sir,
Can a procedure be declared,defined and used with in a
* Function *?Could you please provide a simpel example for
that?
Please do reply.

Dear Tom,
Do you have a simple example for the format
sql> select * from table(func(cursor(..some select stmt)));
Please do reply.

ops$tkyte@ORA9IR2> @etl
=================================================================
 
For external tables, we need to use a directory object -- we'll
start with that, mapping to the temp directory
 
 
demo@ORA9IR2>
demo@ORA9IR2> create or replace directory data_dir as '/tmp/'
  2  /
 
Directory created.
 
demo@ORA9IR2>
demo@ORA9IR2>
demo@ORA9IR2> set echo off
Enter to continue
 
=================================================================
 
 
Now, we'll create the external table.
part of its definition is what looks like a control file -- it is
 
 
demo@ORA9IR2>
demo@ORA9IR2>
demo@ORA9IR2> create table external_table
  2  (EMPNO NUMBER(4) ,
  3   ENAME VARCHAR2(10),
  4   JOB VARCHAR2(9),
  5   MGR NUMBER(4),
  6   HIREDATE DATE,
  7   SAL NUMBER(7, 2),
  8   COMM NUMBER(7, 2),
  9   DEPTNO NUMBER(2)
 10  )
 11  ORGANIZATION EXTERNAL
 12  ( type oracle_loader
 13    default directory data_dir
 14    access parameters
 15    ( fields terminated by ',' )
 16    location ('emp.dat')
 17  )
 18  /
 
Table created.
 
demo@ORA9IR2>
demo@ORA9IR2>
demo@ORA9IR2>
demo@ORA9IR2> set echo off
Enter to continue
 
=================================================================
 
 
We'll create a flat file to work with now
 
 
demo@ORA9IR2>
demo@ORA9IR2> host flat scott/tiger emp > /tmp/emp.dat
 
demo@ORA9IR2>
demo@ORA9IR2> set echo off
Enter to continue
 
=================================================================
 
 
And finally, select * from it...
 
 
demo@ORA9IR2>
demo@ORA9IR2> select empno, ename, hiredate from external_table
  2  where ename like '%A%'
  3  /
 
     EMPNO ENAME      HIREDATE
---------- ---------- ---------
      7499 ALLEN      20-FEB-81
      7521 WARD       22-FEB-81
      7654 MARTIN     28-SEP-81
      7698 BLAKE      01-MAY-81
      7782 CLARK      09-JUN-81
      7876 ADAMS      12-JAN-83
      7900 JAMES      03-DEC-81
 
7 rows selected.
 
demo@ORA9IR2>
demo@ORA9IR2> pause
 
demo@ORA9IR2>
demo@ORA9IR2> set echo off
=================================================================
 
 
We'll set up a PLSQL "etl" routine to ingest the flat file
and output live data to be merged or inserted
 
demo@ORA9IR2> pause
 
demo@ORA9IR2> create or replace type emp_scalar_type as object
  2  (EMPNO NUMBER(4) ,
  3   ENAME VARCHAR2(10),
  4   JOB VARCHAR2(9),
  5   MGR NUMBER(4),
  6   HIREDATE DATE,
  7   SAL NUMBER(7, 2),
  8   COMM NUMBER(7, 2),
  9   DEPTNO NUMBER(2)
 10  )
 11  /
 
Type created.
 
demo@ORA9IR2> create or replace type emp_table_type as table of emp_scalar_type
  2  /
 
Type created.
 
demo@ORA9IR2>
demo@ORA9IR2>
demo@ORA9IR2> create or replace
  2  function emp_etl( p_cursor in sys_refcursor )
  3  return emp_table_type
  4  PIPELINED
  5  as
  6          l_rec  external_table%rowtype;
  7  begin
  8          loop
  9                  fetch p_cursor into l_rec;
 10                  exit when (p_cursor%notfound);
 11
 12                  -- validation routine
 13                  -- log bad rows elsewhere
 14                  -- lookup some value
 15                  -- perform conversion
 16                  pipe row( emp_scalar_type( l_rec.empno,
 17                            LOWER(l_rec.ename),
 18                            l_rec.job, l_rec.mgr, l_rec.hiredate, l_rec.sal,
 19                                    l_rec.comm, l_rec.deptno ) );
 20          end loop;
 21
 22          return;
 23  end;
 24  /
 
Function created.
 
demo@ORA9IR2> set echo off
 
=================================================================
 
 
that routine, our code, works just like a TABLE


demo@ORA9IR2> select empno, ename
  2    from TABLE(emp_etl(cursor( select * from external_table ) ) )
  3   where ename like '%a%';
 
     EMPNO ENAME
---------- ----------
      7499 allen
      7521 ward
      7654 martin
      7698 blake
      7782 clark
      7876 adams
      7900 james
 
7 rows selected.
 
demo@ORA9IR2>
demo@ORA9IR2> set echo off
 
=================================================================
 
 
we'll create an "existing" table of data, we would like to
'refresh' it from the source systems
 
demo@ORA9IR2> create table emp as select * from scott.emp where mod(empno,2) = 0;
 
Table created.
 
demo@ORA9IR2> select empno, ename from emp;
 
     EMPNO ENAME
---------- ----------
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7844 TURNER
      7876 ADAMS
      7900 JAMES
      7902 FORD
      7934 MILLER
 
10 rows selected.
 
demo@ORA9IR2>
demo@ORA9IR2>
demo@ORA9IR2> set echo off
 
=================================================================
 
 
and here is the "merge" -- from flat file, through ETL,
straight to the table without hitting the disk with staging
files
 
demo@ORA9IR2> merge into EMP e1
  2  using ( select *
  3            from TABLE(emp_etl(cursor( select * from external_table ) ) )
  4  ) e2
  5  on ( e2.empno = e1.empno )
  6  when matched then
  7          update set e1.sal = e2.sal, e1.ename = e2.ename
  8  when not matched then
  9          insert ( empno, ename, job, mgr,
 10                   hiredate, sal, comm, deptno )
 11          values ( e2.empno, e2.ename, e2.job, e2.mgr,
 12                   e2.hiredate, e2.sal, e2.comm, e2.deptno )
 13  /
 
14 rows merged.
 
demo@ORA9IR2> commit;
 
Commit complete.
 
demo@ORA9IR2>
demo@ORA9IR2> pause
 
demo@ORA9IR2> select empno, ename from emp;
 
     EMPNO ENAME
---------- ----------
      7566 jones
      7654 martin
      7698 blake
      7782 clark
      7788 scott
      7844 turner
      7876 adams
      7900 james
      7902 ford
      7934 miller
      7369 smith
      7499 allen
      7521 ward
      7839 king
 
14 rows selected.
 
demo@ORA9IR2>
demo@ORA9IR2>
demo@ORA9IR2> set echo off
 

Dear Sir,
we learnt a lot from your followup.But some doubts still
persist.They are
1)As you have said in one of your followups that "We use an
external table to query a flat file".Do External tables act
like views?If we delete the flat file, Can the external tables still exist or their validity gets destroyed after
the flat file is deleted?
2)sometimes when we use object types,we create a nested table type from the base object type.Is there any predefined rule or terminology to do that?
For example:
you create a scalar object type and transform that to a table and in the Function definition , the return clause
specifies a table type as a return object and in the function body it returns a base object type.I could not understand this transformation and could you please explain
why you are doing so?
Please do reply.
Your answers are excellent,undisputed and touch the hard core.Nice work.

Dear Tom,
I gained a lot of knowledge from your thoughts.Thanks a lot.

Regards
Vijay

Hi Tom,

Can you tell me why you need to use CREATE OR REPLACE when I am creating a procudure or function or package. Instead can i first drop the package and recreate.

Is there any difference better CREATE OR REPLACE and drop and create the sub program.

Thanks in Advance.

Hi Tom,
Just a simple question.
I want to know that how can we assign a variable in one procedure the out parameter of another procedure or the return parameter of another function.
I hope you understood what I require,
Thanks as always..

<BR>
<script language="JavaScript1.1" type="text/javascript">
document.getElementById("latestLink").style.display = "inline"
</script>
<table class="topLink"><tr><td class="topLink"><a href="f?p=4950:8:::::F4950_P8_DISPLAYID:2863595794233#20234791692531" style="padding-right:6px;">Bookmark Review</a> | <a href="#PAGEBOTTOM" style="padding-left:6px;padding-right:6px;">Bottom</a> | <a href="#PAGETOP" style="padding-left:6px;" name="20234791692531">Top</a></td></tr></table>
<FONT FACE="verdana,arial,helvetica" SIZE="-1">
<IMG SRC="/i/stars4.gif">Simple question for you!!!!!!!&nbsp;&nbsp;June 25, 2004
<BR>
Reviewer:&nbsp;
A&nbsp;reader
</FONT>
<BR>
<pre>
Hi Tom,
Just a simple question.
I want to know that how can we assign a variable in one procedure the out
parameter of another procedure or the return parameter of another function.
I hope you understood what I require,
Thanks as always..

</pre>
<BR>
<TABLE cellspacing="2" cellpadding="2">
<TR>
<td width="5%" valign="top" align="left">
<FONT FACE="verdana,arial,helvetica" SIZE="-1">

Tom,

Sorry about the post above, can you please delete it.

Thanks.

Hi Tom,

I used following to create a function which would return data in XML format but I am getting the error as mentioned below.

SQL> CREATE OR REPLACE
  2      FUNCTION simplexml
  3      RETURN CLOB
  4      IS
  5      BEGIN
  6      return DBMS_XMLQUERY.GETXML( ' SELECT empno "emp.empno"
  7                                     , ename "e.ename"
  8                                     , job "e.job"
  9                                    , mgr "e.mgr"
 10                                    , hiredate "e.hiredate"
 11                                    , sal "e.sal"
 12                                    , comm "e.comm"
 13                                    , deptno "e.deptno"
 14                                    FROM emp where rownum < 2',dbms_xmlquery.schema);
 15   
 16    END;
 17   
 18  /

Warning: Function created with compilation errors.

SQL> show err
Errors for FUNCTION SIMPLEXML:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/5      PL/SQL: Statement ignored
13/76    PLS-00302: component 'SCHEMA' must be declared


When I removed the above ",dbms_xmlquery.schema" part the function got created but when I fire the following query I got the error.

SQL> select simplexml from dual;
select simplexml from dual
       *
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "APPS.SIMPLEXML", line 5
ORA-06512: at line 1

Could you please let me know what is the problem 

Hi.

I'm using the PLSXML toolkit, and when I try to use the DBDOM.xml function to transfer the buffer content to an output parameter of the type long, I get the following error inside of the DBDOM package:

ORA-06502: PL/SQL: numeric or value error

What could be happening?

Iam doing Front End in VB, when I pass the parameter to the following procedure as
BackupRestore('A:','UF3153103')

------------------Procedure----------------------

CREATE OR REPLACE PROCEDURE Backup_Restore(p_filedir IN varchar2, p_filename IN varchar2) AS

p_TotalInserted NUMBER;
CURSOR Cur_Backup IS SELECT * FROM CDYYMMDD1;
v_Cur Cur_Backup%RowType;
v_Date DATE;
v_Mach VARCHAR2;
v_TNo VARCHAR2;
v_Amt NUMBER;

v_FileHandle utl_file.file_type;
v_NewLine varchar2(100);

BEGIN

v_FileHandle := UTL_FILE.FOPEN(p_filedir, p_filename, 'r');
p_TotalInserted := 0;
BEGIN
UTL_FILE.GET_LINE(v_FileHandle, v_NewLine);
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIT;
END;

v_Date := SUBSTR(V_NewLine,2,14);
v_TNo := SUBSTR(V_NewLine,21,1);
v_Mach := SUBSTR(V_NewLine,77,3);
FOR v_Cur IN Cur_Backup LOOP
SELECT RecptAmt INTO v_Amt WHERE TrnNo = v_TNo AND TrnDate = v_Date And Machine = v_Mach;
IF v_Amt = 0 Then
INSERT INTO cDYYMMDD1 VALUES (v_NewLine);
p_totalinserted := p_totalinserted + 1;
DBMS_OUTPUT.PUT_LINE('RECORD INSERTED');
COMMIT;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Total Records inserted '||p_totalinserted);
UTL_FILE.FCLOSE(v_FileHandle);
EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
DBMS_OUTPUT.PUT_LINE('Not a valid path');
ROLLBACK;
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
DBMS_OUTPUT.PUT_LINE('Not a valid filename');
ROLLBACK;
WHEN UTL_FILE.INTERNAL_ERROR THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM(SQLCODE));
ROLLBACK;
WHEN UTL_FILE.ACCESS_DENIED THEN
DBMS_OUTPUT.PUT_LINE('PATH CANNOT BE ACCESSED');
ROLLBACK;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM(SQLCODE));
ROLLBACK;
END;
/

The way which I have implemented is right or wrong?
How I should move to the next line of the text file in the procedure?
Iam inserting the record directly, which a v_NewLine contains?

The line in the text file will be

( '12-Dec-2004','D ','1','111111111111','11',11111,11111,'',0,'Z','N','M',' ','N',11111,0,' ','12-Dec-2004',' ',0,' ',' ',' ',' ',0,' ','12-Dec-2004',' ',0,'1','315','tpsl','06:45:26',1,'N','C','Y','D','1111','F','192.100.10.1','N','1111111111111','','','','','','19-Jan-2005','','','','')

I have read that we need to first create a directory, should I also create a directory?
This text file will be in a floppy(A:)
I saw so many examples like creating a directory as '\tmp' but never I saw creating a directory as 'A:' so what I have to do now?

I couldnt debug this procedure as my server is down.
Please help me.
I have worked whole day but I couldnt do more than this as

I couldnt find from where I have to post my question so I have done this.Please help me.
Its very urgent..
Waiting for your reply, sorry for taking your time.
Thanks in advance,
Prasanna

Hi Tom,
Really thanks for your reply.
When the server was down the data will be copied into the floppy and when up, they will insert this data(FLOOPY) into the database.
during this process, I have to pass the file to the procedure so that it open that file and retrieve the records and insert into the table. This is the process.
Please kindly help me.
Iam not understanding how to do this.

Questions
1)How to open file from floppy, should I create any directory for this?
2) how to go to the next line of the file.
3)While inserting is it possible to insert the line which the filehandler retrieved.
For example.
UTL_FILE.GET_LINE(v_FileHandle, v_NewLine);
INSERT INTO cDYYMMDD1 VALUES (v_NewLine);

The line in the text file will be as follows.
( '12-Dec-2004','D ','1','111111111111',
'11',11111,11111,'',0,'Z','N','M',' ','N',11111,0,' ',
'12-Dec-2004',' ',0,' ',' ',' ',' ',0,' ',
'12-Dec-2004',' ',0,'1','315','tpsl','06:45:26',1,
'N','C','Y','D','1111','F','192.100.10.1','N',
'1111111111111','','','','','','19-Jan-2005','','','','')

I hope that Iam clear here.
Please kindly reply me with your suggesions.
if you already worked on the same issue kindly send in your procedure.

Waiting for your reply.
Your reply is so valuable for me.
Please reply me Iam in need of your reply,
Prasanna

Hi Tom,

Thanks for your reply. The floppy will be on client side.
Actually Net will be down some time so, they just copy the trasaction into there floppy(CLIENT)
and once the net is up they update to the database all the transactions including the records in the floppy(client).

1.Is it possible to get the next line of the text?.Which is in follwing format.

The data in the text file(floppy) will be as follows.
( '12-Dec-2004','D ','1','111111111111','11',11111,11111,'',0,'Z','N','M',' ','N',11111,0,' ','12-Dec-2004',' ',0,' ',' ',' ',' ',0,' ','12-Dec-2004',' ',0,'1','315','tpsl','06:45:26',1,'N','C','Y','D','1111','F','192.100.10.1','N','1111111111111','','','','','','19-Jan-2005','','','','')

( '12-Dec-2004','D ','2','222222222222','22',1254,1254,'',0,'Z','N','M',' ','N',1254,0,' ','12-Dec-2004',' ',0,' ',' ',' ',' ',0,' ','12-Dec-2004',' ',0,'2','315','TPSL','07:07:11',1,'N','C','Y','D','2222','F','192.100.10.1','N','2222222222222','','','','','','19-Jan-2005','','','','')

I have to retrieve the line starting with '(' and ending with ')'.

2.Iam thinking the other way of doing, like copying the file from floppy and pasting it into some other location and calling that location from oracle procedure.but copying also to be done in procedure itself.is this way correct?how to copy the file from procedure.again do we need to use the UTL_FILE for doing this?

3.Iam unable to get the whole line into the insert statement.It is giving error.How can I correct it?

4.for spooling also do we need to use the UTL_FILE package?
Currently we are using Oracle 8i.

Sir, it would be helpful for me, if you send in any sample procedure.

I pasting you my procedure which I have done.Please suggest me
----------------------------------------------------
CREATE OR REPLACE PROCEDURE BackupRestore (file_name IN VARCHAR2) IS
vSFile utl_file.file_type;
vNewLine VARCHAR2(200);
vTNO VARCHAR2(6);
vTDate DATE;
vMach VARCHAR2(3);

BEGIN
vSFile := utl_file.fopen('A:', file_name,'r');
LOOP
BEGIN
utl_file.get_line(vSFile, vNewLine);
DBMS_OUTPUT.PUT_LINE(vNewLine);
vTDate = substr(vNewLine,1,13)
vTNo := SUBSTR(vNewLine,21,1);
vMach := SUBSTR(vNewLine,77,3);
FOR I IN (SELECT TRNNO, CONS_NO,MACHINE FROM CDYYMMDD1
WHERE TRNDATE = vTDate AND
(TRNNo = vTNo AND MACHINE = vMach)) LOOP
IF I = 0 then
INSERT INTO CDYYMMDD1 VALUES(vNewLine);
END IF;
END LOOP;

EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIT;
END;
END LOOP;
COMMIT;
utl_file.fclose(vSFile);
EXCEPTION
WHEN utl_file.invalid_mode THEN
RAISE_APPLICATION_ERROR (-20051, 'Invalid Mode Parameter');
WHEN utl_file.invalid_path THEN
RAISE_APPLICATION_ERROR (-20052, 'Invalid File Location');
WHEN utl_file.invalid_filehandle THEN
RAISE_APPLICATION_ERROR (-20053, 'Invalid Filehandle');
WHEN utl_file.invalid_operation THEN
RAISE_APPLICATION_ERROR (-20054, 'Invalid Operation');
WHEN utl_file.read_error THEN
RAISE_APPLICATION_ERROR (-20055, 'Read Error');
WHEN utl_file.internal_error THEN
RAISE_APPLICATION_ERROR (-20057, 'Internal Error');
WHEN utl_file.file_open THEN
RAISE_APPLICATION_ERROR (-20059, 'File Already Opened');
WHEN utl_file.invalid_maxlinesize THEN
RAISE_APPLICATION_ERROR(-20060,'Line Size Exceeds 32K');
WHEN utl_file.invalid_filename THEN
RAISE_APPLICATION_ERROR (-20061, 'Invalid File Name');
WHEN utl_file.access_denied THEN
RAISE_APPLICATION_ERROR (-20062, 'File Access Denied By');
WHEN others THEN
RAISE_APPLICATION_ERROR (-20099, 'Unknown UTL_FILE Error');
END read_demo;
/
-----------------------------------------------
Sorry for taking your valuable time.

Waiting for your reply,
Thank you,
Prasanna

Hi Sir,
Thanks for your reply.
Ok then, If it is not possible then how to get the line from the file in floppy? in procedure.
Is that possible?.if not using UTL_FILE how I can open the file and store the data into the database?
(without using frontend VB)
Is there other way of doing this?

Waiting for your reply.
Thanking you,
Prasanna

Thanks Sir, 
   The client will be having the floppy disk.save the records in the floppy(in the text file).is it possible to open a file in the floppy?  

Iam sending you the full details of the table, procedure and error. Kindly help me.
What to do with floppy?

---------------------------------------------------------
Create table MyTab (
MACHINE VARCHAR(3),
TRNNO VARCHAR(6),
Name varchar2(10),
TDATE DATE,
CMODE VARCHAR(1));



SQL> CREATE OR REPLACE PROCEDURE MyProc(file_name VARCHAR2) IS
2 vSFile utl_file.file_type;
3 vNewLine VARCHAR2(200);
4 vTNO VARCHAR2(6);
5 vTDate DATE;
6 vMach VARCHAR2(3);
7 X NUMBER(3);
8 BEGIN
9 vSFile := utl_file.fopen('c:\temp', file_name,'r');
10 LOOP
11 BEGIN
12 utl_file.get_line(vSFile, vNewLine);
13 DBMS_OUTPUT.PUT_LINE(vNewLine);
14 vTDate := substr(vNewLine,14,11);
15 DBMS_OUTPUT.PUT_LINE('Date '||vTDate);
16 vTNo := SUBSTR(vNewLine,5,1);
17 DBMS_OUTPUT.PUT_LINE('TRNNO '||vTNo);
18 vMach := SUBSTR(vNewLine,2,3);
19 DBMS_OUTPUT.PUT_LINE('MACHINE '||vMach);
20 SELECT count(*) into X FROM MyTab WHERE TDate = vTDate AND
21 TRNNo = vTNo AND MACHINE = vMach;
22 IF X = 0 then
23 INSERT INTO MyTab values(
24 substr(vNewLine,2,3),substr(5,1),substr(vNewLine,7,6),
25 substr(vNewLine,9,11),substr(vNewLine,14,3));
26 COMMIT;
27 DBMS_OUTPUT.PUT_LINE('RECORD INSERTED');
28 END IF;
29 EXCEPTION
30 WHEN NO_DATA_FOUND THEN
31 EXIT;
32 END;
33 END LOOP;
34 COMMIT;
35 utl_file.fclose(vSFile);
36 EXCEPTION
37 WHEN utl_file.invalid_mode THEN
38 RAISE_APPLICATION_ERROR (-20051, 'Invalid Mode Parameter');
39 WHEN utl_file.invalid_path THEN
40 RAISE_APPLICATION_ERROR (-20052, 'Invalid File Location');
41 WHEN utl_file.invalid_filehandle THEN
42 RAISE_APPLICATION_ERROR (-20053, 'Invalid Filehandle');
43 WHEN utl_file.invalid_operation THEN
44 RAISE_APPLICATION_ERROR (-20054, 'Invalid Operation');
45 WHEN utl_file.read_error THEN
46 RAISE_APPLICATION_ERROR (-20055, 'Read Error');
47 WHEN utl_file.internal_error THEN
48 RAISE_APPLICATION_ERROR (-20057, 'Internal Error');
49 WHEN utl_file.invalid_maxlinesize THEN
50 RAISE_APPLICATION_ERROR(-20060,'Line Size Exceeds 32K');
51 WHEN others THEN
52 RAISE_APPLICATION_ERROR (-20099, 'Unknown UTL_FILE Error');
53 END;
54 /

Procedure created.

ERROR:
******


SQL> execute MyProc('Temp.txt');
User-Defined Exception

PL/SQL procedure successfully completed.

SQL> begin 
2 MyProc('temp.txt');
3 end;
4 /
User-Defined Exception

PL/SQL procedure successfully completed.

I have also created directory as 
Conn System/Manager

Create or Replace Directory MyDir as 'C:\Temp';
Grant Read on Directory MyDir to Public;

Conn Scott/Tiger

But still Iam getting the same User Defined Exception error;
Iam Using Oracle 8i

The Line in the Text file is
****************************
(234,1,'asdf','12-DEC-2005','D')
(243,2,'asdf','12-DEC-2005','D')
(244,3,'asdf','12-DEC-2005','C')
(242,4,'asdf','12-DEC-2005','Q')
(253,5,'asdf','12-DEC-2005','C')
(256,6,'asdf','12-DEC-2005','D')

I hope that Iam clear.
Waiting for your reply.

Thanks in advance, 
Prasanna
 

Tom,
We upgraded our 9.1.0.4 database to 9.2.0.6.
One of the procedure could not be recompiled with errors:
ods@oit4>show error
LINE/COL ERROR
-------- -----------------------------------------------------------------
272/10 PL/SQL: SQL Statement ignored
274/17 PL/SQL: ORA-00942: table or view does not exist
949/7 PL/SQL: SQL Statement ignored
951/14 PL/SQL: ORA-00942: table or view does not exist
971/10 PL/SQL: SQL Statement ignored
973/17 PL/SQL: ORA-00942: table or view does not exist

I want to match the lines to the lines in the procedure.

ods@oit4> edit
Wrote file afiedt.buf
...
...

272 -- ----------------------------------------------------------------------------
273 -- ---------------------< get error module location get_where >---------------
274 -- ----------------------------------------------------------------------------
275 -- get error module location
276 --

The lines in the procedure seems no relating to the error lines in "show error".

How could we find corresponded lines in the plsql code relating to the error lines in "show error"?

Thanks In Advance

select text
 from user_source
where line between 270 and 275
  and name = 'XXX'
  and type = 'PACKAGE BODY'
/

if the code is not in the run buffer.  if the code is in the run buffer, just:

SQL> show error

<see line numbers>

SQL> l 270 275

to list lines 270 and 275

(I'm assuming it is a package body, because if you have a procedure or function with over 973 lines, that is just wrong :) 

Hi Sir,
Thanks for your reply.
I need one help regarding one query.
Is is possible to check the expression in decode function?is there anyother way to do this?
Iam here with sending you the exact details of the query.

SELECT CM.CTRNO,COUNT(C.CMODE) STUB, NVL(CTR.CHQSTUB+CASHSTUB,0) SUM,
NVL(TO_NUMBER(COUNT(C.CMODE))-(CTR.CHQSTUB+CTR.CASHSTUB),0) DIFF,
DECODE (NVL(TO_NUMBER(COUNT(C.CMODE))-(CTR.CHQSTUB+CTR.CASHSTUB),0),
0,'COUNTER NOT ACTIVE',
(CTR.CHQSTUB+CTR.CASHSTUB),>TO_NUMBER(COUNT(C.CMODE)),'COUNTER OFFLINE') DISCRIPTION
FROM CTR_MST CM,CDYYMMDD C, CTR_CONFIG CTR
WHERE CM.CTRNO=C.MACHINE(+) AND CM.CTRNO = CTR.CTRNO(+)
GROUP BY CM.CTRNO, CTR.CHQSTUB,CTR.CASHSTUB

ERROR
------
(CTR.CHQSTUB+CTR.CASHSTUB),>TO_NUMBER(COUNT(C.CMODE)),'COUNTER OFFLINE') DISCRIPTION
*
ERROR at line 5:
ORA-00936: missing expression

CHECK CONDITION
----------------
IF COUNT(C.CMODE) = 0 AND CHQSTUB+CASHSTUB = 0 THEN
'COUNTER NOT ACTIVE'
IF COUNT(C.CMODE) = 0 AND CHQSTUB+CASHSTUB > 0 THEN
'Counter Offline'
IF COUNT(C.CMODE) > 0 AND CHQSTUB+CASHSTUB =0 THEN
'Annexture Not Entered'
IF COUNT(C.CMODE) < (CHQSTUB+CASHSTUB) And
COUNT(C.CMODE) <> 0 And (CHQSTUB+CASHSTUB) <> 0 THEN
'Complete Data Not Received'

REQUIRED OUTPUT
-----------------
ctrno stubs sum diff discription
=========================================================
304 188 88 100 Complete data not
received
701 25 0 25 counterOffline
702 0 0 0 Counter not active

STRUCTURE OF TABLES
--------------------
CTR_MST
Name Null? Type
------------------------------- -------- ----
CTRNO VARCHAR2(8)
DIS_CODE VARCHAR2(10)

CDYYMMDD

Name Null? Type
------------------------------- -------- ----
TRNDATE DATE
CMODE VARCHAR2(1)
MACHINE VARCHAR2(3)

CTR_CONFIG
Name Null? Type
------------------------------- -------- ----
CTRNO VARCHAR2(8)
CHQSTUB NUMBER(8)
CASHSTUB NUMBER(8)
TODAY DATE


Sir can you help me in this regard, to display the output as I mentioned you before?


I hope that I given all details and clear.
Waiting for your reply.its very urgent.

Thanks in advance
Prasanna

Hi Sir,
Thanks for your reply.
I tried to give the case aswell. Iam getting the following error.


SELECT CM.CTRNO,COUNT(C.CMODE) CNTSTUB,
NVL(CTR.CHQSTUB+CTR.CASHSTUB,0) SUMSTUB,
NVL(TO_NUMBER(COUNT(C.CMODE))-(CTR.CHQSTUB+CTR.CASHSTUB),0) DIFF,
CASE WHEN COUNT(C.CMODE) < (CTR.CHQSTUB+CTR.CASHSTUB) THEN
'Complete Data Not Received'
WHEN TO_NUMBER(COUNT(C.CMODE)) = 0 AND
NVL(CTR.CHQSTUB+CTR.CASHSTUB,0) = 0 THEN 'COUNTER NOT ACTIVE'
WHEN TO_NUMBER(COUNT(C.CMODE)) = 0 AND
NVL(CTR.CHQSTUB+CTR.CASHSTUB,0) > 0 THEN 'Counter Offline'
WHEN TO_NUMBER(COUNT(C.CMODE)) > 0 AND
NVL(CTR.CHQSTUB+CTR.CASHSTUB,0) =0 THEN 'Annexture Not Entered'
WHEN TO_NUMBER(COUNT(C.CMODE)) <> 0 AND
NVL(CTR.CHQSTUB+CTR.CASHSTUB,0) <> 0 THEN 'Complete Data Not Received' ELSE 'COUNTER NOT OPEN' END DISCRIPTION
FROM CTR_MST CM,CDYYMMDD C, CTR_CONFIG CTR
WHERE CM.CTRNO=C.MACHINE(+) AND CM.CTRNO = CTR.CTRNO(+)
GROUP BY CM.CTRNO, CTR.CHQSTUB,CTR.CASHSTUB
/

CASE WHEN COUNT(C.CMODE) < (CTR.CHQSTUB+CTR.CASHSTUB) THEN
*
ERROR at line 4:
ORA-00923: FROM keyword not found where expected

How can I rectify this?

Thanks in advance,
Prasanna

Hello Sir,
Thanks for your reply.Iam using 8i/8 version.
I have requeried the query as follows

Please correct me where I went wrong.

SELECT CM.CTRNO,COUNT(C.CMODE) CNTSTUB,
NVL(CTR.CHQSTUB+CTR.CASHSTUB,0) SUMSTUB,
NVL(TO_NUMBER(COUNT(C.CMODE))-(CTR.CHQSTUB+CTR.CASHSTUB),0) DIFF,
DECODE (COUNT(C.CMODE),
0,'COUNTER NOT ACTIVE',DECODE(NVL(CTR.CHQSTUB+CTR.CASHSTUB,0),0,
'COUNTER NOT ACTIVE',
Decode(Sign(CTR.CHQSTUB+CTR.CASHSTUB-COUNT(C.CMODE)),1,
'Complete Data Not Received',
Decode(CTR.CHQSTUB+CTR.CASHSTUB,0,
'Annexture Not Entered',
Decode(Sign(CTR.CHQSTUB+CTR.CASHSTUB-COUNT(C.CMODE)),-1,
'Counter Offline'))))) DISCRIPTION
FROM CTR_MST CM,DIST_MST D,REG_MST R,CDYYMMDD C, CTR_CONFIG CTR
WHERE CM.DIS_CODE = D.DIS_ID AND D.REG_ID = R.REG_ID AND
CM.CTRNO=C.MACHINE(+) AND CM.CTRNO = CTR.CTRNO(+)
GROUP BY CM.CTRNO, CTR.CHQSTUB,CTR.CASHSTUB
HAVING COUNT(C.CMODE) <> NVL(CTR.CHQSTUB+CTR.CASHSTUB,0)

Thanks and Regards,
Prasanna

Tom,
I get the following error when i am copying the data from table 1 to table 2
ORA-00600: internal error code, arguments: [kcbgcur_3], [1688615], [0], [], [],

ORA-06512: at "ODS.HNPKG"
ANy ideas?

Bryan wrote:

> I get the following error when i am copying the data from
> table 1 to table 2 ORA-00600: internal error code,
> arguments: [kcbgcur_3], [1688615], [0], [], [],

Bryan, Metalink also provides Note 153788.1. "Troubleshoot an ORA-600 Error Using the ORA-600 Argument Lookup Tool"
</code> http://metalink.oracle.com/metalink/plsql/ml2_documents.showNOT?p_id=153788.1&p_showHeader=1&p_showHelp=1 <code>

It is an excellent first stage diagnostics tool, very useful for understanding why the error occurs, what known bugs are filed against it, and so on.

For example, the Lookup Tool points to Metakink Note 70097.1 that describes the [kcbgcur_3] error in detail.

Hi Tom,

I am using a pl-sql to create an extract and I am using UTL utility. When I ran the code I noticed that all the rows returned cursor are not getting inserted into the file.

Please let me know if I need to make changes to the code or there is any setup problem.

Find below the code.

DECLARE
v_input_record VARCHAR2(10000);
v_total NUMBER(30):=0;
v_output_file utl_file.file_type;
v_file_name VARCHAR2(50);

CURSOR Cur_get_data is
SELECT DISTINCT poh.segment1 PO_NUM,
(SELECT POR.RELEASE_NUM FROM PO_RELEASES_all POR
WHERE poh.po_header_id = por.po_header_id) rel_num,
pol.line_num line_num,
pv.vendor_name vendor,
vendor_site_code vendor_site,
vendor_product_num vendor_product,
pol.item_DESCRIPTION item_descr,
pol.QUANTITY quantity,
location_code location,
TRIM(To_CHAR(pol.unit_price,'99999999999.99')) unit_price,
TRIM(TRANSLATE(pol.tax_name,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"@%',0)) tax_name,
(SELECT SUM(ai.invoice_amount)
FROM apps.AP_INVOICES ai
WHERE INVOICE_ID = (SELECT APID.INVOICE_ID
FROM apps.AP_INVOICE_DISTRIBUTIONS APID,apps.PO_DISTRIBUTIONS POD
WHERE APID.PO_DISTRIBUTION_ID = POD.PO_DISTRIBUTION_ID
AND POD.PO_HEADER_ID = Pol.PO_HEADER_ID AND po_line_id = Pol.po_line_id
AND ROWNUM = 1)) inv_amount,
(SELECT porh.segment1 segment1
FROM
PO_REQUISITION_HEADERS_ALL PORH,
PO_REQUISITION_LINES_ALL PORL,
PO_REQ_DISTRIBUTIONS_ALL PORD,
PO_HEADERS_ALL POH
WHERE
poh.po_header_id = POD1.PO_HEADER_ID AND
PORD.DISTRIBUTION_ID = POD1.REQ_DISTRIBUTION_ID AND
PORD.REQUISITION_LINE_ID = PORL.REQUISITION_LINE_ID AND
PORL.REQUISITION_HEADER_ID = PORH.REQUISITION_HEADER_Id) Req_num,
gl.segment15 seg15,
gl.segment11 seg11,
(SELECT pap.segment1 FROM apps.PA_PROJECTS_ALL pap WHERE pod1.project_id =
pap.project_id) project,
pat.task_number TASK,
EXPENDITURE_TYPE_ID exp_type,
gl.segment13 seg13,
mtl.segment1 seg1
FROM
PO_DISTRIBUTIONS_ALL POD1,
PO_HEADERS_ALL POH,
apps.po_lines pol,
po_vendors pv,
mtl_categories mtl,
apps.po_vendor_sites pvs,
AP_INVOICE_DISTRIBUTIONS_all APID,
apps.AP_INVOICES ai,
pa_tasks pat,
gl_code_combinations gl,
PA_EXPENDITURE_TYPES ET,
hr_locations_all loc/*||','||
PA_EXPENDITURE_ITEMS_ALL pax*/
WHERE
poh.po_header_id = POD1.PO_HEADER_ID AND
poh.po_header_id = pol.po_header_id AND
--POD1.REQ_DISTRIBUTION_ID = PORD.DISTRIBUTION_ID AND
--PORD.REQUISITION_LINE_ID = PORL.REQUISITION_LINE_ID AND
--PORL.REQUISITION_HEADER_ID = PORH.REQUISITION_HEADER_ID AND
pod1.QUANTITY_DELIVERED = pod1.QUANTITY_BILLED AND
mtl.category_id = pol.category_id AND
--pv.vendor_id = 89921 AND
--NVL(poh.closed_code||','||'OPEN') ='OPEN' AND
poh.vendor_id = pv.vendor_id AND
pv.vendor_id = pvs.vendor_id AND
ai.vendor_site_id = pvs.vendor_site_id AND
pvs.purchasing_site_flag = 'Y' AND
--poh.segment1 = 91002520--91002324--91002323
AI.INVOICE_ID = APID.INVOICE_ID AND
APID.PO_DISTRIBUTION_ID = POD1.PO_DISTRIBUTION_ID AND
pod1.task_id = pat.task_id AND
pod1.CODE_COMBINATION_ID = gl.CODE_COMBINATION_ID AND
APID.EXPENDITURE_TYPE = ET.EXPENDITURE_TYPE AND
loc.location_id = pod1.DELIVER_TO_LOCATION_ID AND
poh.SEGMENT1 LIKE '910025%';
BEGIN

BEGIN

v_output_file := UTL_FILE.FOPEN('/u604/ineeddev_app11i/app11iap/tmp' , 'rah.txt' , 'w');

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Please check the Path and name of the file');
UTL_FILE.FCLOSE(v_output_file);
return;
END;

UTL_FILE.put_line(v_output_file,'PO_Number,Release_num,Line_Num,vendor_name,Vendor_site,Item_code,Item_description,
Item_price,Tax,Invoice_amount,Requisition_num,Business_nuit,Cost_center,Project,
Task,Expenditure_id,Account,commodity');

FOR gather_data in Cur_get_data
LOOP

BEGIN
v_input_record:=gather_data.PO_NUM||','||
gather_data.REL_NUM||','||gather_data.LINE_NUM||','||gather_data.VENDOR||','||
gather_data.VENDOR_SITE||','||gather_data.VENDOR_PRODUCT||','||
gather_data.ITEM_DESCR||','||gather_data.QUANTITY||','||
gather_data.LOCATION||','||gather_data.UNIT_PRICE||','||
gather_data.TAX_NAME||','||gather_data.INV_AMOUNT||','||gather_data.REQ_NUM||','||
gather_data.SEG15||','||gather_data.SEG11||','||
gather_data.PROJECT||','||gather_data.TASK||','||
gather_data.EXP_TYPE||','||gather_data.SEG13||','||gather_data.SEG1;
v_total:=v_total+1;
UTL_FILE.put_line(v_output_file,v_total||' '||v_input_record);
--DBMS_OUTPUT.PUT_LINE('Data '||v_total||'# #'||gather_data.PO_NUM);
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIT;
WHEN UTL_FILE.INVALID_OPERATION THEN
DBMS_OUTPUT.PUT_LINE ('Invalid Operation ');
DBMS_OUTPUT.PUT_LINE(SQLERRM);
return;
WHEN UTL_FILE.INVALID_PATH THEN
DBMS_OUTPUT.PUT_LINE ('Invalid path ');
DBMS_OUTPUT.PUT_LINE(SQLERRM);
return;
WHEN UTL_FILE.WRITE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('write_error ');
DBMS_OUTPUT.PUT_LINE(SQLERRM);
return;
WHEN UTL_FILE.READ_ERROR THEN
DBMS_OUTPUT.PUT_LINE('read_error ');
DBMS_OUTPUT.PUT_LINE(SQLERRM);
return;
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
DBMS_OUTPUT.PUT_LINE('filehandle ');
DBMS_OUTPUT.PUT_LINE(SQLERRM);
return;
WHEN OTHERS THEN
UTL_FILE.FCLOSE(v_output_file);
DBMS_OUTPUT.PUT_LINE('others '||sqlerrm|| ' Total '||v_total);
return;
END;
END LOOP;


EXCEPTION
/* If an exception is raised, close cursor before exiting. */
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('others '||sqlerrm);
END;

Hi Tom,

Thanks for your response. I was running this pl-sql block on the SQL prompt with a hard coded where clause and which was returning 225 rows but when I checked the file created by UTL it has 224 rows and I noticed that last row was not ther in the file.

today I created a concurrent request for this and when I ran I got all the 225 rows in the file. I didn't change the code.

Also when I ran the program on sql prompt yesterday I was not getting any error.

Could you please let me know what other exceptions do I need to handle error as I was under impression that OTHER will let me know if there is any other error.

Also could you please elaborate the following statement
"either REMOVE all exception blocks or.....
put a RAISE in each one after you clean up, since you are NOT expecting any
errors. "