SQL & PL/SQL :: Converting Varchar2 To NVHAR And NVarchar2?
Feb 16, 2011
I have written a trigger & procedure to call a webservice from pl/sql procedure. Everything was working fine until I was told to use nchar & nvarchar2 instead of varchar2 as per requirement. Now I am not able to run the procedure and getting errorcode with return response from server.
I didn't changed the width of columns but only datatype. What precautions do I need to take in code while doing this and what could have caused the error only by converting the data type.
i have a varchar2 column containing string values that can be converted to date i.e. ('31-JUL-11') and that column also contains text strings in it. i.e. ('Some string data...')
records whose column value can be converted to date are extractable via where clause (i.e. those rows are associated with some fix number / flag)
now when i try to use to_date function i get the error that
" ORA-01858 a non-numeric character was found where a numeric was expected "
in sql i have added a where clause to only pick rows with flag, but even then it gives the error.
using a subquery in the from clause eliminates the error, but when i create it in a view it again gives the same error.
i am fairly new in the oracle arena, but what would cause a statement such as
ALTER TABLE TEST_TABLE MODIFY text_field1 varchar2(100) DEFAULT 'testval' NULL
to change a column's type from VARCHAR2(100) to VARCHAR2(100 byte)? i found a few mentions of the 100 byte concept online but nothing that jumped out at me.
I'm having some issues with my NVarchar2 columns that I want to solve
My current NLS_CHARACTERSET is 'AL16UTF16', and therefore I can only create NVarchar2 columns with an maximum extension of 2000 bytes. But I do need a column with 4000 bytes length. How can I create an 4000 NVarchar2 column?
(I do not want to use a CLOB column...only a NVarchar2)
There are at most 2 entries of a in b. Depending on the value of the type column in B, this determines whether the entry should be male or female. I want to have a select statement that will retrieve 2 rows into one row essentially like below, how is this done:
id male_name female_name 1 paul paula
the column names will appear as such, if its a 0 its a male name if its 1 its a female name, there will generally be 2 entries in B for 1 value of a.
I have a column named "col1" with datatype "varchar2(10)" and row wise entries like "1,1A, 2,3...,10,2A,..." like. I want to order it like "1, 1A ,2,2A, 2B,3... 10...".I tried it with to_number() but it gives me
in our application we are using clob column instead of varchar2 because varchar2 does not allow more that 4000 chars, so Using clob allows to put data of any length, will it cause performance issues ? we have this column in almost in all tables .
SELECT CASE WHEN "PIVOT_ROW_GENERATOR"."ID" = 0 THEN "PIVOT_SOURCE"."ID_NUMBER" WHEN "PIVOT_ROW_GENERATOR"."ID" = 1 THEN "PIVOT_SOURCE"."ID_NUMBER" [code].........
There are millions of DISTINCT ID values in TABLE_1 and corresponding to each ID there are some values for all the days of a month. I need to insert these values in TABLE_2 in the above format.
select * from function('input') where the function dynamically returns different tables.
I have gotten to the point where I can return the cursor but I cannot format it as a table to use in the query. It return's it as XML. This is my function:
create or replace FUNCTION CAMS_FUN_GEN_REPORTS(PARAM1 IN VARCHAR2) RETURN SYS_refCURSOR AS BEGIN DECLARE p_recordset SYS_refCURSOR; begin OPEN p_recordset FOR 'SELECT * from STATS_FLAGGED_TOTALS_ME'; RETURN p_recordset; END; END CAMS_FUN_GEN_REPORTS;
This is my query:
select * from CAMS_FUN_GEN_REPORTS('')
So normally you would do this
select * from TABLE(CAMS_FUN_GEN_REPORTS('') as tabletype)
But I will never know the end result table as it will be dynamic so I have to find a way to cast the result of the function into a new table I can select on.
I have looked into pipe lining also but from what I can tell you still need to know the table definition. Which I can't know from the client side. The server will control the 'routing' and thus the end result of the select.
We are able to insert CLOB into database, Using oracle Text I'm able to search inside clob. [two questions solved ]. The question arise when we need to send this data to application either as file or as text(varchar2).
I'm able to generate file from CLOB using function,unfortunately it resides inside db and developer is not able to access it.
1)There is option to mount application partition inside db and export file over there but it is not viable option according to management.
2)I've Googled the solution to create JAVA API. Which will perform OS command like scp to send file from db to app(or any remote host). For security reason this option is also dropped.
3)I tried dbms_lob.substr but actual text inside clob is too long. File generated from CLOB sized around 5 MB.
So I guess it requires lot of effort if I wanted to convert into varchar2 as out parameter inside function.
So, to give CLOB data to developer as file or as varchar2. I'm not able to get any solution. Is there any other option using database to convert to string/varchar2 from CLOB ? Or do I need to drill down more into third option.
I am converting from 10g to 11g app server and having problem with the exec_sql.execute command. In my 10g version, the code is:
Declare lv_dbms_journal_cursor exec_sql.curstype; lv_status INTEGER; BEGIN IF (exec_sql.IS_OPEN (lv_dbms_journal_cursor)) THEN exec_sql.close_cursor(lv_dbms_journal_cursor); [code].....
After this code caused the 11g forms session to crash, I modified the 11g version to the below, and it still crashes.
Once a year in the application we have a specific query that gets used a lot. It's an UPDATE that updates a single record in a single table with a few different datatypes, but the issue is happening with one of the VARCHAR2 fields. It updates one VARCHAR2(2000) and three VARCHAR2(4000) fields at the same time.
This year, 9 of the 95 times it was used resulted in one of the VARCHAR2(4000) fields as null in the database. The users would not want this field to be null and 5 of the 9 have told us they entered something (the form they're filling out is a research proposal and leaving this field empty would be pointless because it's part of the funding request, so they're not doing it). The application isn't doing it because it's not consistent. I've checked the application and these fields can't be nulled any other way.
We just found the issue so I looked back over the past years back to 2005. Last year it didn't happen at all. In 2010 it happened a handful of times. Some years there were even more times. It's not always the same field but it's always a VARCHAR2 of at least 2000 characters.
I have a lot more information but it's all just details (let me know if you need to know more). I'm wondering if there is a bug in 10g with these types of fields. I don't believe it's malicious behavior on an individual's part but I suppose that's always possible.
how to research something like this. I tried to get access to Oracle Support and the Knowledge Base I heard they have but it doesn't look like I can do that
I have 2 tables.The column in table A is number and Column in table B is a varchar2 datatype.I have to use the Column of table B as a filter to column of Table A.Below is the example.
create table A(Col1 number); Inert into A values(1); Inert into A values(2); Inert into A values(3); Inert into A values(4);
Create table B(Col1 Varchar2(100)); Insert into b value ('1,2,3');
Select * from A where col1 in (select col1 from b) Error: Invalid Number
Is there a way to convert the varchar to number.The varchar field have multiple characters (numbers) seperated by commas.
When I try to convert numeric values � number(19) p.s 111111111111111111, the to_char function returns �1111111111111110000� because the to_char functions doesn�t support precision bigger than 15.
