| DBMS_SQL.PARSE [message #687987] |
Mon, 07 August 2023 01:04  |
chat2raj.s
Messages: 161
Registered: October 2010
Location: Chennai, India
|
Senior Member |
|
|
I am using DBMS_SQL.PARSE for a dynamic SQL statement where the SQL stmt is concatenated in a Varchar2(4000) variable. But my SQL is going beyond 4000 char and so i get PL SQL numeric error. Can i use LONG instead of Varchar2 or should i use only CLOB. Any example on how to achieve this would help me.
Thank you
|
|
|
|
|
|
|
|
|
|
| Re: DBMS_SQL.PARSE [message #687992 is a reply to message #687987] |
Mon, 07 August 2023 07:57  |
chat2raj.s
Messages: 161
Registered: October 2010
Location: Chennai, India
|
Senior Member |
|
|
I was able to manage to removed the formatted sql to few straight lines to reduce the number of chars less than 4000 and continue to use the Parse stmt.
I also found another person suggesting to split the SQL into 2 variables and concatenate when using in the Parse stmt. I will try this also if required to expand my query
DBMS_SQL.parse (ln_cursor, vara ||chr(10)|| varb, DBMS_SQL.native); |
|
|
|
| Re: DBMS_SQL.PARSE [message #687993 is a reply to message #687989] |
Mon, 07 August 2023 08:03 |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
In 19c, dbms_sql.parse is overloaded. It will accept a clob type for the statement parameter. So, use clob.
https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_SQL.html#GUID-9723D6EA-3BD2-4B10-BE29-0E1FD64FDA2B
DBMS_SQL.PARSE (
c IN INTEGER,
statement IN CLOB,
language_flag IN INTEGER[
[,edition IN VARCHAR2 DEFAULT NULL],
apply_crossedition_trigger IN VARCHAR2 DEFAULT NULL,
fire_apply_trigger IN BOOLEAN DEFAULT TRUE]
[,schema IN VARCHAR2 DEFAULT NULL]
[,container IN VARCHAR2)];
|
|
|
|
| Re: DBMS_SQL.PARSE [message #687994 is a reply to message #687987] |
Mon, 07 August 2023 09:35 |
 |
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
chat2raj.s wrote on Mon, 07 August 2023 08:04I am using DBMS_SQL.PARSE for a dynamic SQL statement where the SQL stmt is concatenated in a Varchar2(4000) variable. But my SQL is going beyond 4000 char and so i get PL SQL numeric error. Can i use LONG instead of Varchar2 or should i use only CLOB. Any example on how to achieve this would help me.
Thank you
The error and the title are not related.
A VARCHAR2 variable can be up to 32767 bytes not 4000.
4000 bytes is the limit for a VARCHAR2 returned by a SQL statement or a string literal.
LONG, in PL/SQL, is just a synonym for VARCHAR2(32560), so you can use it.
Usual DBMS_SQL.PARSE supports a VARCHAR2(32567). Here's an example with a 10K bytes statement:
SQL> declare
2 c integer;
3 s long;
4 begin
5 s := 'select /* '||rpad(' ',10000)||'*/ count(*) from emp';
6 c := dbms_sql.open_cursor;
7 dbms_sql.parse (c, s, DBMS_SQL.NATIVE);
8 dbms_sql.close_cursor (c);
9 end;
10 /
PL/SQL procedure successfully completed.
PARSE
C NUMBER(38) (integer) IN
STATEMENT VARCHAR2 IN
LANGUAGE_FLAG NUMBER(38) (integer) IN
PARSE
otherwise you can use one of these versions:
An array of VARCHAR2
PARSE
C NUMBER(38) (integer) IN
STATEMENT DBMS_SQL.VARCHAR2A IN
PL/SQL table of VARCHAR2(32767)
LB NUMBER(38) (integer) IN
UB NUMBER(38) (integer) IN
LFFLG BOOLEAN IN
LANGUAGE_FLAG NUMBER(38) (integer) IN
PARSE
C NUMBER(38) (integer) IN
STATEMENT CLOB IN
LANGUAGE_FLAG NUMBER(38) (integer) IN
SQL> declare
2 c integer;
3 s DBMS_SQL.VARCHAR2A;
4 begin
5 s(1) := 'select /* ';
6 s(2) := rpad('2',10000,'2');
7 s(3) := rpad('3',10000,'3');
8 s(4) := rpad('4',10000,'4');
9 s(5) := '*/ count(*) from emp';
10 c := dbms_sql.open_cursor;
11 dbms_sql.parse (c, s, 1, 5, TRUE, DBMS_SQL.NATIVE);
12 dbms_sql.close_cursor (c);
13 end;
14 /
PL/SQL procedure successfully completed.[Updated on: Mon, 07 August 2023 11:24] Report message to a moderator |
|
|
|
| Re: DBMS_SQL.PARSE [message #688003 is a reply to message #687987] |
Tue, 08 August 2023 08:26 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Do you have variable number of select list expressions? Do you have variable number of bind variables? If nor for both, then there is no need to use DBMS_SQL. Plain EXECUTE IMMEDIATE or REF CURSOR is much simpler to use.
SY.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
