Solved

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

Posted on 2004-04-30
8
3,757 Views
Last Modified: 2012-06-21
I have a procedure  this is the def...

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,
TempTableName  IN OUT VARCHAR2)
AS
....

I want to test this in sql plus. In the procedure it creates a global temp table which preserves the data on commit. After execution I would like to see the table...
I am not sure of the exact syntax.. This is what I want to do..

declare variables
begin
execute the procedure with parameters...
Show the table as select ( name  from output variable TempTableName)
End

I may not be able to test any solution right away as server is unavailable...
But It should be a simple task for Oracle users.. let alone Experts!

Thanks in advance
Shailesh


0
Comment
Question by:Shailesh15
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 10960963
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
0
 
LVL 10

Author Comment

by:Shailesh15
ID: 10961044
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...;-)
0
 

Expert Comment

by:go2guy
ID: 10961238
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:)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 15

Expert Comment

by:andrewst
ID: 10961282
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;
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 10961362
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.
0
 
LVL 6

Accepted Solution

by:
musdu earned 500 total points
ID: 10962424
Hi,

in sqlplus;

var tmptable varchar2(100);

begin
:tmptable:='my_temp_table';
RPTJSREPORT(TempTableName => :tmptable);
end;
/

select * from my_temp_table;


regards
0
 
LVL 10

Author Comment

by:Shailesh15
ID: 10962574
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.



 
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 10962734
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!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question