Inserting a hard coded value & a value from a select statement

Toad for Oracle & DevOps Toolkit
1

Greetings!

(Note: I do not have insert or update or delete privilages)

Will following insert statment work (i.e to insert a hard coded value & a value from a select statement into Oracle 11g table using Toad 10.6)? Before share this script with our DBA for him to run, I want to make sure that my code is valid.

INSERT INTO GOREMAL (GOREMAL_EMAL_CODE, GOREMAL_EMAIL_ADDRESS)
VALUES (‘20’, ( SELECT DISTINCT UOS_PIDM_ID(SIRASGN_PIDM)
FROM SIRASGN
WHERE SIRASGN_TERM_CODE = ‘201620’
AND NOT EXISTS ( SELECT GOREMAL_PIDM
FROM GOREMAL
WHERE GOREMAL_EMAL_CODE = 20
AND GOREMAL_PIDM = SIRASGN_PIDM
)
)
);

Basically, I want to find out if I can use both hard coded values and a select ststament in a single insert statement.

2

Evening,

I've never seen a statement written like that before, so I have no idea if it will definitely work.

What I would have done is changed the values valued to ...

Values ( select distinct 20, uos_pidm_id (...) ...

However some questions come to mind.

Do you need distinct? Can there be more than one email address for the given code of 201620? Distinct implies a sort. Also it looks like you are calling a function to get the I'd, that would imply a single return value, unless it was a pipelined function, but they have different calling conventions. So I don't think you need or want the distinct.

You also don't appear to have an environment where you can test things out. There's nothing worse for a dba to be handed a script to run, perhaps out of normal hours, and for the script to fail. You must get yourself a database where you can test your scripts so that the dba gets a working script.

If you don't have one around, where do your developers test their code? Surely they font just hand over scripts to the dba, untested?

Assuming you have a Windows pc, you can download Oracle XE for free use, including commercial. It's 11g but not the latest. You might, of course, have problems getting permission to do that, but you should ask. Good luck.

It's not really viable to be handing over code you think works.

Sorry about the rant, I'm a dba! :wink:

Cheers,

Norm. [TeamT]

On 11 February 2017 18:06:39 GMT+00:00, jollydoe bounce-jollydoe@toadworld.com wrote:

Inserting a hard coded value & a value from a select statement

Thread created by jollydoe
Greetings!

(Note: I do not have insert or update or delete privilages)

Will following insert statment work (i.e to insert a hard coded value & a value from a select statement into Oracle 11g table using Toad 10.6)? Before share this script with our DBA for him to run, I want to make sure that my code is valid.

INSERT INTO GOREMAL (GOREMAL_EMAL_CODE, GOREMAL_EMAIL_ADDRESS)
VALUES ('20', ( SELECT DISTINCT UOS_PIDM_ID(SIRASGN_PIDM)||'@sharjah.ac.ae'
FROM SIRASGN
WHERE SIRASGN_TERM_CODE = '201620'
AND NOT EXISTS ( SELECT GOREMAL_PIDM
FROM GOREMAL
WHERE GOREMAL_EMAL_CODE = 20
AND GOREMAL_PIDM = SIRASGN_PIDM
)
)
);

Basically, I want to find out if I can use both hard coded values and a select ststament in a single insert statement.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle Forum notifications altogether.

Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.

--

Sent from my Android device with K-9 Mail. Please excuse my brevity.

3

Hi Norm,

Thanks for the prompt response and for suggesting an alternative option.

To answer your question, I do not need distinct. I did it to avoid any duplicate data issue. I can remove the distinct if I have to. Yes, there can be more than one email address per ID.

I spoke to our DBA and he said that I might be able to get additional privileges in dev/test environment pending certain approval. Hopefully the approval will happen soon.

4

If you are looking for a way to have a hard-coded value, why not put it in the select like this (with or without the distinct depending on your data):

INSERT INTO GOREMAL(GOREMAL_EMAL_CODE, GOREMAL_EMAIL_ADDRESS)

SELECT DISTINCT '20', UOS_PIDM_ID(SIRASGN_PIDM) || '@sharjah.ac.ae'

FROM SIRASGN

WHERE SIRASGN_TERM_CODE = '201620'

AND NOT EXISTS

(SELECT GOREMAL_PIDM

FROM GOREMAL

WHERE GOREMAL_EMAL_CODE = 20

AND GOREMAL_PIDM = SIRASGN_PIDM);

On Mon, Feb 13, 2017 at 4:40 AM, jollydoe bounce-jollydoe@toadworld.com wrote:

RE: Inserting a hard coded value & a value from a select statement

Reply by jollydoe
Hi Norm,

Thanks for the prompt response and for suggesting an alternative option.

To answer your question, I do not need distinct. I did it to avoid any duplicate data issue. I can remove the distinct if I have to. Yes, there can be more than one email address per ID.

I spoke to our DBA and he said that I might be able to get additional privileges in dev/test environment pending certain approval. Hopefully the approval will happen soon.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle Forum notifications altogether.

Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.

--
Phyllis Helton

Data Magician

Security Gestapo
Digital Products & Strategies, Cru | Data Sciences & Analytics
Office :phone: 407-515-4452

phyllis.helton@cru.org