Search the Community
Showing results for tags 'plsql'.
Found 3 results
Hi, please find the sample data below ; Requirement is to build the insert statement dynamically through the look up table(test_lkp) so that if any change in the columns can be handled through the look up table rather than modifying the script. To achieve this i have created the look up table TEST_LKP with the same structure as TEST and inserting values as V_COL. i am getting an error for variable "v_col" Is there any way to achieve this DROP TABLE TEST ; DROP TABLE TEST_LKP; DROP TABLE TEST_REF; DROP TABLE TEST_Q; CREATE TABLE TEST (COL VARCHAR2(10)); CREATE TABLE TEST_LKP (COL VARCHAR2(10)); CREATE TABLE TEST_REF (VAL VARCHAR2(10)); CREATE TABLE TEST_Q (ENAME VARCHAR2(10)); INSERT INTO TEST_LKP VALUES ('v_col'); INSERT INTO TEST_REF VALUES ('ENAME'); INSERT INTO TEST_Q VALUES ('TOM'); code : SET SERVEROUTPUT ON; declare v_col VARCHAR2(30); q VARCHAR2(1000); CURSOR c1 IS SELECT 'INSERT INTO TEST ' || 'SELECT ' || COL || ' FROM TEST_Q ' DMLS FROM TEST_LKP; BEGIN SELECT val INTO v_col FROM TEST_REF; dbms_output.put_line(v_col); FOR i IN c1 loop dbms_output.put_line(i.DMLS); execute immediate i.dmls; end loop; END; / erorr : Error report - ORA-00904: "V_COL": invalid identifier ORA-06512: at line 17 00904. 00000 - "%s: invalid identifier" *Cause: *Action: ENAME INSERT INTO TEST SELECT v_col FROM TEST_Q Expected : i want v_col to be replaced with ENAME as INSERT INTO TEST SELECT ENAME FROM TEST_Q what wrong i am doing here?
Hi, I have a program working with xml files that is not performing anymore since we installed Oracle 12 databases. The error we suddenly have is this one : ORA-31011: XML parsing failed ORA-06512: at "MYUSER.MYPACKAGE", line 1663 ORA-19213: error occurred in XML processing at lines 2 LPX-00209: PI names starting with XML are reserved ORA-06512: at "SYS.XMLTYPE", line 272 ORA-06512: at "MYUSER.MYPACKAGE", line 1162 ORA-06512: at line 1 It seems there is a problem on line 2 of the xml. Indeed this file is particular. There is an xml declaration on each line and lines are pretty long, defining several xml blocks : <?xml version="1.0" encoding="utf-8"?><Flag:...... <?xml version="1.0" encoding="utf-8"?><Message:...... <?xml version="1.0" encoding="utf-8"?><Message:...... ..... <?xml version="1.0" encoding="utf-8"?><Message:...... <?xml version="1.0" encoding="utf-8"?><EndFile:...... In the program the first thing done is to create a blob file from this xml file (this is a function called at the beginning : FUNCTION TransformToCLOB(filename varchar2) RETURN CLOB) src_clob:=BFILENAME('MY_REPERTOIRE', filename); dbms_lob.createtemporary(dest_clob, cache=>TRUE); DBMS_LOB.FILEOPEN(src_clob); DBMS_LOB.LoadFromFile( dest_clob , src_clob ,DBMS_LOB.GETLENGTH(src_clob) ); dbms_lob.close(src_clob); RETURN dest_clob; after that, there is this instruction (ligne 1162 where the error occurs): v_xml:=XMLTYPE(dest_clob); What has changed in Oracle 12 that causes the problem? I have the feeling that several declarations in one file are not welcome anymore.
oracbeg posted a topic in Oracle ForumHello Everyone. Request you to please suggest some way on this. I have a csv file which i am uploading to a table using UTL_FILE. This activity runs on daily basis where file name remains same but data in there changes (insertion or update). I need to record that particular row with required columns which was changed in the csv file. I created a procedure and used below as condition which works for upload part. ## merge into test using dual on (Id = v_Id) when not matched then insert (Id, name, address) values (v_Id, v_name, v_address) when matched then update set name = v_name, address = v_address; For recording that update i used a trigger on this table (test) ## create or replace TRIGGER test_trg after update on test for EACH ROW begin If Updating Then insert into test_log (name, address ) values (:new.name, :new.address ); The trouble i am facing is that instead of recording that single record which is changed in table test using utl_file upload it records whole table values of required columns. Trigger works fine if i manually edit or update the test table but in case of UTL_FILE upload procedure trigger records whole table data of required columns.