SQL & PL/SQL :: How To Execute Dynamic Query In Procedure With Special Characters
Dec 17, 2012
I have the following procedure body in a package.
PROCEDURE getrecordsForinspection(i_table_name in varchar2, i_thread_id in varchar2, i_max_count in number default null, o_results out sys_refcursor)
AS
v_sql varchar2(1000):= null;
begin
v_sql := 'update '||'i_table_name||' set status = '||'''IN_PROCESS-'||i_thread_id||''''||' Where final_status = '||''''STATUS_ACCEPTED'''||' and ('||i_max_count||' is null or rownum <= '||i_max_count||');';
EXECUTE IMMEDIATE(v_sql);
commit;
end;
when I execute the above procedure it gives the following error.
ORA-00911: invalid character
cause: Identifiers may not start with any ASCII characters other than letters and numbers.$#_ are also allowed after the first character. Identifiers enclosed by double quotes may contain any character other than a double quote. Alternative quotes(q'#....#') can not use spaces, tabs, or carriage returns as delimiters. For all other contexts, consult the SQL language reference Manual.
I think dynamic sql is not executed because of the pipe character in the sql statement.
create 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?
I want to execute a DML query with execute immediate statement. That DML query length exceeds 4000 characters. This query has Xquery related conditions, i can not split the query. when i tried execute it is giving "string literal too long". I tried with DBMS_SQL.Parse() and DBMS_SQL.Execute also, but it is giving same error. I have to execute this DML query inside a Procedure. We are using oracle 10g version
I want to execute a dynamic query which is stored in a Table.
Output of that query should be stored in database server.
Is there any way i can create a dynamic procedure? I have created a sample code but issue is i cannot make the below data type dynamic as per the query.
en com_fund_info_m%ROWTYPE; CREATE TABLE TEMP (SQLSTATEMENT VARCHAR2(100)) DECLARE TYPE r_cursor IS REF CURSOR; c_emp r_cursor; en com_fund_info_m%ROWTYPE;
I have one column name party_name containing Korean Characters and English characters.Some of the English characters have different symbols.My requirement is to get the data and exclude those symbols but not Korean characters.
Already I used a function to replace special symbols with space.The function contains code based on ASCII values it works good but it filters Korean characters too.the attachment of the screenshot, When I double click the name it shows with some question mark.
We are using Release 11.2.0.3.0 of Oracle. I am having below special characters inserted into one of my columns, how this value got inserted, (what is the source) i need to track it down.
We dont have any audit trigger on this table to track one level below. As per JAVA guys this is uploaded through a file and the file is having well defined characters and no special characters for this column value also they uploaded the file again but its now going fine with no such special characters. So they put it on DBA's to find how special characters came into database?
Again the editor is not recognising all the characters , so i got the ASCII value for each of the characters in the string, its as below.
String - ‡Mw‹O--ggÑ ÆÔéÓÞ³ µmT¤O ˆÓ`ôiyïÎ!Ž ASCII character is : ‡ ASCII Value Is : 14844065 ASCII character is : ‹ ASCII Value Is : 14844089 ASCII character is : -- ASCII Value Is : 14844052 ASCII character is : ASCII Value Is : 49793 ASCII character is : Ñ ASCII Value Is : 50065
I need to removed special characters (!, ", #, $, %, &, /, () from a string, i have a table with sll this special characters and words that i have to remove from the string.
How can i do that ?
i have a string with |R!$#&2-_D%2 and i want to get R2-D2
SELECT '|R!$#&2-_D%2' as Original, 'R2-D2' as Correct FROM DUAL
I am using the below query to get the persons details for shell script. Its throwing the error like string not properly terminated but the issue is due to the data is having special characters like below.
select first_name||last_name from employees;
Data output is
O'brainsteve Stevebross
how to remove these special characters like ' in the query string.
I have to use bind variable for dynamic sql in a procedure. Is there a way to have control on these values. Say for example:
Procedur MyProc ( In_EmpID Number default null, In_EmpName Varchar2 default null, in_JoinDate Date default null [code]....
I have more than 5 In parameters, all 5 is not compulsory by default they are null and sql formation is also dynamic with in the procedure.I need to map bind variable to a proper one.. Is there a way to handle bind variable.
We have a table with an nvarchar column that we use to store product descriptions.
This field is maintained on a web form created with the htp package.
Whenever a special character is used (ie, the copyright symbol, trademark, etc), the value is stored correctly in the DB. I've verified this because if i query the value in sql developer, i do see the correct symbol.
However, pulling this piece of data out to display in a formtext box (for edit) replaces all the special characters with ?'s.
Our National Character Set value is set to UTF-8.
Is this being caused by our database character set? or is it a web design problem that i'm just not aware of.
I can post code snippets if this isn't enough info...but I was hoping it'd be fairly simple.
We are using dbms_crypto.hash to generate the MD5 hash value for a CLOB that will be sent to other feeder systems. The feeder systems validate the file by generating the MD5 hash value on their end and compare with the MD5 generated in Oracle and accept the file only if the MD5 hash value matches.
This is in Oracle 10.2.0.4.0. I have some blob records (delimited text uploads) with some diacritic characters in them, and the diacritics are being lost when I run CONVERTTOCLOB.
database character set is AMERICAN_AMERICA.AL32UTF8
as long as the password contains no special chars (namely semicolon) it works fine. but when I try to use special chars in the password It stops working, with an invalid username/password error.
i tried different variations of adding " (\") over the password, over the entire pwd=a;b or braces { but nothing seems to work.the thing is that using ODBC works! what I did was: open ODBC, go to System DSN tab, add "Oracle in instantclient11_1", then fill the following information:
Data source name: Anything TNS Service Name: //10.10.101.66:1521/orcl User ID: myUser
then i press test connection, enter the password "a;b" and it works!
I have this table column of varchar2 data type. I need to find out if it contains anything other than numbers or alphabets(basically to see if it contains any special characters in it).
I just need to fetch only those rows that has special characters. How do I get this?
I believe we need to use Translate function to get rid of special characters, But I would not be knowing what sort of special charecters which appear in the string, In that case how do I use Translate?
In my DB there are special characters are stored like "Świętochłowice". So in Unix script while extracting these characters, I have used export LANG="universal.UTF-8" in order to make it English. which works fine.
But my question is how to retain this special character intact as, it is in DB?
So in UNIX script export LANG="universal.UTF-8" sqlplus -s uname/pwd@hostname/schema << ENDSQL | sed -e "s/Connected.//" -e "/^$/d" set pagesize 0 feedback off verify off heading off echo off serveroutput on size 10000 spool /path/out.txt Select name from tablename where is=12; spool off; exit ENDSQL
Output is "Swietochlowice" (makes sense),but how to get the output as "Świętochłowice" which is in DB? I have tried different NLS_LANG option, but no success.
oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production PL/SQL Release 11.1.0.6.0 - Production "CORE 11.1.0.6.0 Production"
I am uploading the data file which is a .csv, into one of the tables.. Few columns of the data file, contains sentences in German - like- Lötstelle Zinnspritzer Zinnschluß .. and while loading the problem that i encounter is - it doesnt recognize the commas, and doesnt know where the field ends.. e.g., in this line 1540,Lötstelle Zinnspritzer Zinnschluß,solder short,45230,94885,0,0,0,0,0,0,0,0,0,0,0,0
sometimes it assumes comma after 1540,Lö,tstelle and then no commas till Lö, tstelle Zinnspritzer Zinnschlußsolder short,
and sometimes, the whole as one line Lötstelle Zinnspritzer Zinnschlußsolder short4523094885,0,0
I'm working with old code that uses dbms_sql.execute to build/execute dynamic sql. In our case, the user can select varying columns(I think up to 20) with different where conditions as needed.
After building the sql, here's an example
WITH ph AS (SELECT ph.* FROM po_header ph WHERE 1 = 2), pf AS (SELECT DISTINCT pf.order_id, pf.fund FROM po_fau pf, ph WHERE 1 = 1 AND ph.order_id = pf.order_id
[code]....
Where table records for
po_header = ~567746 po_fau = ~2153570
and PK "order_id" is a NUMBER(10) not null and a snippet of the code looks like
nDDL_Cursor := dbms_sql.open_cursor; dbms_sql.parse(nDDL_Cursor, sSQLStr, 2); FOR x IN 1 .. nCols LOOP sCols(x) := ''; dbms_sql.define_column(nDDL_Cursor, x, sCols(x), 100); END LOOP; nError := dbms_sql.execute(nDDL_cursor);
why when the "execute" statement is fired off the elapsed time takes ~4.5 seconds but If I change "1 = 1" above to "1 = 2" it takes ~.2 seconds. If I run the above query interactively it takes ~.2 seconds. Shouldn't the above query when joining
ph.order_id = pf.order_id
return zero rows back instantly or does the "dbms_sql_execute" do some other type of parsing internally that takes cpu time.
The HTML code for the bracket character '[' is & # 91; (without spaces). In my SQL, I would like to convert the word [you into & # 91;you. Is there a way to do that?
I know that replace() will work, but with that you have to supply a list of chars to replace; I
How to represent special characters in oracle reports. i have a field which takes some data in varchar format in this i used some special symbol its fine when store it into the database but when i generate report this special symbols are represented by mirror of ?
I am using http url for Google translator to convert into different languages.
While passing the string, to escape the white spaces, I am using utl_url.escape . But many of my words contain special characters like '+ , @ , $ , ) ,. ,; [, ] etc.
Not sure how to pass these special characters in uri type.
VALERIANA OFFICINALIS L. + HYPERICUM PERFORATUM L. + MELISSA OFFICINALIS L. VALERIANA OFFICINALIS L. + HYPERICUM PERFORATUM L. + [ MELISSA OFFICINALIS] L.
We can execute dynamic sql using both execute immediate and ref cursor..But what is the difference between the two and performance-wise which is better?
