Home » SQL & PL/SQL » SQL & PL/SQL » cursor implement
cursor implement [message #596475] Mon, 23 September 2013 12:30 Go to next message
Mary1234
Messages: 4
Registered: March 2012
Location: India
Junior Member
Hi Please help me how to write below query in pl/sql cursor.
The help table has two associated tables, help_txt and help_id, which will have strings of data concatenated into one sales contact record. There are multiple lines of text per comment and multiple lines of resolution text at 40 characters per line. The key to the help_text table (id, date,seqno) is the main key to the help_txt table and help_id t table with a sequence added to each table

The formatted string will contain some text and variables with the comment lines (1-10 or more) concatenated first, followed by the resolution lines (1-10 or more). There will be multiple comment and multiple resolution lines. The Cust_Cmnt_Txt lines and the Resolved_Desc lines should be concatenated and formatted in the following string (% marks the variable string) :

'help taken ' %help.Taken_Dte 'received from the following source: ' %help.id. 'Remark Text: ' %help_text (where help_txt_Seq = 1) %help_text (where help_text_seq = 2-10 or more) 'Resolution: ' %help_id_Res_Txt.Resolved_Desc (where help_ID_Txt_Seq = 1) %help_ID_Res_Txt.Resolved_Desc (where help_id_Txt_Seq = 2-10 or more)


Re: cursor implement [message #596476 is a reply to message #596475] Mon, 23 September 2013 12:37 Go to previous messageGo to next message
BlackSwan
Messages: 21936
Registered: January 2009
Senior Member
It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
It would be helpful if you provided DML (INSERT INTO ...) for test data.
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/311
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
Re: cursor implement [message #596479 is a reply to message #596476] Mon, 23 September 2013 12:44 Go to previous messageGo to next message
Mary1234
Messages: 4
Registered: March 2012
Location: India
Junior Member
create table help (id number(10),date date,seq number(4),desc varcha2(100))

create table help_text (id number(10),date date,seq number(4),varchar2(100))
create table help_id (id number(10),date date,seq number(4),varchar2(100))
Re: cursor implement [message #596480 is a reply to message #596475] Mon, 23 September 2013 12:44 Go to previous messageGo to next message
Michel Cadot
Messages: 57599
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Laughing

Re: cursor implement [message #596483 is a reply to message #596479] Mon, 23 September 2013 12:50 Go to previous messageGo to next message
John Watson
Messages: 4081
Registered: January 2010
Location: Global Village
Senior Member
Mary, I'm feeling generous, so I'll take you through all the errors in just one of your CREATE TABLE statements. You really should test your code before posting it. Here you go:
orclz>
orclz> create table help (id number(10),date date,seq number(4),desc varcha2(100))
  2  /
create table help (id number(10),date date,seq number(4),desc varcha2(100))
                                 *
ERROR at line 1:
ORA-00904: : invalid identifier


orclz> create table help (id number(10),datecol date,seq number(4),desc varcha2(100))
  2  /
create table help (id number(10),datecol date,seq number(4),desc varcha2(100))
                                                            *
ERROR at line 1:
ORA-00904: : invalid identifier


orclz> create table help (id number(10),datecol date,seq number(4),description varcha2(100))
  2  /
create table help (id number(10),datecol date,seq number(4),description varcha2(100))
                                                                               *
ERROR at line 1:
ORA-00907: missing right parenthesis


orclz> create table help (id number(10),datecol date,seq number(4),description varchar2(100))
  2  /

Table created.

orclz>
Re: cursor implement [message #596484 is a reply to message #596480] Mon, 23 September 2013 12:51 Go to previous messageGo to next message
BlackSwan
Messages: 21936
Registered: January 2009
Senior Member
Somebody needs a refresher course in normalizing data to Third Normal Form.

It makes no sense to have multiple tables with identical columns.

Posted SQL contains syntax error.
KEYWORD like "DATE" should never be used as column_name.

[Updated on: Mon, 23 September 2013 12:51]

Report message to a moderator

Re: cursor implement [message #596485 is a reply to message #596484] Mon, 23 September 2013 12:55 Go to previous messageGo to next message
Mary1234
Messages: 4
Registered: March 2012
Location: India
Junior Member
right now i dont have exact ddl and dml.so i want the logic below question how to implement it using cursor in a procdure with a temporary table .
Re: cursor implement [message #596486 is a reply to message #596485] Mon, 23 September 2013 13:05 Go to previous messageGo to next message
BlackSwan
Messages: 21936
Registered: January 2009
Senior Member
If you don't know column names how can you write code?
More often than not, Oracle does not require any temporary table.
Never write PL/SQL when same can be done using plain SQL.
How will you, I or anyone recognize any particular post as being correct?
icon11.gif  Re: cursor implement [message #596487 is a reply to message #596485] Mon, 23 September 2013 13:05 Go to previous message
Michel Cadot
Messages: 57599
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The correct first question is: how to do it in SQL before anything in code and even worse with a temporary table.
Are you sure you are working with an Oracle database?

Previous Topic: Time Datatype
Next Topic: how to get Max(value) with its corresponding datetime ...
Goto Forum:
  


Current Time: Wed Apr 16 07:33:55 CDT 2014

Total time taken to generate the page: 0.08709 seconds