Hi there,
I have created a procedure and passing values via parameters but then the sql statement takes it as a string rather than parameters. Could you please let me know what it is that I am doing wrong.
Code is as follows:
CREATE OR REPLACE FUNCTION GetTableData(p_vInTname IN VARCHAR2
,p_dInLastPubTms IN DATE
)
RETURN CLOB
IS
vStrSqlQuery VARCHAR2(32767);
TYPE ref_cursor IS REF CURSOR;
rc_tablevalues ref_cursor;
lc_XML CLOB;
BEGIN
vStrSqlQuery:= q'[SELECT dbms_xmlgen.getxml('SELECT * FROM p_vInTname ' ||' WHERE record_update_tms >= TO_DATE(p_dInLastPubTMS,'MM/DD/YYYY HH24:MI:SS')) FROM dual]';
DBMS_OUTPUT.put_line(vStrSqlQuery);
OPEN rc_tablevalues FOR vStrSqlQuery;
FETCH rc_tablevalues INTO lc_XML;
CLOSE rc_tablevalues;
RETURN lc_XML;
printClob(lc_XML);
END GetTableData;
This compiled successfully.
Another procedure for printing
Create or replace 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;
I am trying to test it to see how it behaves, so created a test procedure which is:
CREATE OR REPLACE PROCEDURE 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;
The above one too compiles successfully. However when I set a break point and then try to pass through the function instead of seeing the parameter in the second part of the query, I am seeing it as:
SELECT dbms_xmlgen.getxml('SELECT * FROM p_vInTname ' ||' WHERE record_update_tms >= TO_DATE(p_dInLastPubTMS,'MM/DD/YYYY HH24:MI:SS')) FROM dual
What is that I am doing wrong that I am unable to see the runtime parameters being passed into the variables of the query? Can any one suggest something? I am handling dynamic queries and also a solution such as the above for the first time.
Thanks in advance.