Stored procedure from stored procedure
Is it possible to create a stored procedure from a stored procedure?
When I attempt this inanity, it doesn't blow up until syntax error at
the first "end procedure" statement of the SP I'm trying to create
within the outer SP. Can this be done?
Using IDS 7.31-UC4 on Solaris 2.7/Intel (don't ask).
On 24 Sep 2003 11:47:09 -0700, email@example.com (Red Valsen)
>Is it possible to create a stored procedure from a stored procedure?
>When I attempt this inanity, it doesn't blow up until syntax error at
>the first "end procedure" ...calling a stored procedure from a stored procedure
I'm trying to define a (I thought) trivial stored procedure (SP) as
a front-end to the standard SP "dbms_system.set_ev",
which I want to use to generate a 10046 tracing event. I'm trying
to execute the following PL/SQL:
create or replace procedure rob_enable_tracing (
sid in integer,
serial in integer
dbms_system.set_ev(sid, serial, 10046, 4, '');
and getting the error:
PLS-00201: identifier 'DBMS_SYSTEM.SET_EV' must be declared
If I provide the schema in which "set_ev" is defined, in this ...Calling a stored procedure from another stored procedure...
I'm quite new to PostgreSQL, started off with version 8.0 some time ago.
Recently I've begun working with stored procedures and now I've come
accross a little problem / issue I can't seem to figure out.
A quick overview of the situation:
I have one stored procedure with returns a SET OF a new (record) type
function calculate_cost(...): returns set of mytype;
This stored procedure works without a problem. I can just do a &quo...Getting Data from a storeed procedure in a stored procedure
What I am looking to do is use a complicated stored procedure to get
data for me while in another stored procedure.
Its like a view, but a view you can't pass parameters to.
In essence I would like a sproc that would be like this
Create Procedure NewSproc
Select * from MAIN_SPROC 'a','b',.....
Or Delcare Table @TEMP
@Temp = MAIN_SPROC 'a','b',.....
Any ideas how I could return rows of data from a sproc into another
sproc and then run a WHERE clause on that data?
Instead of using the complicated stored proced...Executing Oracle stored procedure from a Java stored procedure
Here is my pb :
Executing an Oracle stored procedure from a Java method included within a
Java class stored on the Oracle database causes an error ORA-03113
"End of file on communication canal".
When executing the Java method straight from a command line everything is
ok. If executed from the Oracle server, I get this message.
I need your help !
Alain ROUILLON wrote:
> Here is my pb :
> Executing an Oracle stored procedure from a Java method included within a
> Java class stored on the Oracle database causes an e...Procedure in Stored procedure??
Question from a guy who knows PL/SQL, but is starting with
Transac-SQL on SQL Server....
Is it possible to have a sub procedure in a Stored Procedure, like
we do in a PL/sql package ? Or better yet, have a kind of general
Stored Procedure that would contain procedures called by more than one
I can't find anything like this in the online books....
Thanks for your inputs....
SQL Server does not have packages and limited scope for shared variables.
ht...Oracle Stored Procedures VERSUS SQL Server Stored Procedures
I want to know the differences between SQL Server 2000 stored
procedures and oracle stored procedures? Do they have different
syntax? The concept should be the same that the stored procedures
execute in the database server with better performance?
Please advise good references for Oracle stored procedures also.
<firstname.lastname@example.org> wrote in message
> I want to know the differences between SQL Server 2000 stored
> procedures and oracle stored procedures? Do they have different
> syntax? The concept shou...Stored Procedure to Insert OR Update
I have a simple stored procedure that takes couple of values and
inserts a new record using them into a table.
However, if the primary key, is duplicated it throws an error and
doesn't insert anything. Good so far. However is it possible to change
that SP so that if the record being inserted has a primary key that
already exists in table, it treats is as an UPDATE and now searches
for the primary key and updates the record with the new entries.
Also is it possible to just UPDATE columns in the record that are
different from the ones already in the table?
Thanks for any help or a...Insert Query with stored procedure
is it possible to create an "INSERT INTO ..... "Select from stored
I want to create an temporary table. In this table I want to enter the data,
which I can get from an stored procedure.
But in the FROM-clause a stored procedure is not allowed?
"Harald" <email@example.com> wrote in message news:firstname.lastname@example.org...
> is it possible to create an "INSERT INTO ..... "Select from stored
> procedure" Query?
> I want to create an temporary table. In this table I want to enter the data,
> wh...Stored procedure Bulk replace
I have the need to replace all occurrences of a string in all stored
procedures of one user.
is it possible to do it ?
in practice we have an Oracle based application which uses Oracle
cartidge to produce html
Since Oracle cartridge will be dismissed we have to change all the
cartridge Api with some other instructions.
In this example :
CREATE OR REPLACE PACKAGE BODY dummy AS
htp.p('<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//
htp.p('<html><head><title>WIND R.D.C.P. MENU</title></head...Using stored procedure result set in another stored procedure
I've been developing a stored procedure that uses a user defined
function in the query portion of the procedure. However, since the end
product needs to allow for dynamic table names, the UDF will not work.
I've been trying to get this to work with converting the UDF to a
procedure, but I'm having no luck.
Here is the background on what I'm trying to accomplish. I need to
perform a sub-identity on a table, I have the normal identity set, but
there are multiple duplicates in the table and I need each set of
duplicates numbered also (1,2,3,4 for duplicate set 1, 1,2,3 for dup
2)...LARGE INSERT in STORED PROCEDURE
I am trying to insert a large number of rows into a table
inside a SPL. But every time, I run the SPL, the table is
locked because of the INSERT. When I tried to issue a COMMIT,
right after the INSERT in the SPL. I got an error stating that
there was no open cursor.
I have done some research on here and found that it's not
possible to issue a COMMIT inside an SPL. But it can be done
so on the client side. Can anyone shade me some light on this ?
I'm quite new here and not sure how to do that. What are other
options to get around this if I have to commit after each
...DB2 COBOL stored procedure to UDB SQL stored procedure
I have been given the task of taking a 3,200 line COBOL stored
procedure and duplicating the same functionality in UDB 7.2 on the
Windows platform with a procedural SQL stored procedure. I have
fiddled with procedural SQL stored procs on UDB, but mostly
short,trivial ones. Looking for good advice, links, etc. on the best
approach to this. There doesn't seem to be the concept of subroutines
within a stored proc, so I am guessing one main stored proc that may
be calling multiple smaller stored procs? Just found out about this
need late this afternoon and wondering what the best a...How to return a Pk value from one stored procedure to another stored procedure
I have one stored procedure like sp_insertEmployee
Employee Table Fileds
Eno int pk,
In stored Procedure After inserting the ename and designation it has
to return the Eno pk Id automatically
I have another Department
deptno int pk,
Eno int fk,
In this stored procedure I need to execute the sp_insertEmployee
Stored procedure and we need that Pk return value after executing
By using that Id in this Department table we will insert the eno
can u help me out on this issue
CREATE PROCEDURE sp_i...how to call a stored procedure in an insert command
i had a small doubt , i have a table xxx with two columns (a int,b int
) and i have inserted 5 rows my query is to add the two colums using a
stored procedure and the result has to be displayed in an separate
column --this has to be done only stored procedures ---i know how to
solve the problem using computed columns concept
create table yyy(a int ,b int ,total as a+b)
insert into yyy values (12,13)
select * from yyy
i need the answer using stored procedures---is it possible
pls help me
create proc get_your_calc
select a, b, total = a + b
...INSERT or UPDATE
I've got sql statement:
set PRLT = (select detmin from lib/t2
where kod = prdc)
prdc in (select kod from lib/t2)
and UNIT = 'SZT'
-- and second one
insert into lib/t1 (
select kod, 'PLN' , 'SZT' , detmin from (
select * from lib/t2
left outer join lib/t1
on prdc = kod ) z
where prdc is NULL )
as You can imagine my point is insert not existing records (into t1) from t2
and update existing.
SQL solution is ok, but looking for more elegan way I've found
RPG useful (basically CHAIN opcode and WRITE if NOT %FOUND).
No...insertion using stored procedure in oracle.
I need to write to a stored procedure in oracle 9i to insert values in
a table in which:
1) Values in one column are to be retrived from a column in another
2)Getting three parameters as inputs in the procedure,and inserting
those values in the same row which has the value that has been taken
from the different table.
am very new to sp and am really confused when do we use wat..like
so plzz provide some sample code to handle this insertion.
This sounds like a homework assignment and homework should be done by
the person it is assinged to.
You c...Execute Stored procedure scripts in bulk
I have a folder containing about 150 stored procedures. Is there a way
- through batch or command file to execute all of them at
once..execute all files ending in .sql extension (inside a folder).
Its not possible to list each SP name in a command file or execute
each SP in management studio.
> I have a folder containing about 150 stored procedures. Is there a way
> - through batch or command file to execute all of them at
> once..execute all files ending in .sql extension (inside a folder).
A lowly Windows FOR command can help you here. The FOR comman...Stored Procedure
Against my better judgement I have been thrust into the duty of
creating Crystal reports for my company. I have discovered rather
quickly that I also need to become familar with SQL (creating views
and stored procedures).
I have a stored procedure were I created a workfile. Several thousand
records get inserted in this work file. Is it normally quicker to
join all the tables I need for the insert or is it faster to get all
the base records inserted into the workfile then do one or more
updates to the workfile using the other tables that I didn't join.
...SQL Stored Procedures X Java Stored Procedures in DB2 8.2
We are starting a large data warehousing project using DB2 8.2 on AIX.
There is a direction to move any new internal development to Java and a
question was raised: Would it be a good idea to have all stored
procedures that we might need (ETL, additional transformers, etc) for
this project and any other project written in Java as opposed to SQL/P?
Does anyone have any number or experience in terms of performance
Rafael Faria wrote:
> Hi All,
> We are starting a large data warehousing project using DB2 8.2 on AIX.
>...Inserting Data using a Stored Procedure
In Microsoft SQL I can run the following command:
insert into table execute proc1
to insert data into a table.
Is there a comparable way to do this in DB2?
I am not sure if we can directly call the procedure and insert its
result set into a table in a single statement.
Another way out is to to write a stored procedure for doing the same
..You can associate a cursor with stored procedure result set and hence
fetch the data from the stored procedure result which can be inserted
into the table . You can use ASSOCIATE RESULT SET LOCATORS and
ASSOCIATE CURSOR for the same .
Re...Calling Stored Procedure remotely from other stored procedure (DB2 9.7 LUW)
I'm planning an asynchronous application driven data replication.
Therefor I want to call a stored procedure in database "x" on machine
"b" from a stored procedure in database "y" on machine "a".
Any ideas out there how to do it ?
I found nothing appropriate in the manuals or examples.
On Tuesday, September 4, 2012 8:37:33 PM UTC+2, Frank Mickler wrote:
> Hello db2-experts,
> I'm planning an asynchronous application driven data replication.
> Therefor I want ...Calling an Insert Stored Procedure in ASP
Ok, folks. Here's the deal. Basically, I have a Stored Procedure in
Oracle that inserts data into a table. Simple enough. I want to call
this sp from ASP, and here is the code:
InsertQuery = "'" & serial_num & "','" & payment_type & "', " & "'" &
contract_num & "', '" & delivery_task_order & "', '" & invoice_num &
"', '" & invoice_date & "', '" & invoice_entered & "', " & ...Calling a stored procedure from an insert statement
Can someone provide guidance on how I can call a stored procedure from
a sql statement. We have a procedure which generates sequence numbers.
I want to call this procedure to generate a value for an insert
Any help is greatly appreciated.
On 25 Mar 2006 19:56:40 -0800, "bmaxed" <email@example.com> wrote:
>Can someone provide guidance on how I can call a stored procedure from
>a sql statement. We have a procedure which generates sequence numbers.
> I want to call this procedure to generate a value for an insert