Questions tagged [varchar2]
The VARCHAR2 datatype stores variable-length character strings.
varchar2
197
questions
0
votes
2
answers
69
views
ORA-22921: length of input buffer is smaller than amount requested
While I was working with a table containing a BLOB column:
SELECT id FROM table WHERE blob_column LIKE '%something%';
...I got the following error:
ORA-22835: Buffer too small for CLOB to CHAR or ...
- 10.2k
0
votes
1
answer
47
views
In Oracle what is the default data type of varchar2, CHAR or BYTE? [duplicate]
In oracle you can apparently specify if you want the varchar2 data type length to be represented as byte length or char length.
varchar2(10 byte)
vs
varchar2(10 char)
My question is, if I don't ...
- 1,541
0
votes
1
answer
45
views
create oracle materialized view with fields having varchar2(char)
I have a requirement to create a materialized view in oracle. The base table is already there and in the MV, i just have to extract only a set of fields. The base table is having VARCHAR2(CHAR) for ...
1
vote
1
answer
117
views
out varchar2 in package.procedure is giving numeric or value error
I have a procedure in a package. there is an out variable p_return_msg VARCHAR2.
right after the BEGIN I have p_return_msg := 'Validation successful.'
this causes
ORA-06502: PL/SQL: numeric or value ...
- 23
0
votes
2
answers
41
views
Convert Varchar2 into time
I want to convert a column from VARCHAR2 into a time related data type.
For example, from
153312 into 15:33:12
I tried with
to_timestamp(column, 'hh24:mi:ss') as time_column
, but it gives me as a ...
- 99
0
votes
0
answers
57
views
How to save varchar2 column in oracle using PySpark?
Since this bugfix I'm unable to save data as varchar, only clob.
The problem is that oracle does not allow index on cblobs. I need to save ROWIDs in varchar2 columns to manage MLOG tables. My save ...
- 53
-1
votes
2
answers
71
views
Is there a range of VARCHAR2 which can be automatically convert to NUMBER in SQL?
I have a plsql cursor like this:
CURSOR get_customer_balance(cust_id_ IN VARCHAR2) IS
SELECT t.cust_balance
FROM customer_tab t
WHERE t.customer_id = cust_id_;
Procedure body:
OPEN ...
- 885
-2
votes
1
answer
1k
views
What could cause ORA-00910: specified length too long for its datatype?
While running rdsadmin.rds_file_util.read_text_file, I got error "ORA-00910: specified length too long for its datatype". After extensive investigation, I found out the cause.
The system is ...
- 868
1
vote
1
answer
93
views
VARCHAR2 column formatting in spool file
I'm working on an assignment and the spool output is getting some weird formatting for VARCHAR2 function returns. The class uses Oracle SQL 11g.
I have this PL/SQL script:
SET echo on
SET wrap off
SET ...
- 23
0
votes
1
answer
169
views
Remove Duplicate nested phrases from a string with Oracle Regexp_replace
as part of a PL/SQL script, we are setting a Varchar variable called 'V_COMMENT' as such:
V_COMMENT := INCOMING_COMMENT || '[' || V_COMMENT || ']';
because this statement is run potentially multiple ...
- 18
0
votes
1
answer
840
views
Oracle VARCHAR2 - Inserted value too large for column
Working on a Java/Spring system with Oracle(19c Enterprise) database, I have recently bumped into this error:
database.org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback;
...
- 1,152
0
votes
2
answers
527
views
Split json field to extract value in oracle sql
I have a table with a varchar2 type field in which a simil "json" like this is saved:
json data{
first:one
second:two
}
the string "json" present in the example above is ...
- 117
1
vote
1
answer
4k
views
How to convert CLOB to VARCHAR2 in Oracle
I am trying to transform a data that has been saved in a table as CLOB to varchar2. When the query is executed the value that is returned is still a CLOB. What can I be doing wrong when it comes to ...
- 65
2
votes
1
answer
702
views
LISTAGG 4000 Character Limit - Result of string concatenation is too long [duplicate]
select t.name, listagg(t.text)
from user_source t
group by t.name;
I am trying to execute the code above but since varchar2 is limited by 4000 chars it throws error. I tried to convert listagg ...
- 45
0
votes
1
answer
32
views
Oracle: how to convert varchar2 to date
Oracle 19.3 on Win 2019
I have a VARCHAR2 column that used to store dates as "2013-01-27T10:47:09-08:00" by the front end application, but now it's being stored as "12/09/2021 04:58:24 ...
- 481