DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Is it possible to impose required constraint at value level?

Rajneesh S-OracleFeb 24 2020 — edited Feb 25 2020

Hello All,

Is it possible to impose required constraint at value level?

for example in below xml, I wnt to make sure value for alias1, coli1 and operator is always populated in condition element within where element.

functionalView name="CLAIMS_FV" description="Learning Purpose">

</columns>

</fromviews>

<joins>

</joins>

<where>

</where>

</functionalView>

Below is respective XSD for reference:

<?xml version="1.0" encoding="UTF-8"?>

<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">

<xs:element name="functionalView" >

<xs:complexType>

<xs:sequence>

<xs:element name="columns">

<xs:complexType>

<xs:sequence>

<xs:element name="column" maxOccurs="unbounded" minOccurs="1">

<xs:complexType>

<xs:simpleContent>

<xs:extension base="xs:string">

<xs:attribute type="xs:string" name="name" use="required"/>

<xs:attribute type="xs:string" name="columnAlias" use="required"/>

<xs:attribute type="xs:string" name="description" use="required"/>

</xs:extension>

</xs:simpleContent>

</xs:complexType>

</xs:element>

</xs:sequence>

</xs:complexType>

</xs:element>

<xs:element name="fromviews" maxOccurs="1" minOccurs="1">

<xs:complexType>

<xs:sequence>

<xs:element name="fromview" maxOccurs="unbounded" minOccurs="2">

<xs:complexType>

<xs:simpleContent>

<xs:extension base="xs:string">

<xs:attribute type="xs:string" name="name" use="required"/>

<xs:attribute type="xs:string" name="alias" use="required"/>

</xs:extension>

</xs:simpleContent>

</xs:complexType>

</xs:element>

</xs:sequence>

</xs:complexType>

</xs:element>

<xs:element name="joins" maxOccurs="1" minOccurs="1">

<xs:complexType>

<xs:choice maxOccurs="unbounded" minOccurs="1">

<xs:element name="join" maxOccurs="unbounded" minOccurs="0">

<xs:complexType>

<xs:simpleContent>

<xs:extension base="xs:string">

<xs:attribute type="xs:string" name="alias1" use="required"/>

<xs:attribute type="xs:string" name="col1" use="required"/>

<xs:attribute type="xs:string" name="condition" use="required"/>

<xs:attribute type="xs:string" name="alias2" use="required"/>

<xs:attribute type="xs:string" name="col2" use="required"/>

</xs:extension>

</xs:simpleContent>

</xs:complexType>

</xs:element>

<xs:element name="leftouterjoin" maxOccurs="unbounded" minOccurs="0">

<xs:complexType>

<xs:simpleContent>

<xs:extension base="xs:string">

<xs:attribute type="xs:string" name="alias1" use="required"/>

<xs:attribute type="xs:string" name="col1" use="required"/>

<xs:attribute type="xs:string" name="condition" use="required"/>

<xs:attribute type="xs:string" name="alias2" use="required"/>

<xs:attribute type="xs:string" name="col2" use="required"/>

</xs:extension>

</xs:simpleContent>

</xs:complexType>

</xs:element>

</xs:choice>

</xs:complexType>

</xs:element>

<xs:element name="where" maxOccurs="1" minOccurs="0">

<xs:complexType>

<xs:sequence>

<xs:element name="condition" maxOccurs="unbounded" minOccurs="1">

<xs:complexType>

<xs:simpleContent>

<xs:extension base="xs:string">

<xs:attribute type="xs:string" name="alias1" use="required"/>

<xs:attribute type="xs:string" name="col1" use="required"/>

<xs:attribute type="xs:string" name="operator" use="required"/>

<xs:attribute type="xs:string" name="string" use="optional"/>

<xs:attribute type="xs:string" name="number" use="optional"/>

<xs:attribute type="xs:string" name="date" use="optional"/>

</xs:extension>

</xs:simpleContent>

</xs:complexType>

</xs:element>

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:sequence>

<xs:attribute type="xs:string" name="name"/>

<xs:attribute type="xs:string" name="description"/>

</xs:complexType>

</xs:element>

</xs:schema>

Thanks,

Rajneesh

This post has been answered by odie_63 on Feb 25 2020

Jump to Answer

Mustafa KALAYCI

what is your client version? if it is 12c and above you can simply use:

set markup csv on

select .....

Paulzip

Use

set markup csv on

if 12c or above

You'll have to do something like...

select EmployeeNumber||','||Employeename||','||','||EmployeeDepartment||','||EmployeeDesignation||','||EmployeeSalary

from emp

and remember to use to_char on fields if it's a number or a date.

4068702

Hello Paul,

I have already tried Set markup csv , it is not working, I am using oracle 11g. I cannot use every column separately as I have to use it for a large database and am looking for a dynamic query as i will use it on certain views.

4068702

Hi Mustafa,

It is not working, already tried. I cannot change the query I have to use just select * from table name. I cannot use every other column names separately as it is a reusable code.

Karthick2003

Try sqlcl tool.

https://www.oracle.com/database/technologies/appdev/sqlcl.html

In this tool all you need is this

select /*csv*/ * from emp;

This tool has lots of interesting features.

4068702

Hi Karthick,

Thanks for your help, but we use Toad in our organisation.

Mustafa KALAYCI

your example commands are sqlplus commands! so those are application specific. Toad, I assume, allows you to run sqlplus commands. I simply suggest you install an oracle 12c client. you don't need to have a 12c database. just use new client ans your sqlplus will be able to run set markup csv command.

[Deleted User]

8132c5da-3111-4c9a-865a-bcfd06ff02ac wrote:

we use Toad in our organisation.

A quick search using Google gave me this: https://forums.toadworld.com/t/export-the-results-of-a-query-to-csv/29959 ...

That said: What exactly are you trying to achieve?

 I cannot use every column separately as I have to use it for a large database and am looking for a dynamic query as i will use it on certain views.

Why is the size of the database an issue? Why would you need something "dynamic" (I hate that word...) for views?

 I cannot change the query I have to use just select * from table name. I cannot use every other column names separately as it is a reusable code.

Reusable code? What code? If you need help with code you need to show us that code.

4068702

Hi Stefan,

What exactly are you trying to achieve?

I want to create a csv file from DB which will be transferred in between app and DB servers.

Why is the size of the database an issue? Why would you need something "dynamic" (I hate that word...) for views?

Using all column names is not possible for as not every view might contain all.

Reusable code? What code?

One single procedure which for all views. This particular set of select statement I have to use inside the procedure.

code is confidential.

Thanks for your help.

AndrewSayer

8132c5da-3111-4c9a-865a-bcfd06ff02ac wrote:

Hi Stefan,

What exactly are you trying to achieve?

I want to create a csv file from DB which will be transferred in between app and DB servers.

Why?

Your app should be using SQL to get data. It should not go back to the stone ages and parse a CSV file...

What's the real business problem you're trying to solve here?

Gaz in Oz

As others have alluded to , Oracle sqlplus 12.2 and above has the "set markup csv on"" command.

Sqlplus 12.2.0.1.0 New Features

instantclient and sqlplus is available from here:

https://www.oracle.com/database/technologies/instant-client/downloads.html

If you are using tOAd then go look in a tOAd forum:

https://forums.toadworld.com/t/export-the-results-of-a-query-to-csv/29959

If you want to stick with your current script then change the query to actually select columns concatenated with the comma.

For example:

...

select col1||','||col2||','||col3...

from table

where ...

[Deleted User]

8132c5da-3111-4c9a-865a-bcfd06ff02ac wrote:

Using all column names is not possible for as not every view might contain all.

Sorry, I don't understand what you mean. If really necessary you could use the data dictionaries to get every column name from every table and every view, without typing a single column name yourself (well, data dictionary columns excepted of course)

8132c5da-3111-4c9a-865a-bcfd06ff02ac wrote:

One single procedure which for all views. This particular set of select statement I have to use inside the procedure.

So do you mean that you want a single procedure that exports all your views to csv?

8132c5da-3111-4c9a-865a-bcfd06ff02ac wrote:

code is confidential.

What is so confidential about a procedure that exports table contents to a csv? If I google "oracle export to csv" I get hundreds of results. Surely there must be one that you can use....

It would be beneficial to you if you clearly explain what the business problem is that you try to resolve with an export to csv. And I do not mean that the business says that they need an export to csv, I'm talking about the problem above that. Why do they need that?

1 person found this helpful

1 - 12

Added on Feb 24 2020

#general-xml, #xml

8 comments

129 views

DevOps, CI/CD and Automation

Is it possible to impose required constraint at value level?

Comments

Post Details