cancel
Showing results for 
Search instead for 
Did you mean: 

Dynamic SQL

SOLVED
Go to solution
Printaporn_1
Esteemed Contributor

Dynamic SQL

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.




enjoy any little thing in my life
5 REPLIES
Alexander M. Ermes
Honored Contributor

Re: Dynamic SQL

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
.. and all these memories are going to vanish like tears in the rain! final words from Rutger Hauer in "Blade Runner"
Alexander M. Ermes
Honored Contributor

Re: Dynamic SQL

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
.. and all these memories are going to vanish like tears in the rain! final words from Rutger Hauer in "Blade Runner"
Christian Gebhardt
Honored Contributor
Solution

Re: Dynamic SQL

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
Christian Gebhardt
Honored Contributor

Re: Dynamic SQL

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
Printaporn_1
Esteemed Contributor

Re: Dynamic SQL

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.
enjoy any little thing in my life