help with complex insert inside a procedure

From: Chris Boyle <cboyle_at_no.spam.hargray.com>
Date: Mon, 4 Feb 2002 12:10:18 -0500
Message-ID: <a3mf8u$enpk$1@news3.infoave.net>

Good morning all,
  I am having a problem with putting complex insert statements inside of a procedure. Oracle 8.1.6 on a Compaq tru 64 system. This is for a conversion routine I have a script that generates the insert statements such as
INSERT INTO chris_b.SWT_DIALOG_ACTIONS (SDA_UID_PK, SDA_SWT_CMDS_UID_FK, SDA_SWT_DIAL_QUESTIONS_UID_FK, SDA_SWT_DIAL_RESPONSES_UID_FK, SDA_ACTION_FL,SDA_ACTIVE_FL,SDA_QUES_RESP_SEQ_NO, CREATED_BY, CREATED_DATE )VALUES (sda_seq.nextval,
(SELECT
  swc_uid_pk
FROM
  swt_cmds
WHERE
  SWC_SWT_EQUIPMENT_UID_FK = ( select seq_uid_pk from swt_equipment where seq_code = 'CVX')and
  swc_features_uid_fk = (select ftp_uid_pk from features where ftp_code =
'CCB')and

  swc_active_fl = 'Y' and
  swc_swt_cmd_codes_uid_fk = (select scc_uid_pk from swt_cmd_codes where scc_code = 'ADD') and

  swc_action_fl = 'A' and
  swc_dialogue_fl = 'Y'and
  swc_process_seq_no = '5'and

nvl(SWC_SWT_CMD_OPTIONS_UID_FK,-1) = nvl((select sco_uid_pk from swt_cmd_options where sco_code = ''),-1)) ,(select sdq_uid_pk from swt_dial_questions where sdq_question = 'Access code:'),
(select sdr_uid_pk from SWT_DIAL_RESPONSES where sdr_code = 'CHR(10)'),
'A',
'Y',
'4',
'CONVERSION',

sysdate);

We use sequences to generate the UIDs which is why the swc_uid_pk sub query has 4 sub sub queries in it. This statement works fine if I execute it as a stand alone but when I put it inside of a procedure and try to compile it I get the error

PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:

( - + mod not null others <an identifier> <a double-quoted delimited-identifier> <a bind variable> avg count c
referring to the first subselect in the string (the SELECT swc_uid_pk FROM swt_cmds block). I have searched metalink, ask Tom and the newsgroups and could find no reference to this type of behavior. Could somebody point me in the right direction please? I know this could be very slow but this is a relatively small table and performance is not going to be an issue. Might I be better off to just have the procedure call the sql script instead of embedding all the inserts inside of it?

Thanks for any advice, Received on Mon Feb 04 2002 - 11:10:18 CST