I am facing the coding proble in the following Stored Procedure wherein I am trying to create a table after deleting it. and then I am trying to insert records from two other tables based on certain condition. I am getting error(s) in it.
Can someone help me in solving it.
Regards
=============== =============== =============== ======== -
-
CREATE OR REPLACE PROCEDURE TMPDUMP IS
-
-
TMPINWARDNO NUMBER(6);
-
TMPDRN NUMBER(7);
-
TMPDPID VARCHAR2(8);
-
TMPCLID NUMBER(8);
-
TMPCONF_NO NUMBER(7);
-
TMPCONF_DT VARCHAR2(10);
-
-
DECLARE CURSOR C IS
-
select inwardno, drn, dpid, clid, conf_no, conf_dt from ndl.mstd where conf_dt is not null or conf_dt <> ' '
-
Union all
-
select inwardno, drn, dpid, clid, conf_no, conf_dt from cdl.mstd where conf_dt is not null or conf_dt <> ' ';
-
BEGIN
-
delete from TMPDT;
-
dbms_output.put_line('Data is deleted');
-
COMMIT;
-
create table TMPDT (INWARDNO NUMBER(6),DRN NUMBER(7),DPID VARCHAR2(8), CLID NUMBER(8), CONF_NO NUMBER(7), CONF_DT VARCHAR2(10));
-
OPEN C;
-
LOOP
-
fetch C into :INWARDNO,:DRN,:DPID, :CLID, :CONF_NO, :CONF_DT;
-
exit when c%NOTFOUND;
-
insert into inout.TMPDT values (INWARDNO,DRN,DPID, CLID, CONF_NO, CONF_DT);
-
END LOOP;
-
CLOSE C;
-
COMMIT;
-
EXCEPTION
-
WHEN OTHERS THEN
-
dbms_output.put_line(SQLERRM);
-
ROLLBACK;
-
END;
-
/
-
=============== =============== =============== ========
3 1974
First thing is you CANNOT run DDL statements like that in a Stored program. You need to use Dynamic SQL method to execute your DDL Statement!!
Saii 145
Recognized Expert New Member
In addition to that, delete statement deletes the rows but does not drop table so you cannot create an already existing table.
First thing is you CANNOT run DDL statements like that in a Stored program. You need to use Dynamic SQL method to execute your DDL Statement!!
I am facing the coding proble in the following Stored Procedure wherein I am trying to create a table after deleting it. and then I am trying to insert records from two other tables based on certain condition. I am getting error(s) in it.
Can someone help me in solving it.
Regards
=============== =============== =============== ======== -
-
CREATE OR REPLACE PROCEDURE TMPDUMP IS
-
-
TMPINWARDNO NUMBER(6);
-
TMPDRN NUMBER(7);
-
TMPDPID VARCHAR2(8);
-
TMPCLID NUMBER(8);
-
TMPCONF_NO NUMBER(7);
-
TMPCONF_DT VARCHAR2(10);
-
-
DECLARE CURSOR C IS
-
select inwardno, drn, dpid, clid, conf_no, conf_dt from ndl.mstd where conf_dt is not null or conf_dt <> ' '
-
Union all
-
select inwardno, drn, dpid, clid, conf_no, conf_dt from cdl.mstd where conf_dt is not null or conf_dt <> ' ';
-
BEGIN
-
delete from TMPDT;
-
dbms_output.put_line('Data is deleted');
-
COMMIT;
-
create table TMPDT (INWARDNO NUMBER(6),DRN NUMBER(7),DPID VARCHAR2(8), CLID NUMBER(8), CONF_NO NUMBER(7), CONF_DT VARCHAR2(10));
-
OPEN C;
-
LOOP
-
fetch C into :INWARDNO,:DRN,:DPID, :CLID, :CONF_NO, :CONF_DT;
-
exit when c%NOTFOUND;
-
insert into inout.TMPDT values (INWARDNO,DRN,DPID, CLID, CONF_NO, CONF_DT);
-
END LOOP;
-
CLOSE C;
-
COMMIT;
-
EXCEPTION
-
WHEN OTHERS THEN
-
dbms_output.put_line(SQLERRM);
-
ROLLBACK;
-
END;
-
/
-
=============== =============== =============== ========
hi
you cannot use direct DDL stmts in Procedure instead you have to use dynamic sql stmts
execute immediate' create table a (n number)';
and delete will remove records from the table not the table structure.
so use another
execute immediate ' drop table a' ;
this will work
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Nyul |
last post by:
Gurus,
I have a verb big problem which I'm unable to explain.
We have a DB2 V6.1.0 on AIX 4.3
I want to make a C stored procedure which at the end will be called by a PHP
script.
The development server was an UDB V6.1.0 on W2K.
Everything went well. I was able to call the stored proc from C and from
Delphi.
|
by: Kent Lewandowski |
last post by:
hi all,
Recently I wrote some stored procedures using java jdbc code
(admittedly my first stab) and then tried to implement the same within
java packages (for code reuse). I encountered problems doing this.
I wanted to implemented a generic "Helper" class like this:
/**
* Helper
|
by: Peter |
last post by:
Hi, there
I have created an stored procedure using the DDL below for my MS Access
Database and no error occurs. Also it can create an stored procedure if I
changed the parameter from "" to ""@zSampleName".
OleDbcmd.CommandText = _
"CREATE PROCEDURE udpGetSampleIDByName" & vbCrLf & _
"( VarChar(64))" & vbCrLf & _
"AS" & vbCrLf & _
|
by: sam |
last post by:
I want to convert standard SQL update syntax into calling stored procedure.
Normal Coding
-------------
Dim constr As String = "server='SQLSVR'; user id='USER';
password='password'; Database='ERP'"
Dim sqlcon As System.Data.SqlClient.sqlconnection = New
System.Data.SqlClient.sqlconnection(constr)
Dim sqlcmd As System.Data.SqlClient.SqlCommand = New SqlCommand()
|
by: Dabbler |
last post by:
I'm using an ObjectDataSource with a stored procedure and am getting the
following error when trying to update (ExecuteNonQuery):
System.Data.SqlClient.SqlException: Procedure or Function 'UpdateRegistrant'
expects parameter '@EMail', which was not supplied.
The field value was null in the database and not changed in the FormView so
is null going back into the stored procedure. I'm stumped and would greatly
appreciate any suggestions.
| |
by: Siv |
last post by:
Hi,
I have a stored procedure that I want to execute and then wait in a loop
showing a timer whilst it completes and then carry on once I get
notification that it has completed. The main reason for this being to stop
the user thinking the application has frozen when in fact it is just waiting
for a long SP to complete. Another reason for doing it like this is that I
also have had a problem in the past where the SP takes longer than the...
|
by: sheenaa |
last post by:
Hello Members,
I m creating my application forms in ASP.Net 2005 C# using the backend SQL Server 2005.
What i have used on forms :: ?
On my first form i have used some label,textboxs,dropdownlists,radiobutton and checkbox asp standard controls.
On the click event of the command button the data gets stored into the database.
I have created the stored procedures for the insert,update,delete.
I have...
|
by: =?Utf-8?B?X3ByZWZpeA==?= |
last post by:
Hello All,
I have written a page with a sqlserverdatasource control and a formview web
control. I want to use stored procedures to perform CRUD operations.
Formview textboxes bind to table columns to present the data for direct
table operations, but textboxes do not bind with the stored procedure
parameter name in any simple way.
I don’t have an problem with creating and deleting a row because inserting
|
by: SOI_0152 |
last post by:
Hi all!
Happy New Year 2008. Il hope it will bring you love and happyness
I'm new on this forum.
I wrote a stored procedure on mainframe using DB2 7.1.1 and IBM language c.
Everything works fine.
Now we decided to move from mainframe IMS-DB2 to Windows 2003 server-DB2 UDB
for LUW 9.5.
|
by: sweatha |
last post by:
Hi
I am quite new in writing stored procedure. I have created a stored procedure for binding the data from DB. I have to bind data from Specialty table with 2 feilds. For that my stored procedure coding is
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
| |
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |