need to create a proc or anonymous block

Question

hi,

we have one table as following

Table name: metadata_table

strucutre is as follows

S.No    Tablename             Column

 1     Fct_sales_summary      wk_units
 2     Fct_exp_summary       mn_units

wk_units as in the table Fct_sales_summary are columns and are in range wk_units1 to wk_units105 and similarly in Fct_exp_summary

we need to create proc or anonymous block which will pick the columns name from tables and give us the result as that of select query.

for eg..

the proc should give us a result of following query

select wk_units1,wk_units2....wk_units105 from cmn_sls_dm.Fct_sales_summary;

after that similarly for

select mn_units1,mn_units2....mn_units105 from cmn_sls_dm.Fct_exp_summary;

Answer 1

Hi Irveen,

wk_units as in the table Fct_sales_summary are columns and are in range wk_units1 to wk_units105 and similarly in Fct_exp_summary<

No idea what you're talking about here.

we need to create proc or anonymous block which will pick the columns name from tables and give us the result as that of select query.<

You can query user or system tables/ views to obtain this information. For example, all_tab_columns will give you all the columns in an individual table:

SELECT column_name FROM all_tab_columns WHERE table_name = 'FCT_SALES_SUMMARY';

You could then iterate through these, and built a dynamic SQL query in your procedure, for example:

FOR x IN (/* INSERT QUERY ABOVE HERE. */) LOOP l_sql_statement := l_sql_statement || ' ' || x.column_name;
END LOOP;

l-sql-statement would be defined in your definition block and begin with 'SELECT'. You would then append your WHERE clause, grouping, etc. to the statement. This is a very messy way to do things, and you should avoid it.

for eg.. the proc should give us a result of following query select wk_units1,wk_units2....wk_units105 from cmn_sls_dm.Fct_sales_summary; after that similarly for select mn_units1,mn_units2....mn_units105 from cmn_sls_dm.Fct_exp_summary; <

Try using a reference cursor to return the results from the stored procedures. If you are returning the results to an application, almost every Oracle library and extension offers support for reference cursors.

Having said all this, these look like they would be perfect candidates for standard views. You don't give any indication as to why they should be stored within a package.