Re: Dynamic SQL

In our last gripping episode NeedaHoliday wrote:
> On Fri, 12 Jan 2001 19:41:08 GMT, David Fitzjarrell <oratune_at_aol.com>
> wrote:
>
> >In our last gripping episode NeedaHoliday wrote:
> >> Can someoneplease tell how to create aprocedure that will accept
 input
> >> as follows;
> >>
> >> Table_Name
> >> IN_Column_nm
> >> IN_Input_data
> >>
> >> Create dynamic SQL to create the following and execute
> >>
> >> --Prepare cursor to select rowids of the records to be deleted.
> >> v_Insert_Stmt :=
> >> 'INSERT INTO '||IN_Tname||' ('|| IN_Field_Parms || ')'
> >> ||' VALUES ( || :Input_Parms || )';
> >>
> >> v_Sel_Id := DBMS_SQL.OPEN_CURSOR;
> >> DBMS_SQL.PARSE(v_Sel_Id,v_Insert_Stmt,dbms_sql.v7);
> >> DBMS_SQL.BIND_VARIABLE(v_Sel_Id, ':Input_Parms', IN_Input_Parms);
> >> v_Exec_Sel := DBMS_SQL.EXECUTE(v_Sel_Id);
> >> DBMS_SQL.CLOSE_CURSOR(v_Sel_Id);
> >> END;
> >>
> >> I want to be able to send any table with its columns and input data
> >> and create the SQL.
> >>
> >> I keep getting errors.
> >>
> >> Thanks in advance
> >>
> >> Jim
> >>
> >> james.stewart_at_nospamPWGSC.GC.CA
> >>
> >
> >
> >It would help greatly to know what errors you are generating.
>
> It doesn't like the DBMS_SQL.PARSE command. Keep telling me
>
> Debugger OCI Error:
> ORA-00936: missing expression
>

Use this instead:

v_Insert_Stmt :=

       'INSERT INTO '||IN_Tname||' ('|| IN_Field_Parms || ')'
                                ||' VALUES ( '|| :Input_Parms ||')';

You've missed two single quotes making the statement one that will not parse.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com
http://www.deja.com/