f



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).
0
red_valsen
9/24/2003 6:47:09 PM
comp.databases.informix 16083 articles. 0 followers. Post Follow

3 Replies
985 Views

Similar Articles

[PageSpeed] 48

On 24 Sep 2003 11:47:09 -0700, red_valsen@yahoo.com (Red Valsen)
wrote:

>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).

Write it out to flatfile and execute the create from command line
using dbaccess???
0
John
9/24/2003 7:03:25 PM
John Carlson wrote:

> On 24 Sep 2003 11:47:09 -0700, red_valsen@yahoo.com (Red Valsen)
> wrote:
> 
> 
>>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).
> 
> 
> Write it out to flatfile and execute the create from command line
> using dbaccess???

As John said, somewhat obliquely, there isn't a way to do it directly.
Why do you think you want to do it?

-- 
Jonathan Leffler                   #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

0
Jonathan
9/25/2003 4:25:28 AM
I brilliantly, ingeniously and redundantly re-invented the wheel by
creating a storedprocedure which returns a string containing either
the foreign key columns or the primary key columns of a table. 
However, it needs to call another stored procedure which will return
the colname based on index part.  If this latter SP isn't available, I
want the PK/FK SP to be able to create it.

Be happy to play open kimono and show you if you'd like.

Using IDS 7.31-UC4 on Solaris 2.7/Intel (don't ask).

Jonathan Leffler <jleffler@earthlink.net> wrote in message news:<Y6ucb.2672$NX3.1594@newsread3.news.pas.earthlink.net>...
> John Carlson wrote:
> 
> > On 24 Sep 2003 11:47:09 -0700, red_valsen@yahoo.com (Red Valsen)
> > wrote:
> > 
> > 
> >>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).
> > 
> > 
> > Write it out to flatfile and execute the create from command line
> > using dbaccess???
> 
> As John said, somewhat obliquely, there isn't a way to do it directly.
> Why do you think you want to do it?
0
red_valsen
10/2/2003 2:22:43 PM
Reply:

Similar Artilces:

Stored procedure in database X, executes stored procedure in database Y, wrapped in transaction?
Is it possible to execute a stored procedure in one database, which then itself executes a stored procedure from another database? We have decide to split our data into a tree structure (DB1) and data blobs (DB2) (we are using MSDE and we have a 2gb limit with each DB so we've done it this way for that reason). I would like to, say, execute a stored procedure in DB1, passing in the data blob and other details, DB1 will create a tree node in DB1 and then add the blob record to DB2. DB1 will wrap in a transaction of course, as will DB2 when it adds the blob. Is this possible? [poste...

calling a stored procedure from a stored procedure
Hi Folks, 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 ) is begin dbms_system.set_ev(sid, serial, 10046, 4, ''); end; 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 case "SYS", as in: create or replace procedure rob_enable_tracing ( sid in integer, serial in integer ) is begin sys.dbms_system.set_ev(sid, serial, 10046, 4, ''); end; I get: PLS-00201: identifier 'SYS.DBMS_SYSTEM' must be declared Any suggestions? thanks, RU On May 10, 1:25 pm, RU <r...@vakuum.de> wrote: > Hi Folks, > > 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 > ) > is > begin > dbms_system.set_ev(sid, serial, 10046,...

Calling a stored procedure from another stored procedure...
Goodmorning, 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 I've declared. For example: mytype index INTEGER; description VARCHAR; cost FLOAT; 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 AS Select * from MAIN_SPROC 'a','b',..... WHERE ......... 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? Thanks Chris Auer Instead of using the complicated stored proced...

Executing Oracle stored procedure from a Java stored procedure
Hi, 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 Alain ROUILLON wrote: > Hi, > > 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??
Hi all, 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 Stored Proc?? I can't find anything like this in the online books.... Thanks for your inputs.... Philip Hi SQL Server does not have packages and limited scope for shared variables. http://vyaskn.tripod.com/oracle_sql_server_differences_equivalents.htm http://tinyurl.com/n7v6 ht...

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)...

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. thanks!! <jrefactors@hotmail.com> wrote in message news:1120793932.351921.5760@g43g2000cwa.googlegroups.com... > I want to know the differences between SQL Server 2000 stored > procedures and oracle stored procedures? Do they have different > syntax? The concept shou...

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
Dear All, I have one stored procedure like sp_insertEmployee Employee Table Fileds Eno int pk, ename varchar(100), designation varchar 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, Depname varchar In this stored procedure I need to execute the sp_insertEmployee Stored procedure and we need that Pk return value after executing that By using that Id in this Department table we will insert the eno can u help me out on this issue Thanks CREATE PROCEDURE sp_i...

Calling Stored Procedure remotely from other stored procedure (DB2 9.7 LUW)
Hello db2-experts, 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. TIA Frank 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 ...

SQL Stored Procedures X Java Stored Procedures in DB2 8.2
Hi All, 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 differences? Regards, Rafael Faria Rafael Faria wrote: > Hi All, > > We are starting a large data warehousing project using DB2 8.2 on AIX. >...

Storing all stored procedures in one database to minimize different connection strings
In order to minimize the number of connection strings I have to use to access different databases on the same Sql Server, I was considering storing all stored procedures in just one database. I want to do this because connection pooling in my application - ASP.NET is based on this connection string. So if I need to access 6 different databases on one sql server & set 6 different connection strings, I end up creating 6 different connection pools. Other than it might create more management work for the DBA, are there any performance implications with implementing this scheme? Do stored p...

Where are Views, stored procedures, stored?
Where are sored procedures, views etc, stored? Are they objets for the OS/400 as Logical files or Phisical files? how to save and restore them? Thanks in advance. On Aug 16, 5:21 am, "CENTRINO" <ning...@nigunelandia.com> wrote: > Where are sored procedures, views etc, stored? Are they objets for the > OS/400 as Logical files or Phisical files? how to save and restore them? > > Thanks in advance. Tables are Physical Files Views are Logical Files Stored procedures are Program Objects All are stored in the library of choice, and get backed up when the...

Informix Stored Procedure
Hello! I am using Informix 7 se and having problems with updating the stored procedure everytime I run it. Basically, I have made changes to the sql file that drops and create a store procedure, but it still shows outputs from the old copy of the same stored procedure. The only way I am able to see changes in the results is by creating a stored procedure with a different name. I am using DBACCESS to create and execute my stored procedure. Pls help. Thanks, Ahmer sending to informix-list On Fri, 12 Sep 2003 16:13:52 +0800, "Ahmer Sajjad" <ahmer@conceptfert.com.au> wrote: > >Hello! > >I am using Informix 7 se and having problems with updating the stored >procedure everytime I run it. > >Basically, I have made changes to the sql file that drops and create a store >procedure, but it still shows outputs from the old copy of the same stored >procedure. The only way I am able to see changes in the results is by >creating a stored procedure with a different name. I am using DBACCESS to >create and execute my stored procedure. I think that you have to close database after makeing changes to SP and open DB again. nebojsa ------------------------------------ Remove spam block (DELETE_) to reply ...

Not stored "stored procedure".
Dear Experts, I have to test a stored procedure without inserting it's codes into a database. How can I do this trick in Oracle? Regards, Serguei. On 17 Jan 2005 09:34:44 -0800, "Serguei" <gumenyuk@canada.com> wrote: >Dear Experts, >I have to test a stored procedure without inserting it's codes into a >database. How can I do this trick in Oracle? > Regards, > Serguei. convert it into an anonymous begin end block and run it through sql*plus. Parameters can be replaced by VARIABLES but of course they would need to be prefixed by a colon in the ...

Create Store Procedures from RPGLE Procedures
I am trying to create some store procedures that I can call from .NET that will use an RPGLE proccedure that I have written. The RPGLE program will actually contain multiple procedures as well. I can't seem to figure out how to call just the one procedure I need. I have tried creating the store procedures already but I don't seem to be getting anywhere. Is this possible or do I have to create an RPG program to call my procedures? Also, does the program type for the external program have to be label as SQLRPGLE? Thanks ----== Posted via Newsgroups.com - Usenet Access to over ...

Not stored "stored procedure".
Dear Experts, I have a stored procedure code, which I can not insert into my Oracle database for some time. But I like to debug and test this code now. Is it possible to create and execute a temporary Oracle stored procedure, which will exist in the database during my session only (like a temporary table)? Regards, Serguei. PS. Please, post an answer to a news group or use Serguei.Goumeniouk@cibc.ca Email address. ...

Trigger: To fill another Database with using Stored Procedures of the other Database
Hello everyone, I face currently a problem where I could need some input for searching the source of the Problem System: SQL Server 9.0 I fill from Database A with triggers Database B, everything works fine. On Database B there is a Stored Procedures that checks the records and add additional information accordingly, this Stored Procedures is normally called by the application on "update and insert" in the according table. When I try to call this Stored Procedures from the Database A, the trigger does not work anymore, even if I do a try catch over the whole trigger, he never rea...

Script to store stored procedures in seperate files
How does MicroSoft store the stored procedures in seperate files. What tools are used. Cause that's what I like to do too. Arno de Jong, The Netherlands. (SCPTFXR does not have the option to store the stored procedures in different files I think) Enterprise Manager will do this for you: Right-click on the database, click "Generate SQL script", select the option "Create one file per object". You can do this programmatically using the Script method of the DMO StoredProcedure object. Call .Script once for each SP with an appropriate file name. http://msdn.microsoft....

Storing stored procedure output in a temp table.
Hi, I know this is probably an easy question that has been answered before, but after crawling through google groups for two days now I find I am still stuck. My end aim is to use an external program (using CT-lib) to manipulate results from stored procedures (such as sp_who, sp_spaceused etc). It seems to do this one of the ways I have seen in SQL is like this: -- SET NOCOUNT ON CREATE TABLE #TMPWHO ( FID INT, SPID INT, STATUS CHAR(100), LOGINAME CHAR(100), ORIGNAME CHAR(100), HOSTNAME CHAR(100), BLK INT, DBNAME CHAR(100), CMD CHAR(100), BLKXL INT) INSERT INTO #TMPWHO EXEC sp_who SELECT * FROM #TMPWHO DROP TABLE #TMPWHO go -- I am using ASE 12.5.1 on Windows XP Pro. When I put the above into isql I get: -- Msg 156, Level 15, State 2: Line 14: Incorrect syntax near the keyword 'exec'. -- I am obviously doing something wrong, but I cannot seem to fix it. Can someone please let me know what I am doing wrong, or if there is a better way of achieving what I want, don't be shy :) Regards Luke PS: I am relatively new to Sybase ASE and not an experienced DBA. On Fri, 20 Aug 2004 11:36:29 +0800, Luke wrote: > Hi, > I know this is probably an easy question that has been answered before, > but after crawling through google groups for two days now I find I am > still stuck. > > My end aim is to use an external program (using CT-lib) to manipulate > results from st...

Is there a way to check which procedure, stored procedure is not compiled.
Hello, I would like to know if it is possible to write a script or function which check all procedures, functions, stored procedures and display which one are not compiled. thanks in advance for your help. Rangdalf select * from user_objects where object_type in ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE','TRIGGER','VIEW') and status='INVALID' "rangdalf" <rangdalf@hotmail.com> wrote in message news:fd099c36.0411040131.788c1f85@posting.google.com... > Hello, > > I would like to know if it is possible to write a script or function > which check all procedures, functions, stored procedures and display > which one are not compiled. > > thanks in advance for your help. > > Rangdalf "rangdalf" <rangdalf@hotmail.com> a �crit dans le message de news:fd099c36.0411040131.788c1f85@posting.google.com... > Hello, > > I would like to know if it is possible to write a script or function > which check all procedures, functions, stored procedures and display > which one are not compiled. > > thanks in advance for your help. > > Rangdalf There is no need to know that as Oracle dynamically recompiles invalid objects. It's more important to know which ones contain errors with: select distinct owner, name, type from dba_errors; -- Regards Michel Cadot Hello, I know that my package...

Stored procedure calling another stored procedure_
Hi all, I have a stored procedure that return a resultset e.g. stored proc: get_employee_details select emp_id, emp_name, emp_salary, emp_position from empoloyee I would like to write another stored procedure that executes the above stored procedure - returning the same number of records but it will only show 2 columns e.g. new stored proc: get_employee_pay -- executes get_employee_details I only need to know emp_id, emp_salary. How can this be done in sql stored procedure? Thanks, June Moore. June, I would make the first stored proc a UDF (user defined function) that ...

Why is it called Stored Procedure instead of Stored Sets?
Since RDMBS and its language SQL is set-based would it make more sense to call a given stored process "Stored Sets" instead of current theorically misleading Stored Procedure, as a measure to prod programmers to think along the line of sets instead of procedure? You are not storing a set; you are storing a procedure which hopefully works on a set. A stored procedure is a chunk of logic, not of data. I vote that the name is right. Not that your or mine opinion matters on this. MS and other vendors made their naming decision years ago and I cannot see any of the major RDBMS vendor...

Web resources about - Stored procedure from stored procedure - comp.databases.informix

Invasiveness of surgical procedures - Wikipedia, the free encyclopedia
There are three main categories which describe the invasiveness of surgical procedures . These are: non-invasive procedures , minimally invasive ...

Eye tattoos: Tattooist Luna Cobra defends practice following calls to ban procedure
IT SOUNDS like the really gross scene in a horror movie.

Rushed cosmetic procedures a 'recipe for disaster'
&#8203;When Chanelle O'Hare went searching online for a deal on potential cosmetic procedures, she could not have imagined that what she ended ...

Survivors of female genital mutilation outraged by 'compromise' which could legalise procedure
Survivors of female genital mutilation are fighting back against a so-called compromise, which could let a modified form of the procedure continue. ...

Doctors Perform Medical Procedure On Wrong Newborn
The procedure was meant for another child.

Old Lady Lawyer: Uncivil Procedure
What is some of the worst behavior you've witnesses by attorneys?

Butt procedures, male breast reduction growing slices of plastic surgery
Every 30 min of 2015, a rump was remodeled. Men had 40% of all breast reductions.

NFL Announces Changes to Officiating Procedures for Playoffs - Bleacher Report
The NFL formally approved changes to its postseason officiating procedures to allow referees the opportunity to consult Vice President of Officiating ...

"Gynecologists Kavita Shah Arora and Allan Jacobs said procedures that slightly changed the look of a ...
"... were comparable to male circumcision or cosmetic procedures in Western countries like labiaplasty. Laws against mild modifications were ...

We need to reform the culture of law enforcement, not just the procedures
We need to reform the culture of law enforcement, not just the procedures by digby I have a new piece up at Salon this morning about police ...

Resources last updated: 3/2/2016 10:32:04 PM