OraFAQ Forum: SQL & PL/SQL » HELP UNDERSTANDING CODE

Home » SQL & PL/SQL » SQL & PL/SQL » HELP UNDERSTANDING CODE (Oracle 9i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

HELP UNDERSTANDING CODE [message #339840]

Fri, 08 August 2008 18:00

manningda
Messages: 31
Registered: January 2008

Member

All:

Coding inexperience showing through and looking for some help.

Thanks in advance for all who remember when they had similar questions... Confused

Working to truncate all tables within a schema and found some very code to use as an example. However, have a line in the loop that I don't quite understand the "role" of.

First step to disable all existing constraints...

DECLARE

  CURSOR v_ConstraintRefCur IS 

    				SELECT constraint_name, table_name
    				FROM user_constraints
    				WHERE constraint_type='R'
    				AND table_name IN
      				(SELECT table_name
      				FROM user_tables);
  
  v_SQL VARCHAR2(100);


BEGIN
  FOR v_Cntr
  IN v_ConstraintRefCur 
  LOOP
    v_SQL:='ALTER TABLE '||v_Cntr.TABLE_NAME||
           ' DISABLE CONSTRAINT '||v_Cntr.CONSTRAINT_NAME;
           
    EXECUTE IMMEDIATE v_SQL;
  END LOOP;
    
END;
/

Specifically what's confusing me is the line

 FOR v_Cntr

I don't see where v_Cntr is being declared.

(What's a little more embarrassing is that I think Ive asked this question before)

Can someone help me understand how one would use what appears to me to be the use of a variable that appears to me to be undeclared?

Thanks in advance to those willing to share from their experience!!

Report message to a moderator

Re: HELP UNDERSTANDING CODE [message #339841 is a reply to message #339840]

Fri, 08 August 2008 18:24

nazimm
Messages: 1
Registered: August 2008

Junior Member

v_Cntrl is more like an implicit variable, known as the record_index.

Example Syntax
FOR record_index in cursor_name
LOOP
{.statements.}
END LOOP;

Official terminology
where record_index is a record declared implicitly by PL/SQL with the %ROWTYPE attribute against the cursor specified by cursor_name.

Report message to a moderator

Re: HELP UNDERSTANDING CODE [message #339861 is a reply to message #339840]

Sat, 09 August 2008 01:08

Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Maybe you better understand if you read PL/SQL User's Guide and Reference. You can't understand a french book if you didn't learn a little bit french before.

Chapter 6 Performing SQL Operations from PL/SQL
Section Querying Data with PL/SQL
Paragrahs Looping Through Multiple Rows: Cursor FOR Loop and followings.

Regards
Michel

[Updated on: Sun, 10 August 2008 08:20]

Report message to a moderator

Re: HELP UNDERSTANDING CODE [message #339963 is a reply to message #339840]

Sun, 10 August 2008 07:28

michael_bialik
Messages: 621
Registered: July 2006

Senior Member

IMHO - you can simplify your code by removing :

AND table_name IN
(SELECT table_name
 FROM user_tables)

HTH.

Report message to a moderator

Re: HELP UNDERSTANDING CODE [message #339976 is a reply to message #339963]

Sun, 10 August 2008 11:03

William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK

Senior Member

michael_bialik wrote on Sun, 10 August 2008 13:28

IMHO - you can simplify your code by removing :

AND table_name IN
(SELECT table_name
 FROM user_tables)

Unless this clause is there to exclude object tables Wink

(rather unlikely I admit).

Report message to a moderator

Re: HELP UNDERSTANDING CODE [message #340128 is a reply to message #339840]

Mon, 11 August 2008 09:47

manningda
Messages: 31
Registered: January 2008

Member

Thank you all for excellent information!!

Specific thanks to Michael Cadot for a terrific reference.

I promise Mike that i did have that help link as a reference before posting my question, (and did work to review it as well) -- for whatever reason was not finding that page however.

Thank you so much that is exactly what I was trying to understand!!

Report message to a moderator

Previous Topic:	how to extract the same date when it is last day of the month?
Next Topic:	two oldest values

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Dec 09 19:46:48 CST 2024