Home » SQL & PL/SQL » SQL & PL/SQL » PLS-00457:"expressions have to be of SQL types"
PLS-00457:"expressions have to be of SQL types" [message #203028] Mon, 13 November 2006 10:41 Go to next message
mafc73
Messages: 38
Registered: November 2006
Member
I have Oracle 9iR2 and I get the error PLS-00457:"expressions have to be of SQL types" in the line that I have marked in red. How Can I use "execute immediate" with this insert command?. Is this posible?.

Thanks


CREATE OR REPLACE PROCEDURE PASO_HISTORICO IS

TYPE typ_hist_tablas IS TABLE OF hist_prueba%ROWTYPE;
v_hist_tablas typ_hist_tablas;

BEGIN

EXECUTE IMMEDIATE
'SELECT * FROM hist_prueba'
BULK COLLECT INTO v_hist_tablas;

BEGIN
FORALL i IN v_hist_tablas.FIRST..v_hist_tablas.LAST
SAVE EXCEPTIONS


EXECUTE IMMEDIATE 'INSERT INTO HIST_PRUEBA_BACKUP VALUES :1'
USING v_hist_tablas(i);


EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(SUBSTR(SQLERRM,1,255));
FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Encontrado un error en ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX ||
' Error Msg: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE) );
END LOOP;
END;

END;
/
Re: PLS-00457:"expressions have to be of SQL types" [message #203032 is a reply to message #203028] Mon, 13 November 2006 11:23 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
No, you can't do that with 9i. Collection type variables are not allowed in insert statements.
Re: PLS-00457:"expressions have to be of SQL types" [message #203182 is a reply to message #203028] Tue, 14 November 2006 03:50 Go to previous messageGo to next message
mafc73
Messages: 38
Registered: November 2006
Member
I need change the name of the table in the insert command (I have a x number of tables where I want to apply the insert command. I read all the possible tables from other table), but I want to do the insert in each table with bulk load. Is it possible?. How Can I do this?

Thanks

[Updated on: Tue, 14 November 2006 03:56]

Report message to a moderator

Re: PLS-00457:"expressions have to be of SQL types" [message #203184 is a reply to message #203028] Tue, 14 November 2006 03:55 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
If you use the method for passing parameter names and values that I suggested, then inserting them all into a table using bulk load is very easy. If you want to change the name of the table depending on which procedure fails, then you would have to use dynamic SQL, but it's still possible to do bulk load providing you have at least 9i.
Re: PLS-00457:"expressions have to be of SQL types" [message #203190 is a reply to message #203028] Tue, 14 November 2006 04:03 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Sorry, I was replying to the wrong message there. I thought it was a different thread.

You can use bulk load dynamically in 9i, but the restriction on record types still applies.
Re: PLS-00457:"expressions have to be of SQL types" [message #203357 is a reply to message #203190] Tue, 14 November 2006 20:54 Go to previous message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi,
not sure if this is what you need:


SQL> create table sv (x int);

Table created

SQL> declare
  2    type nt is table of sv.x%type;
  3    nr nt;
  4  begin
  5    select deptno
  6    bulk collect into nr
  7    from dept;
  8  
  9    forall i in nr.first..nr.last save exceptions
 10    insert into sv values(nr(i));
 11  end;
 12  /

PL/SQL procedure successfully completed

SQL> select * from sv;

                                      X
---------------------------------------
                                     10
                                     20
                                     30
                                     40

SQL> 

Previous Topic: Oracle hint?
Next Topic: SQL syntax
Goto Forum:
  


Current Time: Sat Dec 03 16:19:52 CST 2016

Total time taken to generate the page: 0.07855 seconds