A simple question..How to test procedure in SQL plus?

See if this will get you started:

create or replace procedure junk (myvar in out varchar2) as
begin
      myvar := 'Hello';
end;
/

show errors

var junk varchar2(100)

exec junk(:junk);

print junk

I am looking for solution specific to my procedure... (It's the reason why I posted procedure def & 500 pts!)

May be pl sql block which I can use as 'cut & paste'.

Thanks for your reply though...;-)

Keep in mind that for a global temporary table, even if ON COMMIT PRESERVE ROWS is set, that those rows are only visible during the SAME session; the rows go away if the session terminates.  Also, unless the global temporary table is dropped, it would result in an error to try to recreate a same-named table in a future session.

That said, it appears that the table name is dynamic, which means that you could use NDS (native dynamic sql) to execute any SQL against it.  The syntax for that would be similar to:

DECLARE
     vTableName   VARCHAR2(30);
     vCol1             VARCHAR2(30);
BEGIN
     vTableName := 'MyTable';
     DBMS_OUTPUT.PUT_LINE('Using table_name ' || vTableName);
     EXECUTE IMMEDIATE 'SELECT col1 FROM ' || vTableName || ' where pk_col = 123' INTO vCol1;
     DBMS_OUTPUT.PUT_LINE('vCol1='|| vCol1);
EXCEPTION
     WHEN NO_DATA_FOUND THEN
          DBMS_OUTPUT.PUT_LINE('key pk_col 123 was not found');
     WHEN OTHERS THEN
          RAISE;
END;

HTH:)

Why is the procedure CREATING the global temporary table?  The correct way to use GTTs is to create them ONCE like any other table - it is the DATA that is temporary and private to your session, not the table definition.

create global temporary table gtt (...);

CREATE OR REPLACE  PROCEDURE "SA"."RPTJSREPORT" (
JNum  IN NUMBER  DEFAULT NULL,
HearingType  IN NUMBER  DEFAULT NULL,
HearingDateFrom  IN VARCHAR2  DEFAULT NULL,
HearingDateTo  IN VARCHAR2  DEFAULT NULL,
CaseType  IN VARCHAR2  DEFAULT NULL)
AS
....

begin
  RPTJSREPORT( 1, 2, 'A', 'B', 'C' );
End
/

select * from gtt;

Here is what you suggested:
1. declare variables
2. begin
3. execute the procedure with parameters...
4. Show the table as select ( name  from output variable TempTableName)

I would suggest a slightly different way to do that that is much simpler in SQL*Plus:
(This does require changing your procedure to remove the "out" parameter.)
1. execute the procedure with parameters you provide when you call it.
2. Show the table as select * from [global temp table]

Here are the two commands you need in SQL*Plus:
exec SA.RPTJSREPORT (&jnum,&HearingType,'&HearingDateFrom','&HearingDateTo','&CaseType');
select *from [global temp table name];

Just provide the values for each parameter when prompted.

Do you need the "out" parameter for an application?  It is possible for PL\SQL procedures to return an array or recordset like that, but that is not the default (nor the easiest) way to do things in PL\SQL.  That looks a SQL Server style approach to coding PL\SQL.

Well as some of you have already noted,  It is a SQL database converted to Oracle. So I would like to keep the procedure definition similar to SQL SP.

@go2guy,
           I was looking for something like that... Is it possible for you to customize to my procedure. (Bit busy with other issues right now!).

@andrewst,
            Since it is a migrated SQL database , I guess I have to keep the def as SQL SP.Thank you for the suggestion.

@markgeer,
             In the procedure temptable is created & also the name is generated Which is sent in output parameter. This table name I have to bind to SQL to see the results. (This is for testing purpose only)


Thanks for all the comments.

Looks like @musdu's post may solve my problem ( as I told you I am looking for a copy paste solution).  I will give it a try later in the evening.

Thanks again.



 

Its your database, so its your decision, but be aware that the best (or easiest) way to get things done in SQL Server is *NOT* always the same as the best (or easiest) way to get things done in Oracle.  Also some parts of the system (especially those  involving null values or manual record locking) *WILL NOT* work in Oracle as they do in SQL Server!