Home » SQL & PL/SQL » SQL & PL/SQL » Creating a dynamic cursor
Creating a dynamic cursor [message #193802] Tue, 19 September 2006 08:35 Go to next message
Messages: 18
Registered: November 2005
Junior Member
I am creating a PL/SQL package to create views based on a table, schema and a (vin or op) value. What that means is every table I may be creating a view from will have either a 'vin' or an 'op' field.

In a procedure I want to create a cursor to look for every distinct vin or op in a given table, and create a view on that information.

For example, lets say I have a schema: test_sch with tables tab1 and tab2. tab1 has a field named 'vin' and tab2 has a field named 'op'.

If I pass the values test_sch and tab1 to the package, it should create a view for each 'vin' found. And if I pass the values test_sch and tab2 to the package, it should create a view for each 'op' found.

What I am trying to do is create a cursor to run the following query:

SELECT DISTINCT([vin or op]) FROM [schema].[table]

The schema and table are passed to this particular procedure, and the vin or op is returned in this procedure from another procedure. Is it possible to create a Cursor with variables as column or location identifiers? Or is there a better way of trying to do what I want to do?
Re: Creating a dynamic cursor [message #193841 is a reply to message #193802] Tue, 19 September 2006 11:42 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
I'm confused as to why you are creating these views in a procedure. Why not just create the views - referencing the vin or op column as appropriate - in a script?

[Updated on: Tue, 19 September 2006 11:42]

Report message to a moderator

Re: Creating a dynamic cursor [message #193956 is a reply to message #193841] Wed, 20 September 2006 02:21 Go to previous message
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I agree - In general, if you are executing DDL from PL/SQL for anything other than a one use script (like a release script), you're probably doing something wrong.
Previous Topic: String Formatting
Next Topic: Reindexing the tables in oracle
Goto Forum:

Current Time: Thu Jul 27 23:58:25 CDT 2017

Total time taken to generate the page: 0.19107 seconds