Home » SQL & PL/SQL » SQL & PL/SQL » Building Strings (merged)
Building Strings (merged) [message #379721] Wed, 07 January 2009 15:14 Go to next message
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 #379724 is a reply to message #379721] Wed, 07 January 2009 15:22 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Too few information, I'm afraid.

Quote:
I tried but keep getting an error.

I'm quite a long time here, but I have never seen anyone to be capable of mind reading. So - what error do you get? What is "out_rec(x)"? Why did you start this string (I presume that "l_valotl_string" is a character variable) with a bracket instead of a single quote? What is its size? What are other variables' datatypes?
Re: Building strings [message #379725 is a reply to message #379721] Wed, 07 January 2009 15:23 Go to previous messageGo to next message
BlackSwan
Messages: 25035
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 Go to previous messageGo to next message
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 #379736 is a reply to message #379721] Wed, 07 January 2009 16:57 Go to previous messageGo to next message
BlackSwan
Messages: 25035
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.

Re: Building Strings (merged) [message #379740 is a reply to message #379736] Wed, 07 January 2009 17:25 Go to previous messageGo to next message
ThomasG
Messages: 3189
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 Go to previous messageGo to next message
cookiemonster
Messages: 12403
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.
Re: Building Strings (merged) [message #379925 is a reply to message #379877] Thu, 08 January 2009 08:09 Go to previous messageGo to next message
decarlomw
Messages: 12
Registered: January 2009
Junior Member
That code is provided look up top.
Re: Building Strings (merged) [message #379927 is a reply to message #379721] Thu, 08 January 2009 08:13 Go to previous message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
REally?

I see code that loops over and modifies the contents of a pre-populated out_rec. I don't see any code that populates it in the first place.
Previous Topic: Exception Handling
Next Topic: ORA-01799
Goto Forum:
  


Current Time: Sat Dec 03 17:54:15 CST 2016

Total time taken to generate the page: 0.06637 seconds