Home » SQL & PL/SQL » SQL & PL/SQL » Concatenation and Insertion
Concatenation and Insertion [message #314833] Thu, 17 April 2008 23:59 Go to next message
McLan
Messages: 36
Registered: April 2008
Member

I have variable v_number =1234
I want this to be stored in two formats, one is ‘a.b.c.d.-1234’ and other one is ‘/a.b.c.d:1234/’
1. How should I concatenate or transform v_number to above 2 forms?
I want to contactnate into temp variable var_temp1, var_temp2
So, 2. How can I insert into table for varchar using variable?,
insert into xxx (col1,col2) values (‘var_temp1’,’var-_temp2’)
when I use in single quotes, its inserting var_temp1 rather than ‘a.b.c.d.-1234’
Please suggest on question#1 and question#2
Re: Concatenation and Insertion [message #314837 is a reply to message #314833] Fri, 18 April 2008 00:12 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Obviously, you have tried to do that yourself (and that's great). Now, you should share your code with the rest of the community because - you won't get a complete solution, but we'll help you improve your own code.
Re: Concatenation and Insertion [message #314842 is a reply to message #314833] Fri, 18 April 2008 00:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Before posting what you did, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: Concatenation and Insertion [message #314904 is a reply to message #314842] Fri, 18 April 2008 04:14 Go to previous messageGo to next message
McLan
Messages: 36
Registered: April 2008
Member
Thanks Michel, for bringing me into right track

I want to insert two strings into table TEMP, one is ‘a.b.c.d.-12345’ other one is ‘/a.b.c.d:12345/’.
Here is my code
 
CREATE TABLE TEMP (
  COL_FRMT1 VARCHAR2(255 BYTE),
  COL_FRMT2 VARCHAR2(255 BYTE));

set serveroutput on size 1000000

DECLARE
  v_Number   INT;
   v_Format1  TEMP.col_frmt1%TYPE;
   v_Format2  TEMP.col_frmt2%TYPE;
BEGIN
  v_Number := 12345;

        v_Format1 :='a.b.c.d-'||v_Number
        v_Format2 :='/'||'a.b.c.d:'||v_Number||'/'
        dbms_Output.Put_Line(v_Format1);

  dbms_Output.Put_Line(v_Format1);
  dbms_Output.Put_Line(v_Format2);

  INSERT INTO TEMP
  VALUES     ('v_Format1',
              'v_Format2');
END;
/


I get the below error:
v_Format2 :='/'||'a.b.c.d:'||v_Number||'/'
*
ERROR at line 9:
ORA-06550: line 9, column 2:
PLS-00103: Encountered the symbol "V_FORMAT2" when expecting one of the
following:
. ( * @ % & = - + ; < / > at in is mod remainder not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
LIKE4_ LIKEC_ between || member SUBMULTISET_
The symbol ";" was substituted for "V_FORMAT2" to continue.
ORA-06550: line 10, column 2:
PLS-00103: Encountered the symbol "DBMS_OUTPUT" when expecting one of the
following:
* & = - + ; < / > at in is mod remainder not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
LIKE4_ LIKEC_ between || member

Cheers,
McLan
Re: Concatenation and Insertion [message #314908 is a reply to message #314904] Fri, 18 April 2008 04:35 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
End executable statements with a semi - colon.
Quick hint, the error PLS-00103: Encountered the symbol ...
Is very often (not always, but very often) caused by a missing semi colon

[Updated on: Fri, 18 April 2008 04:35]

Report message to a moderator

Re: Concatenation and Insertion [message #314955 is a reply to message #314908] Fri, 18 April 2008 06:42 Go to previous message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
So, after you add 2 semi-colons and remove 4 single quotes from the INSERT statement, you'll have the result you needed.
Previous Topic: column alias
Next Topic: Assigning cursor to cursor?
Goto Forum:
  


Current Time: Fri Dec 02 20:41:38 CST 2016

Total time taken to generate the page: 0.08985 seconds