Home » SQL & PL/SQL » SQL & PL/SQL » Create a variable (Oracle 9.2.0.6, Windows XP)
Create a variable [message #307961] Thu, 20 March 2008 10:25 Go to next message
slmorrison64
Messages: 9
Registered: March 2008
Location: Houston TX
Junior Member

I have a SELECT query that extracts information from an existing table and then creates insert statements to migrate the information into a different database (i.e. moving list information from DEV to TEST). That query works correctly and is below:

1 SELECT
2 'INSERT INTO FC_LIST
3 (LIST_NAME,LIST_INDEX,GMS_TIME_STAMP,USERNAME_INDEX,
4 LIST_TYPE,GMS_DATE)
5 VALUES ('
6 || ''''
7 || L.LIST_NAME
8 || ''''
9 || ',(SELECT MAX(LIST_INDEX) FROM FC_LIST) + 1,CURRENT_TIMESTAMP,'
10 ||'(SELECT USERNAME_INDEX from FC_SECURITY_USER where USERNAME = '||''''||S.USERNAME||''''||'),'
11 ||''''||LIST_TYPE||''''
12 ||',CURRENT_TIMESTAMP);'
13 FROM FC_LIST L
14 left join FC_SECURITY_USER S on
15 S.USERNAME_INDEX = L.USERNAME_INDEX
16 WHERE L.LIST_NAME like '05-MIT_ELEC%';

Result set:

INSERT INTO FC_LIST
(LIST_NAME,LIST_INDEX,GMS_TIME_STAMP,USERNAME_INDEX,
LIST_TYPE,GMS_DATE)
VALUES ('05-MIT_ELEC',(SELECT MAX(LIST_INDEX) FROM FC_LIST) + 1,CURRENT_TIMESTAMP,(SELECT USERNAME_INDEX from FC_SECURITY_USER where USERNAME = 'LKIRKPATRICK'),'L',CURRENT_TIMESTAMP);

I then need to use the LIST_NAME in another SELECt query to pull the associated location indexs and meter indexs from a different table.

I have coded in other languages so I think that's what's
causing some of my issues with trying to declare and use
a variable.

Here is what I have:

1 declare listname varchar2(50);
2 set &listname = '05-All_meters_and_Locations_Unregulated';
3 begin
4 select &listname from dual;
5 end;

Since I am really unfamiliar with Oracle and it's use of SQL, I would appreciate someone helping me with the proper syntax. I am currently using a hard-coded value to test the code but will be replacing the hard-coded list name with L.LIST_NAME.

Upon first glance, I don't think the begin and end are where they are supposed to be but I have no clue where to go from there even reading some suggestions online.
Re: Create a variable [message #308077 is a reply to message #307961] Fri, 21 March 2008 01:21 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Yes, your syntax is wrong.
If you want to get familiar with it, start studying the documentation, found eg. online on http://tahiti.oracle.com/. It is described in PL/SQL User's Guide and Reference; you may also find that SQL Reference and Concepts are useful.

The correct assignment statement syntax is
DECLARE
  listname VARCHAR2(50) := '<anything you want>';
BEGIN
  <code>
END;
/
If you do not want to change it, you may declare this variable as CONSTANT.

(SELECT MAX(LIST_INDEX) FROM FC_LIST) + 1

Seems you want to fill LIST_INDEX with unique number. But this approach is NOT ensuring it when multiple users run it at the same time. You shall use SEQUENCE for this purpose.
Re: Create a variable [message #308492 is a reply to message #308077] Mon, 24 March 2008 09:44 Go to previous messageGo to next message
slmorrison64
Messages: 9
Registered: March 2008
Location: Houston TX
Junior Member

Thank you for your response.

The reason I need the variable for LIST_NAME is that there is another table I populate from that only has the LIST_INDEX number and I have to tie it back to the LIST_NAME from FC-LIST. I would prefer to use a variable so that I don't have to do an additional join.

I used the syntax you recommended but I'm still getting errors. It is my understanding that you have to use an ampersand (&) in front of the variable name as I will be using it in several select statements, but you can't declare it with the &.
Re: Create a variable [message #308673 is a reply to message #307961] Tue, 25 March 2008 03:11 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> I used the syntax you recommended but I'm still getting errors.

As only you know how you re-wrote the block and the errors you got, I am afraid nobody may help you (maybe just guessing...).

By the way, I do not have the idea of the dynamic SELECT in your first post. If you want to just execute that statement, stop any dynamic and simply issue INSERT.

It seems to me you mix together PL/SQL variables and SQL*Plus substitution variables, created by DEFINE or ACCEPT SQL*Plus commands. Only the second ones are used with "&".
Re: Create a variable [message #308734 is a reply to message #308673] Tue, 25 March 2008 05:35 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Try This Out:-
set serveroutput on;
declare 
 listname varchar2(50);
begin
 select '05-All_meters_and_Locations_Unregulated' into   listname from dual;
DBMS_OUTPUT.PUT_LINE(listname);
end;
Re: Create a variable [message #308743 is a reply to message #308734] Tue, 25 March 2008 05:50 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why the select?
Re: Create a variable [message #308762 is a reply to message #308743] Tue, 25 March 2008 06:47 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Quote:

This is only a dummy example.

The actual requirement of user is that the variable should hold the value from a table and he don't know the syntax to assign that value.


Instead of
select '05-All_meters_and_Locations_Unregulated' into   listname from dual


He has to put
 select column_name into listname from table_name


Re: Create a variable [message #308822 is a reply to message #308743] Tue, 25 March 2008 10:15 Go to previous messageGo to next message
slmorrison64
Messages: 9
Registered: March 2008
Location: Houston TX
Junior Member

The purpose of the select is that I am copying information from one table in an Oracle environment to a table in a different Oracle environment to avoid having the recreate the lists everytime the environment is refreshed. The select statement will dynamically create the insert statements for the table in the other environment.
Re: Create a variable [message #308825 is a reply to message #308673] Tue, 25 March 2008 10:24 Go to previous messageGo to next message
slmorrison64
Messages: 9
Registered: March 2008
Location: Houston TX
Junior Member

I need for the variable to be assigned a value from the SELECT statement. The next statement which I did not attach in the original post uses the LIST_NAME derived in the select statement to obtain the new LIST_INDEX from the new FC_LIST table and insert it into the new FC_LIST_LOCATION_METER_MEMBER table which only holds index values.

For that reason, I presume I need the substitution variable.

The code I attached in the first post will ultimately not include the WHERE statement at the end so it will include all items in the list. The WHERE statement is only used for testing purposes right now to limit the results to a manageable number while I develop the rest of the code.
Re: Create a variable [message #308855 is a reply to message #307961] Tue, 25 March 2008 13:14 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> For that reason, I presume I need the substitution variable.

I am afraid, it is not possible to assign anything to substitution variable from SELECT. Consider using SQL*Plus VARIABLE instead. It is possible to assign it a value from SELECT, as rajatratewal showed in his second post, or as described in SELECT INTO statement description.

[Updated on: Tue, 25 March 2008 13:15]

Report message to a moderator

Re: Create a variable [message #308906 is a reply to message #308825] Tue, 25 March 2008 22:47 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Quote:
slmorrison64 can you please put the whole code so that we can better understand the problem. And this time please format it properly.
Re: Create a variable [message #311144 is a reply to message #307961] Thu, 03 April 2008 09:56 Go to previous messageGo to next message
slmorrison64
Messages: 9
Registered: March 2008
Location: Houston TX
Junior Member

The whole purpose of this exercise is to take a new item that was created in production environment and move it to test without having to manually enter all the information into multiple tables. I need the variable that I'm trying to create to be passed to each of the select statements so they can create the new insert statements.

The declare statement seems to work but I can't get it passed to the other SELECT statements.

RESULT from declare statement execution:

DBMS_OUTPUT Results Batch 1
05-All_meters_and_Locations_Unregulated


When I try to run the declare with the first SELECT 'INSERT statement I get the following error:


ORA-06550: line 9, column 1:
PLS-00103: Encountered the symbol "SELECT"
ORA-06550: line 22, column 1:
PLS-00103: Encountered the symbol "LEFT" when expecting one of the following:

, ; for group having intersect minus order start union where
connect


In the second and third SELECT 'INSERT statements, I have the code commmented out that deals with the list name until I get the variable thing figured out. The code works correctly and has been tested outside the variable issue. Example of insert run without LIST_NAME or variable (2nd statement).


INSERT INTO FC_LIST_LOCATION_METER_MEMBER
((SELECT MAX(LIST_LOC_MET_INDEX) FROM FC_LIST_LOCATION_METER_MEMBER)
+1,(SELECT LOCATION_INDEX from FC_LOCATION where LOCATION_NUMBER = 'B04_Mid.Only.Inl');
INSERT INTO FC_LIST_LOCATION_METER_MEMBER
((SELECT MAX(LIST_LOC_MET_INDEX) FROM FC_LIST_LOCATION_METER_MEMBER)
+1,(SELECT LOCATION_INDEX from FC_LOCATION where LOCATION_NUMBER = 'B04_MidOnl.Out#2');



Ultimately, this statement should only return records based on the variable. Currently it returns all values in the table.

--CODE STARTS HERE--
1 declare
2 listname FC_LIST.LIST_NAME%TYPE;
3 begin
4 select LIST_NAME into
5 listname from FC_LIST WHERE LIST_NAME = '05-All_meters_and_Locations_Unregulated';
6 DBMS_OUTPUT.PUT_LINE(listname);
7 end;
8
9 SELECT
10 'INSERT INTO FC_LIST(LIST_NAME,LIST_INDEX,GMS_TIME_STAMP,USERNAME_INDEX,LIST_TYPE,GMS_DATE)VALU
ES ('
11 || ''''
12 || L.LIST_NAME
13 || ''''
14 || ',(SELECT MAX(LIST_INDEX) FROM FC_LIST) + 1,CURRENT_TIMESTAMP,'
15 ||'(SELECT USERNAME_INDEX from FC_SECURITY_USER where USERNAME = '||''''||S.USERNAME||''''||'),
'
16 ||''''||LIST_TYPE||''''
17 ||',CURRENT_TIMESTAMP);'
18 FROM FC_LIST L
19 left join FC_SECURITY_USER S on
20 S.USERNAME_INDEX = L.USERNAME_INDEX
21 WHERE L.LIST_NAME like '&listname';
22
23 SELECT
24 'INSERT INTO FC_LIST_LOCATION_METER_MEMBER
25 ((SELECT MAX(LIST_LOC_MET_INDEX) FROM FC_LIST_LOCATION_METER_MEMBER)
26 +1,'
27 --||'(SELECT LIST_INDEX FROM FC_LIST where LIST_NAME = '||
28 --''''||L.LIST_NAME||''''||'),'
29 ||'(SELECT LOCATION_INDEX from FC_LOCATION where LOCATION_NUMBER = '||
30 ''''||LC.LOCATION_NUMBER||''''||');'
31 FROM FC_LIST_LOCATION_METER_MEMBER ML
32 left join FC_LOCATION LC on
33 ML.LOCATION_INDEX = LC.LOCATION_INDEX;
34
35 SELECT
36 'INSERT INTO FC_LIST_LOCATION_METER_MEMBER
37 ((SELECT MAX(LIST_LOC_MET_INDEX) FROM FC_LIST_LOCATION_METER_MEMBER)
38 +1,'
39 --||'(SELECT LIST_INDEX FROM FC_LIST where LIST_NAME = '||
40 --''''||L.LIST_NAME||''''||'),'
41 ||'(SELECT METER_NUMBER_INDEX from FC_METER where
42 METER_NUMBER = '||
43 ''''||MT.METER_NUMBER||''''||');'
44 FROM FC_LIST_LOCATION_METER_MEMBER ML
45 left join FC_METER MT on
46 ML.METER_NUMBER_INDEX = MT.METER_NUMBER_INDEX;
Re: Create a variable [message #311150 is a reply to message #311144] Thu, 03 April 2008 10:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You seems to be unable to format your post.
I seem to be unable to answer you.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.

Regards
Michel
Re: Create a variable [message #311156 is a reply to message #311150] Thu, 03 April 2008 10:37 Go to previous messageGo to next message
slmorrison64
Messages: 9
Registered: March 2008
Location: Houston TX
Junior Member

I've seen the same response from you on multiple other posts and find that your response is somewhat "belittling" which is discouraged in the formattting instructions.

It my understanding that we post to this site because we are not advanced users so it seems a little unrealistic that a response from a more advanced user does not contain suggestions for helping us become better users.

I read the formatting instructions and am having a difficult time determining how my post is not formatted correctly. As specific as you request us to be, it would help if you would give some specific information as to which part of the post is not formatted correctly.

Re: Create a variable [message #311161 is a reply to message #311156] Thu, 03 April 2008 11:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read the section I posted, you'll see an example of what is not formatted and the same formatted.
Then read your post and tell us if you don't see what is not formatted.

There is a Test forum that is there to make post tests and it is written in red: before posting to the other forums.

Regards
Michel

Re: Create a variable [message #311198 is a reply to message #311161] Thu, 03 April 2008 12:21 Go to previous messageGo to next message
slmorrison64
Messages: 9
Registered: March 2008
Location: Houston TX
Junior Member

Thank you. I read the initial information and missed the link further down. I will reformat and repost.
Re: Create a variable [message #311199 is a reply to message #311198] Thu, 03 April 2008 12:29 Go to previous messageGo to next message
slmorrison64
Messages: 9
Registered: March 2008
Location: Houston TX
Junior Member

I used the formatting function within Rapid Sql and the tag tool above. Does this meet the formatting requirement?


  1  DECLARE
  2     listname FC_LIST.LIST_NAME%TYPE;
  3  BEGIN
  4     SELECT LIST_NAME
  5     INTO listname 
  6     FROM FC_LIST 
  7     WHERE LIST_NAME = '05-All_meters_and_Locations_Unregulated';
  8     DBMS_OUTPUT.PUT_LINE(listname);
  9  END;
 10  SELECT
 11  'INSERT INTO FC_LIST(LIST_NAME,LIST_INDEX,GMS_TIME_STAMP,USERNAME_INDEX,LIST_TYPE,GMS_DATE)VALU
ES (' || '''' || L.LIST_NAME || '''' || 
 12  ',(SELECT MAX(LIST_INDEX) FROM FC_LIST) + 1,CURRENT_TIMESTAMP,' ||
 13  '(SELECT USERNAME_INDEX from FC_SECURITY_USER where USERNAME = '||''''||S.USERNAME
 14  ||''''||'),' ||''''||LIST_TYPE||'''' ||',CURRENT_TIMESTAMP);' 
 15  FROM FC_LIST L left join FC_SECURITY_USER S ON S.USERNAME_INDEX = L.USERNAME_INDEX
 16  WHERE L.LIST_NAME LIKE '&listname';
 17  SELECT
 18  'INSERT INTO FC_LIST_LOCATION_METER_MEMBER((SELECT MAX(LIST_LOC_MET_INDEX) FROM FC_LIST_LOCATIO
N_METER_MEMBER)+1,' 
 19     --||'(SELECT LIST_INDEX FROM FC_LIST where LIST_NAME = '||
 20     --''''||L.LIST_NAME||''''||'),'
 21     ||'(SELECT LOCATION_INDEX from FC_LOCATION where LOCATION_NUMBER = '|| ''''
 22     ||LC.LOCATION_NUMBER||''''||');' 
 23  FROM FC_LIST_LOCATION_METER_MEMBER ML left join FC_LOCATION LC ON ML.LOCATION_INDEX
 24  = LC.LOCATION_INDEX;
 25  SELECT
 26  'INSERT INTO FC_LIST_LOCATION_METER_MEMBER((SELECT MAX(LIST_LOC_MET_INDEX) FROM FC_LIST_LOCATIO
N_METER_MEMBER)+1,' 
 27     --||'(SELECT LIST_INDEX FROM FC_LIST where LIST_NAME = '||
 28     --''''||L.LIST_NAME||''''||'),'
 29     ||'(SELECT METER_NUMBER_INDEX from FC_METER where METER_NUMBER = '|| ''''||MT.METER_NUMBER||
''''||');' 
 30  FROM FC_LIST_LOCATION_METER_MEMBER ML left join FC_METER MT ON ML.METER_NUMBER_INDEX
 31  = MT.METER_NUMBER_INDEX;
Re: Create a variable [message #311280 is a reply to message #307961] Thu, 03 April 2008 22:21 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
It is much better at least.

Look at the anonymous block preceding the SELECT statement causing error.
Look at the anonymous block example I posted in my first post.
You are missing slash ('/') after its final END. Just add it (put it into new line).

To your code functionality: it seems you did not read my message #308855 from Tue, 25 March 2008 18:14 UTC with referenced documentation part or you misunderstood it.
You cannot use substitution variable (&) for the SQL assignment, however you still use it in following SELECTs. So, if you did not DEFINE it earlier, will be still asked to enter its value.
You can use PL/SQL variable as you do, but it will not be visible outside its scope (the anonymous PL/SQL block in this case).
You can use SQL*Plus bind variable (:), as I pointed in that post with link to the documentation. You may find examples of its usage in http://download.oracle.com/docs/cd/B10501_01/server.920/a90842/ch6.htm#1007557 (just one click from that link).

Learn to consult the documentation. It contains very useful information often pictured with examples.
Re: Create a variable [message #311408 is a reply to message #311280] Fri, 04 April 2008 07:16 Go to previous message
slmorrison64
Messages: 9
Registered: March 2008
Location: Houston TX
Junior Member

I appreciate all your comments and suggestions. I do consult the documentation and try all the suggestions. It is a little more difficult when you don't have immediate access for questions when you're trying something. You would think I could catch it a little quicker with my previous C++, VB and Java coding experience but it doesn't appear to be going that way. I've looked at numerous examples from other documentation that I've pulled in addition to the recommendations in the post and the light bulb just hasn't come on yet. I appreciate everyone's patience.
Previous Topic: Query for user created databases in Oracle 9i and 10g
Next Topic: please CHECK THE QUERY?
Goto Forum:
  


Current Time: Sun Dec 11 08:19:54 CST 2016

Total time taken to generate the page: 0.15858 seconds