Dynamic cursor with insert and update

I need to create a dynamic cursor that changes the where clause based upon reusing the data for a update and a insert. I am having trouble understanding how best to handle this task any help.
earngreenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
You 'can' use a ref cursor and put the query in a string:
Declare
Mycur sys_refcursor;
Junk date;
Begin
Open mycur for ‘select sysdate from dual’;
Fetch mycur into junk;
Dbms_output.put_line(‘Got: ‘ || junk);
Close mycur;
End;
/

Open in new window


What I prefer is to do is write the SQL in such a way that you don't need dynamic SQL.

If you can provide more detail about what is dynamic, we can provide a better model.
0
slightwv (䄆 Netminder) Commented:
Here is a quick procedure that shows a cursor that will allow a null parameter or a value and still run:
Create or replace procedure myproc(v_value in char)
Is
Cursor mycur is select sysdate from dual where (v_value is null or dummy=v_value);
Junk date;
Begin
Open mycur;
Fetch mycur into junk;
Dbms_output.put_line(‘Got: ‘ || junk);
Close mycur;
End;
/

Show errors


Exec myproc(null);
Exec myproc(‘X’);

Open in new window

0
earngreenAuthor Commented:
It would be something like this

declare
SelectClause       clob; --this will save the dynamic sql statement of search
  whereClause       clob;
    begin
    SelectClause:='';
    whereClause:='';
    SelectClause:=' select * from MyTable';
    whereClause := ' where {FixedConditionsHere} '
--Generating dynamic conditions
  begin
       open result_out for   selectclause||whereclause ;--using whereClause;
       update statement from result_out
     
       end
     
       begin
       open result_out for   selectclause||whereclause ;
     
       insert from result_out
       end
       
       commit;
       end;
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

slightwv (䄆 Netminder) Commented:
>>It would be something like this

OK, and?  I don't understand what that is supposed to tell me.
0
earngreenAuthor Commented:
For the update using cursor(c1) data I do not need the where clause and the insert using the cursor(c1) data I need to concat the where clause to filter the data for insert.
0
slightwv (䄆 Netminder) Commented:
Create two cursors?

Once the cursor is closed, you should be able to reopen it with whatever string you want.
0
earngreenAuthor Commented:
I guess my question is how to do that dynamically with the select statement that defines the cursor as a string
0
earngreenAuthor Commented:
So where you say

Open mycur

Is it appropriate to say

Open mycur for sqlstr1||sqlstr2;
0
slightwv (䄆 Netminder) Commented:
>>Is it appropriate to say

It takes 10 seconds to set up a test and try it:
Declare
Mycur sys_refcursor;
Junk date;
sel_sql varchar2(50) := 'select sysdate from dual';
where_sql varchar2(50) := ' where dummy=''X''';

Begin
Open mycur for sel_sql;
Fetch mycur into junk;
Dbms_output.put_line('no where: ' || junk);
Close mycur;

Open mycur for sel_sql||where_sql;
Fetch mycur into junk;
Dbms_output.put_line('with where: ' || junk);
Close mycur;
End;
/

                                          

Open in new window

0
Mark GeerlingsDatabase AdministratorCommented:
Oracle can support dynamic SQL in PL/SQL procedures.  But that is not how Oracle works most efficiently.  This question looks like you have already decided on an approach that you want to use (whether that approach works well on Oracle or not) and now you want someone to help you write the code for that approach in Oracle.

It may be better to describe the business or data problem you are trying to solve, then have us suggest the best approach to use for that in Oracle.
0
earngreenAuthor Commented:
The best and easiest approach is to use a temporary table to do this operation but the db has some configuration issues. So taking the second best approach of using cursors. I don't want anyone to write the code just generating lots of errors when I try to generate the cursor trying to come up with ideas. I really just need to generate a insert and update using the same dataset while filtering the data for the insert hence the where clause.
0
slightwv (䄆 Netminder) Commented:
>>The best and easiest approach is to use a temporary table

In memory Pl/SQL tables (collections) or Oracle GlobalTemporary Tables? I think we covered these in your previous questions but I'm not going to go back through them all.

>>the second best approach of using cursors

Second best?  Based on what?

>>using the same dataset

A cursor really isn't the same as a dataset.  It is forward only processing and is regenerated every time it is opened.  You need to weigh that against a temp table of some type and querying/processing from that.

You mentioned INSERT and UPDATE based on certain criteria.  I also think we covered MERGE in a previous question or three.   INSET and UPDATE is what MERGE was created for.  If is Oracle's solution to UPSERT.

I think I remember some older questions where the logic you were trying appeared to be overly complex to start with.  It appears it still is a little overly complex and you haven't even posted any code this time.
0
earngreenAuthor Commented:
I agree that it is overly complex. It's one of those situations were doesn't belong in the database but it's the only thing available. I
0
earngreenAuthor Commented:
Found this on oracle site which is actually what I need but it throws errors saying that employees is not defined. Do you know how to fix??

DECLARE
  TYPE cursor_ref IS REF CURSOR;
  c1 cursor_ref;
  TYPE emp_tab IS TABLE OF employees%ROWTYPE;
  rec_tab emp_tab;
  rows_fetched NUMBER;
BEGIN
  OPEN c1 FOR 'SELECT * FROM employees';
  FETCH c1 BULK COLLECT INTO rec_tab;
  rows_fetched := c1%ROWCOUNT;
  DBMS_OUTPUT.PUT_LINE('Number of employees fetched: ' || TO_CHAR(rows_fetched));
END;
0
slightwv (䄆 Netminder) Commented:
>>Do you know how to fix??

Create an employees table.  That code is referencing an employees table.

>>which is actually what I need

That is a PL/SQL table not a CURSOR.  I mentioned those in my last post and I think we covered those in your previous questions.

You decided on using CURSORs.

So, what do you need again?
0
earngreenAuthor Commented:
It throws an error that says 0021: identifier 'employees must be declared'
0
slightwv (䄆 Netminder) Commented:
I didn't mean the error message.  I already told you what was causing that.  If you try to run code that references an employees table, you need the employees table or you get the error you are receiving.


I meant:  What do you need for this question.  This question is strictly about cursors not pl/sql collections.

You said above that you could not use them, now you are trying to use them.
0
earngreenAuthor Commented:
What I am saying is that I created the table but still got the error do I need to create a table type
0
slightwv (䄆 Netminder) Commented:
I still don't know why you are changing from cursors to collections.


Why did you create an employees table?

You need to change the code to use the tables you have.

Post what you created and what you are running.
0
earngreenAuthor Commented:
I am just trying to get it to run without the error. When I put my tables in there it throws the same error.
0
slightwv (䄆 Netminder) Commented:
Again:
Post what you created and the code generating the error.

From above:
The best and easiest approach is to use a temporary table to do this operation but the db has some configuration issues.

I asked what you meant by "temporary table" and you never answered.

What do you think a PL/SQL Table will solve that a "temporary table" will not?

As far as getting what you posted to run, it is pretty straight forward:  Create a table, change the table references:
--drop table tab1 purge;
create table tab1(col1 char(1), col2 char(1)); 
insert into tab1 values('a','b'); 
commit;

DECLARE
   TYPE cursor_ref IS REF CURSOR;
   c1 cursor_ref;
   TYPE emp_tab IS TABLE OF tab1%ROWTYPE;
   rec_tab emp_tab;
   rows_fetched NUMBER;
 BEGIN
   OPEN c1 FOR 'SELECT * FROM tab1';
   FETCH c1 BULK COLLECT INTO rec_tab;
   rows_fetched := c1%ROWCOUNT;
   DBMS_OUTPUT.PUT_LINE('Number of employees fetched: ' || TO_CHAR(rows_fetched));  
 END;
/

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
earngreenAuthor Commented:
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.