SQL & PL/SQL :: Passing Bind Char Correctly
Dec 7, 2010
I tried google and most of bind example using number instead of character, ?
I have the code as below:
variable oldId varchar2(5)
exec :oldId :='&1'
variable newId varchar2(5)
exec :newId :='&2'
[Code]...
Since in TAB1, ID is defined as varchar2(5), and I have defined both oldId and newId as varchar2(5) in my code, I wonder why it doesn't work.
It returned this error:
DECLARE
*
ERROR at line 1: ORA-01722: invalid number ORA-06512: at line xx
After supply the value for oldId(val2) and newId(val1)
SQL > @thisScript val1 val2
I use dbms_output.put_line to print the command executed, it seems it is doing
UPDATE TABLE1 SET ID = val1 WHERE ID = val2
without the single quote ('val1'), ('val2'). But if I put in single quotes, the output statement
UPDATE TABLE1 SET ID = ':newId' WHERE ID = ':oldId'
The same bind logic works in SQL prompt
SQL > variable oldId varchar2(5);
SQL > variable newId varchar2(5);
SQL > exec :oldId :='val2'
SQL > exec :newId :='val1'
SQL > UPDATE TABLE1 SET ID = :newId WHERE ID = :oldId;
1 row updated.
I am wondering what should I do then? With or without single quotes?
View 9 Replies
ADVERTISEMENT
Apr 9, 2013
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
[Code]...
View 9 Replies
View Related
Apr 9, 2013
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 takinghere is the detailed program ... any body can tell 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='%'
[code]....
View 4 Replies
View Related
Oct 27, 2010
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.
View 6 Replies
View Related
Jul 22, 2009
I have a table with column val1 having data's starting with 0920 and 4 digit char values.
so we have the query to find
select * from table
where val1 between '0920' and 'ZZZZ'
will it work fine?
View 1 Replies
View Related
Jul 25, 2011
I would like to exit from a cursor loop based on certain conditional checking. I am checking for a lot of different parameters and if they fail, I want to bypass it and fetch the next record in the cursor. I tried just putting an 'Exit' statement in the logic, but it fails. An example of my code is below:
For Row1 in cursor1
Loop
If amount < 0 then
balance := 0;
Else
[code]...
View 8 Replies
View Related
Aug 13, 2012
The thing is I am not getting any output when i run your procedure.I need to set any nls language? .. even i try to run below qry from the another thread,
column french format a20
with t as (
select 'fish' txt from dual union all
select 'dog' txt from dual union all
[code]...
My output is blank for the french values. I am sure that I missing something, But i dont know what I am missing .currently my CHARACTER SET WE8MSWIN1252
View 3 Replies
View Related
Oct 25, 2012
I am using Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production installed in IBM AIX 6.1.0.0
I find this peculiar situation that the alert log is not getting update with proper timestamp for example i see an update now under my alert log of this fashion
Tue Oct 23 08:38:46 2012
ARC2 started with pid=37, OS id=50
Tue Oct 23 08:38:46 2012
ARC3 started with pid=38, OS id=178
Tue Oct 23 08:38:46 2012
[Code]...
But the current system time is
Tue Oct 23 18:40:20 GMT+05:30 2012
My memory_target parameter value is 2GB
SQL> SELECT TZ_OFFSET(SESSIONTIMEZONE), TZ_OFFSET(DBTIMEZONE) FROM DUAL;
TZ_OFFS TZ_OFFS
------- -------
+05:30 -05:30
I am clueless.
View 3 Replies
View Related
Sep 13, 2012
Im trying spooling a file in DB with below mentioned details.
SET SERVEROUTPUT OFF
set heading on
SET PAUSE OFF
SET FEED OFF
SET PAGES 0
[code]........
file .sql has 20 odd columns.
When file is spooled, it shows no columns headers at the top, why is it happening, I don't want to spool column headers separately.
View 12 Replies
View Related
Apr 11, 2013
I have a block based on a view. The view is a join on 2 tables, the first table always brings back 4 records for each parameter passed to it in the where clause. The second table is outer joined to the first table and may contain no matched records or some matched records. In some cases there will be a 1:1 match to the first table.
The problem is how to create the table handler procedure correctly. I need to update 2 tables in the table handler procedure.
The block is only enabled for update (to preserve the 4 rows), however some values on the block correspond to values from the second table. When you update a row in the block, how do you know if you are actually inserting a row into the second table or updating an already existing record on the 2nd table.
View 1 Replies
View Related
Aug 14, 2012
I have a page process that calls back end PL/SQL. I have a page item that is populated by said PL/SQL procedure, P35_PROCESSING_MSG. As you know the Process allows me to specify a message for Success and another for Failure. My desire is that, if there is a back end failure, the error will display in the dialog area, using the built-in "Process Error Message" functionality. Instead, after execution, the page item still has its default value and the following, ugly, error displays in the tabular report region.
report error:
ORA-20001: Error fetching column value: ORA-01403: no data found
ORA-06510: PL/SQL: unhandled user-defined exception
I just don't get why it displays here instead of in the usual failure area. Moreover, I don't understand why the error is "*+unhandled+* user-defined exception" when, as you will see, I have handled it.
I have a page process to perform custom MRD for a tabular form. (I don't think this is really germane to the more generic issue but, I bring it up as an explanation for the following code sample). I am deliberately causing a DIVIDE BY ZERO error to test the error handling form the back end.
PROCEDURE PROCESS_MARGIN_CALL_DELETES( as_StatusMsg OUT VARCHAR2 )
IS
lb_InnerErrorOccured BOOLEAN := FALSE;
ln_DeleteTargetCnt NUMBER := 0;
ln_DeleteTargetRow NUMBER := 0;
BEGIN
[code].......
View 20 Replies
View Related
Oct 15, 2012
I am using Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production version
I am having the data in following table -
drop table stud_fact;
create table stud_fact(stud_NM, LVL_CD,ST_DT_DIM_KEY,OVRNK) as select
'ABG Sundal','H','20110630','175' from dual union all select
[Code].....
View 8 Replies
View Related
Jul 1, 2010
I have a problem with my DB agent. it doesn't upload data correctly, so I can't see updated information in first page of EM ( it doesn't show cpu usage, Active Sessions, Listener properties,...). when I checked the logs, I found that emagent.trc shows this error: "exceed max amount of upload data: 77 files, 52M, disabling collections warn : collector : disable collector"
( I use Oracle Database 10g on Suse Linux 10.2)
View 3 Replies
View Related
Oct 7, 2013
I have used a HUAWEI dongle directly connected to my PC. First, the operating system and Oracle start up, then I connect to the internet using the Huawei Mobile Partner utility. Any application can connect to Oracle and use it correctly. All OK. Now I have added a 3G router which is connected to the PC by ethernet cable and has the Huawei dongle connected to a USB port on the router. All works correctly if I follow the sequence:
1) Start the PC with the router switched off, and wait until Oracle has fully started.
2) Power the router up and wait 2 minutes until Windows says the 100 Mb ethernet link is open. Oracle.exe in Task Manager is about 210 Mb, and sometimes more, depending on activity.
However, if I switch on the router first and then start the PC, Oracle does not start correctly. It rejects connection requests forever more, indicating Network Adapter could not establish the connection: Code 17002.In Task Manager Oracle.exe occupies only 145Mb. What can I do to get it to start correctly ? I can dig into Listener.ORA, TNSNAMES.ORA and HOSTS without any problems, and have often modified them manually (not the case here), but logs and traces escape me !
View 3 Replies
View Related
Aug 21, 2013
I am running my oracle apex application on 4.2.2 on apex.oracle.com. Exporting the application seems to work but when I go to import the application as a copy the regions within a particular page are not working correctly. I want to be able to export the application and import a new copy as a DEV/TEST application and have it function the same way. On the original application the region will display to the right when hovered over the edit button but in the new one it will go to the left and not format correctly.How I would like it is just for the copy application to perform the same way the original copy does. I selected to install all dependent objects and don't know what steps I have missed to have the regions not working like they do on the original application. related to CSS not exporting/importing correctly?
View 0 Replies
View Related
Aug 22, 2013
I have an application item that receives a web service result. This result is like 'MARIA','JOSE','JESUS'. I'm using this string into the parameters of the interactive report, but this is not recognized. I'm showing the content of the application item into a pl sql region and the content is 'MARIA','JOSE','JESUS', when I include this application item into the query, the IR shows me 'MARIA','JOSE','JESUS'
View 1 Replies
View Related
Dec 13, 2012
oracle 11 g xe
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)?
View 5 Replies
View Related
Feb 11, 2009
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.
View 3 Replies
View Related
Feb 7, 2013
converting a string to date, my string looks like '01022013' '04022013', I tried to_date('01022013', 'dd-mm-yyyy') but couldn't work.
View 6 Replies
View Related
Mar 16, 2011
I've got a table with varchar records and I need to separate numerical value and convert into the number datatype.
create table tmp_mape
(remark varchar2(100) )
insert into tmp_mape values ('Dobitie zdarma 3,32EUR 0910105067 02/02')
insert into tmp_mape values ( 'Dobitie pravidelné 9,00EUR' )
I just need to get values 3.32 and 9 from that example into the new column.
The select below returns ORA-01722: invalid number
how to solve it?
select remark,
to_number( to_number(REGEXP_SUBSTR (remark, '[[:digit:]]+', 1) ) ||','|| to_number(substr(REGEXP_SUBSTR (remark, ',[^EUR]+', 1) ,2) ) )
from tmp_mape
View 7 Replies
View Related
Dec 23, 2012
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;
where the to char function would occur?
tables for reference
CSPRODUCT contains (PRODCODE, PRODNAME, CATCODE, PRICE, PARTOF_PRODCODE)
CSCATEGORY contains (CATECODE, CATNAME)
CSORDER contains (ORDERID, ORDERDATE, CUSTID, PAID)
CSORDERLINE contains (ORDERID, PRODCODE, QUANTITY)
View 3 Replies
View Related
Oct 9, 2013
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.
Part_Description
Probengefäss kompl.
Stützring zu Probenehmer
Zargendichtung m.2 Ventillöcher
Pneumatikdichtung m.2 Ventillöcher
Result from above example
ä.üö
View 8 Replies
View Related
Aug 18, 2011
I am trying to compare 2 char columns using trim to avoid space padding
CREATE TABLE TRIAL_A
(ABC CHAR(6));
INSERT ALL
[Code]....
View 11 Replies
View Related
Apr 1, 2011
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.
View 2 Replies
View Related
May 16, 2008
How to find out lowercase char in a String in a SQL query
for example
"ORAclE" here c and l are lowercase so how can i find out this condition... is there any build in function in oracle?
View 13 Replies
View Related
Oct 28, 2011
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]....
View 17 Replies
View Related
Jul 6, 2011
I am using an portuguese(brazil) Oracle 11g db,which should have ',' as decimal char. it seems to be working fine when i m trying to get numerical data via select query,but when i am trying to retrive the same data using out parameter of an sproc.I am getting data with '.' as decimal char.
View 2 Replies
View Related
Jul 26, 2012
I want to get the last month of my table “a” (Which is in number format) and after that I want to change it to a date format and insert into my table “b”.
I have already done the max month query:
SELECT MAX(MONTH_ID) FROM Table_a;
SELECT DISTINCT MONTH_ID ---This data is the one that I would like to change format "mon-yyyy"
FROM Table_a
WHERE MONTH_ID = (SELECT MAX(MONTH_ID) FROM Table_a);
I am getting trouble with the to_char function, I have been reviewing how it works and how to handle it.
View 3 Replies
View Related
Oct 12, 2012
From a list of values from a number column, I need to verify is there any values not satisfying number(3).
Ex- I have a column col1 where we have following values
'qwq'
'12.3'
'1'
'15'
Can I write a query to check the value not satisfying number(3) datatype.Eg the query should return
'qwq'
'12.3'
as the above two will not fit in to number(3) datatype columns
View 13 Replies
View Related
Jul 20, 2010
With a very large database (VLDB) for a data warehouse (DW) using primarily a STAR based schema in an environment in which time (both human and CPU) is orders of magnitude more valuable than storage capacity, is there any signficant difference in query performance when tables have all fixed length (CHAR) columns compared to tables with variable length (VARCHAR2) columns?
I realize this is one of those "in general" questions so considering "a given VLDB DW environment" with all other things being equal, what, if any, is the time based performance difference between a database of tables with all fixed sized columns versus one of tables with variable length columns ?
View 2 Replies
View Related