Oracle database version is : 10G.
When we try to query a column from a table the output is shown as "?????". Seems the data in that column is stored in a different character set, which is not recognised while displaying the output. The column size is 35byte.
The nls parameter table shows the character set as "we8mswin1252".
get this issue resolved.
How to handle special charater '&' in stored procedure.
Following is a test code I want to Implement
CREATE OR REPLACE PROCEDURE SP_Test(QueryID NUMBER,DateFirst DATE,DateLast DATE) IS
BEGIN
INSERT
INTO TempTable(QueryID,LineID,data,Datetime)
SELECT
1,6,Emp,startdate FROM salary
[code]....
The code above resuts into INVALID state of stored procedure and cannot be executed.
I am trying to name a column from data stored in a row (Phone number) I want to name it from the first 3 digits (Area code) and I have no clue how to do it I have googled but I think the query is a little too complicated for me to explain it and get a result from google or find it in a section of a book.
My phone numbers are strings, so I initially look for the first 3 characters and then start separating and counting, I can do that, the only problem I have is naming the column WITH the the first three digits that I just separated.
I am trying to name a column from data stored in a row (Phone number) I want to name it from the first 3 digits (Area code) and I have no clue how to do it I have Googled but I think the query is a little too complicated for me to explain it and get a result from Google or find it in a section of a book.
My phone numbers are strings, so I initially look for the first 3 characters and then start separating and counting, I can do that, the only problem I have is naming the column WITH the the first three digits that I just separated.
I am trying to learn by myself how to use databases and am using the HR database that comes in oracle since I read in a forum that it is a great way to learn how to use queries.
I read about using Pivot to do it, but in pivot I need to know how many area codes there are. I will post a screen shot of the table that I am working on the other post
[URL].....
I can group by phone number with this:
SELECT SUBSTR(PHONE_NUMBER, 1, 3) AREA, COUNT(SUBSTR(PHONE_NUMBER, 1, 3)) TOTAL_COUNT
FROM EMPLOYEES
GROUP BY SUBSTR(PHONE_NUMBER, 1, 3);
But what I am interested in doing is that instead of Area appearing as a column, that the actual first 3 digits would appear as the column name.
This is kind of what I am trying to do, but instead of AREA appearing as column name I would like for each one of the area codes to appear as column name, here is the
SELECT DE.DEPARTMENT_NAME, EM.SEX, SUBSTR(EM.PHONE_NUMBER, 1, 3) AREA, COUNT(SUBSTR(EM.PHONE_NUMBER, 1, 3)) TOTAL_COUNT FROM EMPLOYEES EM, DEPARTMENTS DE WHERE DE.DEPARTMENT_ID = EM.DEPARTMENT_ID GROUP BY DEPARTMENT_NAME, EM.SEX, SUBSTR(PHONE_NUMBER, 1, 3) ORDER BY DEPARTMENT_NAME;
I have a problem that I don't know how to solve it.here is it:
I have a table test1(intitule varchar,age number):
INTITULE AGE
------------------------------ ----------
nom1 1
nom2 2
nom3 3
nom4 4
nom5 5
nom6 6
nom7 7
nom8 8
nom9 9
nom10 10
I want to disorder(randomize) column INTITULE without touching to 'age'.
My ORACLE DB version is:
('Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production');
('PL/SQL Release 11.2.0.2.0 - Production');
('CORE 11.2.0.2.0 Production');
('TNS for Linux: Version 11.2.0.2.0 - Production');
('NLSRTL Version 11.2.0.2.0 - Production');
I have this XML data stored in a CLOB column:
<Activity>
<Changes>
</Changes>
<Inserts>
</Inserts>
<Definition>
[code]....
I need creating an update that will insert another <Activity>SomeGUID</Activity> into the <Spawned> parent.
Is there a seeded function by which I can check all the rows which stored dates in varchar column.
I have a table say test (test_data varchar2(100));
Now I will insert all types of records into the table varchar,number dates and then i will write q query to etch all those records only which has dates only
INSERT INTO test(1);
INSERT INTO test('ABC');
INSERT INTO test(SYSDATE);
INSERT INTO test(TO_CHAR(SYSDATE,'DD-MON-YYYY'));
INSERT INTO test(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
INSERT INTO test('15/01/2012');
...
commit;
In one of the table, dates are stored in character format. I want to find the ID where start_date is greater than stop date have converted the char to date by using to_date function but still I am getting error.
with t as(select 122 as ID, 'JAN-12' as start_date, 'MAR-13' stop_date from dual unionselect 123 as ID, 'MAR-12' as start_date, 'FEB-12' stop_date from dual unionselect 124 as ID, 'MAR-12' as start_date, 'JAN-12' stop_date from dual unionselect 125 as ID, '01-MAR-1949' as start_date, '13-APR-1947' stop_date from dual unionselect 126 as ID, '01-MAR-1959' as start_date, '13-APR-1970' stop_date from dual unionselect 127 as ID, '11-MAR-1950' as start_date, '13-JAN-1951' stop_date from dual unionselect 128 as ID, '21-JUL-1969' as start_date, '20-JUL-1969' stop_date from dual)select * from tWHERE to_date(start_date) > to_date(stop_date)
I have written a stored procedure that has started returning the error:
Error starting at line 1 in command:
call p_glpost('DSTUK', 'L', '2008-01-01', '2008-01-01', '2011-02-18', 1, 1, 1, 0, 'Hi there')
Error report:
SQL Error: ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "CLARITY.P_GLPOST", line 173
06502. 00000 - "PL/SQL: numeric or value error%s"
I can't seem to find a tool that will let me step into the actual stored procedure line by line to see where the error occurs. It mentions line 173, which seems to be a red-herring, as line 173 is simply one of the 'END IF' lines within this block:
IF NVL(r_dist.transtype,'wild') = 'wild' THEN
NULL;
elsif r_wip.transtype = r_dist.transtype THEN
v_matchCount := v_matchCount+1;
elsif r_wip.transtype <> r_dist.transtype THEN
[code]......
Tell me if it is possible to trace through a SP, and which tool is best (I am trying to use Oracle SQL Developer).
I'm try to import a table of data (character set: CL8ISO8859P5) to another database (character set: AL32UTF8) using exp/imp utility.After the import, all Cyrillic text was corrupted!
View 5 Replies View RelatedI have a XML data that needs to be stored in oracle table and it is read from a java application. what datatype will suit for XML storage varchar2 or CLOB. The length of XML will be less than 4000.
View 4 Replies View RelatedI have successfully Stored image file in Oracle 10g Database Table using Forms 6i. But I cannot retrieve that image and display on Forms 6i. Image Item on Form is not a database Item.
View 8 Replies View Relatedwe have a Oracle 11g database, but it does not support UTF8 character set. Now we have received a file which contains records with Trade Mark symbol (special character). Now when we are trying to load the record, it is getting loaded with a sign "?". As a result when we try to display record in web application, the application is not able to show the records properly.
View 5 Replies View RelatedI would like to send a raw command to the Oracle server. from .NET (System.Data.OracleClien), but I got invalid statement (ORA-00900) exception
.Excecute string:string SQL = "EXECUTE SP_THREADFILE_INSERT('" + Guid.NewGuid() + "','" + filename + "',utl_raw.cast_to_raw('" + content + "'))";
I need to create an Oracle Stored Procedure to read a Flat file(pipe delimited) and load the data into an Oracle table. I believe the file should be located in any of the path as logged in dba_directories table or it can be anywhere on the local client machine?
View 14 Replies View Relatedhow do we know database character set is either single character set or multi character set?
While changing character-set from AL32UTF8 to WE8MSWIN1252 got "ORA-12712: new character set must be a superset of old character set".
Below are steps taken to resolve the issue -
ALTER DATABASE CHARACTER SET WE8MSWIN1252;
i got this error: ORA-12712: new character set must be a superset of old character set
below are the commands executed by me:
SQL> SHUTDOWN IMMEDIATE;
SQL> CONNECT SYS/password AS SYSDBA;
SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE WE8MSWIN1252;
SQL> SHUTDOWN;
SQL> STARTUP;
SQL> QUIT;
And its working...
I have not done it in proper order. Neither have done ccsscan. Still, no user reported any issues. Do my changes truncated the data?
There is a table like below
seq no. number(6)
amount varchar2(20,2)
The values in the table are:
Rec1
123456
100.00
Rec2
123457
99.01
Rec3
123458
9a.01
Rec4
123459
NULL
Now I want to summarize the amount column replacing non numeric and null values to '0'(zero's).
how to frame query for this.
I have a table stud_mst In which i have following columns STUD_NAME , ROLL_NO , COURSE
Table values are for e.g
STUD_NAME , ROLL_NO , COURSE
Harshita 1323 BSC
Atul 1734 BCOM
L'Pradhan 3739 BA
Still today user can enter any special character value in stud_name like L'Pradhan . I want to put a restriction on stud_name so that user can enter only alphabetical value into stud_name column.
how to insert data in a table by deleting previous entered data and only inserting current data like:
CREATE TABLE test
(
name VARCHAR2(20),
id NUMBER
)
INSERT INTO test VALUES ('aaa',5500);
[code]....
I got two rows. now when I do insert statement I want to delete the previously stored data and only insert the current data like:
INSERT INTO test VALUES ('aaa',8);
INSERT INTO test VALUES ('aaa',9);
it must show aaa,8 and aaa,9 bt not the previous values.
NOTE: we can not do sth like: update set... where id = ... becoz the values are dynamic.
I'm using Oracle 11g enhanced ADD COLUMN Functionality. Adding new columns with DEFAULT values and NOT NULL constraint no longer requires the default value to be stored in all existing records.
Sometimes we change DB columns from NOT NULL with DEFAULT to NULL with DEFAULT. This operation "materialize" column default. Is there an easy way (Dictionary view) how to find, that COLUMN default value is stored as metadata or is "materialized" ?
Oracle RDBMS version : Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
Any 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 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.
I got a primary database with a logical standby database running Oracle 11g. I got two client applications, one is the production site pointing to the primary one, another one is just a backup site pointing to the logical one.Things will only be written into the primary database every mid night and client applications can only query the database but not add, update nor delete.And now, I want to apply the latest patch on both of my databases. I am also the DNS administrator, I can make the name server pointing to the backup site instead of the production one.I want to firstly apply the patch on the logical one, and then the physical one.
I found some reference which explains how to apply patches by adopting "Rolling Upgrade Method". however, I want to avoid doing any "switch over" mentioned in the reference because I can make use of name server. Can I just apply patches as the following way?
1)Stop SQL apply
2)Apply patches on logical standby database
3)let the name server point to the backup site
4)Apply patches on the primary database
5)Start SQL apply
6)Let the name server point back to the production site
i want to convert oracle database(schema data) to postgresql database
which tool would be best and url to download and steps to convert from one database to another ?
I have table t1 and (a1,a2,a3) are columns
I have this query
select a1 from t1
union
select a2 from t1
union
select a2 from t1
It's alternative is unpivot, but internally it calls table multiple times. I want a way, where I want the same output but calling the table only once.
how to get the national character set of the database?
View 1 Replies View RelatedWe have production DB 10g with character set US7ASCII. This DB stores Arabic data and English data.Production DB located in HP unix Operating System.
When I query data from DB through SQL developer data is shown as Junk or Unknown characters(Square Boxes).
Client (Workstation from where query is issued from SQL develope- Windows XP OS) Settings: NLS_LANG = AMERICAN_AMERICA.US7ASCII
In Client workstation Oracle 10g client is installed from where I used to query data through SQL developer. The problem is I am unable to see Arabic characters in the sense that it is displayed as Junk character. However English characters and Eneglish numeric values are displayed properly.
I tried below way to make sure that data is not corrupted: Converted "Name" column to hex value (rawtohex) and displayed its HEX value. Executed below query in UTF-8 DB.
select UTL_I18N.RAW_TO_CHAR(hex_value_of-name) from dual;
This displayed Arabic name properly in UTF8 DB.
Character set for this production DB can not be changed at this time. There are many applications which is based on this DB. All these applications are well capable of converting Junk data to Arabic to display in application.
My concern is: What I should required to do to view Arabic data properly through SQL developer? Is there any settings needs to be done at my client workstation?
I am trying to insert data into table through SQL.
However my table has a column by name "User Name". Now I have a user name " x,yz" (see there is comma b/w x and y).
how do I escape and insert this data(x,yz) into the table.I need data as it is in DB for my application.
I am trying to insert the following greek character in the database. DB is not supporting the character and saved as junk character in the DB. create table MULTI_LANG (id number, comments NVarchar2(200)); insert into MULTI_LANG values (1,'Οι παραπάνω υπολογισμο ');
DB version Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
LANGUAGE AMERICAN
TERRITORY AMERICA
CHARACTER SET US7ASCII
NLS_CHARACTERSET US7ASCII
NLS_NCHAR_CHARACTERSETAL16UTF16
I have the same problem with the following db version also:
1) LANGUAGE AMERICAN
TERRITORY AMERICA
CHARACTER SET WE8MSWIN1252
2) NLS_CHARACTERSET WE8MSWIN1252
NLS_NCHAR_CHARACTERSET AL16UTF16
3) Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
i want to store special characters in database. i have tried following but not succeed. how to store and retrieve it. Herewith i'm posting my try.I want to store and retrieve following character.
SQL> select nchr(181) from dual;
N
-
µ
SQL> create table temp (uom varchar2(10));
Table created.
SQL> insert into temp values(nchr(181));
1 row created.
SQL> commit;
Commit complete.
SQL> select * from temp;
UOM
----------
?
here i'm getting the '?' but i want 'µ'.