Home » SQL & PL/SQL » SQL & PL/SQL » stored procedure inside a stored procedure dynamically (oracle )
stored procedure inside a stored procedure dynamically [message #378132] Mon, 29 December 2008 01:44 Go to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
I have table "table_name" which has 3 columns
step                NUMBER(3)                 NOT NULL,
procedure_name       VARCHAR2(80 BYTE)         NOT NULL,
TABLA_DETALLE       VARCHAR2(40 BYTE),


I want run a procedure inside another stored procedure, the input parameters for the inner proc. will come from cursor values fetched from table_name.

CREATE OR REPLACE PROCEDURE TEST1
IS
execString varchar 2(40);
CURSOR T_CURSOR
  IS
  SELECT * FROM table_name WHERE step =1 ;
  T_RECORD T_CURSOR%ROWTYPE;

begin
OPEN T_CURSOR;
FETCH T_CURSOR INTO T_RECORD;

execString := 'BEGIN ' || T_RECORD.PROCEDURE_NAME ||'( '|| T_RECORD.step|| ',' || T_RECORD.TABLA_DETALLE || '); END;';
 execute immediate (execString);

close t_cursor;
EXCEPTION
     /*If there is no data */
       WHEN NO_DATA_FOUND
       THEN
             raise_application_error (-20001,' NO DATA FOUND ');

     /* If too much data*/
       WHEN TOO_MANY_ROWS
       THEN
             raise_application_error (-20002,' THERE ARE TOO MANY ROWS ');

    /* If internal error in program*/

END;
/


Also
SELECT * FROM NPPI_PURGADO_P WHERE PASO = 1
gives step = 1 , procedure_name = test_proc , TABLA_DETALLE = test_table.

But there is some problem in execString, what changes do i need to make.
Re: stored procedure inside a stored procedure dynamically [message #378134 is a reply to message #378132] Mon, 29 December 2008 01:52 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
What is wrong with it? Do you get an error? If so, please post it as well.

Edit: You may also need to change "varchar 2" to "varchar2"!
Re: stored procedure inside a stored procedure dynamically [message #378136 is a reply to message #378132] Mon, 29 December 2008 02:10 Go to previous messageGo to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
This is the error message.

ORA-06550: line 1, column 97:
PLS-00103: We have found the symbol ")" when expecting one of the following:

    (- + Case mod new not null others <an identifier>
    <a double-quoted <a delimited-identifier> bind variable> avg
    count exists current max min prior sql stddev sum variance
    execute forall merge time timestamp interval date
    <a string literal character set with specification>
    <a number> <a single-quoted SQL string> jhonihster
The symbol "null" has been replaced by ")" to continue
Re: stored procedure inside a stored procedure dynamically [message #378138 is a reply to message #378136] Mon, 29 December 2008 02:13 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Did you change the "varchar 2" to "varchar2"?
Re: stored procedure inside a stored procedure dynamically [message #378148 is a reply to message #378132] Mon, 29 December 2008 02:49 Go to previous messageGo to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
Yeah ..

execstring varchar2(200);
Re: stored procedure inside a stored procedure dynamically [message #378150 is a reply to message #378148] Mon, 29 December 2008 03:01 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Well, your line 1 doesn't have a column 97. So, the error you've posted is probably for something else.
Re: stored procedure inside a stored procedure dynamically [message #378154 is a reply to message #378132] Mon, 29 December 2008 03:06 Go to previous messageGo to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
But this is the error which i am getting while running my code through TOAD.
Re: stored procedure inside a stored procedure dynamically [message #378160 is a reply to message #378154] Mon, 29 December 2008 03:29 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
And if you run it in SQL*Plus?
Re: stored procedure inside a stored procedure dynamically [message #378165 is a reply to message #378132] Mon, 29 December 2008 03:35 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Maybe it is time to start learn to debug the code.

It is not clear what is the code you are running.
It may be the code you posted (CREATE PROCEDURE statement), but as the error does not correspond with its code and you comply about "problem in execString", I would say that you get this error when calling TEST1.
If so, there may be problem in the called code (you did not post; maybe TOAD "somehow" manages it, but you shall learn to use sqlplus for this purposes), or there is really a problem in EXECSTRING. You may easily find it out printing the dynamic SQL before its execution.
At the first sight, strings shall be always enclosed in single quotes, otherwise they are treated as identifiers (although this is probably not cause of the error you got).
Re: stored procedure inside a stored procedure dynamically [message #378187 is a reply to message #378132] Mon, 29 December 2008 05:06 Go to previous messageGo to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
Actually there was a problem while fecthing the values from the table and now i am getting this error.

	
ORA-06550: line 1, column 39:
PLS-00357: Reference to the table, view or sequence 'NPPI_HIST_PROMO_APLIC_MAR' is not allowed in this context
ORA-06550: line 1, column 7:
PL / SQL: Statement ignored
ORA-06512: at "NPPI_PROC.TEST1", line 29
ORA-06512: at line 2
Re: stored procedure inside a stored procedure dynamically [message #378191 is a reply to message #378187] Mon, 29 December 2008 05:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no NPPI_HIST_PROMO_APLIC_MAR in what you posted.

Quote:
PLS-00357: Table,View Or Sequence reference 'string' not allowed in this context
Cause: A reference to database table, view, or sequence was found in an inappropriate context. Such references can appear only in SQL statements or (excluding sequences) in %TYPE and %ROWTYPE declarations. Some valid examples follow: SELECT ename, emp.deptno, dname INTO my_ename, my_deptno, my_dept .FROM emp, dept WHERE emp.deptno = dept.deptno; DECLARE last_name emp.ename%TYPE; dept_rec dept%ROWTYPE;
Action: Remove or relocate the illegal reference.


Regards
Michel
Re: stored procedure inside a stored procedure dynamically [message #378194 is a reply to message #378132] Mon, 29 December 2008 05:28 Go to previous messageGo to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
Ohh Sorry for that , will reply back in an hour , will again do some research in my original code.Till than bye
Re: stored procedure inside a stored procedure dynamically [message #378195 is a reply to message #378132] Mon, 29 December 2008 05:31 Go to previous messageGo to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
Actually the error is .

ORA-06550: line 1, column 39:
PLS-00357: Reference to the table, view or sequence 'test_table' is not allowed in this context
ORA-06550: line 1, column 7:
PL / SQL: Statement ignored
ORA-06512: at "NPPI_PROC.TEST1", line 29
ORA-06512: at line 2


Michel i also googled for this error, but not able to find the way to get out of this error.
Re: stored procedure inside a stored procedure dynamically [message #378208 is a reply to message #378195] Mon, 29 December 2008 05:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Until you post:
- the actual code or a test case that I can reproduce to get same error
- what you exactly executed

I can't help.

Regards
Michel
Re: stored procedure inside a stored procedure dynamically [message #378226 is a reply to message #378132] Mon, 29 December 2008 06:25 Go to previous messageGo to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
Michel

Here is the error i am getting.

ORA-06550: line 1, column 39:
PLS-00357: Reference to the table, view or sequence 'test_table' is not allowed in this context
ORA-06550: line 1, column 7:
PL / SQL: Statement ignored
ORA-06512: at "NPPI_PROC.TEST1", line 29
ORA-06512: at line 2


actually tabla_detalle has values test_table,test_table1,test_table2 etc like that....
Re: stored procedure inside a stored procedure dynamically [message #378228 is a reply to message #378226] Mon, 29 December 2008 06:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Display the string you want to execute and you then see why it can't work.

Regards
Michel
Re: stored procedure inside a stored procedure dynamically [message #378235 is a reply to message #378132] Mon, 29 December 2008 06:58 Go to previous messageGo to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
Yeah now i am able to do that.

The reason was tabla_detalle is varchar and i was using it directly into the stored procedure as paramter while calling.

execString := 'BEGIN ' || T_RECORD.PROCEDURE_NAME ||'( '|| T_RECORD.step|| ',''' || T_RECORD.TABLA_DETALLE || '''); END;';


Re: stored procedure inside a stored procedure dynamically [message #378243 is a reply to message #378235] Mon, 29 December 2008 07:57 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
shaksing wrote on Mon, 29 December 2008 13:58
The reason was tabla_detalle is varchar and i was using it directly into the stored procedure as paramter while calling.

Did not I say it before?
Quote:
You may easily find it out printing the dynamic SQL before its execution.
At the first sight, strings shall be always enclosed in single quotes, otherwise they are treated as identifiers.

But, for your own good, do not hardcode parameter values, bind them instead:
execString := 'BEGIN ' || T_RECORD.PROCEDURE_NAME ||'( :1, :2 ); END;';
execute immediate execString using T_RECORD.step, T_RECORD.TABLA_DETALLE;

Is not it better? At least no problem with extra quotes.
Re: stored procedure inside a stored procedure dynamically [message #378247 is a reply to message #378132] Mon, 29 December 2008 08:07 Go to previous message
shaksing
Messages: 115
Registered: November 2008
Senior Member
Yeah Flyboy thats gud suggestion. and i will use that ...Thanks a lot
Previous Topic: Conversion of SQL to function
Next Topic: will this code work properly
Goto Forum:
  


Current Time: Fri Dec 02 18:34:35 CST 2016

Total time taken to generate the page: 0.22639 seconds