I need to make my output readable. I have succeeded in doing so, for queries that I write directly.
I need to do the same for queries that involve a cursor, i.e. to be specific, I need the records returned by a cursor to be formatted as well. But I am unable to do.
I instead explicitly print the column names, and the print the records. Still, the same. Is there any way, I could retrieve the headers as well, and then based on their length, format the received records as well?
If I understand you correctly, what you need is Dynamic Parsing and lpad-rpad functions.
With DBMS_SQL package, you can parse a SQL statement dynamically. I threw the code block below at the top of my head so there might be syntax errors, but it should give you the idea.
declare
ln_cur number;
ln_colCnt number;
l_sqlDesc DBMS_SQL.DESC_TAB;
begin
ln_cur := DBMS_SQL.open_cursor;
DBMS_SQL.parse(ln_cur, 'select * from dual', DBMS_SQL.NATIVE);
DBMS_SQL.describe_columns(ln_cur, ln_colCnt, l_sqlDesc);
DBMS_OUTPUT.put_line(l_sqlDesc(1).col_name); --First Column Name.
DBMS_SQL.close_cursor(ln_cur);
end;
You also need to check the lpad-rpad functions if you want to format your output. Try this and see for yourself:
begin
DBMS_OUTPUT.put_line(lpad(20, "Ford"));
DBMS_OUTPUT.put_line(lpad(20, "Prefect"));
end;
