topic Re: Dynamic SQL in Operating System - HP-UX

Dynamic SQL

Printaporn_1 — Wed, 06 Nov 2002 04:42:49 GMT

dynamic SQL strings

Hi,

I am newbie for PL/SQL .
I create a small program
-------------
CREATE OR REPLACE procedure snm_update as
xsn varchar2(25) :='0003BA16AED4';
asn varchar2(25);
last varchar2(25);
time varchar2(25);
curr varchar2(25);
dml_str VARCHAR2(500);
CURSOR c1 IS
SELECT sn FROM tmp_sn;
BEGIN
open c1;
loop
fetch c1 into xsn;
exit when c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(xsn);
dml_str := 'update sn_master
set laststation=
(select laststation from sn_master@sunsfr where sn= '||xsn||' and shiped='N')
where sn= '||xsn||' and shiped='N' ';
DBMS_OUTPUT.PUT_LINE(dml_str);
-- execute immediate dml_str;
commit;
END LOOP;
close c1;
END;
/
-----------------------------
when complie it got error
SQL> show errors
Errors for PROCEDURE SNM_UPDATE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
18/75 PLS-00103: Encountered the symbol ")
where sn= " when expecting one of the following:
. ( * @ % & = - + ; < / > at in mod not rem
<> or != or ~= >= <= <> and or like
between is null is not || is dangling
The symbol "(" was substituted for ")
where sn= " to continue.

19/33 PLS-00103: Encountered the symbol " " when expecting one of the
following:
. ( ) , * @ % & | = - + < / > at in mod not rem => ..

LINE/COL ERROR
-------- -----------------------------------------------------------------
<> or != or ~= >= <= <> and or like as
between from using is null is not || is dangling
The symbol ") was inserted before " " to continue.
-----------------------------------------------

How can my program can execute SQL like
update sn_master
set laststation=
(select laststation from sn_master@sunsfr
where sn='0003BA16AED4' and shiped='N')
where sn='0003BA16AED4' and shiped='N'
------------------------
I want to substitute 0003BA16AED4 with variable xsn
please suggest , thanks in advance.

Re: Dynamic SQL

Alexander M. Ermes — Wed, 06 Nov 2002 06:35:21 GMT

Hi there.
Is this line as you wrote it ?

dml_str := 'update sn_master

If yes change it to

dml_str := 'update sn_master';

Rgds
Alexander M. Ermes

Re: Dynamic SQL

Alexander M. Ermes — Wed, 06 Nov 2002 06:36:43 GMT

Hi there.
Is this line as you wrote it ?

dml_str := 'update sn_master

If yes change it to

dml_str := 'update sn_master';

Rgds
Alexander M. Ermes

Re: Dynamic SQL

Christian Gebhardt — Wed, 06 Nov 2002 07:05:12 GMT

Hi
it's a problem with '

(select laststation from sn_master@sunsfr where sn= ''||xsn||'' and shiped=''N'') where sn= ''||xsn||'' and shiped=''N'' ';

you hae to quote ' with another '

Chris

Re: Dynamic SQL

Christian Gebhardt — Wed, 06 Nov 2002 07:08:47 GMT

Hi
Attention:
In my former posting the ''N'' are two single qoutes not a double quote. The output from my browser doesn't show this.

Chris

Re: Dynamic SQL

Printaporn_1 — Wed, 06 Nov 2002 08:35:18 GMT

thanks Christian, you give me the guide but the right one is
-------------------
CREATE OR REPLACE procedure snm_update as
xsn varchar2(25) :='0003BA16AED4';
asn varchar2(25);
last varchar2(25);
time varchar2(25);
curr varchar2(25);
dml_str VARCHAR2(500);
CURSOR c1 IS
SELECT sn FROM tmp_sn;
BEGIN
open c1;
loop
fetch c1 into xsn;
exit when c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(xsn);
dml_str := 'update sn_master set laststation=(select laststation from sn_master@sunsfr where sn= '''||xsn||''' and shiped=''N'') where sn= '''||xsn||''' and shiped=''N'' ';
DBMS_OUTPUT.PUT_LINE(dml_str);
-- execute immediate dml_str;
commit;
END LOOP;
close c1;
END;
/
--------------------
and the result:
SQL> execute snm_update
0003BA16AED4
update sn_master set laststation=(select laststation from sn_master@sunsfr where
sn= '0003BA16AED4' and shiped='N') where sn= '0003BA16AED4' and shiped='N'

PL/SQL procedure successfully completed.