Solved

Need procedure assistance

Posted on 2012-04-09
2
215 Views
Last Modified: 2012-06-27
Good evening,

I have two tables one is BASE_TABLES with table_id, table_name, and a few other cols.
The other is BASE_TABLE_COLS  with table_id, col_name, col_Alias

I would like to build a query based on the requisite table columns of the base tables.

that would be something like  'select '||col_name1||' , '||col_alias1||' ,...'||col_n||' from '||base_table ;
How could I build that query string in the procedure then execute into a ref_cursor?

Any assistance would be greatly appreciated.
0
Comment
Question by:Focker513
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 16

Accepted Solution

by:
Wasim Akram Shaik earned 500 total points
ID: 37826459
check in this site, you can see how to use a refcursor

http://www.oracle-base.com/articles/misc/using-ref-cursors-to-return-recordsets.php

and for your code, you just have to use dynamic sql for using refcursor..

just posting a sample reference code. you can make the prepare the whole code basis on this approach

ie., store the sql in a variable

declare
cur_contr_details refcursor;
v_var varchar2(1000);
begin
v_var:= 'select '||col_name1||' , '||col_alias1||' ,...'||col_n||' from '||base_table ;
  OPEN cur_contr_details FOR v_var;

....
OPEN cur_contr_details FOR sql_stmt1 USING i.sncode;
0
 
LVL 2

Expert Comment

by:datakeyword
ID: 37826654
why not try esProc to solve it?
esProc is a tool for mass data computation, especially fit for the complex data computation like yours.
Check below for more details :http://www.esproc.com/library/product/bid-farewell-to-stored-procedure.html
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to recover a database from a user managed backup
Suggested Courses

626 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question