- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: Dynamic SQL
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО11-05-2002 08:42 PM
тАО11-05-2002 08:42 PM
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
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
-------- -----------------------------------------------------------------
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.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО11-05-2002 10:35 PM
тАО11-05-2002 10:35 PM
Re: Dynamic SQL
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО11-05-2002 10:36 PM
тАО11-05-2002 10:36 PM
Re: Dynamic SQL
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО11-05-2002 11:05 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО11-05-2002 11:08 PM
тАО11-05-2002 11:08 PM
Re: Dynamic SQL
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО11-06-2002 12:35 AM
тАО11-06-2002 12:35 AM
Re: Dynamic SQL
-------------------
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.