How do I initialize a user variable from inside a plsql block? 1

[rleiman]

Hi.

Can you tell me how to initialize a user variable from within a pl/sql code block?

I wish to initialize it and outside the block use an if statement to determine if the code outside the block should execute.

The following code is what we did so far.

Thanks.

Emad

Clear columns
Clear breaks
Clear computes

Set echo off
Set feedback off
Set verify off

Prompt
Prompt "Import DMR Trips, Clients, Segments, Groups, and Programs into the AVL server."
Prompt

Accept aUser prompt 'Enter User Name: '

connect &aUser@dmrads

Accept aTravelDate prompt 'Enter TravelDate as [DD-MON-YYYY] (Example: 01-SEP-2005): '

SET serverOutput ON

Declare
   aTotalTrips   Number (10);
   
Begin
   Select count(*) 
     Into aTotalTrips 
     From dev.myTable
    Where travel_date = '&&aTravelDate';

   If aTotalTrips > 0
   Then      
      dbms_output.put_line ('');
      dbms_output.put_line ('');
      dbms_output.put_line ('');
      dbms_output.put_line ('');
      dbms_output.put_line ('Please remove the schedules first for this date.');
      dbms_output.put_line ('');
      dbms_output.put_line ('');
      dbms_output.put_line ('');
   Else
      dbms_output.put_line ('');
      dbms_output.put_line ('');
      dbms_output.put_line ('Processing...');
      dbms_output.put_line ('');
      dbms_output.put_line ('');

-- I wish to initialize the user variable here.

   End If;
End;
/

-- The if statement would go here 

-- If the user variable = theValue
   Then
      -- Code I want to execute goes here.
   End If;

Prompt "Processing has finished. Press any key to continue."
pause

Exit

 

[LKBrwnDBA]

You can only use the IF statement inside a PL/SQL block.
But anyway, try something like this:

Clear ...etc...
[b][red]VAR MyFlag VARCHAR2(5);[/red][/b]
...etc...
Declare
...etc...
BEGIN
  :MyFlag:='FALSE';
  ...etc...
  If A_OK Then
    :MyFlag:='TRUE';
  End If;
END;
/
-- Well, you get the idea.

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

rleiman,

First, to get the statements, "dbms_output.put_line ('');" to print blank lines, you must turn on your serveroutput with the following command:

SET serverOutput ON format wrapped

Next, in your code:

-- I wish to initialize the user variable here.

   End If;
End;
/

-- The if statement would go here 

-- If the user variable = theValue
   Then
      -- Code I want to execute goes here.
   End If;

1) What variables do you wish to initialize?

2) The "if statement would go here" cannot go beyond the END; of your PL/SQL block.

3) What do you mean by "Code I want to execute goes here"?


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[rleiman]

Hi Everyone,

Thanks for the quick response.

To Mufasa,

I was thinking about creating a user variable like this:
Variable aOkToProcess

And set it in the code block like:
aOkToProcess := 'OK';

And in the if statement outside the block:
If aOkToProcess = 'OK'
Then
drop sequence mySequence;

create sequence .....

myStoredProcedure.myFunction ('A STRING');
End If;

I just don't know what the syntax should be to perform this logic would be.

Thanks.

Emad-ud-deen

 

[SantaMufasa]

Emad,

I don't see any reason why you cannot do something similar to the following:

Clear columns
Clear breaks
Clear computes

Set echo off
Set feedback off
Set verify off

Prompt
Prompt "Import DMR Trips, Clients, Segments, Groups, and Programs into the AVL server."
Prompt

Accept aUser prompt 'Enter User Name: '

connect &aUser@dmrads

Accept aTravelDate prompt 'Enter TravelDate as [DD-MON-YYYY] (Example: 01-SEP-2005): '

SET serverOutput ON

Declare
   aTotalTrips   Number (10);
   
Begin
   Select count(*) 
     Into aTotalTrips 
     From dev.myTable
    Where travel_date = to_date('&aTravelDate','DD-MON-YYYY');
   If aTotalTrips > 0
   Then      
      dbms_output.put_line ('');
      dbms_output.put_line ('');
      dbms_output.put_line ('');
      dbms_output.put_line ('');
      dbms_output.put_line ('Please remove the schedules first for this date.');
      dbms_output.put_line ('');
      dbms_output.put_line ('');
      dbms_output.put_line ('');
   Else
      dbms_output.put_line ('');
      dbms_output.put_line ('');
      dbms_output.put_line ('Processing...');
      dbms_output.put_line ('');
      dbms_output.put_line ('');
      If aOkToProcess = 'OK' Then
          execute immediate ('drop sequence mySequence');
          execute immediate ('create sequence .....');
          myStoredProcedure.myFunction ('A STRING');
      End If;
   End If;
End;
/
Prompt "Processing has finished. Press any key to continue."
pause
Exit

I just don't know where "aOkToProcess" comes from (whether you want it to be a PL/SQL variable or an Oracle bind variable similar to LK's definition method. Regardless, the above scheme should work fine.

Let us know if you have follow-up questions on this.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[rleiman]

Hi.

Thanks Mufasa.

The execute immediate worked well!

Truly,
Emad-ud-deen