Home » SQL & PL/SQL » SQL & PL/SQL » Converting a sql script to pl/sql
Converting a sql script to pl/sql [message #430967] Fri, 13 November 2009 08:08 Go to next message
californiagirl
Messages: 79
Registered: May 2007
Member
I'm a newbie to pl/sql so just trying to get an understanding of how I would convert a sql script into pl/sql.

See script below. How would I convert this to pl/sql, would I use a cursor, loops etc? I know I would be receiving in one variable as a parameter, but any other suggestions that I would need to do? Thanks.


SELECT      'Import num^Code^Call Num.^Title RR^Crs Abpp^Crs Number1^Crs Sec.^Time^'
         || 'Stop Time^Start Date^End Time^Activity Time^Days^'
         || 'Building codes^Room num.^Enrollment Count^Enrollment code limit^substitute Name^'
         || 'Instructor pass Identification^Assignment^Num of Terms^',
         1 sort_col
FROM DUAL
UNION
SELECT DISTINCT    CONCAT(a.ssb_term_code,a.ssb_crn)
                || '^'
                || SUBSTR (ssk.f_get_ssrx_group (ssrm_term_code,ssrm_crn),1,2)
                || '^'
                || a.ssb_crn
                || '^'
                || NVL (a.ssb_crse_title, c.scbc_title)
                || '^'
                || a.ssb_subj_code
                || '^'
                || a.ssb_crse_numb
                || '^'
                || a.ssb_seq_numb
                || '^'
                || b.ssrm_begin_time
                || '^'
                || b.ssrm_end_time
                || '^'
                || b.ssrm_start_date
                || '^'
                || b.ssrm_end_date
                || '^'
                || a.ssb_schd_code
                || '^'
                || b.ssrm_mon_day||b.ssrm_tue_day||b.ssrm_wed_day||b.ssrm_thu_day||
b.ssrm_fri_day||b.ssrm_sat_day||b.ssrm_sun_day
                || '^'
                || b.ssrm_bldg_code
                || '^'
                || b.ssrm_room_code
                || '^'
                || a.ssb_enrl
                || '^'
                || a.ssb_max_enrl
                || '^'
                || SUBSTR(f.spri_last_name||', '||f.spri_first_name,1,30)
                || '^'
                || f.spri_id
                || '^'
                || a.ssb_term_code
                || '^'
                || CONCAT(a.ssb_term_code,a.ssb_ptrm_code),
                2 sort_col
          FROM ssb a, ssrm b, scbc c, sir e, spri f
          WHERE sir_id = spri_id
            AND spri_change_ind IS NULL
            AND sir_crn = ssb_crn
            AND ssb_crn = ssrm_crn
            AND sir_t_code = ssb_t_code
            AND ssb_t_code = ssrm_t_code
            AND ssrm_t_code = '515101'                         --'&t_code'
            AND ssb_subj_code = scbc_subj_code
            AND ssb_crest_numb = scbc_crest_numb
            AND scbc_ineff_term =
                   (SELECT MAX (g.scbc_ineff_term)
                      FROM scbc g
                     WHERE g.scbc_subj_code = ssb_subj_code
                       AND g.scbc_crest_numb = ssb_crest_numb
                       AND g.scbc_ineff_term <= ssb_t_code)
                            ORDER BY 2;










Californiagirl

[Updated on: Fri, 13 November 2009 08:46] by Moderator

Report message to a moderator

Re: Converting a sql script to pl/sql [message #430973 is a reply to message #430967] Fri, 13 November 2009 08:19 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
The big question here is what you want to do with the results of the select.
A sql script run in sqlplus will display the results automatically. PL/SQL won't.
So what is going to be using the results of this for what purpose?
Re: Converting a sql script to pl/sql [message #430975 is a reply to message #430973] Fri, 13 November 2009 08:31 Go to previous messageGo to next message
californiagirl
Messages: 79
Registered: May 2007
Member
Good question and sorry I didn't give details..that would make sense. The purpose is to extract data from an oracle database (10G), then it will be sent to a defined output location, that will be loaded into another system. The file has to be comma delimitted.

Californiagirl
Re: Converting a sql script to pl/sql [message #430976 is a reply to message #430975] Fri, 13 November 2009 08:34 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
Why not just use spool then. No need for PL/SQL. If possible could you provide more detailed requirements as they are still a bit on the vague side.
Re: Converting a sql script to pl/sql [message #430977 is a reply to message #430976] Fri, 13 November 2009 08:47 Go to previous messageGo to next message
californiagirl
Messages: 79
Registered: May 2007
Member
The process has to be written in pl/sql because the system it is running from only recognizes pl/sql, perl, pro c etc. but just not plain old sql. Therefore all the sql customized reports etc,have to be converted to pl/sql.

The pl/sql process will be inserted into a system called Banner as a job and will then send the data file to a defined output locaton on a server.

Re: Converting a sql script to pl/sql [message #430979 is a reply to message #430977] Fri, 13 November 2009 08:52 Go to previous messageGo to next message
californiagirl
Messages: 79
Registered: May 2007
Member
Also this process will be ran 2-3 times a day with probably 2000-3000 transactions. It will have one parameter for the users to pick in order to run this job.
Re: Converting a sql script to pl/sql [message #430984 is a reply to message #430979] Fri, 13 November 2009 09:20 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
You can use asktom`s dumpcsv method..search for that....

sriram Smile
Re: Converting a sql script to pl/sql [message #431022 is a reply to message #430979] Fri, 13 November 2009 12:27 Go to previous message
joy_division
Messages: 4615
Registered: February 2005
Location: East Coast USA
Senior Member
I'd be interest to know what kind of system cannot run plain old SQL but only PL/SQL.

Be aware that PL/SQL cannot output data like SQL can with a SELECT command. You would have to us UTL_FILE or the like and that data will only be put out on the server. You would need to know the Oracle-defined valid directories (not filesystem directories) where you can output data.
Previous Topic: Group By
Next Topic: Minus in Select
Goto Forum:
  


Current Time: Sun Sep 25 02:38:00 CDT 2016

Total time taken to generate the page: 0.09031 seconds