|
|
|
|
|
|
| Author |
Message |
Malcolm Dew-Jones
*nix forums Guru
Joined: 04 Mar 2005
Posts: 418
|
 Posted: Fri Feb 04, 2005 8:16 pm Post subject:
Re: If EXISTS before insert clause
|
|
|
JPike (jpike@welcom.com) wrote:
: In my sql script file for SQL Server I have:
: IF EXISTS(SELECT PRD_UID FROM WST_PRD WHERE PRD_UID = 502)
: BEGIN
: INSERT INTO WST_PFD (PFG_UID, OBJ_UID) VALUES('14014', 'GRP_VIEW_RSK')
: .
: .
: .
: END
: Basically I want to see if an entry exists, reflecting that a product
: in installed, and if so run a group of inserts, etc.
: This works fine in SQL Server but Oracle is not liking it. I know IF
: EXISTS doesn't exist here so how should I best do this to run in a
: script file?
PL/SQL
or (my syntax might not be quite right)
INSERT INTO WST_PFD (PFG_UID, OBJ_UID)
select '14014', 'GRP_VIEW_RSK'
from some_table_such_as_DUAL
where exists
(
SELECT PRD_UID FROM WST_PRD WHERE PRD_UID = 502
)
;
--
This space not for rent. |
|
| Back to top |
|
 |
JPike
*nix forums beginner
Joined: 04 Feb 2005
Posts: 7
|
| Posted: Fri Feb 04, 2005 9:07 pm Post subject:
Re: If EXISTS before insert clause
|
|
|
I suppose that will work but it is a pretty big script file and adding
that to every insert would be a pain. Also there are some table
creations, alters, update and stuff falling under the contraints of the
if, so wrapping the whole block in a big IF would be easiest if it's
somehow possible.
Somehow though I am think easy and Oracle don't belong in the same
sentence.
Jim |
|
| Back to top |
|
|
Malcolm Dew-Jones
*nix forums Guru
Joined: 04 Mar 2005
Posts: 418
|
| Posted: Fri Feb 04, 2005 9:28 pm Post subject:
Re: If EXISTS before insert clause
|
|
|
JPike (jpike@welcom.com) wrote:
: I suppose that will work but it is a pretty big script file and adding
: that to every insert would be a pain. Also there are some table
: creations, alters, update and stuff falling under the contraints of the
: if, so wrapping the whole block in a big IF would be easiest if it's
: somehow possible.
As I said, use PL/SQL, __or__ SQL such as I showed. If plain SQL is not
to your liking then use PL/SQL (as I said).
: Somehow though I am think easy and Oracle don't belong in the same
: sentence.
Depends what you mean by easy, doesn't it.
--
This space not for rent. |
|
| Back to top |
|
|
JPike
*nix forums beginner
Joined: 04 Feb 2005
Posts: 7
|
| Posted: Fri Feb 04, 2005 9:55 pm Post subject:
Re: If EXISTS before insert clause
|
|
|
OK, say I had the following in a .sql file
IF EXISTS(SELECT PRD_UID FROM WST_PRD WHERE PRD_UID = 502)
BEGIN
INSERT INTO WPS_DELB (TABLE_TYPE,FLD_NAME,FKEY_TABLE,FKEY_FLD_NAME)
VALUES ('CFLD','CAGR_UID','CAGR','CAGR_UID') ;
INSERT INTO WPS_DELB (TABLE_TYPE,FLD_NAME,FKEY_TABLE,FKEY_FLD_NAME)
VALUES ('CFLD','LKUP_UID','LKUP','LKUP_UID') ;
INSERT INTO WPS_DELB (TABLE_TYPE,FLD_NAME,FKEY_TABLE,FKEY_FLD_NAME)
VALUES ('CIMP','IMPT_UID','IMPT','IMPT_UID') ;
INSERT INTO WPS_DELB (TABLE_TYPE,FLD_NAME,FKEY_TABLE,FKEY_FLD_NAME)
VALUES ('ITTH','TOLR_UID','TOLR','TOLR_UID') ;
INSERT INTO WPS_DELB (TABLE_TYPE,FLD_NAME,FKEY_TABLE,FKEY_FLD_NAME)
VALUES ('RISK','CATG_UID','CATG','CATG_UID') ;
INSERT INTO WPS_DELB (TABLE_TYPE,FLD_NAME,FKEY_TABLE,FKEY_FLD_NAME)
VALUES ('SCOR','IMPT_UID','IMPT','IMPT_UID') ;
INSERT INTO WPS_DELB (TABLE_TYPE,FLD_NAME,FKEY_TABLE,FKEY_FLD_NAME)
VALUES ('SCOR','PROB_UID','PROB','PROB_UID') ;
INSERT INTO WPS_DELB (TABLE_TYPE,FLD_NAME,FKEY_TABLE,FKEY_FLD_NAME)
VALUES ('SCOR','SEV_UID','SEV','SEV_UID') ;
END
How would I covert that to PL/SQL and run it? It sounds to me like you
are just saying change the top part to:
Declare a NUMBER;
SELECT COUNT(PRD_UID) into a FROM WST_PRD WHERE PRD_UID = 502;
IF (a > 0)
BEGIN
..
..
..
END IF
But if I do that and try to run in TOAD it doesn't work.
I am new to Oracle so any help would be appreciated. I just don't
define Oracle as easy. I have worked two places and everyone I have met
hates dealing with it. |
|
| Back to top |
|
|
Galen Boyer
*nix forums Guru Wannabe
Joined: 24 Mar 2005
Posts: 106
|
| Posted: Sat Feb 05, 2005 3:28 am Post subject:
Re: If EXISTS before insert clause
|
|
|
On 4 Feb 2005, jpike@welcom.com wrote:
| Quote: | I suppose that will work but it is a pretty big script file and adding
that to every insert would be a pain.
|
Get an editor that affords you the power to not have to make such a weak
statement. I could do it in a matter of minutes.
| Quote: | Also there are some table creations, alters, update and stuff falling
under the contraints of the if, so wrapping the whole block in a big
IF would be easiest if it's somehow possible.
|
It is possible.
| Quote: | Somehow though I am think easy and Oracle don't belong in the same
sentence.
|
And these kind of statements will make sure you get no help.
--
Galen deForest Boyer |
|
| Back to top |
|
|
Anurag Varma
*nix forums Guru Wannabe
Joined: 25 Mar 2005
Posts: 194
|
| Posted: Sat Feb 05, 2005 4:09 am Post subject:
Re: If EXISTS before insert clause
|
|
|
"JPike" <jpike@welcom.com> wrote in message news:1107557736.605423.126420@o13g2000cwo.googlegroups.com...
| Quote: | OK, say I had the following in a .sql file
IF EXISTS(SELECT PRD_UID FROM WST_PRD WHERE PRD_UID = 502)
BEGIN
INSERT INTO WPS_DELB (TABLE_TYPE,FLD_NAME,FKEY_TABLE,FKEY_FLD_NAME)
VALUES ('CFLD','CAGR_UID','CAGR','CAGR_UID') ;
INSERT INTO WPS_DELB (TABLE_TYPE,FLD_NAME,FKEY_TABLE,FKEY_FLD_NAME)
VALUES ('CFLD','LKUP_UID','LKUP','LKUP_UID') ;
INSERT INTO WPS_DELB (TABLE_TYPE,FLD_NAME,FKEY_TABLE,FKEY_FLD_NAME)
VALUES ('CIMP','IMPT_UID','IMPT','IMPT_UID') ;
INSERT INTO WPS_DELB (TABLE_TYPE,FLD_NAME,FKEY_TABLE,FKEY_FLD_NAME)
VALUES ('ITTH','TOLR_UID','TOLR','TOLR_UID') ;
INSERT INTO WPS_DELB (TABLE_TYPE,FLD_NAME,FKEY_TABLE,FKEY_FLD_NAME)
VALUES ('RISK','CATG_UID','CATG','CATG_UID') ;
INSERT INTO WPS_DELB (TABLE_TYPE,FLD_NAME,FKEY_TABLE,FKEY_FLD_NAME)
VALUES ('SCOR','IMPT_UID','IMPT','IMPT_UID') ;
INSERT INTO WPS_DELB (TABLE_TYPE,FLD_NAME,FKEY_TABLE,FKEY_FLD_NAME)
VALUES ('SCOR','PROB_UID','PROB','PROB_UID') ;
INSERT INTO WPS_DELB (TABLE_TYPE,FLD_NAME,FKEY_TABLE,FKEY_FLD_NAME)
VALUES ('SCOR','SEV_UID','SEV','SEV_UID') ;
END
How would I covert that to PL/SQL and run it? It sounds to me like you
are just saying change the top part to:
Declare a NUMBER;
SELECT COUNT(PRD_UID) into a FROM WST_PRD WHERE PRD_UID = 502;
IF (a > 0)
BEGIN
.
.
.
END IF
But if I do that and try to run in TOAD it doesn't work.
I am new to Oracle so any help would be appreciated. I just don't
define Oracle as easy. I have worked two places and everyone I have met
hates dealing with it.
|
Well if you have not worked much in oracle ... some things might appear to be hard for you.
There are multiple ways to do this in oracle ...
I'll show you two ways to do it in oracle 9iRel2 (first should work in previous versions too):
declare
a number;
begin
select count(*) into a from mytable1 where m = 1;
if (a > 0) then
insert into mytable (x,y) values (1,1);
insert into mytable (x,y) values (2,2);
insert into mytable (x,y) values (3,3);
end if;
end;
/
insert all
into mytable (x,y) values (1,1)
into mytable (x,y) values (2,2)
into mytable (x,y) values (3,3)
select 1 from mytable1 where m = 1 and rownum = 1;
Anurag |
|
| Back to top |
|
|
Mark C. Stock
*nix forums Guru
Joined: 05 May 2005
Posts: 730
|
| Posted: Sat Feb 05, 2005 11:21 am Post subject:
Re: If EXISTS before insert clause
|
|
|
"Anurag Varma" <avdbi@hotmail.com> wrote in message
news:8yYMd.32122$ZD1.24865@twister.nyroc.rr.com...
| Quote: |
"JPike" <jpike@welcom.com> wrote in message
news:1107557736.605423.126420@o13g2000cwo.googlegroups.com...
OK, say I had the following in a .sql file
IF EXISTS(SELECT PRD_UID FROM WST_PRD WHERE PRD_UID = 502)
BEGIN
INSERT INTO WPS_DELB (TABLE_TYPE,FLD_NAME,FKEY_TABLE,FKEY_FLD_NAME)
VALUES ('CFLD','CAGR_UID','CAGR','CAGR_UID') ;
INSERT INTO WPS_DELB (TABLE_TYPE,FLD_NAME,FKEY_TABLE,FKEY_FLD_NAME)
VALUES ('CFLD','LKUP_UID','LKUP','LKUP_UID') ;
INSERT INTO WPS_DELB (TABLE_TYPE,FLD_NAME,FKEY_TABLE,FKEY_FLD_NAME)
VALUES ('CIMP','IMPT_UID','IMPT','IMPT_UID') ;
INSERT INTO WPS_DELB (TABLE_TYPE,FLD_NAME,FKEY_TABLE,FKEY_FLD_NAME)
VALUES ('ITTH','TOLR_UID','TOLR','TOLR_UID') ;
INSERT INTO WPS_DELB (TABLE_TYPE,FLD_NAME,FKEY_TABLE,FKEY_FLD_NAME)
VALUES ('RISK','CATG_UID','CATG','CATG_UID') ;
INSERT INTO WPS_DELB (TABLE_TYPE,FLD_NAME,FKEY_TABLE,FKEY_FLD_NAME)
VALUES ('SCOR','IMPT_UID','IMPT','IMPT_UID') ;
INSERT INTO WPS_DELB (TABLE_TYPE,FLD_NAME,FKEY_TABLE,FKEY_FLD_NAME)
VALUES ('SCOR','PROB_UID','PROB','PROB_UID') ;
INSERT INTO WPS_DELB (TABLE_TYPE,FLD_NAME,FKEY_TABLE,FKEY_FLD_NAME)
VALUES ('SCOR','SEV_UID','SEV','SEV_UID') ;
END
How would I covert that to PL/SQL and run it? It sounds to me like you
are just saying change the top part to:
Declare a NUMBER;
SELECT COUNT(PRD_UID) into a FROM WST_PRD WHERE PRD_UID = 502;
IF (a > 0)
BEGIN
.
.
.
END IF
But if I do that and try to run in TOAD it doesn't work.
I am new to Oracle so any help would be appreciated. I just don't
define Oracle as easy. I have worked two places and everyone I have met
hates dealing with it.
Well if you have not worked much in oracle ... some things might appear to
be hard for you.
There are multiple ways to do this in oracle ...
I'll show you two ways to do it in oracle 9iRel2 (first should work in
previous versions too):
declare
a number;
begin
select count(*) into a from mytable1 where m = 1;
if (a > 0) then
insert into mytable (x,y) values (1,1);
insert into mytable (x,y) values (2,2);
insert into mytable (x,y) values (3,3);
end if;
end;
/
insert all
into mytable (x,y) values (1,1)
into mytable (x,y) values (2,2)
into mytable (x,y) values (3,3)
select 1 from mytable1 where m = 1 and rownum = 1;
Anurag
|
Anurag,
(sorry for the extra email, hit the wrong button for my last reply)
Good suggestion on the multi-table insert (which also has an ELSE
clause) -- however, that is specific to the INSERT statements. FOR
loop syntax would be needed in the OP's case since he also wants to include
table creates:
for r1 in ( SELECT PRD_UID FROM WST_PRD WHERE PRD_UID = 502)
loop
INSERT INTO WST_PFD (PFG_UID, OBJ_UID)
VALUES('14014', 'GRP_VIEW_RSK')
.
.
execute immediate 'create ...';
.
end loop;
Jpike,
Look into EXECUTE IMMEDIATE for including DDL in your PL/SQL -- but
understand how Oracle handles explicit commits. Keep in mind that including
DDL in PL/SQL is not typically done for a runtime operation, but is
reasonable for an install or maintenance script.
A little advise: when something 'doesn't work', be sure to read the error
messages -- your 'doesn't work in TOAD' code was missing a THEN keyword in
the IF statement. Just like C# is not VB, PL/SQL is not TSQL, so you'll
have to learn new syntax.
Also, realize that people 'hate dealing with' things that they don't
understand and don't care (or have time) to learn about -- don't fall into
the same trap, it ensures failure.
++ mcs |
|
| Back to top |
|
|
JPike
*nix forums beginner
Joined: 04 Feb 2005
Posts: 7
|
| Posted: Mon Feb 07, 2005 2:07 pm Post subject:
Re: If EXISTS before insert clause
|
|
|
Thanks  The first one works in a simple test but now I am trying it
on the whole thing. Here is the first part of my file.
declare
risk number;
portfolio number;
begin
select count(*) into risk from wst_prd where prd_uid = 502;
select count(*) into portfolio from wst_prd where prd_uid = 501;
ALTER TABLE WPS_TABL ADD BASE_TABTYP VARCHAR2 (4) NULL;
..
..
..
It errors when it hits the ALTER. Is that an illegal thing to use? The
error it gives me is that ORA-06550: line 19, column 2:
PLS-00103: Encountered the symbol "ALTER" when expecting one of the
following:
and then lists a bunch of other keywords.
I am just trying to understand what is wrong with ALTER? |
|
| Back to top |
|
|
Sybrand Bakker
*nix forums Guru
Joined: 03 Apr 2005
Posts: 1766
|
| Posted: Mon Feb 07, 2005 2:17 pm Post subject:
Re: If EXISTS before insert clause
|
|
|
On 7 Feb 2005 07:07:57 -0800, "JPike" <jpike@welcom.com> wrote:
| Quote: | Thanks The first one works in a simple test but now I am trying it
on the whole thing. Here is the first part of my file.
declare
risk number;
portfolio number;
begin
select count(*) into risk from wst_prd where prd_uid = 502;
select count(*) into portfolio from wst_prd where prd_uid = 501;
ALTER TABLE WPS_TABL ADD BASE_TABTYP VARCHAR2 (4) NULL;
.
.
.
It errors when it hits the ALTER. Is that an illegal thing to use? The
error it gives me is that ORA-06550: line 19, column 2:
PLS-00103: Encountered the symbol "ALTER" when expecting one of the
following:
and then lists a bunch of other keywords.
I am just trying to understand what is wrong with ALTER?
|
Alter table is DDL. DDL is only supported in Pl/sql by Execute
immediate
Execute immediate 'Alter table ....'
Not sure why you want to perform an alter table in an anonymous PL/SQL
block.
--
Sybrand Bakker, Senior Oracle DBA |
|
| Back to top |
|
|
JPike
*nix forums beginner
Joined: 04 Feb 2005
Posts: 7
|
| Posted: Mon Feb 07, 2005 2:31 pm Post subject:
Re: If EXISTS before insert clause
|
|
|
Is DROP the same situation? What do you mean by having it in an
anonymous block?
Basically someone wrote this script file. THe purpose is to migrate a
database from the 1.0 version of our product to the 2.0 version. The
script gets run and updates the db. Some changes need to take place no
matter what (like the ALTERs you are referring to), but some only get
executed if they have parts of the product installed (those inside the
IFs).
I am new to dealind with Oracle so if I am going about this the wrong
way please let me know. I just need to be able to have a .sql script
that someone can just type in a run.
jim |
|
| Back to top |
|
|
JPike
*nix forums beginner
Joined: 04 Feb 2005
Posts: 7
|
| Posted: Mon Feb 07, 2005 2:33 pm Post subject:
Re: If EXISTS before insert clause
|
|
|
Oops. Also what about CREATE? The whole script contains ALTER, CREATE,
DROP, and INSERTS. do all of the first three only work with Execute
Immediate '... ? |
|
| Back to top |
|
|
Sybrand Bakker
*nix forums Guru
Joined: 03 Apr 2005
Posts: 1766
|
| Posted: Mon Feb 07, 2005 3:13 pm Post subject:
Re: If EXISTS before insert clause
|
|
|
On 7 Feb 2005 07:33:37 -0800, "JPike" <jpike@welcom.com> wrote:
| Quote: | Oops. Also what about CREATE? The whole script contains ALTER, CREATE,
DROP, and INSERTS. do all of the first three only work with Execute
Immediate '... ?
|
select, insert, update, delete ---> ordinary DML, no problem
All the rest (CREATE, ALTER, DROP, TRUNCATE) ---> DDL, need execute
immediate.
--
Sybrand Bakker, Senior Oracle DBA |
|
| Back to top |
|
|
Sybrand Bakker
*nix forums Guru
Joined: 03 Apr 2005
Posts: 1766
|
| Posted: Mon Feb 07, 2005 3:20 pm Post subject:
Re: If EXISTS before insert clause
|
|
|
On 7 Feb 2005 07:31:51 -0800, "JPike" <jpike@welcom.com> wrote:
| Quote: | Is DROP the same situation? What do you mean by having it in an
anonymous block?
Basically someone wrote this script file. THe purpose is to migrate a
database from the 1.0 version of our product to the 2.0 version. The
script gets run and updates the db. Some changes need to take place no
matter what (like the ALTERs you are referring to), but some only get
executed if they have parts of the product installed (those inside the
IFs).
I am new to dealind with Oracle so if I am going about this the wrong
way please let me know. I just need to be able to have a .sql script
that someone can just type in a run.
jim
|
anonymous block --> any pl/sql that is not part of a procedure, or a
function or a package.
Is not stored in the database, so DBAs don't exercise any control over
that. Actually, *any* DDL, in *any* *regularly* called
procedure/function etc, should make a DBA feel unconfortable. Someone
might just apply something to the database, making some parts
unfunctional, or mucking the whole thing up.
I'm currently reviewing such a situation, and I don't feel happy about
a client program creating a 10G temporary tablespace on the fly, and
dropping it in the same procedure, knowing it may run every week or
even more often.
Your case is somewhat different. This is a one time operation. Let's
hope the person(s) designing that script knew what they were doing,
and there is no problem.
However, DDL as part of your daily routine, in a fashion, you can't
control it, should make everyone shudder. (And in my case: report my
concerns to customer, stating I can't maintain it in this fashion)
--
Sybrand Bakker, Senior Oracle DBA |
|
| Back to top |
|
|
G Quesnel
*nix forums addict
Joined: 29 Apr 2005
Posts: 77
|
| Posted: Mon Feb 07, 2005 3:21 pm Post subject:
Re: If EXISTS before insert clause
|
|
|
Create, Drop, Alter are DDLs, and are typically submitted with an
execute immediate 'cmd'; statement when used inside a PL/SQL
block/script. We use the execute immediate structure inside anonymous
blocks to add intelligence to our release scripts.
For example, let say that in release x.y, we want to add a column and
an index to table t1, we would use a script that would contain the
following logic;
Declare
vobj number; -- count used to determine if an object exist
vtblspname varchar2(30); -- tablespace name
begin
select count(*) into vobj
from user_tab_columns
where table_name='T1' and column_name='C1';
if vobj < 1 then
execute immediate 'Alter table T1 add C1 number';
end if;
Select count(*) into vobj
from user_indexes
where index_name='AK_T1_C1' and table_name='T1';
if vobj < 1 then
Select tablespace_name into vtblspname
from user_indexes
where index_name='PK_T1';
execute immediate 'Create index 'AK_T1_C1 on T1 (C1)'||
' tablespace '||vtblspname;
end if;
end;
Note that the amount of control you have over the schema will dictate
the complexity of your scripts.
For example, using an index name may not be a good ideal. We use the
table name, with ..._ind_columns, since the production dba may have
rebuilt the index under a different name (out of our control).
This method buys you a lot of flexibility, so that if your schema
upgrade scripts need to run in multiple environments, you can have the
object sized automatically calculated, reducing the need for manual
intervention.
This aproach also allows you to maintain only one schema upgrade script
for one release, and since it is rerunable, you don't have to worry
about which delta may have been run in which database.
You will need to find your happy middle of the road point, between the
complexity of predicting every possibilities and the benefits of it
being handled automatically (at some point, the people implementing the
schema upgrade sripts have to take some responsibilities ! - sorry for
the vent) |
|
| Back to top |
|
|
JPike
*nix forums beginner
Joined: 04 Feb 2005
Posts: 7
|
| Posted: Mon Feb 07, 2005 4:23 pm Post subject:
Re: If EXISTS before insert clause
|
|
|
ok. After adding all the Execute Immediate's I just get this one error
when I select to execute statement:
The following error has occurred:
ORA-06550: line 0, column 0:
PLS-00123: program too large
If I select the "Execute all of content window as script" button in
Toad, I get a file riddled with errors.
Any suggestions? |
|
| Back to top |
|
|
Google
|
|
| Back to top |
|
|
|
|
The time now is Thu Jan 08, 2009 2:19 am | All times are GMT
|
|
Literatura fantastica | Refinance | Secured Loans | Web Hosting by Safehosting | Online MBA Degree
|
|
Copyright © 2004-2005 DeniX Solutions SRL
|
|
|
|
Other DeniX Solutions sites:
Unix/Linux blog |
electronics forum |
medicine forum |
science forum |
|
|
Privacy Policy
|
Powered by phpBB © 2001, 2005 phpBB Group
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
Forum index
»
Databases
»
Oracle
