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

 
 CREATE OR REPLACE PROCEDURE procc
    (p_item IN number,p_rows_del OUT varchar2)
  IS
    cursor_name   INTEGER;
    a varchar2(200);
  BEGIN
    a:='CREATE TABLE Temp_BlokID_Values AS (SELECT ename,empno FROM emp WHERE deptno=)'|| p_item
    cursor_name := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(cursor_name, a,
                DBMS_SQL.NATIVE );
    p_rows_del := DBMS_SQL.EXECUTE (cursor_name);
    DBMS_SQL.CLOSE_CURSOR(cursor_name);
 END;
 /
 

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.....USING clause to CREATE a table and post back if it does not work.