Spool name concatenated with Sysdate
Date: Tue, 23 Mar 1999 08:14:48 PST
Date: Tue, 23 Mar 1999 10:44:00 -0600
Date: Wed, 24 Mar 1999 02:26:06 -0800 (PST) Get one Record at a time
On Sat, 10 Apr 1999, E_Qi Tsui wrote:
On 11 Apr 99 at 21:09, SAURABH K. DESHMUKH wrote:
Date: Tue, 13 Apr 1999 05:04:33 MYT-8 Assigning parameter in query
From: Mohan_Rajaganapathy@notes.seagate.com
Date: Thu, 15 Apr 1999 04:05:33 PDT Searching for text string within a LONG
From: shawn.mcadams@natinst.com
Date: Thu, 17 Jun 1999 18:31:33 -0700 (PDT)
Date: Fri, 18 Jun 1999 19:53:33 +0530 Inserting into a 'Long' field
Date: Wed, 23 Jun 1999 10:57:45 -0500
Date: Wed, 23 Jun 1999 10:48:13 PDT
Date: Thu, 24 Jun 1999 11:10:05 +0100
From: "JANAKI ALLU"
Subject: spooling file name concantenating with sysdate
hay guys,
i am tring to spool sqlfile ,the spool file must concantenate sysdate
but it's not spooling ...could anybody give suggestions to solve..
AIM: I WANT TO SPOOL FILE CONCANTENATING WITH SYSDATE
the following code creating spooling file FILE1||sysdate but i want
spoolinf file name file03-12-99 something like that !!!
define x=sysdate
SPOOL M:\EDMS\output\FILE1 ||&x
SELECT
"RECORD"
,"ORG_ID"
,to_char("DATE_TRANSMITTED",'YYYYMMDD') "DATE_TRANSMITTED"
FROM WVUFIN_FLAT where org_id='0463';
SPOOL OFF
From: Kelvin Stapleton
Subject: RE: spooling file name concantenating wi
You need to do a to_char on sysdate
From: Kevin Gillins
Subject: RE: spooling file name concantenating wi
Try this method out.
column sp_file new_value _sp_file
select 'c:\temp\outfile'||to_char(sysdate,'YYYYMMDD') sp_file from
dual;
spool &_sp_file
select 'Hello' from dual;
spool off
Kevin
Suppose I have an Employee table and I want to select all the
contractors from it. But each time I only want to have one record
(contrator), for example, give me "Contractor 1" first, and then
"Contractor 2"...
Can anyone please advise how could I achieve this "each time give me
only one record"?
you can use the 'rownum' virtual column of the table,
hope that would help..
From: philip@aleytys.pc.my
To: "SAURABH K. DESHMUKH" saurabhk.deshmukh@tatainfotech.com,
Subject: Re: SQL (select)
Hi!
rownum is not guaranteed to be consistent between invocations of the same
SQL script.
Philip
Subject: Assiging Parameter in query
Date: Thu, 15 Apr 1999 04:02:16 -0400 (EDT)
Hai friends,
I have a small query in reports . I
want to
assign a value to a parameter during run-time in a
query . But i get an error . I sending the query for u below
select ith.imt_header_id, :p_segment, sum(itd.qty_ship)
seg_qty_shipped
from mtl_system_items msi,
imt_transaction_header ith,
imt_transaction_detail itd,
mtl_item_categories mic,
mtl_categories mc
where ith.shp_organization_id :p_org_id
and ith.imt_header_id :p_imt_header_id
and itd.imt_header_id ith.imt_header_id
and msi.organization_id ith.shp_organization_id
and mic.organization_id msi.organization_id
and mic.inventory_item_id msi.inventory_item_id
and msi.segment1 itd.partno
and mic.category_set_id :p_cat_set_id
and mc.category_id mic.category_id
and mc.structure_id :p_structure_id
group by ith.imt_header_id, :p_segment
Here the :p_segment is a parameter and appears both in the
select statement and the group by clause
I have used both lexical and bind parameters but i get an error
So i just wanted to know how this can be solved
Mohan
From: "Guneesh Bhatia" guneesh1@hotmail.com
Subject: Re: Assiging Parameter in query
Hi Mohan,
Have you assigned an initial value to the lexical parameter(i do not
think a bind parameter will work but exical parameters we use quite
frequently in our reports) that you have used in both the SELECT and
the GROUP by expression.Without a valid initial value,it will always
give you an error.
Just a thought.
Guneesh
Subject: SQL: ****URGENT**** : Searching for text string within a LONG
Date: Friday, June 18, 1999 3:42 AM
Hi Folks,
I'm having one heck of a time trying to return all records from a table
where a
field of type 'LONG' contains a particular string. All of the usual
varchar
operands (like, instr, ) return the error 'inconsistent datatypes'.
Does anyone know of a way to search a 'LONG' column for a certain text
string?
Your help is greatly appreciated,
Shawn McAdams
Programmer/Analyst
National Instruments
From: Rajesh TL priyarajesh@yahoo.com
Subject: Re: SQL: ****URGENT**** : Searching for text string within a LONG
Hi Shawn
Attached below is the Oracle Help for a LONG Data Type.
Under the context of the help you could insert the
long database column and rowid into a temp
table that maps the long to a varchar and rowid
to rowid datatype and search on the mapped varchar
field of temp table using any operands.
Please let me know if there are other workarounds
that does not involve creation of another object.
thanks and regards
Rajesh
*********************************
LONG
You use the LONG datatype to store variable-length character strings.
The LONG datatype is like the VARCHAR2 datatype, except that the
maximum length of a LONG value is 32760 bytes.
You can insert any LONG value into a LONG database column because the
maximum width of a LONG column is 2147483647 bytes. However, you cannot
select a value longer than 32760 bytes from a LONG column into a LONG
variable.
LONG columns can store text, arrays of characters, or even short
documents. You can reference LONG columns in UPDATE, INSERT, and (most)
SELECT statements, but not in expressions, SQL function calls, or
certain SQL clauses such as WHERE, GROUP BY, and CONNECT BY. For more
information, see Oracle7 Server SQL Reference.
*******************************
Copyright (c) 1994, Oracle Corporation.
From: "Orafin" orafin@sohm.soft.net
Subject: Re: ****URGENT**** : Searching for text string within a LONG
hi shawn,
kindly refer to
Programmer's guide to the Oracle Call Interface
In which there is a mention of OFLNG routine with example for 'piecewise
fetch' of long column.
Hope this helps you.
Ashraf
From: Sistla.Sambasivarao@indsys.ge.com
To: oraapps-dba@cpa.qc.ca
Subject: LONG datatype
Hi All,
I have two tables
case
----
a long
&
case2
------
a long
when I'm issuing a simple command like "insert into case2(select a from case2)"
I'm getting this message 'ORA-00997: illegal use of LONG datatype'
Thanks in advance,
Shiva Sistla
From: Venkat Dulla venkatdulla@hotmail.com
To: oraapps-dba@cpa.qc.ca
Subject: Re: LONG datatype
You cannot use insert into select statement if you have long
columns.
Write a simple pl/sql program.
Ex : If you have two tables a1 and a2 with long columns
Declare
v1 long;
begin
select long_column into v1;
insert into a2(long_column) values(v1)
end;
/
Hope this will help you.
Venkat Dulla
From: Philip West Philip.West@exco.co.uk
To: oraapps-dba@cpa.qc.ca
Subject: RE: LONG datatype
You can do this using sql*copy to get round the long data type.
set long 20000 - this is the limit of chars in the long datatype that will
be copied
COPY FROM rg/rg@TEST -
INSERT ras_temp -
USING -
SELECT * FROM rg_report_axis_sets -
WHERE structure_id=50140
Best Regards
Phil West - Oracle Financials DBA
Telephone: 44 171 9509385
E-mail: philip.west@exco.co.uk