Home » SQL & PL/SQL » SQL & PL/SQL » How to convert a table of CLOBS to VARCHAR & How to come up with a magic number for any table that returns more than 32KB & ... ? (merged 3) (O/S:Windows 7 PRO; Product: Oracle; version: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)
| How to convert a table of CLOBS to VARCHAR & How to come up with a magic number for any table that returns more than 32KB & ... ? (merged 3) [message #636783] |
Sat, 02 May 2015 17:13  |
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
|
Senior Member |
|
|
Hi all,
I have created a function in a package that takes one parameter as a NUMBER and then converts the output based on business logic to an array (table of CLOBS; associative array to be more precise). This procedure compiles successfully and runs as well. No issues.
I created a test procedure and want to view the output of the function and using DBMS_OUTPUT.put_line, I am unable to view it because this only takes characters and not anything other than that. So is there any way to convert the return values (which comes out as array to VARCHAR). My test procedure is as follows:
CREATE OR REPLACE PROCEDURE SANDEEP_TEST
IS
--TYPE tClobTyp1 IS TABLE OF CLOB INDEX BY BINARY_INTEGER;
--g_tClob1 tClobTyp1;
g_tClob1 CTN_PUB_CNTL_EXTRACT_PUBLISH.tClobTyp;
BEGIN
g_tClob1:=CTN_PUB_CNTL_EXTRACT_PUBLISH.GetCtnData(4471382);
DBMS_OUTPUT.PUT_LINE(g_tClob1); --Here I am trying to pass the value of g_tclob1 which is an array and I am unable to figure out how. If I pass the way it is, raises compilation errors.
END SANDEEP_TEST;
/
I looked up on google but could not find any proper answers and even varchar2_table on Oracle documentation is not helping me much. Please help if you can. Many thanks in advance.
|
|
|
|
|
|
|
|
| Re: How to convert a table of CLOBS to VARCHAR [message #636787 is a reply to message #636786] |
Sat, 02 May 2015 17:56  |
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
|
Senior Member |
|
|
BlackSwan wrote on Sat, 02 May 2015 17:40>This procedure compiles successfully and runs as well. No issues.
How do you know the returned value is correct?
Why are you using datatypes that you don't know how to properly use & test?
Please SHOW us the actual function so we don't have to depend upon you to you accurately report what exists or not.
a CLOB is a different datatype from an associated array so a different solution is required each datatype.
By using TO_CHAR function in DBMS_OUTPUT.PUT_LINE(TO_CHAR(g_tClob1(1))); I was able to generate the output.
The output that I was looking was to generate an XML and it did generate an XML. I did a spot checking of what the XML output was against the table with which it was querying and found it to be precise. This is the way I know it is working.
Why is that I am using datatypes that I don't know... well, a good question, the data types that I need to use are CLOB to generate XML. I have never done this so far in my life including associative arrays (quite embrassing), but considering the task assigned to me and the delivery date (by end of next week), I have no other choice but to learn it through trial and error method. Unfortunately I don't have the luxury of time at work to do it in a structured way. I will lose my job if I cannot deliver it and that's not what I wish to be in at the end of the day.
Only after compiling (in my earlier message) I did tell you that the function compiled successfully. I don't shoot things in the air.
|
|
|
|
|
|
| Re: How to convert a table of CLOBS to VARCHAR [message #636789 is a reply to message #636788] |
Sat, 02 May 2015 18:07 |
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
|
Senior Member |
|
|
and what is PLONK mister?
Now coming to this discussion, the XML that I generate is being stored in as a CLOB and CLOB has limitiation of 32KB. The minute my query output is greater than 32KB I am getting hit by a run time error. Is there any way around this?
|
|
|
|
|
|
|
|
| How to come up with a magic number for any table that returns more than 32KB? [message #636792 is a reply to message #636783] |
Sat, 02 May 2015 23:23 |
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
|
Senior Member |
|
|
I am in a unique situation where in I am trying to retrieve values from multiple tables and publish them as XML output. The problem is based on the condition a few tables could retrieve data more than 32KB and a few less than 32KB. Less than 32KB is not an issue as XML generation is smooth. The minute it reaches greater than 32KB it generates a run time error. Just wondering if there is any way to ensure that the minute the query's results is greater than 32 kb, it should break say - if the results is 35KB, then I should break that result into 32 KB and 3kb; once done then pass this data to be published as an XML output. This is again not just for one table, but all the tables that are called in the function.
Is there any way?? I am unable to get any ideas nor have I done anything so complex from production support stand point. Would appreciate if someone can guide me on this.
The way it is, is as follows:
I have a table called ctn_pub_cntl
CREATE TABLE CTNAPP.ctn_pub_cntl
(ctn_pub_cntl_id NUMBER(18)
,table_name VARCHAR2(50)
,last_pub_tms DATE
,queue_name VARCHAR2(50)
,dest_system VARCHAR2(50)
,frequency NUMBER(6)
,status VARCHAR2(8)
,record_create_tms DATE
,create_user_id VARCHAR2(8)
,record_update_tms DATE
,update_user_id VARCHAR2(8)
,CONSTRAINT ctn_pub_cntl_id_pk PRIMARY KEY(ctn_pub_cntl_id)
);
Data for this is:
INSERT INTO CTNAPP.ctn_pub_cntl
(ctn_pub_cntl_id
,table_name
,last_pub_tms
,queue_name
,dest_system
,frequency
)
VALUES
(CTNAPP_SQNC.nextval
,'TRKFCG_SBDVSN'
,TO_DATE('10/2/2004 10:17:44PM','MM/DD/YYYY HH12:MI:SSPM')
,'UT.TSD.TSZ601.UNP'
,'SAP'
,15
);
INSERT INTO CTNAPP.ctn_pub_cntl
(ctn_pub_cntl_id
,table_name
,last_pub_tms
,queue_name
,dest_system
,frequency
)
VALUES
(CTNAPP_SQNC.nextval
,'TRKFCG_TRACK_SGMNT_DN'
,TO_DATE('02/06/2015 9:50:00AM','MM/DD/YYYY HH12:MI:SSPM')
,'UT.TSD.WRKORD.UNP'
,'SAP'
,30
);
INSERT INTO CTNAPP.ctn_pub_cntl
(ctn_pub_cntl_id
,table_name
,last_pub_tms
,queue_name
,dest_system
,frequency
)
VALUES
(CTNAPP_SQNC.nextval
,'TRKFCG_FXPLA_TRACK_LCTN_DN'
,TO_DATE('10/2/2004 10:17:44PM','MM/DD/YYYY HH12:MI:SSPM')
,'UT.TSD.YRDPLN.INPUT'
,'SAP'
,30
);
INSERT INTO CTNAPP.ctn_pub_cntl
(ctn_pub_cntl_id
,table_name
,last_pub_tms
,queue_name
,dest_system
,frequency
)
VALUES
(CTNAPP_SQNC.nextval
,'TRKFCG_FXPLA_TRACK_LCTN2_DN'
,TO_DATE('02/06/2015 9:50:00AM','MM/DD/YYYY HH12:MI:SSPM')
,'UT.TSD.TSZ601.UNP'
,'SAP'
,120
);
INSERT INTO CTNAPP.ctn_pub_cntl
(ctn_pub_cntl_id
,table_name
,last_pub_tms
,queue_name
,dest_system
,frequency
)
VALUES
(CTNAPP_SQNC.nextval
,'TRKFCG_FXPLA_TRACK_LCTN2_DN'
,TO_DATE('04/23/2015 11:50:00PM','MM/DD/YYYY HH12:MI:SSPM')
,'UT.TSD.YRDPLN.INPUT'
,'SAP'
,10
);
INSERT INTO CTNAPP.ctn_pub_cntl
(ctn_pub_cntl_id
,table_name
,last_pub_tms
,queue_name
,dest_system
,frequency
)
VALUES
(CTNAPP_SQNC.nextval
,'TRKFCG_FIXED_PLANT_ASSET'
,TO_DATE('04/23/2015 11:50:00AM','MM/DD/YYYY HH12:MI:SSPM')
,'UT.TSD.WRKORD.UNP'
,'SAP'
,10
);
INSERT INTO CTNAPP.ctn_pub_cntl
(ctn_pub_cntl_id
,table_name
,last_pub_tms
,queue_name
,dest_system
,frequency
)
VALUES
(CTNAPP_SQNC.nextval
,'TRKFCG_OPRLMT'
,TO_DATE('03/26/2015 7:50:00AM','MM/DD/YYYY HH12:MI:SSPM')
,'UT.TSD.WRKORD.UNP'
,'SAP'
,30
);
INSERT INTO CTNAPP.ctn_pub_cntl
(ctn_pub_cntl_id
,table_name
,last_pub_tms
,queue_name
,dest_system
,frequency
)
VALUES
(CTNAPP_SQNC.nextval
,'TRKFCG_OPRLMT_SGMNT_DN'
,TO_DATE('03/28/2015 12:50:00AM','MM/DD/YYYY HH12:MI:SSPM')
,'UT.TSD.WRKORD.UNP'
,'SAP'
,30
);
/
COMMIT;
Once the above data is inserted and committed, then I created a function in a package:
CREATE OR REPLACE PACKAGE CTNAPP.CTN_PUB_CNTL_EXTRACT_PUBLISH
IS
TYPE tNameTyp IS TABLE OF ctn_pub_cntl.table_name%TYPE INDEX BY BINARY_INTEGER;
g_tName tNameTyp;
TYPE tClobTyp IS TABLE OF CLOB INDEX BY BINARY_INTEGER;
g_tClob tClobTyp;
FUNCTION GetCtnData(p_nInCtnPubCntlID IN CTN_PUB_CNTL.ctn_pub_cntl_id%TYPE,p_count OUT NUMBER ) RETURN tClobTyp;
END CTNAPP.CTN_PUB_CNTL_EXTRACT_PUBLISH;
--Package body
CREATE OR REPLACE PACKAGE BODY CTNAPP.CTN_PUB_CNTL_EXTRACT_PUBLISH
IS
doc xmldom.DOMDocument;
main_node xmldom.DOMNode;
root_node xmldom.DOMNode;
root_elmt xmldom.DOMElement;
child_node xmldom.DOMNode;
child_elmt xmldom.DOMElement;
leaf_node xmldom.DOMNode;
elmt_value xmldom.DOMText;
tbl_node xmldom.DOMNode;
table_data XMLDOM.DOMDOCUMENTFRAGMENT;
l_ctx DBMS_XMLGEN.CTXHANDLE;
vStrSqlQuery VARCHAR2(32767);
l_clob tClobTyp;
--
l_xmltype XMLTYPE;
--
--Local Procedure to build XML header
PROCEDURE BuildCPRHeader IS
BEGIN
child_elmt := xmldom.createElement(doc, 'PUBLISH_HEADER');
child_node := xmldom.appendChild (root_node, xmldom.makeNode (child_elmt));
child_elmt := xmldom.createElement (doc, 'SOURCE_APLCTN_ID');
elmt_value := xmldom.createTextNode (doc, 'CTN');
leaf_node := xmldom.appendChild (child_node, xmldom.makeNode (child_elmt));
leaf_node := xmldom.appendChild (leaf_node, xmldom.makeNode (elmt_value));
child_elmt := xmldom.createElement (doc, 'SOURCE_PRGRM_ID');
elmt_value := xmldom.createTextNode (doc, 'VALUE');
leaf_node := xmldom.appendChild (child_node, xmldom.makeNode (child_elmt));
leaf_node := xmldom.appendChild (leaf_node, xmldom.makeNode (elmt_value));
child_elmt := xmldom.createElement (doc, 'SOURCE_CMPNT_ID');
elmt_value := xmldom.createTextNode (doc, 'VALUE');
leaf_node := xmldom.appendChild (child_node, xmldom.makeNode (child_elmt));
leaf_node := xmldom.appendChild (leaf_node, xmldom.makeNode (elmt_value));
child_elmt := xmldom.createElement (doc, 'PUBLISH_TMS');
elmt_value := xmldom.createTextNode (doc, TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
leaf_node := xmldom.appendChild (child_node, xmldom.makeNode (child_elmt));
leaf_node := xmldom.appendChild (leaf_node, xmldom.makeNode (elmt_value));
END BuildCPRHeader;
--Get table data based on table name
FUNCTION GetCtnData(p_nInCtnPubCntlID IN CTN_PUB_CNTL.ctn_pub_cntl_id%TYPE,p_Count OUT NUMBER) RETURN tClobTyp IS
vTblName ctn_pub_cntl.table_name%TYPE;
vLastPubTms ctn_pub_cntl.last_pub_tms%TYPE;
BEGIN
g_vProcedureName:='GetCtnData';
g_vTableName:='CTN_PUB_CNTL';
SELECT table_name,last_pub_tms
INTO vTblName, vLastPubTms
FROM CTN_PUB_CNTL
WHERE ctn_pub_cntl_id=p_nInCtnPubCntlID;
-- Start the XML Message generation
doc := xmldom.newDOMDocument;
main_node := xmldom.makeNode(doc);
root_elmt := xmldom.createElement(doc, 'PUBLISH');
root_node := xmldom.appendChild(main_node, xmldom.makeNode(root_elmt));
--Append Table Data as Publish Header
BuildCPRHeader;
--Append Table Data as Publish Body
child_elmt := xmldom.createElement(doc, 'PUBLISH_BODY');
leaf_node := xmldom.appendChild (root_node, xmldom.makeNode(child_elmt));
DBMS_SESSION.SET_NLS('NLS_DATE_FORMAT','''YYYY:MM:DD HH24:MI:SS''');
vStrSqlQuery := 'SELECT * FROM ' || vTblName
|| ' WHERE record_update_tms <= TO_DATE(''' || TO_CHAR(vLastPubTms, 'MM/DD/YYYY HH24:MI:SS') || ''', ''MM/DD/YYYY HH24:MI:SS'') ' ;
-- || ' AND rownum < 16'
--;
DBMS_OUTPUT.PUT_LINE(vStrSqlQuery);
l_ctx := DBMS_XMLGEN.NEWCONTEXT(vStrSqlQuery);
DBMS_XMLGEN.SETNULLHANDLING(l_ctx, 0);
DBMS_XMLGEN.SETROWSETTAG(l_ctx, vTblName);
-- Append Table Data as XML Fragment
l_clob(1):=DBMS_XMLGEN.GETXML(l_ctx);
elmt_value := xmldom.createTextNode (doc, l_clob(1));
leaf_node := xmldom.appendChild (leaf_node, xmldom.makeNode (elmt_value));
xmldom.writeToBuffer (doc, l_clob(1));
l_clob(1):=REPLACE(l_clob(1),'<?xml version="1.0"?>', NULL);
l_clob(1):=REPLACE(l_clob(1),'<', '<');
l_clob(1):=REPLACE(l_clob(1),'>', '>');
RETURN l_clob;
DBMS_OUTPUT.put_line('Answer is' ||l_clob(1));
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.put_line('There is no data with' || SQLERRM);
g_vProcedureName:='GetCtnData';
g_vTableName:='CTN_PUB_CNTL';
g_vErrorMessage:=SQLERRM|| g_vErrorMessage;
g_nSqlCd:=SQLCODE;
ctn_log_error('ERROR',g_vErrorMessage,'SELECT',g_nSqlCd,p_nInCtnPubCntlID,g_vPackageName,g_vProcedureName,g_vTableName);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR : ' || SQLERRM);
ctn_log_error('ERROR',g_vErrorMessage,'OTHERS',g_nSqlCd,p_nInCtnPubCntlID,g_vPackageName,g_vProcedureName,g_vTableName);
END GetCtnData;
PROCEDURE printClob (result IN OUT NOCOPY CLOB) IS
xmlstr VARCHAR2 (32767);
line VARCHAR2 (2000);
BEGIN
xmlstr := DBMS_LOB.SUBSTR (result, 32767);
LOOP
EXIT WHEN xmlstr IS NULL;
line := SUBSTR (xmlstr, 1, INSTR (xmlstr, CHR (10)) - 1);
DBMS_OUTPUT.put_line (line);
xmlstr := SUBSTR (xmlstr, INSTR (xmlstr, CHR (10)) + 1);
END LOOP;
END printClob;
END CTN_PUB_CNTL_EXTRACT_PUBLISH;
-- Start the XML Message generation
doc := xmldom.newDOMDocument;
main_node := xmldom.makeNode(doc);
root_elmt := xmldom.createElement(doc, 'PUBLISH');
root_node := xmldom.appendChild(main_node, xmldom.makeNode(root_elmt));
--Append Table Data as Publish Header
BuildCPRHeader;
--Append Table Data as Publish Body
child_elmt := xmldom.createElement(doc, 'PUBLISH_BODY');
leaf_node := xmldom.appendChild (root_node, xmldom.makeNode(child_elmt));
DBMS_SESSION.SET_NLS('NLS_DATE_FORMAT','''YYYY:MM:DD HH24:MI:SS''');
vStrSqlQuery := 'SELECT * FROM ' || vTblName
|| ' WHERE record_update_tms <= TO_DATE(''' || TO_CHAR(vLastPubTms, 'MM/DD/YYYY HH24:MI:SS') || ''', ''MM/DD/YYYY HH24:MI:SS'') ' ;
-- || ' AND rownum < 16'
--;
DBMS_OUTPUT.PUT_LINE(vStrSqlQuery);
l_ctx := DBMS_XMLGEN.NEWCONTEXT(vStrSqlQuery);
DBMS_XMLGEN.SETNULLHANDLING(l_ctx, 0);
DBMS_XMLGEN.SETROWSETTAG(l_ctx, vTblName);
-- Append Table Data as XML Fragment
l_clob(1):=DBMS_XMLGEN.GETXML(l_ctx);
elmt_value := xmldom.createTextNode (doc, l_clob(1));
leaf_node := xmldom.appendChild (leaf_node, xmldom.makeNode (elmt_value));
xmldom.writeToBuffer (doc, l_clob(1));
l_clob(1):=REPLACE(l_clob(1),'<?xml version="1.0"?>', NULL);
l_clob(1):=REPLACE(l_clob(1),'<', '<');
l_clob(1):=REPLACE(l_clob(1),'>', '>');
RETURN l_clob;
DBMS_OUTPUT.put_line('Answer is' ||l_clob(1));
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.put_line('There is no data with' || SQLERRM);
g_vProcedureName:='GetCtnData';
g_vTableName:='CTN_PUB_CNTL';
g_vErrorMessage:=SQLERRM|| g_vErrorMessage;
g_nSqlCd:=SQLCODE;
ctn_log_error('ERROR',g_vErrorMessage,'SELECT',g_nSqlCd,p_nInCtnPubCntlID,g_vPackageName,g_vProcedureName,g_vTableName);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR : ' || SQLERRM);
ctn_log_error('ERROR',g_vErrorMessage,'OTHERS',g_nSqlCd,p_nInCtnPubCntlID,g_vPackageName,g_vProcedureName,g_vTableName);
END GetCtnData;
Now I create a test procedure so that I can call the above package for testing purpose and as said it works fine if there are rows less than 32kb but the minute it's greater than 32 KB it generates a run time error(If you notice I commented the runtime sql query's rownum < 16 because the minute it's less than 16 it generates and publishes XML structure as needed but the minute it's greater than 32kb it throws a runtime error.
vStrSqlQuery := 'SELECT * FROM ' || vTblName
|| ' WHERE record_update_tms <= TO_DATE(''' || TO_CHAR(vLastPubTms, 'MM/DD/YYYY HH24:MI:SS') || ''', ''MM/DD/YYYY HH24:MI:SS'') ' ;
-- || ' AND rownum < 16'
My test procedure is:
CREATE OR REPLACE PROCEDURE CTNAPP.SANDEEP_TEST_LAMXML
IS
lv_x CLOB;
BEGIN
lv_x := CTN_PUB_CNTL_EXTRACT_PUBLISH.GetTableData('TRKFCG_SBDVSN',TO_DATE('04/27/2015 19:57:10', 'MM/DD/YYYY HH24:MI:SS'));
END;
/
Anythoughts or ideas based on my requirement will be appreciated.
|
|
|
|
|
|
| Re: How to come up with a magic number for any table that returns more than 32KB? [message #636795 is a reply to message #636793] |
Sat, 02 May 2015 23:48 |
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
|
Senior Member |
|
|
BlackSwan wrote on Sat, 02 May 2015 23:31PL/SQL VARCHAR2 variables are limited to shorter than 32K
CLOB datatype can support strings longer than 32K.
I am unable to BlackSwan. If you notice my query:
vStrSqlQuery := 'SELECT * FROM ' || vTblName
|| ' WHERE record_update_tms <= TO_DATE(''' || TO_CHAR(vLastPubTms, 'MM/DD/YYYY HH24:MI:SS') || ''', ''MM/DD/YYYY HH24:MI:SS'') ' ;
|| ' AND rownum < 16'
;
The minute I comment , it throws an error because this query returns around 600 rows and all of those rows need to be published as XML and the tragedy is that there is a C program in between as well i.e. C will call my packged functions and then will do all the processing. Once this is done will pass the results back to C program. So obviously C does not recognise CLOB and somewhere in the process I have to convert the CLOB to VARCHAR or instead of CLOB I have to use VARCHAR array as a return type. This is my challenge.
|
|
|
|
|
|
| Re: How to come up with a magic number for any table that returns more than 32KB? [message #636797 is a reply to message #636796] |
Sun, 03 May 2015 00:04 |
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
|
Senior Member |
|
|
BlackSwan wrote on Sat, 02 May 2015 23:56Error gets thrown when a PL/SQL VARCHAR2 exceeds 32K.
The only recourse is to not use VARCHAR2 & use CLOB.
I really wish that I can say this and get away in the project, but I cannot. Please understand, there is a C program that acts like a "middleware" that will call my package and based on that I have to send the data back and that will not recognise my CLOB. I have been told about this by a senior developer who is developing this C interface and he has worked with Oracle Corporation, so I cannot even argue because this is for the first time that I am handling a challenge like this (besides being ignorant about XML).
|
|
|
|
|
|
|
|
| Re: How to come up with a magic number for any table that returns more than 32KB? [message #636802 is a reply to message #636799] |
Sun, 03 May 2015 07:15 |
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
|
Senior Member |
|
|
Michel Cadot wrote on Sun, 03 May 2015 00:42
C program can support CLOB; any programming language supporting Oracle API supports CLOB.
So the only alternatives you have are:
* Use VARCHAR2 and be limited to 32K
* Use CLOB and have (almost) no limit
* Use an array of VARCHAR2 (very inefficient)
* Store the result in rows in a temp table of VARCHAR2 and query the table from the C program
These are the only possibilities (I'm currently thinking about) whatever you want.
Using the last pointer "storing the result in rows in a temp table of VARCHAR2,how can one find the magic number? Is there a way to know the cut off limit for 32KB? i.e. the minute the query returns 32KB or more than that, what is the mechanism to break it into such a way that there is no runtime error and we are able to still get the results in 2-3 chunks (or as many chunks as required) and pass the results outside. Using CLOB (if you see my code) I am still unable to as it throws a runtime error.
It's failing at PL/SQL for now the minute the number of rows retrieved from the query is greater than 32KB. So far C is out of picture but then it will come soon and I was trying to give/represent a larger picture.
[Updated on: Sun, 03 May 2015 08:39] Report message to a moderator |
|
|
|
| DBMS_SQL.varchar2_table [message #636807 is a reply to message #636783] |
Sun, 03 May 2015 12:48 |
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
|
Senior Member |
|
|
I am redoing everything from scratch and trying to create a package specs in the following way:
CREATE OR REPLACE PACKAGE CTNAPP.CTN_PUB_CNTL_EXTRACT_PUBLISH
IS
g_tVarCharArray dbms_sql.varchar2_table;
PROCEDURE GetCtnData(p_nInCtnPubCntlID IN NUMBER, p_tVarCharArray OUT g_tVarCharArray, pCount OUT NUMBER);
END CTN_PUB_CNTL_EXTRACT_PUBLISH;
What is wrong with the above style of coding? when I compile I get an error message that says g_tVarCharArray should be declared. Where am I going wrong?
|
|
|
|
| Re: DBMS_SQL.varchar2_table [message #636808 is a reply to message #636807] |
Sun, 03 May 2015 13:02 |
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
|
Senior Member |
|
|
ok.. by doing this I was able to overcome the issue
PROCEDURE GetCtnData(p_nInCtnPubCntlID IN NUMBER, p_tOutVarCharArray OUT dbms_sql.varchar2_table, pCount OUT NUMBER);
As a senior member said "problem exists between chair and keyboard"  Very rightly said.
Anyways since the data returned in the array will be greater than 32K dbms_sql.varchar2_table will not work for me as it's limited to 4K. I would be switching to table of varchar to mitigate this.
[Updated on: Sun, 03 May 2015 13:35] Report message to a moderator |
|
|
|
|
|
Goto Forum:
Current Time: Fri Apr 19 10:05:42 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
