CODEWITHSUNDEEP
plsql
Ruchi Gupta
Ruchi Gupta

Reputation: 89

When using CAST in plsql case, showing error

I want to put single clob data column value in 2 varchar2 columns by checking the length of CLOB column, but i am getting error in case statement, line is marked in * *, it says syntex error , what am i doing wrong

DECLARE
    v_tot_rows   NUMBER (3);
    rqst_xml_1   ISG.CERT_TEST_CASE_GTWY_TXN.RQST_XML_1_TX%TYPE;
    rqst_xml_2   ISG.CERT_TEST_CASE_GTWY_TXN.RQST_XML_2_TX%TYPE;

    CURSOR req_res_populate_cur
    IS
        SELECT scptc.SWR_CERT_PRJCT_TEST_CASE_ID,
               orb_txn.MIME_HEAD_TX,
               orb_txn.RSPNS_XML_TX,
               orb_msg.RQST_GNRL_VLD_JSON_TX,
               orb_msg.RQST_TEST_CASE_VLD_JSON_TX,
               orb_msg.MRCH_ID
               (
                   CASE
                        WHEN DBMS_LOB.GETLENGTH (orb_txn.RQST_XML_TX) <= 4000 THEN 
                            rqst_xml_1 := CAST ( orb_txn . RQST_XML_TX AS VARCHAR2 ( 4000 ) ) * ,
                            rqst_xml_2 := ''
                        WHEN DBMS_LOB.GETLENGTH(orb_txn.RQST_XML_TX)>4000 THEN
                            rqst_xml_1:=CAST(substr(orb_txn.RQST_XML_TX,1,4000) AS VARCHAR2(4000)),
                            rqst_xml_2:=CAST(substr(orb_txn.RQST_XML_TX,4001)
                  END
                )
          FROM ISG.online_messages     msg
               JOIN ISG.SWR_CERT_PRJCT_TEST_CASE scptc
                   ON msg.online_message_id = scptc.TXN_ID,
               ISG.GTWY_PLTFM_TXN_MSG  orb_msg
               JOIN ISG.GTWY_PLTFM_TXN orb_txn
                   ON orb_msg.GTWY_PLTFM_TXN_ID = orb_txn.GTWY_PLTFM_TXN_ID
         WHERE msg.SPEC_ID = 60;;
BEGIN
    FOR req_res IN req_res_populate_cur
    LOOP
        DBMS_OUTPUT.PUT_LINE (req_res.SWR_CERT_PRJCT_TEST_CASE_ID,
                              req_res.MIME_HEAD_TX,
                              req_res.rqst_xml_1,
                              req_res.rqst_xml_2,
                              req_res.RSPNS_XML_TX,
                              req_res.RQST_GNRL_VLD_JSON_TX,
                              req_res.RQST_TEST_CASE_VLD_JSON_TX,
                              req_res.MRCH_ID);
    END LOOP;
END;

Upvotes: 0

Views: 73

Answers (1)

kara
kara

Reputation: 3455

Your problem is your invalid SELECT-statement. You're trying to set variables (of your plsql-block) within a query. That's not intended or allowed.

You need to select the values into columns. Here i added two columns. One for each xml-value.

SELECT scptc.SWR_CERT_PRJCT_TEST_CASE_ID,
       orb_txn.MIME_HEAD_TX,
       orb_txn.RSPNS_XML_TX,
       orb_msg.RQST_GNRL_VLD_JSON_TX,
       orb_msg.RQST_TEST_CASE_VLD_JSON_TX,
       orb_msg.MRCH_ID,
       CASE                                                        --Column-Start
           WHEN DBMS_LOB.GETLENGTH (orb_txn.RQST_XML_TX) <= 4000
           THEN
               CAST (orb_txn.RQST_XML_TX AS VARCHAR2 (4000))
           WHEN DBMS_LOB.GETLENGTH (orb_txn.RQST_XML_TX) > 4000
           THEN
               CAST (
                   SUBSTR (orb_txn.RQST_XML_TX, 1, 4000) AS VARCHAR2 (4000))
       END
           AS my_rqst_xml_1,                                       -- Column-End. In this column you'll have the value for xml_1 
       CASE                                                        --Column-Start
           WHEN DBMS_LOB.GETLENGTH (orb_txn.RQST_XML_TX) <= 4000
           THEN
               ''
           WHEN DBMS_LOB.GETLENGTH (orb_txn.RQST_XML_TX) > 4000
           THEN
               CAST (SUBSTR (orb_txn.RQST_XML_TX, 4001) AS VARCHAR2 (4000))
       END
           AS my_rqst_xml_2                                       -- Column-End. In this column you'll have the value for xml_12
  FROM ISG.online_messages     msg
       JOIN ISG.SWR_CERT_PRJCT_TEST_CASE scptc
           ON msg.online_message_id = scptc.TXN_ID,
       ISG.GTWY_PLTFM_TXN_MSG  orb_msg
       JOIN ISG.GTWY_PLTFM_TXN orb_txn
           ON orb_msg.GTWY_PLTFM_TXN_ID = orb_txn.GTWY_PLTFM_TXN_ID
 WHERE msg.SPEC_ID = 60

Afterwards you can work with the result and get the values from it.

BEGIN
    FOR req_res IN req_res_populate_cur
    LOOP
        DBMS_OUTPUT.PUT_LINE (req_res.SWR_CERT_PRJCT_TEST_CASE_ID,
                              req_res.MIME_HEAD_TX,
                              req_res.my_rqst_xml_1, -- here we can see the values
                              req_res.my_rqst_xml_2, -- here too
                              req_res.RSPNS_XML_TX,
                              req_res.RQST_GNRL_VLD_JSON_TX,
                              req_res.RQST_TEST_CASE_VLD_JSON_TX,
                              req_res.MRCH_ID);

        -- And here we could store the values into variables or call some procedures etc.
        rqst_xml_1 := req_res.my_rqst_xml_1;
        rqst_xml_2 := req_res.my_rqst_xml_2;
    END LOOP;
END;

I've to guess, but it seems you didn't want to declare the variables:

rqst_xml_1  ISG.CERT_TEST_CASE_GTWY_TXN.RQST_XML_1_TX%TYPE;
rqst_xml_2  ISG.CERT_TEST_CASE_GTWY_TXN.RQST_XML_2_TX%TYPE;

This would be only needed if you want to work with the values.

Upvotes: 1

Related Questions