Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL: numeric or value error: character string buffer too small (11.2.0.4)
PL/SQL: numeric or value error: character string buffer too small [message #656933] |
Mon, 24 October 2016 06:08 |
grpatwari
Messages: 288 Registered: June 2008 Location: Hyderabad
|
Senior Member |
|
|
Hi,
I am running below code and getting below error. Please suggest me how to solve this.
create or replace FUNCTION test_f_direct (i_table_name IN VARCHAR2)
RETURN SYS_REFCURSOR
AS
v_ref SYS_REFCURSOR;
v_length number;
v_sql dbms_sql.varchar2a;
--v_createsql dbms_sql.varchar2a;
v_propid NUMBER;
v_intCur pls_integer;
v_intIdx pls_integer;
v_intNumRows pls_integer;
BEGIN
EXECUTE IMMEDIATE
'SELECT MAX (NVL(LENGTH (text_value), 4000)) FROM ' || i_table_name
INTO v_length;
v_intIdx := 1;
v_sql(v_intIdx) := 'SELECT project_id,BUSINESSOBJECT_ID, INSTANCE_NAME';
OPEN v_ref FOR 'SELECT distinct ATTRIBUTE_ID FROM ' || i_table_name;
LOOP
v_intIdx := v_intIdx +1;
FETCH v_ref INTO v_propid;
EXIT WHEN v_ref%NOTFOUND;
v_sql(v_intIdx) := v_sql(v_intIdx-1) || ',CAST(MAX(DECODE(ATTRIBUTE_ID,' || v_propid
|| ',DECODE(ATTRIBUTE_TYPE_ID,1,TEXT_VALUE,2,NUMBER_VALUE, 3, DATE_VALUE,
4, NOTE_VALUE, 5, DROPDOWN_VALUE, 6, BOOLEAN_VALUE,
7, REFERENCE_VALUE, 9, LINK_VALUE, 11, DATETIME_VALUE ,13,PARENT_INSTANCE,14,CHILD_INSTANCE ))) AS VARCHAR2('
|| v_length || '))"' || v_propid || '"';
END LOOP;
v_intIdx := v_intIdx +1;
v_sql(v_intIdx) := v_sql(v_intIdx-1) || ' FROM ' || i_table_name ||
' GROUP BY project_id,BUSINESSOBJECT_ID, INSTANCE_NAME';
v_intCur := dbms_sql.open_cursor;
dbms_sql.parse( c => v_intCur, statement => v_sql, lb => 1, ub => v_intIdx, lfflg => true, language_flag => dbms_sql.native);
v_intNumRows := dbms_sql.execute(v_intCur);
RETURN v_ref;
END test_f_direct;
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SHARCHTEST2.TEST_F_DIRECT", line 25
ORA-06512: at line 7
|
|
|
|
Re: PL/SQL: numeric or value error: character string buffer too small [message #656942 is a reply to message #656934] |
Mon, 24 October 2016 08:36 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
I'm pretty sure it's not getting as far as dbms_sql.parse.
line 25 appears to be:
v_sql(v_intIdx) := v_sql(v_intIdx-1) || ',CAST(MAX(DECODE(ATTRIBUTE_ID,' || v_propid
v_sql is an array of varchar2(32767).
Looks like OP is building up a string of decodes and appending each one till it blows out the size limit. The length of the string is approx 300 char (it varies based on the length of the value of v_propid).
What does this give:
SELECT count(distinct ATTRIBUTE_ID) FROM <whatever table you're actually using>
|
|
|
Re: PL/SQL: numeric or value error: character string buffer too small [message #656945 is a reply to message #656933] |
Mon, 24 October 2016 09:18 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
You completely misunderstand dbms_sql.varchar2a. It is used when SQL text is long and allows splitting such SQL in chunks. Each element of dbms_sql.varchar2a is a chunk of SQL statement while you are concatenating previous chunks. Change:
v_sql(v_intIdx) := v_sql(v_intIdx-1) || ',CAST(MAX(DECODE(ATTRIBUTE_ID,' || v_propid
|| ',DECODE(ATTRIBUTE_TYPE_ID,1,TEXT_VALUE,2,NUMBER_VALUE, 3, DATE_VALUE,
4, NOTE_VALUE, 5, DROPDOWN_VALUE, 6, BOOLEAN_VALUE,
7, REFERENCE_VALUE, 9, LINK_VALUE, 11, DATETIME_VALUE ,13,PARENT_INSTANCE,14,CHILD_INSTANCE ))) AS VARCHAR2('
|| v_length || '))"' || v_propid || '"';
To:
v_sql(v_intIdx) := ',CAST(MAX(DECODE(ATTRIBUTE_ID,' || v_propid
|| ',DECODE(ATTRIBUTE_TYPE_ID,1,TEXT_VALUE,2,NUMBER_VALUE, 3, DATE_VALUE,
4, NOTE_VALUE, 5, DROPDOWN_VALUE, 6, BOOLEAN_VALUE,
7, REFERENCE_VALUE, 9, LINK_VALUE, 11, DATETIME_VALUE ,13,PARENT_INSTANCE,14,CHILD_INSTANCE ))) AS VARCHAR2('
|| v_length || '))"' || v_propid || '"';
SY.
|
|
|
|
Re: PL/SQL: numeric or value error: character string buffer too small [message #656976 is a reply to message #656946] |
Tue, 25 October 2016 00:02 |
grpatwari
Messages: 288 Registered: June 2008 Location: Hyderabad
|
Senior Member |
|
|
I tried without using array but same error I am getting even I change to varchar2. If the sql is less than 32767 then I am not getting the below error. Please find the exact error as below.
create or replace FUNCTION test_f_direct (i_table_name IN VARCHAR2)
RETURN SYS_REFCURSOR
AS
v_ref SYS_REFCURSOR;
v_length number;
v_sql VARCHAR2(32767);
--v_sql dbms_sql.varchar2a;
v_createsql dbms_sql.varchar2a;
v_propid NUMBER;
v_intCur pls_integer;
v_intIdx pls_integer;
v_intNumRows pls_integer;
BEGIN
EXECUTE IMMEDIATE
'SELECT MAX (NVL(LENGTH (text_value), 4000)) FROM ' || i_table_name
INTO v_length;
--v_intIdx := 1;
v_sql := 'SELECT project_id,BUSINESSOBJECT_ID, INSTANCE_NAME';
OPEN v_ref FOR 'SELECT distinct ATTRIBUTE_ID FROM ' || i_table_name;
LOOP
--v_intIdx := v_intIdx +1;
FETCH v_ref INTO v_propid;
EXIT WHEN v_ref%NOTFOUND;
v_sql := v_sql || ',CAST(MAX(DECODE(ATTRIBUTE_ID,' || v_propid --line 25
|| ',DECODE(ATTRIBUTE_TYPE_ID,1,TEXT_VALUE,2,NUMBER_VALUE, 3, DATE_VALUE,
4, NOTE_VALUE, 5, DROPDOWN_VALUE, 6, BOOLEAN_VALUE,
7, REFERENCE_VALUE, 9, LINK_VALUE, 11, DATETIME_VALUE ,13,PARENT_INSTANCE,14,CHILD_INSTANCE ))) AS VARCHAR2('
|| v_length || '))"' || v_propid || '"';
END LOOP;
--v_intIdx := v_intIdx +1;
CLOSE v_ref;
v_sql := v_sql || ' FROM ' || i_table_name ||
' GROUP BY project_id,BUSINESSOBJECT_ID, INSTANCE_NAME';
OPEN v_ref FOR v_sql;
/*
log_de_bug(11,'sql:'||v_sql);
v_intIdx := 1;
v_createsql(v_intIdx) := chr(9) || v_sql;
--dbms_output.put_line('createSQL1:'||v_createsql(v_intIdx));
log_de_bug (8, 'createSQL:'||v_createsql(v_intIdx));
v_intCur := dbms_sql.open_cursor;
dbms_sql.parse( c => v_intCur, statement => v_createsql, lb => 1, ub => v_intIdx, lfflg => true, language_flag => dbms_sql.native);
v_intNumRows := dbms_sql.execute(v_intCur); */
RETURN v_ref;
END test_f_direct;
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SHARCHTEST2.TEST_F_DIRECT", line 26
ORA-06512: at line 7
|
|
|
|
|
Re: PL/SQL: numeric or value error: character string buffer too small [message #657198 is a reply to message #656988] |
Tue, 01 November 2016 01:59 |
grpatwari
Messages: 288 Registered: June 2008 Location: Hyderabad
|
Senior Member |
|
|
Followed Solomon suggestion. Not at all executing but I am getting the error. Please find the details as below.
The line number 32 is:
v_sql(v_intIdx) := v_sql(v_intIdx-1) || ' FROM ' || i_table_name ||
' GROUP BY project_id,BUSINESSOBJECT_ID, INSTANCE_NAME';
ORA-01403: no data found
ORA-06512: at "SHARCHTEST2.TEST_F_DIRECT", line 32
ORA-06512: at line 7
Process exited.
|
|
|
Re: PL/SQL: numeric or value error: character string buffer too small [message #657203 is a reply to message #657198] |
Tue, 01 November 2016 05:06 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
You have same issue and also math problem. Look at your loop. You execute:
before
EXIT WHEN v_ref%NOTFOUND;
Therefore v_intIdx = v_sql.count + 1 when you exit loop. And then, for some reason you do another
so now v_intIdx = v_sql.count + 2
And
v_sql(v_intIdx) := v_sql(v_intIdx-1) || ' FROM ' || i_table_name ||
' GROUP BY project_id,BUSINESSOBJECT_ID, INSTANCE_NAME';
obviously fails with no data found since v_sql(v_intIdx-1) doesn't exist. Also, you are again concatenating with previous line while, as I already explained v_sql is an array where each element is consecutive chunk of SQL statement. So change:
v_intIdx := v_intIdx +1;
v_sql(v_intIdx) := v_sql(v_intIdx-1) || ' FROM ' || i_table_name ||
' GROUP BY project_id,BUSINESSOBJECT_ID, INSTANCE_NAME';
to
v_sql(v_intIdx) := ' FROM ' || i_table_name ||
' GROUP BY project_id,BUSINESSOBJECT_ID, INSTANCE_NAME';
SY.
|
|
|
Re: PL/SQL: numeric or value error: character string buffer too small [message #658793 is a reply to message #657203] |
Tue, 27 December 2016 06:16 |
grpatwari
Messages: 288 Registered: June 2008 Location: Hyderabad
|
Senior Member |
|
|
Sorry Solomon..
After long time I am working on the same requirement. I have changed the function but still I am getting the below error
"ORA-01002: fetch out of sequence". Please advice on this.
create or replace FUNCTION test_f_with_main_direct (i_table_name IN VARCHAR2)
RETURN SYS_REFCURSOR
AS
v_ref SYS_REFCURSOR;
v_length number;
v_sql dbms_sql.varchar2a;
--v_createsql dbms_sql.varchar2a;
v_propid NUMBER;
v_intCur pls_integer;
v_intIdx pls_integer;
v_intNumRows pls_integer;
BEGIN
EXECUTE IMMEDIATE
'SELECT MAX (NVL(LENGTH (text_value), 4000)) FROM ' || i_table_name
INTO v_length;
v_intIdx := 1;
v_sql(v_intIdx) := 'SELECT project_id,BUSINESSOBJECT_ID, INSTANCE_NAME, PARENT_INSTANCE_NAME';
OPEN v_ref FOR 'SELECT distinct ATTRIBUTE_ID FROM ' || i_table_name;
LOOP
FETCH v_ref INTO v_propid;
EXIT WHEN v_ref%NOTFOUND;
v_intIdx := v_intIdx +1;
v_sql(v_intIdx) := ',CAST(MAX(DECODE(ATTRIBUTE_ID,' || v_propid
|| ',DECODE(ATTRIBUTE_TYPE_ID,1,TEXT_VALUE,2,NUMBER_VALUE, 3, DATE_VALUE,
4, NOTE_VALUE, 5, DROPDOWN_VALUE, 6, BOOLEAN_VALUE,
7, REFERENCE_VALUE, 9, LINK_VALUE, 11, DATETIME_VALUE ))) AS VARCHAR2('
|| v_length || '))"' || v_propid || '"';
END LOOP;
v_intIdx := v_intIdx +1;
v_sql(v_intIdx) := ' FROM ' || i_table_name ||
' GROUP BY project_id,BUSINESSOBJECT_ID, INSTANCE_NAME, PARENT_INSTANCE_NAME';
v_intCur := dbms_sql.open_cursor;
dbms_sql.parse( c => v_intCur, statement => v_sql, lb => 1, ub => v_intIdx, lfflg => true, language_flag => dbms_sql.native);
v_intNumRows := dbms_sql.execute(v_intCur);
RETURN v_ref;
END test_f_with_main_direct;
|
|
|
Re: PL/SQL: numeric or value error: character string buffer too small [message #658818 is a reply to message #658793] |
Tue, 27 December 2016 20:45 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
The following is my best guess at what you are trying to do, with a table and some data added for testing. This seems like a very roundabout way of doing things, but I suppose it may be a simplification of something more complex. It might help if you provided a sample table, data, desired results, and explanation of why you have chosen this method rather than just a simple select statement.
-- test table and test data:
SCOTT@orcl_12.1.0.2.0> CREATE TABLE test_tab
2 (text_value VARCHAR2(15),
3 project_id NUMBER,
4 businessobject_id NUMBER,
5 attribute_id NUMBER,
6 instance_name VARCHAR2(15),
7 attribute_type_id NUMBER,
8 number_value NUMBER,
9 date_value DATE,
10 note_value VARCHAR2(15),
11 dropdown_value VARCHAR2(15),
12 boolean_value NUMBER,
13 reference_value NUMBER,
14 link_value VARCHAR2(15),
15 datetime_value DATE,
16 parent_instance NUMBER,
17 child_instance NUMBER)
18 /
Table created.
SCOTT@orcl_12.1.0.2.0> INSERT INTO test_tab VALUES
2 ('textval1', 1, 1, 1, 'instname1', 1, 1, SYSDATE, 'noteval1', 'dropdown1', 1, 1, 'linkval1', SYSDATE, 1, 1)
3 /
1 row created.
SCOTT@orcl_12.1.0.2.0> INSERT INTO test_tab VALUES
2 ('textval2', 2, 2, 2, 'instname2', 2, 2, SYSDATE, 'noteval2', 'dropdown2', 2, 2, 'linkval2', SYSDATE, 2, 2)
3 /
1 row created.
SCOTT@orcl_12.1.0.2.0> INSERT INTO test_tab VALUES
2 ('textval3', 3, 3, 3, 'instname3', 3, 3, SYSDATE, 'noteval3', 'dropdown3', 3, 3, 'linkval3', SYSDATE, 3, 3)
3 /
1 row created.
SCOTT@orcl_12.1.0.2.0> INSERT INTO test_tab VALUES
2 ('textval1', 4, 4, 4, 'instname4', 4, 4, SYSDATE, 'noteval4', 'dropdown4', 4, 4, 'linkval4', SYSDATE, 4, 4)
3 /
1 row created.
-- function:
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE FUNCTION test_f_direct
2 (i_table_name IN VARCHAR2)
3 RETURN SYS_REFCURSOR
4 AS
5 v_length NUMBER;
6 v_intIdx PLS_INTEGER;
7 v_sql DBMS_SQL.VARCHAR2A;
8 v_ref SYS_REFCURSOR;
9 v_propid NUMBER;
10 v_intCur PLS_INTEGER;
11 v_intNumRows PLS_INTEGER;
12 BEGIN
13 EXECUTE IMMEDIATE
14 'SELECT MAX (NVL (LENGTH (text_value), 4000)) FROM ' || i_table_name INTO v_length;
15 v_intIdx := 1;
16 v_sql (v_intIdx) := 'SELECT project_id,businessobject_id,instance_name';
17 OPEN v_ref FOR 'SELECT DISTINCT attribute_id FROM ' || i_table_name || ' ORDER BY attribute_id';
18 LOOP
19 FETCH v_ref INTO v_propid;
20 EXIT WHEN v_ref%NOTFOUND;
21 v_intIdx := v_intIdx +1;
22 v_sql (v_intIdx) := ',CAST(MAX(DECODE(ATTRIBUTE_ID,' || v_propid
23 || ',DECODE(ATTRIBUTE_TYPE_ID,1,TEXT_VALUE,2,NUMBER_VALUE, 3, DATE_VALUE,
24 4, NOTE_VALUE, 5, DROPDOWN_VALUE, 6, BOOLEAN_VALUE,
25 7, REFERENCE_VALUE, 9, LINK_VALUE, 11, DATETIME_VALUE ,13,PARENT_INSTANCE,14,CHILD_INSTANCE ))) AS VARCHAR2('
26 || v_length || '))"' || v_propid || '"';
27 END LOOP;
28 CLOSE v_ref;
29 v_intIdx := v_intIdx +1;
30 v_sql (v_intIdx) := ' FROM ' || i_table_name || ' GROUP BY project_id,businessobject_id,instance_name'
31 || ' ORDER BY project_id,businessobject_id,instance_name';
32 v_intCur := DBMS_SQL.OPEN_CURSOR;
33 DBMS_SQL.PARSE
34 (c => v_intCur,
35 statement => v_sql,
36 lb => 1,
37 ub => v_intIdx,
38 lfflg => TRUE,
39 language_flag => DBMS_SQL.NATIVE);
40 v_intNumRows := DBMS_SQL.EXECUTE (v_intCur);
41 v_ref := DBMS_SQL.TO_REFCURSOR (v_intcur);
42 RETURN v_ref;
43 END test_f_direct;
44 /
Function created.
SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
-- usage of function:
SCOTT@orcl_12.1.0.2.0> SELECT test_f_direct ('TEST_TAB') FROM DUAL
2 /
TEST_F_DIRECT('TEST_
--------------------
CURSOR STATEMENT : 1
CURSOR STATEMENT : 1
PROJECT_ID BUSINESSOBJECT_ID INSTANCE_NAME 1 2 3 4
---------- ----------------- --------------- -------- -------- -------- --------
1 1 instname1 textval1
2 2 instname2 2
3 3 instname3 Tue 27-D
4 4 instname4 noteval4
4 rows selected.
1 row selected.
SCOTT@orcl_12.1.0.2.0>
|
|
|
Re: PL/SQL: numeric or value error: character string buffer too small [message #658819 is a reply to message #658793] |
Tue, 27 December 2016 21:18 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Barbara already gave you the answer. Your code is missing DBMS_SQL cursor to REFCURSOR conversion: v_ref := DBMS_SQL.TO_REFCURSOR (v_intcur); As a results V_REF still points to 'SELECT DISTINCT attribute_id FROM ' || i_table_name || ' ORDER BY attribute_id'. And function already fetched all rows from it, so when V_REF is returned back to caller and caller issues another fetch from V_REF "fetch out of sequence" is raised:
SQL> create or replace
2 function f1
3 return sys_refcursor
4 is
5 v_cur sys_refcursor;
6 v_dummy varchar2(1);
7 begin
8 open v_cur for 'select * from dual';
9 loop
10 fetch v_cur into v_dummy;
11 exit when v_cur%notfound;
12 end loop;
13 return v_cur;
14 end;
15 /
Function created.
SQL> variable v_cur refcursor
SQL> exec :v_cur := f1;
PL/SQL procedure successfully completed.
SQL> print v_cur
ERROR:
ORA-01002: fetch out of sequence
no rows selected
SQL>
SY.
|
|
|
|
Re: PL/SQL: numeric or value error: character string buffer too small [message #660147 is a reply to message #658951] |
Thu, 09 February 2017 23:14 |
grpatwari
Messages: 288 Registered: June 2008 Location: Hyderabad
|
Senior Member |
|
|
Hi,
I am using the below function and would like to create the table with the below table structure and data before returning into the function. I am stuck up in the middle.
I am trying with open v_ref for.... but not able to continue. Please suggest me.
CREATE OR REPLACE FUNCTION test_f_direct
(i_table_name IN VARCHAR2)
RETURN SYS_REFCURSOR
AS
v_length NUMBER;
v_intIdx PLS_INTEGER;
v_sql DBMS_SQL.VARCHAR2A;
v_ref SYS_REFCURSOR;
v_propid NUMBER;
v_intCur PLS_INTEGER;
v_intNumRows PLS_INTEGER;
BEGIN
EXECUTE IMMEDIATE
'SELECT MAX (NVL (LENGTH (text_value), 4000)) FROM ' || i_table_name INTO v_length;
v_intIdx := 1;
v_sql (v_intIdx) := 'SELECT project_id,businessobject_id,instance_name';
OPEN v_ref FOR 'SELECT DISTINCT attribute_id FROM ' || i_table_name || ' ORDER BY attribute_id';
LOOP
FETCH v_ref INTO v_propid;
EXIT WHEN v_ref%NOTFOUND;
v_intIdx := v_intIdx +1;
v_sql (v_intIdx) := ',CAST(MAX(DECODE(ATTRIBUTE_ID,' || v_propid
|| ',DECODE(ATTRIBUTE_TYPE_ID,1,TEXT_VALUE,2,NUMBER_VALUE, 3, DATE_VALUE,
4, NOTE_VALUE, 5, DROPDOWN_VALUE, 6, BOOLEAN_VALUE,
7, REFERENCE_VALUE, 9, LINK_VALUE, 11, DATETIME_VALUE ,13,PARENT_INSTANCE,14,CHILD_INSTANCE ))) AS VARCHAR2('
|| v_length || '))"' || v_propid || '"';
END LOOP;
CLOSE v_ref;
v_intIdx := v_intIdx +1;
v_sql (v_intIdx) := ' FROM ' || i_table_name || ' GROUP BY project_id,businessobject_id,instance_name'
|| ' ORDER BY project_id,businessobject_id,instance_name';
v_intCur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE
(c => v_intCur,
statement => v_sql,
lb => 1,
ub => v_intIdx,
lfflg => TRUE,
language_flag => DBMS_SQL.NATIVE);
v_intNumRows := DBMS_SQL.EXECUTE (v_intCur);
v_ref := DBMS_SQL.TO_REFCURSOR (v_intcur);
RETURN v_ref;
END test_f_direct;
/
|
|
|
|
|
Re: PL/SQL: numeric or value error: character string buffer too small [message #660157 is a reply to message #656933] |
Fri, 10 February 2017 03:28 |
grpatwari
Messages: 288 Registered: June 2008 Location: Hyderabad
|
Senior Member |
|
|
I have tried with the below option but not able to do it.
CREATE OR REPLACE FUNCTION test_f_direct
(i_table_name IN VARCHAR2)
RETURN SYS_REFCURSOR
AS
v_length NUMBER;
v_intIdx PLS_INTEGER;
v_sql DBMS_SQL.VARCHAR2A;
v_ref SYS_REFCURSOR;
v_propid NUMBER;
v_format_table varchar2(100);
v_tab_count number;
v_createsql dbms_sql.varchar2a;
v_intCur pls_integer;
v_intCur1 pls_integer;
v_intIdx pls_integer;
v_intIdx1 pls_integer;
v_intNumRows pls_integer;
BEGIN
EXECUTE IMMEDIATE
'SELECT MAX (NVL (LENGTH (text_value), 4000)) FROM ' || i_table_name INTO v_length;
v_intIdx := 1;
v_sql (v_intIdx) := 'SELECT project_id,businessobject_id,instance_name';
OPEN v_ref FOR 'SELECT DISTINCT attribute_id FROM ' || i_table_name || ' ORDER BY attribute_id';
LOOP
FETCH v_ref INTO v_propid;
EXIT WHEN v_ref%NOTFOUND;
v_intIdx := v_intIdx +1;
v_sql (v_intIdx) := ',CAST(MAX(DECODE(ATTRIBUTE_ID,' || v_propid
|| ',DECODE(ATTRIBUTE_TYPE_ID,1,TEXT_VALUE,2,NUMBER_VALUE, 3, DATE_VALUE,
4, NOTE_VALUE, 5, DROPDOWN_VALUE, 6, BOOLEAN_VALUE,
7, REFERENCE_VALUE, 9, LINK_VALUE, 11, DATETIME_VALUE ,13,PARENT_INSTANCE,14,CHILD_INSTANCE ))) AS VARCHAR2('
|| v_length || '))"' || v_propid || '"';
END LOOP;
CLOSE v_ref;
v_intIdx := v_intIdx +1;
v_sql (v_intIdx) := ' FROM ' || i_table_name || ' GROUP BY project_id,businessobject_id,instance_name'
|| ' ORDER BY project_id,businessobject_id,instance_name';
v_intCur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE
(c => v_intCur,
statement => v_sql,
lb => 1,
ub => v_intIdx,
lfflg => TRUE,
language_flag => DBMS_SQL.NATIVE);
v_intNumRows := DBMS_SQL.EXECUTE (v_intCur);
v_ref := DBMS_SQL.TO_REFCURSOR (v_intcur);
---used for table creation with the above sql query structure start
v_format_table:= replace(i_table_name,'_T','_D');
select count(*) into v_tab_count
from user_tables
where table_name=v_format_table;
if v_tab_count >=1 then
execute immediate 'DROP TABLE ' ||v_format_table;
end if;
--Initialize the Temporary Clob variable to hold dynamic sql statments.
v_intIdx := 1;
v_createsql(v_intIdx) := 'CREATE TABLE '|| v_format_table;
v_intIdx := v_intIdx + 1;
v_createsql(v_intIdx) := chr(9) || ' AS '|| v_sql(v_intIdx);
v_intCur := dbms_sql.open_cursor;
dbms_sql.parse( c => v_intCur, statement => v_createsql, lb => 1, ub => v_intIdx, lfflg => true, language_flag => dbms_sql.native);
v_intNumRows := dbms_sql.execute(v_intCur);
dbms_sql.close_cursor(v_intCur);
---used for table creation with the above sql query structure end
RETURN v_ref;
END test_f_direct;
/
Getting the below Error in line 70
dbms_sql.parse( c => v_intCur1, statement => v_createsql, lb => 1, ub => v_intIdx1, lfflg => true, language_flag => dbms_sql.native);
ORA-00928: missing SELECT keyword
ORA-06512: at "SYS.DBMS_SQL", line 1321
ORA-06512: at "SHARCHTEST2.DYN_TAB_CREATE_INSERT", line 70
ORA-06512: at "SHARCHTEST2.DYN_TAB_CREATE_INSERT", line 340
ORA-06512: at line 6
|
|
|
|
|
|
|
Re: PL/SQL: numeric or value error: character string buffer too small [message #660183 is a reply to message #660157] |
Fri, 10 February 2017 15:56 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
As others have stated, I suspect that you may be doing things the hard way. However, the following contains enough minimal corrections to make it run. I have provided comments indicating new variables and section since the last demo I provided and different execution.
-- test table (test_tab) and test data (same as previous demo):
SCOTT@orcl_12.1.0.2.0> DESC test_tab
Name Null? Type
----------------------------------------- -------- ----------------------------
TEXT_VALUE VARCHAR2(15)
PROJECT_ID NUMBER
BUSINESSOBJECT_ID NUMBER
ATTRIBUTE_ID NUMBER
INSTANCE_NAME VARCHAR2(15)
ATTRIBUTE_TYPE_ID NUMBER
NUMBER_VALUE NUMBER
DATE_VALUE DATE
NOTE_VALUE VARCHAR2(15)
DROPDOWN_VALUE VARCHAR2(15)
BOOLEAN_VALUE NUMBER
REFERENCE_VALUE NUMBER
LINK_VALUE VARCHAR2(15)
DATETIME_VALUE DATE
PARENT_INSTANCE NUMBER
CHILD_INSTANCE NUMBER
SCOTT@orcl_12.1.0.2.0> SELECT * FROM test_tab
2 /
TEXT_VALUE PROJECT_ID BUSINESSOBJECT_ID ATTRIBUTE_ID INSTANCE_NAME
--------------- ---------- ----------------- ------------ ---------------
ATTRIBUTE_TYPE_ID NUMBER_VALUE DATE_VALUE NOTE_VALUE DROPDOWN_VALUE
----------------- ------------ --------------- --------------- ---------------
BOOLEAN_VALUE REFERENCE_VALUE LINK_VALUE DATETIME_VALUE PARENT_INSTANCE
------------- --------------- --------------- --------------- ---------------
CHILD_INSTANCE
--------------
textval1 1 1 1 instname1
1 1 Fri 10-Feb-2017 noteval1 dropdown1
1 1 linkval1 Fri 10-Feb-2017 1
1
textval2 2 2 2 instname2
2 2 Fri 10-Feb-2017 noteval2 dropdown2
2 2 linkval2 Fri 10-Feb-2017 2
2
textval3 3 3 3 instname3
3 3 Fri 10-Feb-2017 noteval3 dropdown3
3 3 linkval3 Fri 10-Feb-2017 3
3
textval1 4 4 4 instname4
4 4 Fri 10-Feb-2017 noteval4 dropdown4
4 4 linkval4 Fri 10-Feb-2017 4
4
4 rows selected.
-- function with minimal corrections:
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE FUNCTION test_f_direct
2 (i_table_name IN VARCHAR2)
3 RETURN SYS_REFCURSOR
4 AS
5 v_length NUMBER;
6 v_intIdx PLS_INTEGER;
7 v_sql DBMS_SQL.VARCHAR2A;
8 v_ref SYS_REFCURSOR;
9 v_propid NUMBER;
10 v_intCur PLS_INTEGER;
11 v_intNumRows PLS_INTEGER;
12 -- new variables:
13 v_format_table VARCHAR2(100);
14 v_tab_count NUMBER;
15 BEGIN
16 EXECUTE IMMEDIATE
17 'SELECT MAX (NVL (LENGTH (text_value), 4000)) FROM ' || i_table_name INTO v_length;
18 v_intIdx := 1;
19 v_sql (v_intIdx) := 'SELECT project_id,businessobject_id,instance_name';
20 OPEN v_ref FOR 'SELECT DISTINCT attribute_id FROM ' || i_table_name || ' ORDER BY attribute_id';
21 LOOP
22 FETCH v_ref INTO v_propid;
23 EXIT WHEN v_ref%NOTFOUND;
24 v_intIdx := v_intIdx +1;
25 v_sql (v_intIdx) := ',CAST(MAX(DECODE(ATTRIBUTE_ID,' || v_propid
26 || ',DECODE(ATTRIBUTE_TYPE_ID,1,TEXT_VALUE,2,NUMBER_VALUE, 3, DATE_VALUE,
27 4, NOTE_VALUE, 5, DROPDOWN_VALUE, 6, BOOLEAN_VALUE,
28 7, REFERENCE_VALUE, 9, LINK_VALUE, 11, DATETIME_VALUE ,13,PARENT_INSTANCE,14,CHILD_INSTANCE ))) AS VARCHAR2('
29 || v_length || '))"' || v_propid || '"';
30 END LOOP;
31 CLOSE v_ref;
32 v_intIdx := v_intIdx +1;
33 v_sql (v_intIdx) := ' FROM ' || i_table_name || ' GROUP BY project_id,businessobject_id,instance_name'
34 || ' ORDER BY project_id,businessobject_id,instance_name';
35 v_intCur := DBMS_SQL.OPEN_CURSOR;
36 DBMS_SQL.PARSE
37 (c => v_intCur,
38 statement => v_sql,
39 lb => 1,
40 ub => v_intIdx,
41 lfflg => TRUE,
42 language_flag => DBMS_SQL.NATIVE);
43 v_intNumRows := DBMS_SQL.EXECUTE (v_intCur);
44 v_ref := DBMS_SQL.TO_REFCURSOR (v_intcur);
45 -- new section:
46 v_format_table:= REPLACE (i_table_name, '_T', '_D');
47 SELECT COUNT (*) INTO v_tab_count FROM user_tables WHERE table_name = v_format_table;
48 IF v_tab_count >= 1 THEN
49 EXECUTE IMMEDIATE 'DROP TABLE ' || v_format_table;
50 END IF;
51 v_sql(1) := 'CREATE TABLE ' || v_format_table || ' AS ' || v_sql(1);
52 v_intCur := DBMS_SQL.OPEN_CURSOR;
53 DBMS_SQL.PARSE
54 (c => v_intCur,
55 statement => v_sql,
56 lb => 1,
57 ub => v_intIdx,
58 lfflg => TRUE,
59 language_flag => DBMS_SQL.NATIVE);
60 v_intNumRows := DBMS_SQL.EXECUTE (v_intCur);
61 DBMS_SQL.CLOSE_CURSOR (v_intCur);
62 -- end of new section
63 RETURN v_ref;
64 END test_f_direct;
65 /
Function created.
SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
-- exection (cannot execute from within SQL select statement due to DML, so use PL/SQL):
SCOTT@orcl_12.1.0.2.0> -- declare variable for results:
SCOTT@orcl_12.1.0.2.0> VARIABLE g_ref REFCURSOR
SCOTT@orcl_12.1.0.2.0> -- execute function:
SCOTT@orcl_12.1.0.2.0> BEGIN :g_ref := test_f_direct ('TEST_TAB'); END;
2 /
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> -- display ref cursor returned:
SCOTT@orcl_12.1.0.2.0> PRINT g_ref
PROJECT_ID BUSINESSOBJECT_ID INSTANCE_NAME 1 2 3 4
---------- ----------------- --------------- -------- -------- -------- --------
1 1 instname1 textval1
2 2 instname2 2
3 3 instname3 Fri 10-F
4 4 instname4 noteval4
4 rows selected.
SCOTT@orcl_12.1.0.2.0> --show new table created (test_dab) and data:
SCOTT@orcl_12.1.0.2.0> DESC test_dab
Name Null? Type
----------------------------------------- -------- ----------------------------
PROJECT_ID NUMBER
BUSINESSOBJECT_ID NUMBER
INSTANCE_NAME VARCHAR2(15)
1 VARCHAR2(8)
2 VARCHAR2(8)
3 VARCHAR2(8)
4 VARCHAR2(8)
SCOTT@orcl_12.1.0.2.0> SELECT * FROM test_dab
2 /
PROJECT_ID BUSINESSOBJECT_ID INSTANCE_NAME 1 2 3 4
---------- ----------------- --------------- -------- -------- -------- --------
1 1 instname1 textval1
2 2 instname2 2
3 3 instname3 Fri 10-F
4 4 instname4 noteval4
4 rows selected.
|
|
|
|
Goto Forum:
Current Time: Fri Jun 28 10:50:57 CDT 2024
|