SQL & PL/SQL :: Escape Sequence Didn't Work

Dec 30, 2011

I'd like to use escape sequences in plsql but it didn't work.I have to use a variable 'v_jahr'..This is what I like to do:

SELECT COUNT(TABLE_NAME) FROM USER_TABLES
WHERE TABLE_NAME = 'TABLE' || v_jahr;

But I think I should use an 'EXECUTE IMMEDIATE' like this:

EXECUTE IMMEDIATE 'SELECT COUNT(TABLE_NAME) FROM USER_TABLES
WHERE TABLE_NAME = 'TABLE' || v_jahr';

But then I have to escape my tics. How can I do this?

I tried this:

EXECUTE IMMEDIATE 'SELECT COUNT(TABLE_NAME) FROM USER_TABLES
WHERE TABLE_NAME = '||''''||'TABELLE_'||'''' || v_jahr ||'''';

View 3 Replies


ADVERTISEMENT

SQL & PL/SQL :: How Does Oracle Sequence Work Internally

Sep 26, 2011

how does a oracle sequence work internally ?

View 1 Replies View Related

SQL & PL/SQL :: Escape Character Meaning

Jul 6, 2010

i want to understand each and every concept of oracle.in this book they explained about escape character, but stiil i cant get it..i want to understand why used escape character in 2nd query and whats its effects...

1. SQL> SELECT first_name, last_name
FROM employees
WHERE first_name LIKE 'Su%'
AND last_name NOT LIKE 'S%';

2. SELECT job_id, job_title
FROM jobs
WHERE job_id like 'AC\_%' ESCAPE '';

View 9 Replies View Related

SQL & PL/SQL :: Escape Character In Query

Jul 11, 2013

I am creating a dynamic sql and to do so I want to concat this string:

v_consulta := v_listado_condiciones || 'UPPER(nombre) like UPPER(''/%'' '|| PRAZONSOCIAL ||' ''/%'') escape ''/'' '

But I get errors when I try to escape % operator, how could I do that ?

View 9 Replies View Related

SQL & PL/SQL :: Escape Operator Symbol?

Feb 28, 2011

1) Can we set a different symbol other than '' for escape operator.
2) If yes, how to see the current escape operator symbol.
3) How to find out the below name with escape operator?

Employee name ----> rama_krishna_raj

View 2 Replies View Related

SQL & PL/SQL :: Escape Character For Single Quote?

May 6, 2011

I have a simple update statement. Sometimes the data in this statement has single quotes in it (like shown below).

Update table1 set account = 'CD'S NOT MINE' WHERE NUMBER = '0027201'

When I run this SQL, I get SQL Error: ORA-01756: quoted string not properly terminated
01756. 00000 - "quoted string not properly terminated"

Is there an escape charecter that I can use?

View 9 Replies View Related

SQL & PL/SQL :: Using Escape Character In Dynamic Query?

Aug 28, 2012

I'm trying to replace the variable value v_tblname in dynamic SQL. But not able to escape the single quotes character.

DECLARE
v_sqlcols VARCHAR2(2000);
v_sqlcols1 VARCHAR2(2000);
v_tblname VARCHAR2(50) := 'DEPT';
BEGIN
v_sqlcols := q'[SELECT LISTAGG(COL,',' ||CHR(10)) WITHIN GROUP (ORDER BY COL)
FROM (

[code]....

View 5 Replies View Related

SQL & PL/SQL :: Escape Special Character Word

Jan 7, 2011

E1 Table Data

Job_id
Sa_Clerk
Sa_Manager
Sahil

select job_id from E1 where instr(job_id,'_')>0;

returns output

Sa_Clerk
Sa_Manager

same as

select job_id from E1 where job_id like 'Sa_\%' escape '';

resturns

Sa_Clerk
Sa_Manager

Same Result , Then What is exact need of Escape

View 4 Replies View Related

Export Data Using Escape Character

Jul 2, 2012

I have got a requirement where i need to export data from oracle with escape character.

eg. I am using a delimiter 237(í) and if the same character is present in data it should be escaped by escape character eg. /.

Once this file will get created i need to load this file in Netezza database which supports escape character.

Data in oracle table
FirstName     Lastname     Designation
abc     xyz     mnz
def     ghío     pqr

Data should be exported like below
FirstnameíLastnameíDesignation
abcíxyzímnz
defígh/íoípqr

View 3 Replies View Related

PL/SQL :: Escape HTML Content Without Escaping Tags

Jul 4, 2013

How can I escape HTML content without escaping the HTML tags?

View 4 Replies View Related

SQL & PL/SQL :: How To Escape Comma While Exporting Data From Table Into CSV File

Apr 9, 2012

How we can escape comma while exporting data from table into csv file.

CREATE TABLE emp
(
EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10 BYTE),
JOB VARCHAR2(9 BYTE),
MGR NUMBER(4),
HIREDATE DATE,
address varchar2(100),
[code].......

i have to export data from emp table which has address column and address column contain comma, when i am running below script, the comma part in address field comes in next tab in csv file, is there any way we can avoid shifting to next tab and can have complete address in one tab.

set echo off
set verify off
set termout on
set heading off
set pages 50000
[code]....

View 9 Replies View Related

Server Utilities :: How To Use Escape Character For Loading Data Via Sql Loader

Mar 7, 2012

I have to load a fixed width file using sql loader utility. But the records have multiple special characters. writing / modifying the loader utility to load the data.

--Script to create the table
create table t1 (
ip1 varchar2(2),
ip2 number,
ip3 number);

--loader utility
LOAD DATA
INFILE 'c:inputfile.dat'
BADFILE 'c:adfile.bad'
REPLACE
INTO TABLE t1
FIELDS TERMINATED BY '' OPTIONALLY ENCLOSED BY '°'
(
ip1POSITION(1:2) CHAR,
ip2POSITION(3:17) INTEGER EXTERNAL ":ip2/100",
ip3POSITION(18:32) INTEGER EXTERNAL ":ip3/100",
)

--data file
9900000000000000000000059762160°
9900009694635473¶00009693856712-
99000024383898654000025664467904

--sql version i am using
SQL*Loader: Release 9.2.0.1.0 - Production on Wed Mar 7 18:32:33 2012
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

In the above mentioned data file, records has multiple special characters like '°','¶' ,'-'.
All these special characters have some meaning.
eg: '°' specifies the above column needs to be multiplied by -1
'¶' specifies the above column needs to be multiplied by -0.1

what changes need to be made in loader utility for the same? Also, will there be any change in the utility if I am using higher version of oracle?

View 13 Replies View Related

Server Utilities :: Escape Character For Loading Data Via Sql Loader

Dec 1, 2006

I have a text file which is comma separated with values enclosed in double quotes.

In my text file which I have to load into database, one of the field have the value like

Your "offspring"

When I run my normal sqlloader ctl file, it gives the error as

Record 304: Rejected - Error on table BUYER, column BUYERS_NAME.
no terminator found after TERMINATED and ENCLOSED field

Is there any way I can use some escape character for loading this type of data.

View 16 Replies View Related

Client Tools :: Escape / Quote Ampersand (&) Because No Variable Substitution

Apr 5, 2011

I wish to replace the string "Benutzername&Kontakt" with nothing.

SELECT REPLACE (role_owner, 'Benutzername&Kontakt', '')
FROM JC_NAME_62
WHERE jc_name LIKE 'SAP_R4_BIN5\_%' ESCAPE ''

But my Toad prompts me always to fill in the variable. I don't wish to do that, I wish to escape the &

View 5 Replies View Related

JavaArch8v3 Work With 11g

May 28, 2013

Can JavaArch8v3 work with Oracle 11g?

View 2 Replies View Related

PL/SQL :: NOT IN Clause Does Not Work

Oct 4, 2012

with the below example I want to insert new translation in a translation table but the NOT IN clause does not work. I have also tried an ANTI-JOIN after reading much about why the NOT IN might not work but the ANTI-JOIN did not work either.The result of the above query is 0 rows inserted. There are about 1000 rows that do already exist and I want to avoid inserting those again. Note, only the Text is the same not the ID or SOURCE.

INSERT INTO T_Translations
(
     SELECT ID, SOURCE, DOMAIN, TEXT FROM
     (
          SELECT distinct ID, SOURCE, DOMAIN,
          TransText AS TEXT
          FROM (
[code]....

View 3 Replies View Related

Text :: How Does ACCUM Work

Jun 21, 2012

how ACCUM works. We are using a text index over XML documents and want to apply a very simple relevance weighting based on occurrences within a certain tag. For example, if TAG1 has a weight of 10, each occurrence in TAG1 should add 10 to the score. We have 13 Tags that should be weighted and multiple occurrences in different tags should be cumulated. So 3 occurrences in TAG1 (weight 10) and one occurence in TAG2 (weight 7) should result in a score of 37.

Just searching in TAG1 works as expected:
( DEFINESCORE( foo, OCCURRENCE ) WITHIN TAG1 ) * 10Three occurrences score 30.

Just searching in TAG2 works also as expected. One occurrence scores 7. However, when I combine both searches
(( DEFINESCORE( foo, OCCURRENCE ) WITHIN TAG1 ) * 10) ACCUM (( DEFINESCORE( foo, OCCURRENCE ) WITHIN TAG2 ) * 7)the score is 61 instead of 37. More interestingly, including all 13 tags (of which most do not contain the search word) lowers the score again.

I ended up with searches where two documents cont

View 7 Replies View Related

XE :: Will 11G Work On 64 Bit Windows Machine

Oct 3, 2012

I would like to down load and install 11g XE beta but I do not know if it will install and run on my 64 bid Windows machine. I had trouble with the 10 G XE trying to down load it on 64 bits.

If I cannot use it on 64 bits could I make it work if I down load Unbuntu Linux? System info as follows:

OS Name     Microsoft Windows 7 Professional
Version     6.1.7601 Service Pack 1 Build 7601
Other OS Description      Not Available
OS Manufacturer     Microsoft Corporation
System Name     XXXXXXXXX (censored)
[code]........

View 1 Replies View Related

SQL & PL/SQL :: What Is The Work Of Owt_text Package

Jun 19, 2013

i just wanted to know what is the work of owt_text package and as well as owt_util package? this package we have to create in database or its in build packages?

View 4 Replies View Related

Backup And Restore Don't Work In Oracle

Mar 22, 2011

I try this for backup:

$ exp scott/tiger FULL=y FILE='d:\demo.dmp';

and this for restore:

$ imp scott/tiger FULL=y FILE='d:\demo.dmp';

and after restore i can't see the data in my tables. (i work on Oracle 11.2.0 64bit)

View 2 Replies View Related

SQL & PL/SQL :: Oracle Procedure Doesn't Work On Web

Jul 28, 2010

I was working on an applications program It is written in PL/SQL . It is running on an oracle 8i database It was not written by me but now I am responsible of it. I made some changes to database tables for this years regulations. Everything was working fine but yesterday I have recompiled two of the packages for some regulations and one of the procedures was stopped working.

It is called 'sayfa2'. Packages names are 'aday1' and 'aday2'. They both have a procedure called sayfa2 and both of them don't work now. They gave me no compilation errors. All I did is I have copied the original code from toad and compiled it at sqlplus I have changed nothing except some texts inside html tags. I couldn't guess what is wrong.

It actually converts to html pages. All procedures are working fine except sayfa2. aday1 is for to fill information and submit but when I click submit button it gives an error:

The requested URL /pls/kon/aday1.sayfa1 was not found on this server. Oracle HTTP Server Powered by Apache/1.3.19 Server at ...

There is also a problem with Turkish characters they don't appear correctly.

Below is sayfa2 and I also added aday1 package could you have a look at it .

PROCEDURE sayfa2(stuid VARCHAR2,tckimlik VARCHAR2, ad VARCHAR2, ad2 VARCHAR2, soyad VARCHAR2,
sex VARCHAR2, mdurum VARCHAR2, bad VARCHAR2, aad VARCHAR2,
dyer VARCHAR2, dgun VARCHAR2, day VARCHAR2, dyil VARCHAR2,
nil VARCHAR2, nilce VARCHAR2, nkoy VARCHAR2, cilt VARCHAR2,
asira VARCHAR2, sira VARCHAR2, asuba VARCHAR2, kang VARCHAR2, agorev VARCHAR2,
[code].......

View 10 Replies View Related

Forms :: How To Work Without Opening OC4J

Jun 13, 2010

How to work on forms without opened oc4j..

am working on 10G .. Forms 10G

am using the OC4J for the oracle FORMS 10G to me the engine for woring maybe about 20 client ..

my question :

1- if am close the OC4j the forms closed ?
2- how to work forms to be like off line if the oc4j is closed and working tell before save .. ?

View 3 Replies View Related

Client Tools :: How To Work With SQL From Home PC

Feb 16, 2012

I want to practice Oracle SQL from home.What software i need to install. Even if i install SQL developer,SQL+ which database i can access n how.

View 10 Replies View Related

SQL & PL/SQL :: Can Auto Extend Work With Quota

Apr 9, 2012

I have given a specific user default tablespace on Users and a quota 5M. However I want to check if the user have used up 70% of that 5M. If so, I want to increase quota by 500K so in this will be an additional to the 5M. The code I wrote is overwriting the 5M I initially gave the user with 500K. Attached is a sample of the code.

View 14 Replies View Related

Forms :: Go_block Doesn't Seem To Work?

May 7, 2013

I have a master block (MASTER with Item 1 and 2) and details block (DETAILS). If the user enters the query mode and queries the Master block, the Details get populated automatically. So far so good.

Now I attached an LOV to Item 1 in the Master Block and the requirement is that when the user picks an item in the LOV, the details should automatically populate. Because of "restricted procedures" (GO_BLOCK) in triggers, I put a Button in a Control Data Block and the code in the WHEN-BUTTON-PRESSED trigger is as follows :

GO_BLOCK('DETAILS');
EXECUTE_QUERY;

It does go to the Details block and also displays all the details. However, when I try to update, insert or delete data, it fails with "Insufficient Privileges". So when I looked at the query being executed (Cntrl + Shift + E), I can see that it is trying to perform these operations on the Master block. (ie for some reason the scope is still in the Master and not Details even after Go_Block(DETAILS) has been executed.

I then tried to use Go_Item('DETAILS.Dept_No') to see if that would work. That too did not work. I tried to set the Navigation Style on these Data Blocks to "Change Data Block". Still no success.

View 3 Replies View Related

Forms :: Set_menu_item Property Not Work

Jun 6, 2010

The code on menu item is UnVisible_Menuitem('ITEM193');

Menu procedure is

PROCEDURE UnVisible_Menuitem( menuitem_name VARCHAR2) IS
mi_id MenuItem;
ALT NUMBER;
BEGIN
mi_id := Find_Menu_Item( menuitem_name );
[code]...

i try it for the both visible and non-visible items but it dosnt work.

View 1 Replies View Related

Reports & Discoverer :: Will The SRW.MESSAGE Work In 10g

Sep 29, 2010

Will the SRW.MESSAGE work in Report 10g? what command has to be used?

View 1 Replies View Related

PL/SQL :: Why TRUNCATE Command Would Not Work In Block

Sep 10, 2012

Why the TRUNCATE command would not work in PL/SQL block ? A work arround would be dynamic SQL, but I'm avoiding it...

begin
truncate table test;
end;

View 7 Replies View Related

How Does Local Index Work In Partitioned Table

Nov 28, 2012

I have table with 4 partition by range partition. I am loading the table in bulk mode to latest partition. Before I load , I dropped the index and after Load I will be creating index. So when I am dropping index, it is dropping index from all the partitions and when creating the index, I am creating the index for all partitions. When I am creating index using local, it is telling you have to create local index for all partitions at the same time. because of that I have to drop and recreate all indexes again. Again I have to gather stats for whole table .

I was thinking we can build index for one partition and index should remain as is for old partitions If this is not the case, how do I plan my load for a partitioned table using bulk mode to latest partition.

View 4 Replies View Related

Enterprise Manager / Unable To Work On Windows?

Apr 21, 2011

I attempt to log to the Enterprise Manager via --> [URL] but I only receive the 'Page not found' error

As this is on a windows server I attempt to restart the service to no avail Also I try to start emctl start dbconsole - whcih also does not work

The Listener is up and running

If I issue a emctl status oms - I receive error Oracle Enterprise Manager 10g Grid Control not installed. Request Ignored.

with --> emctl status dbconsole

output is Oracle Enterprise Manager 10g is not running.

Logfiles:

Logs are generated in directory D:\oracle\product\10.2.0\db_1/ukedgas71.pcroot.com_OEMREP/sysman/log

Logfiles:

Directory of D:\oracle\product\10.2.0\db_1\UKEDGAS71.pcroot.com _OEMREP\sysman\log

20/04/2011 15:06 10,297 emagent.log
05/06/2009 09:48 0 emagentfetchlet.log
05/06/2009 09:46 0 emdctl.log
20/04/2011 15:05 6,363,995 emoms.log
20/04/2011 15:14 29 OracleDBConsoleOEMREPexit.log
20/04/2011 15:14 188,284 OracleDBConsoleOEMREPsrvc.log
05/06/2009 09:46 4,383 secure.log
7 File(s) 6,566,988 bytes

Tracefiles:

Directory of D:\oracle\product\10.2.0\db_1\UKEDGAS71.pcroot.com _OEMREP\sysman\log

20/04/2011 15:14 1,418,777 emagent.trc
05/06/2009 09:48 0 emagentfetchlet.trc
28/11/2009 16:21 8,360 emagent_perl.trc
21/04/2011 11:12 2,060,670 emdctl.trc
20/04/2011 15:05 1,649,464 emoms.trc
5 File(s) 5,137,271 bytes
0 Dir(s) 3,274,207,232 bytes free

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved