Where do SQL/PL programs execute from ?

[GUEST]

Hi all.

I'm a newbie to ORacle and am planning to take a course in Oracle this
January. Can anyone tell me where a SQL/PL script is run from ? I tried
to run a simple SQL/PL script under SQL Plus but it doesn't work. Maybe
I was doing something wrong.

Any suggestions would be greatly appreciated...

Thanks in advance

Victor
--
inderpaul_s1234 AT yahoo DOT com

To reply please remove the "1234" and translate the rest.

[Daniel Roy]

Re: Where do SQL/PL programs execute from ?

You've got it perfectly, a PL/SQL program can run from SQL*Plus.
Please post your code, and the reason why you think it doesn't run.

Daniel

Hi all.
>
I'm a newbie to ORacle and am planning to take a course in Oracle this
January. Can anyone tell me where a SQL/PL script is run from ? I tried
to run a simple SQL/PL script under SQL Plus but it doesn't work. Maybe
I was doing something wrong.
>
Any suggestions would be greatly appreciated...
>
Thanks in advance
>
Victor

[FaheemRao]

Re: Where do SQL/PL programs execute from ?

for running a pl/sql script you need to put a "begin" at start and an
"end" at the end of script an yes you can run it from sql* plus
prompt.

if you can post the code you are trying to run that would be easier
for group to answer.;


Faheem

danielroy10junk @hotmail.com (Daniel Roy) wrote in message news:<3722db.03 12151937.2625d5 c1@posting.goog le.com>...

You've got it perfectly, a PL/SQL program can run from SQL*Plus.
Please post your code, and the reason why you think it doesn't run.
>
Daniel
>

Hi all.

I'm a newbie to ORacle and am planning to take a course in Oracle this
January. Can anyone tell me where a SQL/PL script is run from ? I tried
to run a simple SQL/PL script under SQL Plus but it doesn't work. Maybe
I was doing something wrong.

Any suggestions would be greatly appreciated...

Thanks in advance

Victor

[GUEST]

Re: Where do SQL/PL programs execute from ?

On Mon, 15 Dec 2003 22:14:42 GMT a@b.com says...

Hi all.
>
I'm a newbie to ORacle and am planning to take a course in Oracle this
January. Can anyone tell me where a SQL/PL script is run from ? I tried
to run a simple SQL/PL script under SQL Plus but it doesn't work. Maybe
I was doing something wrong.
>
Any suggestions would be greatly appreciated...
>
Thanks in advance
>
Victor
>

Ok here it is. If you need for me to post the SQL code which created the
tables and populated I will.

declare
cursor bc is select * from bank;
cursor brc (bn bank.b#%type) is select t#, city from branch where bn =
branch.b#;
cursor cc (cbn bank.b#%type, ctn branch.t#%type) is
select distinct customer.c#, customer.name, customer.status ,
customer.city, account.balance from customer, account
where account.c# = customer.c# and account.b# = cbn and account.t# =
ctn;
begin
dbms_output.put _line
('+============ =============== =============== ====+');
dbms_output.put _line('| Bank# Name City
|');
dbms_output.put _line
('------------------------------------------------');
for btuple in bc loop
dbms_output.put _line('| '||btuple.b#||' '||btuple.name| |'
'||btuple.city| |' ');
dbms_output.put _line('| +============== =============== =============+
|');
dbms_output.put _line('| | Branch# City |
|');
dbms_output.put _line('| --------------------------------------------
|');
for brtuple in brc(btuple.b#) loop
dbms_output.put _line('| | '||brtuple.t#|| ' '||brtuple.city ||'
');
dbms_output.put _line('| | +============== =============== =========+ |
|');
dbms_output.put _line('| | |Customer# Name Status City Balance | |
|');
dbms_output.put _line('| | ---------------------------------------- |
|');
for ctuple in cc(btuple.b#, brtuple.t#) loop
dbms_output.put _line('| | | '||ctuple.c#||' '||ctuple.name| |'
'||ctuple.statu s||' '||ctuple.city| |'
'||ctuple.balan ce||' ');
end loop;
dbms_output.put _line('| | ---------------------------------------| |
|');
dbms_output.put _line('| --------------------------------------------
|');
end loop;
dbms_output.put _line
('------------------------------------------------');
end loop;
dbms_output.put _line
('+============ =============== =============== ====+');
end;


--
inderpaul_s1234 AT yahoo DOT com

To reply please remove the "1234" and translate the rest.

[FaheemRao]

Re: Where do SQL/PL programs execute from ?

One way to execute it to make it a stored procedure at the end of code
and another end;
like this
creat or replace procedure Test is

cursor bc is select * from bank;

cursor brc (bn bank.b#%type) is select t#, city from branch where bn =
branch.b#;
cursor cc (cbn bank.b#%type, ctn branch.t#%type) is
select distinct customer.c#, customer.name, customer.status ,
customer.city, account.balance from customer, account
where account.c# = customer.c# and account.b# = cbn and account.t# =
ctn;
begin
dbms_output.put _line
('+============ =============== =============== ====+');
dbms_output.put _line('| Bank# Name City
|');
dbms_output.put _line

cursor bc is select * from bank;

cursor brc (bn bank.b#%type) is select t#, city from branch where bn =
branch.b#;
cursor cc (cbn bank.b#%type, ctn branch.t#%type) is
select distinct customer.c#, customer.name, customer.status ,
customer.city, account.balance from customer, account
where account.c# = customer.c# and account.b# = cbn and account.t# =
ctn;
begin
dbms_output.put _line
('+============ =============== =============== ====+');
dbms_output.put _line('| Bank# Name City
|');
dbms_output.put _line

and remove the declare keyword


like this

create or replace procedure Test is



GUEST <a@b.comwrote in message news:<MPG.1a4d7 11a3427d3ca9896 ce@nntp.slnt.ph ub.net.cable.ro gers.com>...

On Mon, 15 Dec 2003 22:14:42 GMT a@b.com says...

Hi all.

I'm a newbie to ORacle and am planning to take a course in Oracle this
January. Can anyone tell me where a SQL/PL script is run from ? I tried
to run a simple SQL/PL script under SQL Plus but it doesn't work. Maybe
I was doing something wrong.

Any suggestions would be greatly appreciated...

Thanks in advance

Victor

>
Ok here it is. If you need for me to post the SQL code which created the
tables and populated I will.
>
declare
>
('------------------------------------------------');
for btuple in bc loop
dbms_output.put _line('| '||btuple.b#||' '||btuple.name| |'
'||btuple.city| |' ');
dbms_output.put _line('| +============== =============== =============+
|');
dbms_output.put _line('| | Branch# City |
|');
dbms_output.put _line('| --------------------------------------------
|');
for brtuple in brc(btuple.b#) loop
dbms_output.put _line('| | '||brtuple.t#|| ' '||brtuple.city ||'
');
dbms_output.put _line('| | +============== =============== =========+ |
|');
dbms_output.put _line('| | |Customer# Name Status City Balance | |
|');
dbms_output.put _line('| | ---------------------------------------- |
|');
for ctuple in cc(btuple.b#, brtuple.t#) loop
dbms_output.put _line('| | | '||ctuple.c#||' '||ctuple.name| |'
'||ctuple.statu s||' '||ctuple.city| |'
'||ctuple.balan ce||' ');
end loop;
dbms_output.put _line('| | ---------------------------------------| |
|');
dbms_output.put _line('| --------------------------------------------
|');
end loop;
dbms_output.put _line
('------------------------------------------------');
end loop;
dbms_output.put _line
('+============ =============== =============== ====+');
end;

[GUEST]

Re: Where do SQL/PL programs execute from ?

On 19 Dec 2003 23:49:14 -0800 faheemrao@yahoo .com says...

One way to execute it to make it a stored procedure at the end of code
and another end;
like this
creat or replace procedure Test is
>
cursor bc is select * from bank;

cursor brc (bn bank.b#%type) is select t#, city from branch where bn =
branch.b#;
cursor cc (cbn bank.b#%type, ctn branch.t#%type) is
select distinct customer.c#, customer.name, customer.status ,
customer.city, account.balance from customer, account
where account.c# = customer.c# and account.b# = cbn and account.t# =
ctn;
begin
dbms_output.put _line
('+============ =============== =============== ====+');
dbms_output.put _line('| Bank# Name City
|');
dbms_output.put _line

>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
cursor bc is select * from bank;

cursor brc (bn bank.b#%type) is select t#, city from branch where bn =
branch.b#;
cursor cc (cbn bank.b#%type, ctn branch.t#%type) is
select distinct customer.c#, customer.name, customer.status ,
customer.city, account.balance from customer, account
where account.c# = customer.c# and account.b# = cbn and account.t# =
ctn;
begin
dbms_output.put _line
('+============ =============== =============== ====+');
dbms_output.put _line('| Bank# Name City
|');
dbms_output.put _line

>
and remove the declare keyword

Ok thanks I'll give it a try...

--
inderpaul_s1234 AT yahoo DOT com

To reply please remove the "1234" and translate the rest.

[Prince Kumar]

Re: Where do SQL/PL programs execute from ?

You can always do this.

SQLdeclare
<your decalarations>
begin
<you pl/sql stuff>
end ;
/

You can have your PL/SQL in a file and call it from SQL*Plus.

SQL@<your pl_sql file>
/

Also make sure to have the server output on, to see your dbms_output results.

Regards,
Prince.

GUEST <a@b.comwrote in message news:<MPG.1a4fd a86377a38649896 80@nntp.slnt.ph ub.net.cable.ro gers.com>...

On 19 Dec 2003 23:49:14 -0800 faheemrao@yahoo .com says...

One way to execute it to make it a stored procedure at the end of code
and another end;
like this
creat or replace procedure Test is

cursor bc is select * from bank;

cursor brc (bn bank.b#%type) is select t#, city from branch where bn =
branch.b#;
cursor cc (cbn bank.b#%type, ctn branch.t#%type) is
select distinct customer.c#, customer.name, customer.status ,
customer.city, account.balance from customer, account
where account.c# = customer.c# and account.b# = cbn and account.t# =
ctn;
begin
dbms_output.put _line
('+============ =============== =============== ====+');
dbms_output.put _line('| Bank# Name City
|');
dbms_output.put _line

cursor bc is select * from bank;

cursor brc (bn bank.b#%type) is select t#, city from branch where bn =
branch.b#;
cursor cc (cbn bank.b#%type, ctn branch.t#%type) is
select distinct customer.c#, customer.name, customer.status ,
customer.city, account.balance from customer, account
where account.c# = customer.c# and account.b# = cbn and account.t# =
ctn;
begin
dbms_output.put _line
('+============ =============== =============== ====+');
dbms_output.put _line('| Bank# Name City
|');
dbms_output.put _line

and remove the declare keyword

>
Ok thanks I'll give it a try...