SQL & PL/SQL :: Select French Characters Generic Entire Schema
Nov 17, 2010I need to select all names that can possibly have ether accents or french characters what is the easiest way?
View 1 Replies
ADVERTISEMENT
JDeveloper, Java & XML :: Select Entire Message From CLOB - ORA-22806
Jun 20, 2013I have a "simple" query that needs to extract all of the XML messages stored in a CLOB column in a table for a data deidentification project. This query
select c.cc_data_area.getClobVal() as cc_xml
from ci_cc c;
This query returns an ORA-22806: not an object or REF
cc_data_area is the clob column, and contains messages longer than 4000 characters.
SQL & PL/SQL :: Select Data With Special Characters
Feb 16, 2012I have a table in which a column has some data like this:
prod¿ct_liste¿_25175892
prod¿ct_liste¿_76815456
Is there any way to select this data?
SQL & PL/SQL :: Select Query To Check For Special And Non Printable Characters?
Jun 19, 2013Is there any sql select query to check and find for special and non printable characters in a table column.
View 4 Replies View RelatedNeed To Process French Language Data
Apr 6, 2010I've recently been asked to replace certain English text fields with their French translation. I thought I could do this with the replace function: line_desc_reformed := replace(l.Line_Description,'Labor Item for Regular Hours','Main d uvres pour les heures rgulire');
The special characters do not get translated. So I tried the CONVERT function: line_desc_reformed := CONVERT(line_desc_reformed, 'WE8MSWIN1252', 'WE8DEC').
All gets translated except for the tick mark after the d. It gets replaced by an upside down? I have tried several different language sets and can not get that to convert.
also when using the dbms_xmldom to convert this to xml, the also gets replaced by an upside down ?. I don't belive you can change the charset when using this package.
Precompilers, OCI & OCCI :: Data Loading Of French Input File
Aug 16, 2012I am trying to load a input flat file (french data in it) into the database using pro*c (not using sql loader because of some validations ). i am reading the line by line and populating it into the structure and then process it.
The input file is encoded in WE8ISO8895P1 , I want the records to be populated into a table. so i did set NLS_LANG=French_France.WE8ISO8895P1 and ran the pro*c program . i used character host variable in the insert query , i used data from the earlier read structure and set these character host variable.
The problem i am facing is , when i am printing the values before insert i could see correct data.
For example the printed data of variable is "pas de donné " and strlen is 14. the target table field is of varchar type
( name varchar(20 char) )
but after i insert i could see only the truncated value in the database i.e "pas de donn" in the table length is 11.
SQL & PL/SQL :: Generic Application Error
Jan 10, 2013In order to display non-english characters properly in Business Objects reports, We have changed the NLS_LANG parameter at BO server level from ENGLISH_UNITED KINGDOM.WE8MSWIN1252 to AMERICAN_AMERICA.AL32UTF8
After this change non-english characters are coming properly but some of our existing reports started giving below error after this change.
A database error occured. The database error test is: (CS) "Error on Fetch: Generic Application exception" (WIS 10901).
To Write Generic Way Of Oracle 10g PL / SQL Code
Dec 14, 2010I Would like to delete a records based on the parent / child relationship. So I would need to delete a record from parent table , first delete inner most child record then other child then master. To write generic way of Oracle 10g PL/SQL code.
View 2 Replies View RelatedUtl-tcp Connection - Oracle Generic Network Error?
Aug 26, 2010I have a client program that needs to communicate with another server program. My client program is implemented in PL/SQL using utl_tcp running on Oracle 9 while the server program is a 3rd party software running on Windows server 2003.
Basically my client program needs to send a request to the server, waits for the response (wait for the incoming string which ended /w chr(3) ) and then acknowledge (sending chr(6) ).
My problem is that if I wait for the chr(3) and then acknowledge. My send routine (l_sent := utl_tcp.write_text (g_conn, p_content) will raise a Oracle generic network error. It seems like the connection is closed. I checked /w the vendor and they said it might be I am taking too long to response ( I question timeout is the problem). I have also attempted to change my read routine from read_line to read_raw but it only make things worst. I cannot even read the complete string. At least with read_line, I can read back the entire string.
My current work around is to send out the ack before I receive the chr(3) (which is not recommended by our vendor).
Select All Views From Schema
Apr 27, 2010I want to list out all the views in a schema. What table can I query?
View 2 Replies View RelatedInsert Into Select From Another Schema?
Jan 11, 2011I have a schema northwind which has tables and then i have another schema northwind_staging which has its own tables.
CREATE TABLE "NORTHWIND"."CUSTOMERS"
(
"CUSTOMERID" VARCHAR2(5 CHAR),
"COMPANYNAME" VARCHAR2(40 CHAR) NOT NULL ENABLE,
"CONTACTNAME" VARCHAR2(30 CHAR),
"CONTACTTITLE" VARCHAR2(30 CHAR),
"ADDRESS" VARCHAR2(60 CHAR),
[code]...
I created a procedure
CONNECT NORTHWIND_STAGING/NENAGH1;
create or replace procedure INSERTCUSTOMERS_STAGING
as
begin
INSERT INTO NORTHWIND_STAGING.customers_staging
SELECT DISTINCT c.customerid, c.companyname,c.contactname,c.region, c.country
FROM NORTHWIND.CUSTOMERS c;
end;
/
show errors This works fine when i ran my test scripts in SQL developer. I created a role Select on Northwind
SQL & PL/SQL :: Select Data Different Schema
Jan 12, 2011I have 3 users in schema.
User09
User10
User11
Table Name PURCHASE exists in all above three schema with different data(according to date)
Like
User09 ----> purchase ---- data date Jan09 to Dec09
User10 ----> purchase ---- data date Jan10 to Dec10
User11 ----> purchase ---- data date Jan11 to Dec11
I want to select * from purchase, from above all three user with select statement.I can select data one by one with union all
select * from user09.pruchase Union all
select * from user10.pruchase Union all
select * from user11.pruchase
after creating this query I add another User User12 and that User has same purchase table so I will have to add one another line IN Union all Query. when I see this query. It gives me the all owner name of User09,User10,User11......
SELECT * FROM ALL_ALL_TABLES
WHERE OWNER LIKE 'USER%'
and table_name ='PURCHASE'
OWNER TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
USER PURCHASE USERS
USER09 PURCHASE USERS
USER10 PURCHASE USERS
3 rows selected
Can I get data from Purchase Table of all users with select statement .
Networking And Gateways :: Generic Connectivity And Transparent Gateway
Dec 2, 2010We are planning on interfacing Oracle to SQL server connectivity, I'm researching on how to create a dblink to SQL server 2005. I'm a little confused between Generic connectivity and Transparent Gateways..
I think Transparent Gateway is not free and needs to be licensed, is it better in terms of performance? Also, do I need to configure this gateway on a seperate server?
I think Generic connectivity can be configured on the same Oracle database server itself.. but I see there could be some performance issues with it..
We are running on Oracle 10.2, looks like HSODBC is not available in 10g?
How To Grant Select On All Tables Of User1 To Another Schema
Jan 18, 2012User1 is having 10000 tables in his schema...How can i grant "select" on a all tables of a user1 to another schema(user2) so that in future when user1 will create tables , the user2 will have "select" access on those tables automatically.
I dont want user2 to have "select any table" privillege.
User2 should not have "drop" privillege on his own tables.
SQL & PL/SQL :: Removing Special Characters And Get Desired Characters From Column Values
Jul 23, 2013create table test
(
name varchar2(50),
descd varchar2(50)
)
insert into test values ('kethlin','da,dad!tyerx');
insert into test values ('tauwatson','#$dfegr');
insert into test values ('jennybrown','fsa!!trtw$ fda');
insert into test values ('tauwatson','#$dfegr ,try');
how do I get the first three characters and last three characters from name field and remove all the junk characters from descd field?
so my o/p be like;
Quote:('ketlin','dadadtyerx')
('tauson','dfegr')
('jenown','fsatrtw fda')
('tauson','dfegr try')
Schema Permissions - Execute GRANT SELECT ON Xyz - Insufficient Privileges?
Sep 30, 2011I have an automated process which runs on an Oracle 8i database server as user abc.This process creates views/tables in other schemas, on the same database server, which point to objects owned by the abc user.
The issue I'm getting is that when I try to execute GRANT SELECT ON xyz.view123 TO PUBLIC as the abc user, I get an insufficient privileges.I should add that the abc user created the xyz.view123 table/view.
What grants/priviliges or whatever do I have to do to the abc schema?
SQL & PL/SQL :: Trigger - Automatically Grant Select Permission To ODI-MASTER Schema?
Jan 4, 2011I have two schema named ODI_MASTER and ODI_WORK.Under ODI_WORK there are some tables like TEMP1, TEMP2
Further more when any new tables will create under ODI_WORK, then i need automatically grant select permission to ODI _MASTER schema.
for this purpose i choose trigger, and a Stored procedure.
CREATE OR REPLACE TRIGGER ODI_WORK.TRIG1
AFTER CREATE ON ODI_WORK.SCHEMA
ENABLE
call sp1 (ora_login_user)
[code]...
I searched a lot over blogs, if EXECUTE IMMEDIATE commands exist under Trigger it gets problem. Insert/update/delete statement on trigger seems no problem.
Server Utilities :: Import A Schema From One Database Schema To Another Schema B?
Aug 10, 2010I want to import a schema from one database schema to another schema b from db STBTST to STATST and from schema CMSSTAGINGB to CMSSTAGINGA
I first want to test this to my own schema (mvanmannekes) CMSSTAGINGA is filled at the moment.
So i've created a dump from STBTST-CMSTAGINGB For importing im using this statement:
impdp mvanmannekes/password schemas=cmsstagingb remap_tablespace=cmsliveb_data:cmslivea_data
remap_tablespace=cmsliveb_index:cmslivea_index
remap_schema=cmsstagingb:mvanmannekes directory=expdp_dir dumpfile=cmstagingb.dmp
I'm getting this:
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "MVANMANNEKES"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "MVANMANNEKES"."SYS_IMPORT_SCHEMA_01": mvanmannekes/********
schemas=cmsstagingb remap_tablespace=cmsliveb_data:cmslivea_data
[code]....
PL/SQL :: Update Entire Table
Nov 23, 2012If i have a table, my_table, with 20 columns, and i have something like:
declare
...
begin
for r in (select * from my_table) loop
insert into my_table (...) values (...)
end loop;
end;
So, I want to insert a row into that table, with values for all columns being those returned by the for loop, but without a column (i'm giving the value for that column).For example, the first 19 rows should contain the values from the currently record in 'for.. loop', and the last column to contain a static value.Is it possible to do this, without specifying in the 'values' clause, something like (r.col1, r.col2, .... r.col19, 'G')? meaning other insert method.
Recognized Way Of Replacing Entire DB With Impdp
Nov 15, 2012I did an expdp on the prod DB and have been doing a straight impdp as a test, just to have data to work with, but it spews 214 errors. Mostly these:
ORA-31684 lots
ORA-39083 some
ORA-39151 lots
ORA-39082 lots
ORA-39111 lots
ORA-39112 lots
I can see that I can use REUSE_DATAFILES & TABLE_EXISTS_ACTION to overwrite tables by default, but is there a recognised way of replacing the entire DB with the impdp? Do I just create the instance, (with the init file) and not build/init the tables, or what? I'll experiment, but I'm just interested if there is a DBA best practice for this sort of thing.
Backup Entire Database Without Archived Logs?
May 13, 2011Backup your entire database, without archived logs, while the database is open for user activity. This backup should be the base for an incremental backup strategy
View 1 Replies View RelatedApplication Express :: How To Highlight Entire Classic Report Column To Red
Sep 20, 2012We have a requirement, to highlight an entire column of a classic report based on a query to red. So if the search of the report returns column1, column2 as the output. Column 2 (text) should be highlighted in red across all results rows.
In some forum responses, I see using some id format, one can try to highlight a single column in a row. Not sure how can this be done across the whole column of multiple rows.
Forms :: Changing Current Visual Attribute Property For Entire Application?
Feb 18, 2013I want to change the current visual attribute color of my form at the application level, reason being i don't want to generate all the forms again from scratch.I know that we can change this using visual attributes but is there a way to change this through some form setting?(Oracle 10g)
View 3 Replies View RelatedSQL & PL/SQL :: Entire Insert Script Row Will Be Inserted Into Cbxrdlog Table Sqltext Column
Aug 24, 2011I have 2 tables. Cbxrd and Cbxrdlog. If the Cbxrd table having creation date column. when it inserts the row. if the column is null value. then the entire insert script row will be inserted into Cbxrdlog table sqltext column.
i have attached the trigger script. when i execute the table but i shows error like
"Ora-0756 Quoted String not properly terminated".
"Ora-04098: trigger os_wm_sit_owner.cbx trigger is invalid and failed revalidation.
SQL & PL/SQL :: Date Format Picture Ends Before Converting Entire Input String
Jun 29, 2011I am trying to insert a row in a table and getting the below error.
SQL> insert into tbl_force_charging(ANI, date_time, durations,src, circleid)
2 values ('9569333585','29-JUN-11 03.19.41.000000000 PM','1027','51010','BIR'
)
3 ;
values ('9569333585','29-JUN-11 03.19.41.000000000 PM','1027','51010','BIR')
*
ERROR at line 2: ORA-01830: date format picture ends before converting entire input string
Table Structure is
Name Null? Type
----------------------------------------- -------- ---------------------------
ANI VARCHAR2(10)
DATE_TIME DATE
DURATIONS VARCHAR2(10)
SRC VARCHAR2(10)
CIRCLEID VARCHAR2(10)
SQL>
Real Application Clusters :: How To Completely Shutdown The Entire Crs Stack In All Nodes
Nov 11, 2013Grid version: 11.2.0.4Platform : OEL 6.4
To shutdown the entire crs stack, I ran crsctl stop cluster -all from one node. After the command execution, the below mentioned processes were still running on all nodes.
[root@intsmdp01 ~]# ps -ef | grep d.binroot 38562 33228 0 19:12 pts/2 00:00:00 grep d.binroot 80820 1 0 Nov09 ? 00:20:56 /crs/product/11.2.0/bin/ohasd.bin rebootgrid 81448 1 0 Nov09 ? 00:01:22 /crs/product/11.2.0/bin/mdnsd.bingrid 81459 1 0 Nov09 ? 00:01:55 /crs/product/11.2.0/bin/gpnpd.bingrid 81473 1 0 Nov09 ? 00:17:26 /crs/product/11.2.0/bin/gipcd.binroot 81484 1 20 Nov09 ? 09:10:27 /crs/product/11.2.0/bin/osysmond.bin
So , I had to manually run crsctl stop crs on all nodes.
[root@intsmdp01 ~]# /crs/product/11.2.0/bin/crsctl stop crsCRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'intsmdp01'CRS-2673: Attempting to stop 'ora.crf' on 'intsmdp01'CRS-2673: Attempting to stop 'ora.drivers.acfs' on 'intsmdp01'CRS-2673: Attempting to stop 'ora.mdnsd' on 'intsmdp01'CRS-2677: Stop of 'ora.crf' on 'intsmdp01' succeededCRS-2673: Attempting to stop 'ora.gipcd' on 'intsmdp01'CRS-2677: Stop of 'ora.mdnsd' on 'intsmdp01' succeededCRS-2677: Stop of 'ora.drivers.acfs' on 'intsmdp01' succeededCRS-2677: Stop of 'ora.gipcd' on 'intsmdp01' succeededCRS-2673: Attempting to stop 'ora.gpnpd' on 'intsmdp01'CRS-2677: Stop of 'ora.gpnpd' on 'intsmdp01' succeededCRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'intsmdp01' has completedCRS-4133: Oracle High Availability Services has been stopped.[root@intsmdp01 ~]#
Is there a way to bring down the entire crs stack in all the nodes in the cluster from one node ?
SQL & PL/SQL :: ORA-01830 / Date Format Picture Ends Before Converting Entire Input String
Feb 26, 2013when i run this query i am facing date format error.
select sbrueregister.UEIMSI,sbrueregister.fapid,sbrfapslid.slid,sbrfapslid.ACTIVATION_TS,sbrfapslid.DEACTIVATION_TS from SBRFAPSLID INNER JOIN sbrueregister ON sbrfapslid.fapid=sbrueregister.fapid where sbrfapslid.slid='1234567890' and sbrueregister.registeredat between TO_DATE('2013-02-1.12.0. 10. 123000000','YYYY-MM-DD HH.MI.SS.SSSSSS')and TO_DATE('2013-02-1.12.9.10.123000000', 'YYYY-MM-DD HH.MI.SS.SSSSSS');
ORA-01830: date format picture ends before converting entire input string
SQL & PL/SQL :: JSP Limitations - Date Format Picture Ends Before Converting Entire Input String
Feb 21, 2010I am using
SELECT TO_CHAR(TO_DATE((:BILL_VALUE),'J'),'Jsp') FROM sys.dual;
its givinig right result for
1023411->One Million Twenty-Three Thousand Four Hundred Eleven
but if i will change it to 10234111 then this query giving an error
ORA-01830: date format picture ends before converting entire input string.
Is there any limitation or is there any other built in function to get the number to a word.
Server Administration :: Accessing Single Schema From Multiple Schema Logging?
May 16, 2011A single master schema where many developers are accessing. all share same password.
now i would like to trace all the changes made by each users. so i create a individual users for all and grant permission to access that schema.do i have a possibility of auditing the changes did by each user for that particular schema
Security :: Schema Consolidation And User Schema Mapping Based On Service
Jul 24, 2011We have an application with many separate databases (one per customer). Given they share the same business requirements (service hours, change mgmt etc), we're interested in potentially consolidating the separate DBs (which are relatively small) into separate schemas within a fewer no of databases to reduce the overhead.
Our issue is that the application is hard-coded to use a specific administrator and application connection user name. Changing this is unfortunately not an option.
Given this limitation, is there any possibility to map a generic user into a customer-specific schema based on the database service that they connect to? Each customer connects to different database services but may use the same user name. We considered using private synonyms but this seems to acheive the opposite (i.e. many different users could connect and map to a single users schema). One thing to point out is that where there is a single user name, it is acceptable for a single password to be used across the different customer DBs as they will be a single admin/user.