Literal Escape Character? 1

[Petemush]

Hi,

I'm trying to find out whether PLSQL has an escape character to indicate that the text following it should be taken as a literal.

To explain:

I want to use the following statement:

DECLARE

variable1  VARCHAR2 := 'a value'

BEGIN

  IF variable || '1' = 'a value' THEN

The way this reads at the moment, it would be parsed so that the contents of variable would be concatenated with '1' and compared to 'a value'.

What I want to happen is that the word variable is concatenated with '1' and then the contents of the variable variable1 is compared to 'a value'.

I'm convinced there's an escape character for this but I can't remember what it is and can't find it written down anywhere. Assuming the escape character was '&' then the code above would look like:

DECLARE

variable1  VARCHAR2 := 'a value'

BEGIN

  IF &variable || '1' = 'a value' THEN

Then the contents of variable1 would be compared to 'a value'. Hope there is such a thing!

Cheers.

 

[sem]

There's no such symbol, pl/sql doesn't support lexical variables. You should use dynamic sql (REF CURSORs, EXECUTE IMMEDIATE or DBMS_SQL).

Regards, Dima

http://seminihin.narod.ru

 

[Petemush]

Bugger!

Cheers for letting me know Dima, don't really know how to utilise your alternative suggestions with what I want to do though.

I've basically got 7 variables(variable1 to variable7) and I want to do a similar thing to each but need to check that the value of the variable is greater than 0 before I do anything.

Got a FOR loop which goes round seven times and does the required function like so(imagine there is an escape character for a second!):

FOR count IN 1 .. 7
LOOP
  IF (&variable || count) > 0 THEN

    --Do something with the variable.

  END IF
END LOOP

Any ideas how that translates to Dynamic sql?

Cheers!

 

[sem]

Ok, where did that variables come from? Have you declared them somewhere else? Are they just column names? To be used (statically or dynamically) a variable should exist at least

Regards, Dima

http://seminihin.narod.ru

 

[Petemush]

Yeah, the variables have already been defined. Basically they're each a running total from a FOR loop that happened before this LOOP so when you get to this point, some of them will have a value of 0 and others will have a value greater than 0.

CREATE OR REPLACE PROCEDURE HSP_EXT_DIDOS_PO IS

lc_variable1 NUMBER(8) := 0;
lc_variable2 NUMBER(8) := 0;
lc_variable3 NUMBER(8) := 0;
lc_variable4 NUMBER(8) := 0;
lc_variable5 NUMBER(8) := 0;
lc_variable6 NUMBER(8) := 0;
lc_variable7 NUMBER(8) := 0;

CURSOR cur_some_cursor
  SELECT summat
  FROM summat_else;

BEGIN

  -- Loop to get some totals
  FOR rec_some_cursor IN cur_some_cursor
  LOOP
  
    -- Add something to one of the variables depending
    -- on stuff in rec_some_cursor
  END LOOP

  -- Here's the loop I'm working on, once again with imaginary
  -- escape character.
  FOR count IN 1 .. 7
  LOOP

    IF (&variable || count) > 0 THEN

      -- Do something with the variable.

    END IF;
  END LOOP;
END;

Hope that's what you need!

 

[jaggiebunnet]

I don't quite get what you are trying to do here...are you just trying to concate the variable with the loop value? or are you trying to add the loop value to be part of the variable name?

If the later then you could use contexts for just this task, for an example see

http://asktom.oracle.com/pls/ask/f?p=4950:61:3409001306271895847::::P61_ID:1288301763279

 

[sem]

Why don't you use some collection type? It seems to be much more flexible.

declare
type tNumTab is table of number(8) index by BINARY_INTEGER;
var tNumTab;
...
BEGIN
...
FOR count IN 1 .. 7
  LOOP

    IF variable(count) > 0 THEN

      -- Do something with the variable.

    END IF;

END;

Regards, Dima

http://seminihin.narod.ru

 

[Petemush]

I did have that as an option, just thought it might be a bit overkill to create an array(of sorts) for something so simple.

jaggiebunnet: Yes, I was trying to append the loop variable to the variable name and then look at the result as if it was a variable. Cheers for the suggestion about contexts, I think I'll go with a collection type as I had orignally thought it could be pretty simple so I'll stick with the next simplest thing!

Thanks for the suggestion tho and thanks again Dima for sticking with it!

Cheers.

 

[jamieg99]

Hi Peter,

I'm no oracle expert, but I remember using something like this in the past to acheive a similar result...

CURSOR_ID INTEGER := DBMS_SQL.OPEN_CURSOR;
SQLSTMT VARCHAR2(100) := 'if ' || variable || '1 = ''a value'' then ...';
DBMS_SQL.PARSE(CURSOR_ID,SQLSTMT,DBMS_SQL.NATIVE);

-Jamie

 

[sem]

Jamie, this can not work, just read about PL/SQL basics, namely variable scope of view. This cursor is executed in its own context and knows nothing about existance of variable variable.

Regards, Dima

http://seminihin.narod.ru