I have oracle 10g database running on Sun Solaris. My database character set is AL32UTF8. Our user wants to store a double byte character and an image, I want to know what the column type should be using for these.
View 2 RepliesAny way to replace the Double Quotes used to enclose column names with an alternative character. This is the SQL I have now that Works!
select (case when CUST is null then "/BIC/Z_SUPPLNT" else NM1 end) CMPNTSUPSRCE
from TBL1, TBL2
where "/BIC/Z_MAJVEND"=CUST(+) and Material = '1ABCD456'
order by Material
But would like to do something along these lines below but keep getting error "ORA-00936: missing expression".
select (case when CUST is null then chr(34)||/BIC/Z_SUPPLNT||chr(34) else NM1 end) CMPNTSUPSRCE
from TBL1, TBL2
where chr(34)||/BIC/Z_MAJVEND||chr(34)=CUST(+) and Material = '1ABCD456'
order by Material
i what to update a column data type am geting this error
ORA-02267: column type incompatible with referenced column type
my query is
when i run
SELECT b.table_name||
' joins to the '||
a.table_name||
' table on '||
c.column_name||
' = '||
d.column_name Table_relationships
[code]....
i am fairly new in the oracle arena, but what would cause a statement such as
ALTER TABLE TEST_TABLE MODIFY text_field1 varchar2(100) DEFAULT 'testval' NULL
to change a column's type from VARCHAR2(100) to VARCHAR2(100 byte)? i found a few mentions of the 100 byte concept online but nothing that jumped out at me.
I have an Image Type on a forum page. I want a default "not-found" image to display if the BLOB column value is null or if there is no data for that search value. The image is stored with the app: #APP_IMAGES#not-found.png
APEX 4.2 (with listener) on Oracle 11gR2
My application spools the data from database into file and file is fixed length. My application working fine when database has single byte characters. But I am getting fields postion misposition and length mismatch in spooled line when data base has Multi byte characters(ê).
I want to convert the multi byte characters into single byte while spooling the data( ê ==> e)
The below query has converted the multi byte to single byte character. But actual value is modified into lower case.
####################################################
select utl_raw.cast_to_varchar2((nlssort(c.Name,'nls_sort=binary_ai'))) sbyte, '|'
c.Name
from EMP c
viva! experiencias | Viva! Experiências
####################################################
I am trying to insert an image files into a blob column using the following code.
CREATE TABLE MY_IMAGE_TABLE (
ID NUMBER,
NAME VARCHAR2(20),
IMAGE BLOB);
CREATE OR REPLACE DIRECTORY MY_FILES AS '\dppdb-dev est';
GRANT ALL ON DIRECTORY MY_FILES TO PUBLIC;
[code]....
However, I am getting the this error
ORA-22288: file or LOB operation FILEOPEN failed
I've done some tracing on toad and it seems that the file am trying to insert exists yet am unable to open it.
I have wrote this code behind a formula column which take the image from a server. The name of the image and location is carried out from database. When ever there is new record new name of the image is posted in this formula column. But when the file is not present in the location it gives error. When the error occur i cant able to view the report. The error says 'You cant run the report without layout'. When all images are present in the directory then report runs fine.
function CF_QID_IMAGE return Char is
l_file_exists BOOLEAN;
l_file_len NUMBER;
l_blocksize BINARY_INTEGER;
image_link varchar2(50);
[code]...
I have a blob column in one table. I need to show it in my form (6i). If i put an image file in the column, it works (because i show it in a Image item type) but if i put a text file in the blob column, it does n´t work.
View 4 Replies View RelatedI have a web service which gives me the response in Byte array.write this byte array into a binary file? .
View 2 Replies View RelatedI have a table named PRODUCT (ID Number , ProductImage BLOB). I have inserted some records in. I want to create a form for user to view this table.
When I create Data Block, in Data Block Wizard, if I select option 'Table or View' to retrieve data, it's ok (Data type of item ProductImage is Image, when I run form and query, it display image normally).BUT, I want to retrieve data from stored procedure in a package. This is my package
PACKAGE my_pkg IS
TYPE my_rec IS RECORD
(
myID PRODUCT.ID%TYPE,
myImage PRODUCT.ProductImage%TYPE
);
[code].....
I select option 'Stored Procedure' in Data Block Wizard, then select my stored procedure (my_pkg.Do_Query). Next I choose 2 columns from my stored procedure (ID, ProductImage). The wizard still applys data type of ProductImage as Image. I finish Data Block Wizard and Layout Wizard... run form, query and an error message display:
FRM-40364: The data type of item 'ProductImage' does not match the corresponding column in the stored procedure.
I try to change 'myImage PRODUCT.ProductImage%TYPE' -> 'myImage BLOB' in my package but still get that error.
We just migrated our technology platform on one of the six servers yesterday on production d/b as follows :
OAS Server - Linux Patch Applied (2.6.18-53.el5 #1 SMP Wed Oct 10 16:34:19 EDT 2007 x86_64 x86_64 x86_64 GNU/Linux)
D/B Server - From Oracle 10.2.0.2.0 to Oracle 10.2.0.4.0
Batch Server - From OpenVMS to Red Hat Linux
Post Migration, we are facing some issues with Oracle Forms 10g, Text Items which contains one or more than one multibyte characters, is displaying only # character in the entire text box instead of what is stored in the database. In other words, information in the database is stored correctly. Before Migration everything was working perfectly fine.
One other thing which I would like to tell here is that we compile forms using the following environment settings:
NLS_LANG_SEMANTICS=CHAR
NLS_LANG=AMERICAN_AMERICA.UTF8
I create a simple table for image
CREATE TABLE image_table (
Design_no varchar2(10),
filename VARCHAR2(255) PRIMARY KEY,
image LONG RAW);
Then i create simple block of image_table
its show me in this website [URL] My question is i don't want to give my form path always.i want to save my picture path in my database and the name of my picture always same as my design_no column.
For example i want to save a picture in my database i will copy my picture in d:shahzaib1234.jpg now i want the path d:shahzaib save in database and when i copy my picture which path= d:shahzaib and file name = Design no its will automatically save in database.
i want to create a simple form in this form i want to upload the image on the image item with browse button that open a diaglog box that go to the image i select the image and this image is display on image item on the form.
View 1 Replies View Relatedi m getting some problam to display image in forms 6i.
i hv two tables
1st is empmst
emp_code number(5) not null (PK)
emp_name varchar2() not null
2nd is emppic
emp_code number(5) not null (FK of empmst)
emppic blob not null
i create a master detial form and save the data in tables
whn u execute query i m unable to get the pic..
I have to create a table which should store data at Week level. The table have the following columns
Product id, Loc id, Business group id, FISCAL WEEK , Revenue,
Fiscal week column will have data as '2011-W01', '2011-W47' etc.
What should be the data type for fiscal week column. Based on this table i have to create a calculated column which should fetch trailing 12 weeks average for each row.
I am working with Oracle 8i and I have a problem while applying the below query:
select to_char(ENTRYDATE,'MM-DD-YYYY HH24:MI:SS') from TBL_BANDWIDTH_READS
where ENTRYDATE > sysdate-1
and rownum <10
03-03-2011 00:00:00
03-03-2011 00:00:00
03-03-2011 00:00:00
03-03-2011 00:00:00
[code].....
The time appears as 00:00:00. I said it's something weird because if I take the where condition off, then something like this appears:
select to_char(ENTRYDATE,'MM-DD-YYYY HH24:MI:SS') from TBL_BANDWIDTH_READS
12-22-2006 13:13:27
12-22-2006 13:13:27
12-22-2006 13:13:27
12-22-2006 13:13:27
12-22-2006 13:13:27
12-22-2006 13:13:27
12-22-2006 13:13:27
I am using the same query on a different table and there's no problem on it:
select to_char(ENTRYDATE,'MM-DD-YYYY HH24:MI:SS') from TBL_XX_PROGRAM
where ENTRYDATE > sysdate -10 and rownum <10
03-03-2011 17:06:48
03-03-2011 17:06:48
03-03-2011 16:59:08
03-03-2011 16:59:08
03-03-2011 12:04:21
I checked the DDL of both tables and the only difference is that the TBL_XX_PROGRAM have the ENTRYDATE field defined as not null and the ENTRYDATE on the TBL_BANDWIDTH_READS does not have the same constraint, could it affect?
I am trying to run select query on xml using xmltable. One of the tag returns date and I want to take it in date format in xmltable column.
select t.* from XMLTYPETEST xt ,
XMLTable ('/TestData/trade' passing xt.data
columns
test varchar2(100) path 'GenTest/Id',
testdate date path 'DateTest/Date')t;
But its throwing error as -
SQL Error: ORA-29958: fatal error occurred in the execution of ODCIINDEXCREATE routine
ORA-01830: date format picture ends before converting entire input string
29958. 00000 - "fatal error occurred in the execution of ODCIINDEXCREATE routine"
*Cause: Failed to successfully execute the ODCIIndexCreate routine.
I have extracted in the following XML document some Mpeg7 visual descriptors from an image and I saved it in an XMLType column. I would like to use XMLQuery to extract the data from the Value node. how write such a query. I could not get the proper Xpath to any node.
<?xml version="1.0" encoding="UTF-8" standalone="no" ?>
<Mpeg7 xmlns="http://www.mpeg7.org/2001/MPEG-7_Schema" xmlns:xsi="http://www.w3.org/2000/10/XMLSchema-instance">
<DescriptionUnit xsi:type="DescriptorCollectionType">
[code]...
I have a task to update one of the rows in a table (having only 2 columns, number and long) which is long data type. We are on Oracle 10g. Not sure how to use update for a long data type column.
I have tried using dbms_metadata_util.long2varchar, but still not getting what I want.
Is it possible to replace datatype of column of one table with another table's column datatype using %type like below
SQL>create table test1 (v1 varchar2(10));
SQL>create table test2 (v1 test1.v1%type);
Im having mail content column of type long Raw in a table.i just want concat or append a value in that column when i tried it shows error "illegal use of long type".how to append value to it. value will be string type
View 15 Replies View Relatedhow I can avoid PLW-07204 "conversion away from column type may result in sub-optimal query plan" (or rather the problem notified here by Oracle) from my PL/SQL code.My code looks as follows:
create table T(D date);
create or replace procedure P is
cursor C(D_IN IN DATE) is
select D
from T
where trunc(D)=SYSDATE;
begin
null;
end P;
[code]...
I don't want to disable the warning - it is there for a reason.
Additional description for the issue: It seems that D is of typ=12, while trunc(D) is of typ=13. You can use DUMP to read this info. This is described at URL....
I have a table with VARCAHAR2 data type column, I am unable to update that column with the following data.
UPDATE we set sname='(([Emp].Job.Catageory
IN 'SALES,COMPUTER,SCIENCE,TELESERVICE')
AND ([Employee].IDtype IN 'ABC'))'
I am getting the error "ORA-00933: SQL command not properly ended"
I have a table and data as below.I need to select all the records if value have both 'M' and 'D'.If there is only 'M' or 'D' then select should not pull any records.
WITH data as (
Select '1' id, 'M' value from dual union all
Select '1' id, 'M' value from dual union all
Select '1' id, 'D' value from dual union all
Select '1' id, 'D' value from dual )
select value from data group by value
I tried below query but it is not working.
WITH data as (
Select '1' id, 'M' value from dual union all
Select '1' id, 'M' value from dual union all
Select '1' id, 'D' value from dual union all
Select '1' id, 'D' value from dual )
select * from data group by value having sum(count(distinct(value))) > 1
user@tank> create table filetable(id number(6), file_content BLOB);
0 rows affected (0.02 seconds)
vlanke@tank_db> desc filetable;
ID NULL NUMBER(6,0)
FILE_CONTENT NULL
I am new to Database field.
- I created a table with 2 columns id (datatype as number) and file_content datatype as BLOB.
- My question is under desc filetable; query why does it not show me a data type as BLOB.
- create table query does not return any error to me. How do I verify that FILE_CONTENT column has datatype as blob.
i have one table emp in this table i have the column eno,ename,hiredate and i have data also in this table.
my eno colum data type is number so now i have to change this colum data type from number to varchar2.
if yes how can when i am trying to change this colum data type i got this error
Failed to commit: ORA-01439: column to be modified must be empty to change datatype.
I have a table Table_xml_tmp which has a column MESSAGECONTENTS as XMLType data which contains data as follows
"<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<xclimSchemaMain xmlns="http://clones.telcordia.com">
<response>
<clHeader>
<msgName>queryPlace</msgName>
[code]......
I need to get all rows where node <operationStatus> value as SUCCESS. I am running following query but it is returning null value insted of data of columns
SELECT a.MESSAGECONTENTS.extract('/clHeader/msgName/object/msgType/sender/senderInstance/operationStatus/text()').getStringVal()
FROM Table_xml_tmp a
WHERE a.MESSAGECONTENTS.extract('/clHeader/msgName/object/msgType/sender/senderInstance/operationStatus/text()').getStringVal()= 'SUCCESS'
need to dynamically assign value of each column of a row type variable:The example is like that:
Table "Student_list" is as follows:
---------
COL1 VARCHAR2(50),
COL2 VARCHAR2(50),
COL3 VARCHAR2(50)
[code]....
Is there any work around where i can dynamically built my variable or table.column name and assign a value to it?
I have a table with modifieddate column with 'DATE' data type.I am facing date format exception and tried with to_char, to_date but its throwing invalid number exception. how to format date accordingly.
SELECT * FROM EMP WHERE modifieddate > '31-Dec-2011 18:30';
ORA-01722: invalid number
01722. 00000 - "invalid number"