pb cursor


I have an error when I try to execute this cursor :

DECLARE @user_id INT,
DECLARE @lang_id INT,
DECLARE update_user_info CURSOR FOR
select users_id from users where registration >= to_date('24/09/2013','dd/mm/yyyy')  and dn is not null and length(dn) < 130 and language_id=-2 order by registration asc
OPEN update_user_info
FETCH NEXT FROM update_user_info  --Start the cursor
INTO @user_id
WHILE @FETCH_STATUS = 0  --while there is a loaded record, keep processing
select @user_id,orgentity_id,(select max(sequence)+1 from mbrrel where descendant_id=@user_id),(select max(optcounter)+1 from mbrrel where descendant_id=@user_id) from orgentity where orgentityname in('BuyersOrganization')
FETCH NEXT FROM update_user_info INTO @user_id  --fetch next record
CLOSE update_user_info   --close and deallocate
DEALLOCATE update_user_info

PLS-00103: Encountered the symbol "@" when expecting one of the following:
begin function package pragma procedure subtype type use
<an identifier> <a double-quoted delimited-identifier> form
current cursor
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.

johnsoneSenior Oracle DBACommented:
That code looks a lot like TSQL code from a SQL Server database.  What are you actually trying to do, because I don't see it.
bibi92Author Commented:
I try to create oracle pl-sql cursor.
bibi92Author Commented:
I try to do a cursor for executing

insert into test
select '5684222',orgentity_id,(select max(sequence)+1 from test where descendant_id=5684222),(select max(optcounter)+1 from test where descendant_id=5684222) from orgentity
where orgentityname in('MAF_NonValidatedBuyerOrganization');

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

slightwv (䄆 Netminder) Commented:
There are examples of cursor loops in the online docs:

>>I try to do a cursor for executing

Do you need a cursor loop?  It looks like that insert would run by itself.  Typically cursor loops are not faster than native SQL.

What problem are you trying to solve by using a cursor?
bibi92Author Commented:
i don't know how to write it.
slightwv (䄆 Netminder) Commented:
>>i don't know how to write it.

Have you looked at the examples in the docs?  In the link I posted, scroll down a little.  There are links to the examples.

That said:  I understand you don't know how to write it.

My question is:  Do you need to write it in the first place?

The insert into (select) that you posted looks fine to me and is likely the quickest way to get data into one table from another table.
bibi92Author Commented:
Maybe I haven't understand your answer. In the doc, I haven't see examples for create cursor with insert into.
slightwv (䄆 Netminder) Commented:
>>I haven't see examples for create cursor with insert into.

There is the issue:  A cursor processes one record at a time.  Therefore it is a single insert statement.

An "Insert into select" takes a select statement and inserts using an implicit cursor.

We cannot write 100% working code for you without all the necessary information and understanding of your requirements.

This is almost impossible on a Q&A site like this.

We can provide the information on how to write what you need.  You can then take it and apply it to your specific set of requirements.

For example:
In the docs, there are examples of looping through a cursor.  Start off by just getting the loop working and displaying the results with dbms_output.

Once you get the loop working the way you want it, you can add whatever insert you think you need.

If you can provide sample data and expected results we can try to work on a working example based on what you provide.
johnsoneSenior Oracle DBACommented:
This is a total guess based on what little information you have provided, but hopefully it will give you a starting point.

    FOR update_user_info_rec IN (SELECT users_id 
                                 FROM   users 
                                 WHERE  registration >= To_date('24/09/2013', 
                                        AND dn IS NOT NULL 
                                        AND Length(dn) < 130 
                                        AND language_id =- 2) LOOP 
        INSERT INTO test 
        SELECT update_user_info_rec.users_id, 
               (SELECT Max(SEQUENCE) + 1 
                FROM   test 
                WHERE  descendant_id = update_user_info_rec.users_id), 
               (SELECT Max(optcounter) + 1 
                FROM   test 
                WHERE  descendant_id = update_user_info_rec.users_id) 
        FROM   orgentity 
        WHERE  orgentityname IN( 'MAF_NonValidatedBuyerOrganization' ); 
    END LOOP; 

Open in new window


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
slightwv (䄆 Netminder) Commented:
>> but hopefully it will give you a starting point.

I still don't see the need for the cursor loop.
johnsoneSenior Oracle DBACommented:
You could put the list of users_id into a subquery or join, but if they want a sample to get started with to do something more complex, this could be that starting point.
slightwv (䄆 Netminder) Commented:

That is why I asked:
What problem are you trying to solve by using a cursor?

I'm just trying to prevent issues down the road.

I've seen many questions where the answer sought really isn't the 'correct' way to do something.
bibi92Author Commented:
Thanks a lot best regards
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.