Home » Developer & Programmer » Forms » how to check the syntax of the resulting insert in oracle forms 6i (forms 6i)
how to check the syntax of the resulting insert in oracle forms 6i [message #585222] Thu, 23 May 2013 01:40 Go to next message
sameer_da
Messages: 74
Registered: April 2013
Location: India
Member
Hello
I have a package,
PACKAGE PK_EXCEL_TO_DB IS
  TYPE tKeyValue IS RECORD (
    CROUTE         VARCHAR2(255),
    VROUTE         VARCHAR2(1000),
    CTRNDATE       VARCHAR2(255),
    VTRNDATE       VARCHAR2(1000),
    CTTIME	        VARCHAR2(255),
    VTTIME         VARCHAR2(1000),
    CTID           VARCHAR2(255),
    VTID           VARCHAR2(1000));
    
  TYPE tDataList IS TABLE OF tKeyValue;
  PROCEDURE PR_DO_INSERT(i_lData IN tDataList);	
END

Package body is
PACKAGE BODY PK_EXCEL_TO_DB IS
    PROCEDURE PR_DO_INSERT(i_lData IN tDataList) IS
   vcInsert  					VARCHAR2(3500);
  BEGIN
 
    FOR i IN 1..i_ldata.count LOOP
       	
     vcInsert :='INSERT INTO TEST (' || i_ldata(i).CROUTE || ', ' || i_ldata(i).CTRNDATE || ', ' || i_ldata(i).CTTIME || ', '||i_ldata(i).CTID || ')' ||
                 ' VALUES (' || i_ldata(i).VROUTE || ', ' || i_ldata(i).VTRNDATE || ', ' || i_ldata(i).VTTIME || ',' || i_ldata(i).VTID || ')';
       FORMS_DDL(vcInsert);                        
    END LOOP;
    STANDARD.COMMIT;

--COMMIT;                                    
  END;
END;


what i want is: 'output vcInsert to a forms-item and check the syntax of the resulting insert'. How can I do this please help.

Thanks
Sameer

[Updated on: Thu, 23 May 2013 01:54]

Report message to a moderator

Re: how to check output to form item [message #585231 is a reply to message #585222] Thu, 23 May 2013 01:56 Go to previous messageGo to next message
Littlefoot
Messages: 18824
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Put
:block.item_name := vcinsert;

behind the "vcInsert := ..." statement. Note that you'll see only the last VCINSERT's value (because of a loop - all previous values will be overwritten).

But, if you display it with a MESSAGE built-in, you'll see them all (however, you might get tired of acknowledging messages, depending on I_LDATA.COUNT value).
Re: how to check output to form item [message #585243 is a reply to message #585231] Thu, 23 May 2013 03:08 Go to previous messageGo to next message
sameer_da
Messages: 74
Registered: April 2013
Location: India
Member
Thanks a lot Littlefoot.
Re: how to check output to form item [message #585260 is a reply to message #585231] Thu, 23 May 2013 04:11 Go to previous messageGo to next message
sameer_da
Messages: 74
Registered: April 2013
Location: India
Member
Hello littlefoot,
I've one more problem. The values i am inserting all of them are character.
please tell me how should i provide single cotes (' ' ) to each value?

Thanks again.
Sameer
Re: how to check output to form item [message #585261 is a reply to message #585260] Thu, 23 May 2013 04:19 Go to previous messageGo to next message
Littlefoot
Messages: 18824
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You'll have to concatenate them with strings you have. Here are 3 examples; pick one.
1: concatenate single quote (note number of them you need to use!)
2: use CHR(39) which represents a single quote
3: in PL/SQL, create a variable whose value is a single quote

SQL> select ''''    || 1234 || ''''    complex,
  2         chr(39) || 1234 || chr(39) easy
  3  from dual;

COMPLE EASY
------ ------
'1234' '1234'

SQL>
SQL> declare
  2    lq varchar2(1) := chr(39);
  3  begin
  4    dbms_output.put_line(lq || 1234 || lq);
  5  end;
  6  /
'1234'

PL/SQL procedure successfully completed.

SQL>
Re: how to check output to form item [message #585264 is a reply to message #585261] Thu, 23 May 2013 04:40 Go to previous messageGo to next message
sameer_da
Messages: 74
Registered: April 2013
Location: India
Member
Thanks littlefoot, I got it.
Thanks again

Regards
sameer
Re: how to check output to form item [message #585272 is a reply to message #585264] Thu, 23 May 2013 05:47 Go to previous messageGo to next message
cookiemonster
Messages: 10573
Registered: September 2008
Location: Rainy Manchester
Senior Member
Number 4:
SQL> select q'[1''234]' q_mech FROM dual;

Q_MECH
------------------
1''234

Re: how to check output to form item [message #585273 is a reply to message #585272] Thu, 23 May 2013 06:44 Go to previous message
sameer_da
Messages: 74
Registered: April 2013
Location: India
Member
Thanks cookiemonster.
Previous Topic: Canvases..
Next Topic: inserting/retriving images in oracle database 10g
Goto Forum:
  


Current Time: Thu Apr 17 15:09:51 CDT 2014

Total time taken to generate the page: 0.11995 seconds