Home » SQL & PL/SQL » SQL & PL/SQL » Building Strings (merged)
Building Strings (merged) [message #379721] |
Wed, 07 January 2009 15:14  |
decarlomw
Messages: 12 Registered: January 2009
|
Junior Member |
|
|
Hi All,
Can you build a cursor inside a string?
for examble
l_valotl_string := (l_head_string||
out_rec(x).work_ticket||
out_rec(x).repair_order_num||
out_rec(x).crew_number||
out_rec(x).equipment_number||
out_rec(x).charge_code||
out_rec(x).requesting_area||
out_rec(x).wr_nbr||
out_rec(x).performing_area||
out_rec(x).start_miles||
out_rec(x).ending_miles||
out_rec(x).legacy_earn_code||
l_fixed_dist||
l_employee_number||
out_rec(x).woc_job_class
);
I tried but keep getting an error.
|
|
|
|
Re: Building strings [message #379725 is a reply to message #379721] |
Wed, 07 January 2009 15:23   |
 |
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
>I tried but keep getting an error.
Error? What error?
I don't see any error.
You have table & provided us no DDL for it.
You have data & provided us no DML for test data.
http://www.orafaq.com/forum/t/88153/0/
You have not read or followed Posting Guideline as stated in URL above.
You have not shown us via CUT & PASTE what you actually did or how Oracle responded.
You have not provided any expected output.
[Updated on: Wed, 07 January 2009 15:24] Report message to a moderator
|
|
|
Re: Building Strings (merged) [message #379726 is a reply to message #379721] |
Wed, 07 January 2009 15:36   |
decarlomw
Messages: 12 Registered: January 2009
|
Junior Member |
|
|
Ok Here is the original procedure.
PROCEDURE send_string
IS
x NUMBER;
l_head_string VARCHAR2 (100) := ' VALOTL ';
l_mss_return VARCHAR2 (1000);
l_valotl_string VARCHAR2 (5000);
l_performing_area_val VARCHAR2 (50);
l_fixed_dist VARCHAR2 (1);
l_employee_number VARCHAR2 (10);
valotl VARCHAR2 (5000);
val VARCHAR2 (5000);
--
BEGIN
--
write_log ('---> Start mlgw_otl_validation_pkg.send_string <---');
--
x := out_rec.FIRST;
LOOP
EXIT WHEN x IS NULL;
--
--
BEGIN
SELECT SUBSTR (hou.NAME, 2, 6),
(SELECT DECODE (NVL (SUM (pca.proportion), 0), 0, 'N', 'Y')
FROM pay_cost_allocations_f pca
WHERE pca.assignment_id = paf.assignment_id
AND l_effective_date BETWEEN pca.effective_start_date
AND pca.effective_end_date)
proportion,
LPAD (ppf.employee_number, 5, '0')
INTO l_performing_area_val,
l_fixed_dist,
l_employee_number
FROM per_all_assignments_f paf,
hr_all_organization_units hou,
per_all_people_f ppf
WHERE paf.organization_id = hou.organization_id
AND l_effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND l_effective_date BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND ppf.person_id = paf.person_id
AND paf.assignment_type = 'E'
AND paf.primary_flag = 'Y'
AND paf.person_id = out_rec (x).person_id
GROUP BY SUBSTR (hou.NAME, 2, 6),
ppf.employee_number,
paf.assignment_id;
EXCEPTION
WHEN OTHERS
THEN
l_performing_area_val := '000000';
l_fixed_dist := 'N';
l_employee_number := '00000';
END;
--
out_rec (x).performing_area := LPAD (l_performing_area_val, 6, '0');
--
out_rec (x).work_ticket :=
UPPER (NVL (out_rec (x).work_ticket, LPAD (' ', 8, ' ')));
out_rec (x).repair_order_num :=
NVL (out_rec (x).repair_order_num, LPAD (0, 7, '0'));
out_rec (x).crew_number :=
NVL (out_rec (x).crew_number, LPAD (0, 4, '0'));
out_rec (x).equipment_number :=
NVL (out_rec (x).equipment_number, LPAD (0, 4, '0'));
out_rec (x).charge_code :=
NVL (out_rec (x).charge_code, RPAD ('0', 4, '0'));
out_rec (x).requesting_area :=
NVL (out_rec (x).requesting_area, LPAD (0, 6, '0'));
out_rec (x).wr_nbr :=
UPPER (NVL (out_rec (x).wr_nbr, RPAD (' ', 8, ' ')));
out_rec (x).performing_area :=
NVL (out_rec (x).performing_area, LPAD (0, 6, '0'));
out_rec (x).start_miles :=
NVL (out_rec (x).start_miles, LPAD (0, 6, '0'));
out_rec (x).ending_miles :=
NVL (out_rec (x).ending_miles, LPAD (0, 6, '0'));
out_rec (x).legacy_earn_code :=
NVL (out_rec (x).legacy_earn_code, '000');
out_rec (x).woc_job_class :=
NVL (out_rec (x).woc_job_class, LPAD (' ', 6, ' '));
--
l_valotl_string :=
(l_head_string
|| out_rec (x).work_ticket
|| out_rec (x).repair_order_num
|| out_rec (x).crew_number
|| out_rec (x).equipment_number
|| out_rec (x).charge_code
|| out_rec (x).requesting_area
|| out_rec (x).wr_nbr
|| out_rec (x).performing_area
|| out_rec (x).start_miles
|| out_rec (x).ending_miles
|| out_rec (x).legacy_earn_code
|| l_fixed_dist
|| l_employee_number
|| out_rec (x).woc_job_class
);
--
-- Call MSS Function to validate timecard data
--
valotl := valotl || l_valotl_string;
--
x := out_rec.NEXT (x);
END LOOP;
err_tbl.DELETE;
write_log ( 'EE#:'
|| l_employee_number
|| '|time_building_block_id:'
|| x
|| ' |To MSS:'
|| l_valotl_string
);
valotl := ' ';
write_log ('---> End mlgw_otl_validation_pkg.send_string <---');
END send_string;
The out_rec is created by this
type err_tbl_typ is table of varchar2(50) index by binary_integer;
err_tbl err_tbl_typ;
--
--
type out_rec_typ is record(
work_ticket varchar2(50),
repair_order_num varchar2(50),
crew_number varchar2(50),
equipment_number varchar2(50),
charge_code varchar2(50),
requesting_area varchar2(50),
wr_nbr varchar2(50),
performing_area varchar2(50),
start_miles varchar2(50),
ending_miles varchar2(50),
person_id number,
legacy_earn_code varchar2(50),
woc_job_class varchar2(50),
element_type_id number );
type out_rec_typ_tbl is table of out_rec_typ index by binary_integer;
out_rec out_rec_typ_tbl;
the output is as follows
0107:11:40:28.338|3818110|EE#:02404|time_building_block_id:-23 |To MSS: VALOTL 0000000000000000000000000 450190000000000000010Y02404
0107:11:40:28.339|3818110|EE#:02404|time_building_block_id:-21 |To MSS: VALOTL 0000000000000000000000000 450190000000000000010Y02404
0107:11:40:28.340|3818110|EE#:02404|time_building_block_id:-19 |To MSS: VALOTL 0000000000000000000000000 450190000000000000010Y02404
if you look at how the string is build I'm trying to create a cursor to do the same thing.
|
|
|
|
Re: Building Strings (merged) [message #379740 is a reply to message #379736] |
Wed, 07 January 2009 17:25   |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
Quote: |
....
x := out_rec.FIRST;
....
x := out_rec.NEXT;
....
l_valotl_string :=
(l_head_string
....
|
And all these parts don't look like any valid Oracle PL/SQL syntax I have ever seen. Where did you get his procedure?
|
|
|
Re: Building Strings (merged) [message #379877 is a reply to message #379740] |
Thu, 08 January 2009 04:02   |
cookiemonster
Messages: 13965 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
ThomasG wrote on Wed, 07 January 2009 23:25 | Quote: |
....
x := out_rec.FIRST;
....
x := out_rec.NEXT;
....
l_valotl_string :=
(l_head_string
....
|
And all these parts don't look like any valid Oracle PL/SQL syntax I have ever seen. Where did you get his procedure?
|
The first two are standard array functions.
The last is unusual but there's no law says you can't wrap brackets around a string assignment.
decarlomw:
You can't write a cursor that accesses out_rec - it's a PL/SQL and cursors can only see SQL types.
You've got two choices for using a cursor:
1 - change out_rec to a SQL type. Then you can reference it in a SQL query using the CAST function.
2 - Rewrite whatever code populates out_rec in the first place as a cursor. Seeing as you haven't provided that code I've no idea how easy that would be.
|
|
|
|
|
Goto Forum:
Current Time: Tue Feb 18 01:41:12 CST 2025
|