Home » SQL & PL/SQL » SQL & PL/SQL » Dynamically Inserting Clob into tables in SP
Dynamically Inserting Clob into tables in SP [message #184186] Tue, 25 July 2006 12:10 Go to next message
harishgop
Messages: 2
Registered: July 2006
Junior Member
Hi,
I'm quite new to PL/SQL. I need you guys help.
I want to write a stored procedure which takes the table name as argument along with a CLOB object to insert record to that particular table. But I'm not able to execute this particular logic. It says no such table name when I give something like &tablename, it takes that itself as the table name. I'm not sure how to substitute the variable name for the table name. since all our tables columns have similar naming conventions, i wanted to write a common stored procedure which will take the table name as an argument along with other column values. can some one help me.
here is the SP that I'm trying to execute which gives me problem

thanks,
harish

CREATE OR REPLACE PROCEDURE sp_ins_xmldoc_tab (
tablename VARCHAR2,
xmlobject CLOB
)
IS
temptablename VARCHAR2 (20) := '';
tempxmlobject CLOB;
query_str VARCHAR2 (32000) := '';
BEGIN
-- Move the incoming variables to temp objects
temptablename := tablename;
tempxmlobject := xmlobject;
-- Insert into the table with the CLOB object
INSERT INTO &temptablename (&temptablename _pk, xmldoc, active) VALUES (&temptablename _seq.NEXTVAL, XMLTYPE (tempxmlobject), 1 );

--Handle the exceptions
EXCEPTION
WHEN OTHERS
THEN
raise_application_error
(-20101,
'Exception occurred in SP_INS_XMLDOC_TAB procedure :'
|| SQLERRM
);
END sp_ins_xmldoc_tab;
/

Re: Dynamically Inserting Clob into tables in SP [message #184201 is a reply to message #184186] Tue, 25 July 2006 12:42 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
You need to use dynamic SQL when object names are not known until runtime.

create or replace procedure sp_ins_xmldoc_tab
  (
   p_table_name in user_tables.table_name%type,
   p_xml_object in clob
  )
is
begin
  execute immediate 'insert into ' || p_table_name ||
                    ' (' || p_table_name || '_pk, xmldoc, active) values (' ||
                    p_table_name || '_seq.nextval, xmltype(:xml), 1)' using p_xml_object;
exception
  when others then
    raise_application_error(
      -20101, 'Exception occurred in SP_INS_XMLDOC_TAB procedure :' || sqlerrm);
end sp_ins_xmldoc_tab;
Re: Dynamically Inserting Clob into tables in SP [message #184217 is a reply to message #184201] Tue, 25 July 2006 14:40 Go to previous message
harishgop
Messages: 2
Registered: July 2006
Junior Member
thanks Todd, and it works perfectly fine. I tried using execute immediate before, but trying to assign that to a varchar string, so always failing when i was trying to insert a clob. anyhow it fixed my problem with your inputs. thanks again
Previous Topic: rtrim problem
Next Topic: Sql error ORA-00904 invalid identifier
Goto Forum:
  


Current Time: Sun Dec 04 02:17:28 CST 2016

Total time taken to generate the page: 0.17090 seconds