i write a select statement in proc that contains 44 columns.
when i precompile it. it is showing the error: implicit conversion of string literal to "char *" is deprecated.when i compile the same select with 40 columns it is not showing any error.
but for more than 40 columns (41-44) it is showing the above error.
I'm facing some problem even after using INSTR function in Oracle.The problem is I have written the logic in the PL/SQL block which appends all the values fetched in a loop on the basis of whether the string is present or not.
For ex:
The first value fetched from the select query first is ABCDEFG which gets appended to a variable The next value fetched is AB even this has to be appended to the variable since this exactly doesn't match with ABCDEFG. The next value fetched is BCDEF even this has to be appended to the variable since this exactly doesn't match with ABCDEFG. The third Value fetched is ABCDEFG this will not get appended presently according to the logic which is correct.
writing that piece of code to append the value fetched which doesn't exactly match with the existing string
I want to pass english character as a parameter and search a string that having swedish character, this needs to be done for all the swedish characters.
test _t table having the below 3 values
päiväp metervara flerfärgad
1. If user searches based on english char like below then they should get all the 3 values bcz fist & last have swedish ä and second one having english a.
Query : select name from test_t where name like '%a%'
Regsult: päiväp metervara fräg
2. If user searches based on swedish char like below then they should get only 2 values bcz fist & last have swedish ä and second one having english a.
select name from test_t where name like '%ä%'
päiväp fräg
Is any in-built function available, for Text alternative string search. If not how to search the string based on the query.
I wonder if there is any way to return the columns of an select with its letters lowercase?
I have a piece of code that creates an script wich returns an SQL result to be confronted with some templates. My template have the column names in lowercase and because It is case sensitive the Uppercase returned by Oracle,
Using Oracel SQL*Loader, is it possible to specify insert type statements? I need to know if i can pass a lower type call to a column. Specifically, if one of the entries is: username=MyUserName@gmail.com ... Then I need it to do the following lower(username) record inserts as myusername@gmail.com
I'm a bit stuck at the moment. I've googled and found LCase (), but I don't know if I'm barking up the wrong tree with that function.
I am required to manipulate a string in this way.For example ABCDEF)* to $ABCDEF). So I should find * and delete it and instead put $ in the beginning of string.
How can i identify all the occurences of raise_application_error(-20XXX, '<the message>'); within all database objects, and replace them with other text?
I am currently working on a Data Dictionary project where we need to run a few rules against the give data sources to see if they all comply together.
One of the rule is to check if the no. is negative or not. So for that what I tried to do was to check if first the field is number or not and then check on if it is negative or not.
This is the code I am currently trying on and is not looking good.
SELECT 1 FROM DUAL WHERE decode(DECODE( TRANSLATE('-123.45','-0.123456789',''), NULL, 1,0), 1,substr('-123.45',1,1) ,' ' ) = '-'
I have a requirement, where i need to find and replace values in delimited string. For example, the string is
"GL~1001~157747~FEB-13~ CREDIT~ A~N~ USD~ NULL~".
The 4th column gives month and year. I need to replace it with previous month name. For example:
"GL~1001~ 157747~ JAN-13~ CREDIT~ A~N~USD~NULL~".
I need to do same for last 12 months. I thought of first devide the values and store it in variable and then after replacing it with required value, join it back. I just wanted to know if there is any better way to do it?
Quote: I have a table(table name is names) with column as name(varchar) . I have the following data for name column.
Miss Mississ Mississipp
I would like to find a nearest match for Mississippi, that means sql should return row that contains Mississipp( Row #3)
If I try to find nearest match for Mississirr then sql should return row that has column value Mississ (Row#2) Is this possible ? Here is the code for table creation and data.
create table names (name varchar2(20));
insert into names values('Miss'); insert into names values('Mississ'); insert into names values('Mississipp'); commit;
select profilename from PROFILE where user_data like '%DATE_RULE!115%';
Output will be "Test A".Now, this is just a single value from RULES table used to find the data of PROFILE table.I will have to run the query on multiple values of RULES tables to find records containing a string format of sort "DATE_RULE!<rule_no>". How to search on WILD CARDs like these?
1) I want to pass date in '12 Dec 2012'(CHARECTER) while calling SP and in side SP i have declared parameter as DATE so I think it should not create any problem in any condition whether it is passed from oracle client or any other front end?
2) I have date in (date type variable) , how to convert it into char but it should be in iso format (i want iso format , so that it should not create any problem in any condition).
3) How to convert (date in char- in iso or any good format) into (date type variable) (so that it should not create problem and this is why i wanted char date in iso format).
4) If there is any good format and function to convert (date to char) and (char to date)?
Well the company i work for has just recently upgraded from Oracle 9i to 10g. We are having various problems with the migration, and with certain code breaking. My question regards this piece of code;
[code]
to_number(to_char('a_date','YYYY'))
[code]
There are various statements like the one above scattered throughout a query i am trying to fix. When run, the query returns an "invalid number ora-01722" error, which i know is caused by the above code.
if this method of converting from date, to character was discontinued from 9i to 10g?it just seems strange as there are a lot of statements like the one above and they must have worked at some point, but now i cant even get one to work on its own.
i have a problem with my SQL programming which i am learning at the moment. Currently here is my Show how many orders were placed in each month of 2012. (group by question, to char function)
select count(orderdate) from csorder group by orderdate;
I have been tasked with producing a list of all non standard characters that are not one of 0-9, a-z, A-Z from a parts table. The list should capture any non-English characters i.e. áóú etc as well as !"£$%^&* etc. These are a small example of the data.
i trying to pass the char varible to the cursor but it is not taking ,,, if i hardcode the values to the cursor it is taking
here is the detailed program ... why this is not taking and tell me how to pass the values through it..
declare v_name char(6) ; cursor c1(c_name char) is select name, parent,child,status from relation start with name='%' connect by prior parent=child union
I am using forms 6i and oracle 10g. i want to display a char from database. that means a database string 'Bangladesh' is display from by one char to one char.
and display view right to left first display "B" then "BA" then 'BAN' etc.
using listagg function for more than 8000 char.I did the below sample SQL and in "e_orig" and "d_orig" for upto 4000 char it is working fine but I have to use it for more than 8000 char. and it is giving error,I checked the listagg function is having limitation of 4000 char.I tried but I am unable to achieve this.
select d.dname,d.loc,e.hiredate ,listagg(e.ename,',' ) within group (order by e.deptno) over (partition by e.deptno) as e_orig ,listagg(e.ename, ',') within group (order by e.sal) over (partition by e.deptno) as d_orig from emp e, dept d where e.deptno=d.deptno; [code]....