Stored procedure input...

[DreamersDelight]

Hi,
I know I'm missing something but I can't figure out what it is? Something trivial I'm shure.

create or replace PROCEDURE CREATE_BLOK_ID(transId IN NUMBER) AS


BEGIN
      
EXECUTE IMMEDIATE 'CREATE TABLE Temp_BlokID_Values AS (SELECT TRANSPORT_ID,MARK_1,PORT_ID FROM DATABASE.TABLE WHERE DATABASE.TABLE.TRANSPORT_ID = transId)';

         
END CREATE_BLOK_ID;

That's the procedure I have written and the code is used to call the procedure is the code underneath.

DECLARE
      
      
      
BEGIN 
      
      IFSAPP.CREATE_BLOK_ID('25196'); 
END;

The stored procedure compiles fine, but on executing it it gives an error.

"TRANSID": invalid identifier at "DATABASE.CREAT E_BLOK_ID", line 11 at line 6

I'm thinking that it has something to do with the input value of the procedure or the value type of the table I'm checking against with the WHERE clause.

Thanks in advance for the help.

[DreamersDelight]

Never mind people, I found the sollution myself. The only thing I needed was a dynamic cursor. The funniest part if it all was the fact that I found it on the scripts forum.

[debasisdas]

what is this DATABASE.TABLE. TRANSPORT_ID

[subashsavji]

I think, i am not sure

when we use script means within inverted commas that means both compilation and execution happen at a time. so we can not pass value like this.
try this
[code=oracle]

CREATE OR REPLACE PROCEDURE procc
(p_item IN number,p_rows_d el OUT varchar2)
IS
cursor_name INTEGER;
a varchar2(200);
BEGIN
a:='CREATE TABLE Temp_BlokID_Val ues AS (SELECT ename,empno FROM emp WHERE deptno=)'|| p_item
cursor_name := DBMS_SQL.OPEN_C URSOR;
DBMS_SQL.PARSE( cursor_name, a,
DBMS_SQL.NATIVE );
p_rows_del := DBMS_SQL.EXECUT E (cursor_name);
DBMS_SQL.CLOSE_ CURSOR(cursor_n ame);
END;
/
[/code]
this creating procedure successfully but while executing its showing problem. if
you able to solve. plq alap let me know

[amitpatel66]

Why dont you make use of EXECUTE IMMEDIATE rather than DBMS_SQL package.

Try EXECUTE IMMEDIATE.....U SING clause to CREATE a table and post back if it does not work.