One technique is to use PL/SQL, which converts LONG to VARCHAR2 during cursor fetches:
---------- Post updated at 01:10 PM ---------- Previous update was at 01:09 AM ----------
Another technique is to use Oracle's supplied package "dbms_xmlgen" for XML processing. Like PL/SQL, this package converts LONG data to VARCHAR2 as well.
First, check if you have access to the package by executing this sqlplus command:
It should list down all the public procedures and functions in this package.
Then, pass your query to the "getxmltype()" function of this package to convert the output into a canonical XML document.
From the output, it can be seen that:
(a) The outermost tags are <ROWSET> and </ROWSET>, and they are always returned.
(b) Each row of data is then sandwiched within <ROW> and </ROW> tags.
(c) Each column heading (partition_name, high_value in your case) has its own opening and closing tag.
(d) The actual data shows up as the content between the relevant column tags.
This is the canonical XML output. You can then use the "extractValue()" function to convert this XML format to relational aka "rows-and-columns" format.
Hi,
I have a query regarding execution of a sql query having long listing of parameters ..I need to execute this query inside a shell script.
The scenario is like....
Suppose I have a file abc.txt that has the card numbers..it could be in thousands..
then I need to fire a query like
... (12 Replies)
How do I use multiple field separators in awk?
I know that if I use awk -F"", both a and b will be field separators. But what if I need two field separators that both are longer than one letter?
If I want the field separators to be "ab" and "cd", I will not be able to use awk -F"". The ... (2 Replies)
Hi,
Im trying to write a script to first check a directory and if the filename has "ACK" in it do nothing and exit but if it has "ORD" in the filename then compare it with a dummy file created 2 minutes previous and see which one is newer
Im getting a few errors which im unsure how to rectofy... (5 Replies)
Hi,
I have problem with the following code. My IF block is not executed. And I see "syntax error near unexpected token `)'" error for line "EOF" in the stats_function().
but when I comment the IF block I don't see this error.
Kindly help me with this issue.
clean_function()
{... (10 Replies)
Hi Guys,
I need to compare the sql output with a column present in a file.
I am able to read the column from the file and stored in a variable.
Can somebody help how to store output of the below query to a variable. The database is Oracle.
Select count(*) from tableName;
... (2 Replies)
I am use unix shell script to called an sql Script to query data in my shell program.
sqlplus -S /nolog @update.sql
but my script on function "sysdate" not work !!
Could you tel me,How can i use function "sysdate" on unix or can replace the other function in my script to get data in system... (5 Replies)
Hi,
I want to compare today's date(DDMMYYYY) with yesterday(DDMMYYYY) from system date,if (today month = yesterday month) then execute alter query else do nothing.
The above requirement i want in Shell script(KSH)...
Can any one please help me?
Double post, continued here. (0 Replies)
Hi,
I want to extract the value of LENGTH column (high-lighted in red) from a file which will have several lines as shown below:
<INPUT VAR1 ="" DATATYPE ="number(p,s)" VAR2 ="" VAR3 ="3" VAR4="0" VAR5 ="ELEMITEM" VAR6 ="NO" VAR7 ="NOT A KEY" VAR8 ="17" LEVEL ="0" NAME ="UNIX" NULLABLE... (4 Replies)
Guys
trying to compare field in two files.
For Ex:
demo.txt
23.33.4.2 hostname
3.2.4.2 hostname12
demo1.txt
3.3.3.3 hostname23
45.23.23.23 hostname 323
I would like to compare the ips b/w these two files.any... (2 Replies)
It sounds so easy to do.
I have a file thats laid out like this..
number text text text text (etc about 15 times with various text fields)
I want to take the first field, "number", subtract 100 from it, and then put it back in the file. a simple little manipulation of the first field in... (4 Replies)