Results 1 to 4 of 4
  1. #1
    Join Date
    May 2009
    Posts
    14

    Unanswered: Wrapping a select statement in pl/sql procedure

    Hello, Im new to pl/sql

    I am trying to wrap a pl/sql procedure around this sql select statement so I get the same output, but so I can supply the some of the where parameters as variables:

    Code:
    SELECT go.sequence, ft.version, ft.released_title, ft.final_duration
    	FROM FinishedTrack ft, GroupedOn go, Album a
    	WHERE ft.originates_from = go.originates_from 
    	AND go.album_id = a.album_id
    	AND a.is_distributed_as = 'c'
    	AND a.title = 'My Feet'
    	ORDER BY go.sequence;
    I have this so far, but I have erors I cannot fix And I still need to get it to display the output aswell.

    Code:
    CREATE OR REPLACE PROCEDURE list_track
      (type_in 	CHAR,
       title_in    	VARCHAR)
    AS
    v_sequence    	    NUMBER;
    v_version     	    NUMBER;
    v_released_title    VARCHAR(40);
    v_final_duration    VARCHAR(40);
    BEGIN
    
    SELECT  GroupedOn.sequence, 
          	FinishedTrack.version, 
    	FinishedTrack.released_title, 
    	FinishedTrack.final_duration
    INTO v_sequence, v_version, v_released_title, v_final_duration
    FROM FinishedTrack, GroupedOn, Album
    WHERE FinishedTrack.originates_from = GroupedOn.originates_from 
    	AND GroupedOn.album_id = Album.album_id
    	AND Album.is_distributed_as = 'c'
    	AND Album.title = 'My Feet'
    
    END list_track;
    /
    I would be extremly grateful with any help. Im not sure about having multiple coloumns and tables in the select into statements in procedures.
    ERRORS:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    11/1 PL/SQL: SQL Statement ignored
    21/2 PL/SQL: ORA-00933: SQL command not properly ended
    22/15 PLS-00103: Encountered the symbol "end-of-file" when expecting
    one of the following:
    begin case declare end exception exit for goto if loop mod
    null pragma raise return select update while with
    <an identifier> <a double-quoted delimited-identifier>
    <a bind variable> << close current delete fetch lock insert
    open rollback savepoint set sql execute commit forall merge
    <a single-quoted SQL string> pipe
    <an alternatively-quoted SQL string>

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    Last edited by Toshikazu; 05-15-09 at 16:11.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SELECT groupedon.SEQUENCE,
           finishedtrack.version,
           finishedtrack.released_title,
           finishedtrack.final_duration
    INTO   v_sequence,v_version,v_released_title,v_final_duration
    FROM   finishedtrack,
           groupedon,
           album
    WHERE  finishedtrack.originates_from = groupedon.originates_from
           AND groupedon.album_id = album.album_id
           AND album.is_distributed_as = 'c'
           AND album.title = 'My Feet'
    >I have erors I cannot fix
    ERROR? What Error? I do not see any error.

    How can I fix something when I don't know what is broken?


    use <code_tags> is explained in #1 STICKY post (URL below)
    http://www.dbforums.com/oracle/10316...s-posters.html
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    May 2009
    Posts
    14
    My apologies, I have added the erros now. Btw the initial select statement works fine, its pl\sql translation that I cannot get to work.

    Also, where it says 'My Feet' and 'c', those will be compared against the arguments type_in and title_in when i get it to work.

    Thank you
    Last edited by Toshikazu; 05-15-09 at 16:14.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    please use sqlplus for debugging forum posts.

    At a minimum a semi-colon is needed at the end of the SELECT statement.

    use CUT & PASTE of whole sqlplus session so we, too, can see actual line number in code
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •