Re: help with complex insert inside a procedure

This is undoubtedly because the syntax is supported in sql but not pl/sql.

Very, very irritatingly, until version 9 the sql interpreter in the database engine and in the pl/sql engine are different

It might just be the use of = nvl((select ..., in which case you can rewrite it as = ( select nvl(min(sco_uid_pk),-1)...

Otherwise, you might be able to use user-defined functions (although these can perform badly and can have issues with read-consistency)

Chris Boyle <cboyle_at_no.spam.hargray.com> wrote in message news:a3mf8u$enpk$1_at_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 - 15:01:17 CST