We help IT Professionals succeed at work.

How to print  data with FLD NM="COLUMN NAME OF TABLE" </FLD> USING PL-SQL

472 Views
Last Modified: 2013-12-07
How do i print my xml in to following way
--- AUDIO LIMIT IS COLUMN NAME OF ORACLE TABLE AND 0 IS THE VALUE. HOW DO WE CREATE A PROCEDURE TO GET VALUES FROM DATABASE.
IF ANY ONE WANT SCHEEMA I CAN SEND IT TOO.

<Items>
 < Iteminput>
    < FLD NM = "AUDIO LIMIT">0</FLD>     < FLD NM = "TPL LIMIT">2</FLD>
  .......
</iteminput>
</items>
Comment
Watch Question

Try this. Replace tbl_name occurences with ur actual table name.

DECLARE
val VARCHAR2(250);

BEGIN

FOR i IN (SELECT rowid FROM tbl_name) LOOP
   FOR j IN (SELECT column_name FROM user_tab_columns WHERE table_name = UPPER('tbl_name')) LOOP

     EXECUTE IMMEDIATE('SELECT TO_CHAR(' || j.column_name || ') FROM tbl_name WHERE rowid = ''' || i.rowid || '''') INTO val;
     DBMS_OUTPUT.PUT('<FLD NM="' || j.column_name || '">' || val || '</FLD> ');

   END LOOP;
   DBMS_OUTPUT.PUT_LINE('');
END LOOP;

END;
/

Author

Commented:
iTS NOT WORKING
tbl_name variable is not working
tbl_name is not a variable, its just a place-holder for your actual table. As i specified, u definitely need to replace tbl_name occurences with ur actual table name. E.g. if ur actual table name is sample_table, then the code will look as below. Observe how the 'tbl_name' occurences are replaced with 'sample_table' at 3 places.

DECLARE
val VARCHAR2(250);

BEGIN

FOR i IN (SELECT rowid FROM sample_table) LOOP
   FOR j IN (SELECT column_name FROM user_tab_columns WHERE table_name = UPPER('sample_table')) LOOP

     EXECUTE IMMEDIATE('SELECT TO_CHAR(' || j.column_name || ') FROM sample_table WHERE rowid = ''' || i.rowid || '''') INTO val;
     DBMS_OUTPUT.PUT('<FLD NM="' || j.column_name || '">' || val || '</FLD> ');

   END LOOP;
   DBMS_OUTPUT.PUT_LINE('');
END LOOP;

END;
/

Also, since this question is same as https://www.experts-exchange.com/Microsoft/Applications/Q_22769664.html, u may want to close 1 of them.
If u want to use a variable for the table name, observe the changes below. Running this on SQLPLUS will ask for the table name thrice (enter same table all the 3 times), but at least its reusable by means of a variable. Dont forget to SET SERVEROUTPUT ON.

DECLARE
val      VARCHAR2(250);

BEGIN

FOR i IN (SELECT rowid FROM &table_name) LOOP
   FOR j IN (SELECT column_name FROM user_tab_columns WHERE table_name = UPPER('&table_name')) LOOP

     EXECUTE IMMEDIATE('SELECT TO_CHAR(' || j.column_name || ') FROM &table_name WHERE rowid = ''' || i.rowid || '''') INTO val;
     DBMS_OUTPUT.PUT('<FLD NM="' || j.column_name || '">' || val || '</FLD> ');

   END LOOP;
   DBMS_OUTPUT.PUT_LINE('');
END LOOP;

END;
/

Author

Commented:
Jinesh - I tried your previous code.
I am geting error bellow.
Wrote file afiedt.buf

  1  declare
  2  l_output_file   UTL_FILE.FILE_TYPE;
  3  l_output_file_name  VARCHAR2(30):='test_policy';
  4  p_level  number(1);
  5  res_val     VARCHAR2(1024);
  6  XMLDIR        VARCHAR2(100):='/batch/utl_files/fisdb02';
  7  v_level number :=p_level;
  8  v_element  VARCHAR2(30):='POLICY';
  9  cnt  NUMBER(1):=0;
 10  val VARCHAR2(1024);
 11            -------------------- element poliinputs
 12    BEGIN
 13  l_output_file_name := l_output_file_name||'.xml';   ----------xML fILE
 14  l_output_file := UTL_FILE.FOPEN(XMLDIR, l_output_file_name, 'W');
 15    --v_tbl_name :='AC_UW_AUTO_RATE_POLICY_GTMP';
 16     FOR i IN (SELECT ROWID FROM AUTO_RATE_POLICY_GTMP) LOOP
 17      v_level:=-1;
 18            FOR j IN (SELECT column_name FROM user_tab_columns
 19          WHERE table_name = 'AUTO_RATE_POLICY_GTMP' ) LOOP
 20           EXECUTE IMMEDIATE('SELECT TO_CHAR(' || j.column_name ||
 21                       ') FROM AC_UW_AUTO_RATE_POLICY_GTMP WHERE ROWID = ''' || i.rowid || '''') INTO val;
 22        res_val:=trim('<FLD NM="' || j.column_name || '">' || RTRIM(val) || '</FLD> ');
 23       val:=NULL;
 24           IF res_val IS NOT NULL THEN
 25              UTL_FILE.PUT_LINE(l_output_file, res_val);
 26                 res_val:=NULL;
 27                END IF;
 28       END LOOP;
 29     cnt:=cnt+1;
 30    END LOOP;
 31* end;
SQL> /
declare
*
ERROR at line 1:
ORA-00907: missing right parenthesis
ORA-06512: at line 20

This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Can you accept an answer to this question if it is resolved? There are too many questions and im going nuts keeping track of them. Thanks :)
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.