http://www.oraerp.com/ Wed, 19 Jun 2013 08:58:37 +0000 MyBB http://www.oraerp.com/Thread-SQL-Plus-Commands Thu, 17 Feb 2011 09:21:41 -0600 http://www.oraerp.com/Thread-SQL-Plus-Commands
Enter, edit, store, retrieve, and run SQL statements
List the column definitions for any table
Format, perform calculations on, store, and print query results in the form of reports
Access and copy data between SQL databases
The following is a list of SQL*Plus commands and their functions. The most commonly used commands are emphasized in italics:

/ - Execute the current SQL statement in the buffer - same as RUN ACCEPT - Accept a value from the user and place it into a variable
APPEND - Add text to the end of the current line of the SQL statement in the buffer
AUTOTRACE - Trace the execution plan of the SQL statement and gather statistics
BREAK - Set the formatting behavior for the output of SQL statements
BTITLE - Place a title on the bottom of each page in the printout from a SQL statement
CHANGE - Replace text on the current line of the SQL statement with new text
CLEAR - Clear the buffer
COLUMN - Change the appearance of an output column from a query
COMPUTE - Does calculations on rows returned from a SQL statement
CONNECT - Connect to another Oracle database or to the same Oracle database under a different user name
COPY - Copy data from one table to another in the same or different databases
DEL - Delete the current line in the buffer
DESCRIBE - List the columns with datatypes of a table
EDIT - Edit the current SQL statement in the buffer using an external editor such as vi or emacs
EXIT - Exit the SQL*Plus program
GET - Load a SQL statement into the buffer but do not execute it
HELP - Obtain help for a SQL*Plus command (In some installations)
HOST - Drop to the operating system shell
INPUT - Add one or more lines to the SQL statement in the buffer
LIST - List the current SQL statement in the buffer
QUIT - Exit the SQL*Plus program
REMARK - Place a comment following the REMARK keyword
RUN - Execute the current SQL statement in the buffer
SAVE - Save the current SQL statement to a script file
SET - Set a variable to a new value
SHOW - Show the current value of a variable
SPOOL - Send the output from a SQL statement to a file
START - Load a SQL statement located in a script file and then run that SQL statement
TIMING - Used to time the execution of SQL statements for performance analysis
TTITLE - Place a title on the top of each page in the printout from a SQL statement
UNDEFINE - Delete a user defined variable]]>
Enter, edit, store, retrieve, and run SQL statements
List the column definitions for any table
Format, perform calculations on, store, and print query results in the form of reports
Access and copy data between SQL databases
The following is a list of SQL*Plus commands and their functions. The most commonly used commands are emphasized in italics:

/ - Execute the current SQL statement in the buffer - same as RUN ACCEPT - Accept a value from the user and place it into a variable
APPEND - Add text to the end of the current line of the SQL statement in the buffer
AUTOTRACE - Trace the execution plan of the SQL statement and gather statistics
BREAK - Set the formatting behavior for the output of SQL statements
BTITLE - Place a title on the bottom of each page in the printout from a SQL statement
CHANGE - Replace text on the current line of the SQL statement with new text
CLEAR - Clear the buffer
COLUMN - Change the appearance of an output column from a query
COMPUTE - Does calculations on rows returned from a SQL statement
CONNECT - Connect to another Oracle database or to the same Oracle database under a different user name
COPY - Copy data from one table to another in the same or different databases
DEL - Delete the current line in the buffer
DESCRIBE - List the columns with datatypes of a table
EDIT - Edit the current SQL statement in the buffer using an external editor such as vi or emacs
EXIT - Exit the SQL*Plus program
GET - Load a SQL statement into the buffer but do not execute it
HELP - Obtain help for a SQL*Plus command (In some installations)
HOST - Drop to the operating system shell
INPUT - Add one or more lines to the SQL statement in the buffer
LIST - List the current SQL statement in the buffer
QUIT - Exit the SQL*Plus program
REMARK - Place a comment following the REMARK keyword
RUN - Execute the current SQL statement in the buffer
SAVE - Save the current SQL statement to a script file
SET - Set a variable to a new value
SHOW - Show the current value of a variable
SPOOL - Send the output from a SQL statement to a file
START - Load a SQL statement located in a script file and then run that SQL statement
TIMING - Used to time the execution of SQL statements for performance analysis
TTITLE - Place a title on the top of each page in the printout from a SQL statement
UNDEFINE - Delete a user defined variable]]> http://www.oraerp.com/Thread-Bulk-and-reference-cursor Fri, 24 Dec 2010 08:16:27 -0600 http://www.oraerp.com/Thread-Bulk-and-reference-cursor create or replace PROCEDURE proc_1(t_name varchar2) IS
TYPE parent_rec IS RECORD (part_num dynamic.emp_name%type,part_name dynamic.emp_id%type,part_id dynamic.tel_no%type) ;
p_rec parent_rec;

rec_array SYS_REFCURSOR;

BEGIN
OPEN rec_array FOR 'select EMP_NAME, EMP_ID,TEL_NO FROM '||t_name ||' WHERE EMP_ID = ''1''' ;
LOOP
FETCH rec_array BULK COLLECT INTO p_rec.part_num, p_rec.part_name, p_rec.part_id LIMIT 500;
FORALL i IN p_rec.first..p_rec.last
INSERT INTO dynamic_1(emp_name_1,emp_id_1,tel_no_1) VALUES (p_rec.part_num(i), p_rec.part_name(i),p_rec.part_id(i));

EXIT WHEN rec_array%NOTFOUND;
END LOOP;
COMMIT;
CLOSE rec_array;
END proc_1;]]> create or replace PROCEDURE proc_1(t_name varchar2) IS
TYPE parent_rec IS RECORD (part_num dynamic.emp_name%type,part_name dynamic.emp_id%type,part_id dynamic.tel_no%type) ;
p_rec parent_rec;

rec_array SYS_REFCURSOR;

BEGIN
OPEN rec_array FOR 'select EMP_NAME, EMP_ID,TEL_NO FROM '||t_name ||' WHERE EMP_ID = ''1''' ;
LOOP
FETCH rec_array BULK COLLECT INTO p_rec.part_num, p_rec.part_name, p_rec.part_id LIMIT 500;
FORALL i IN p_rec.first..p_rec.last
INSERT INTO dynamic_1(emp_name_1,emp_id_1,tel_no_1) VALUES (p_rec.part_num(i), p_rec.part_name(i),p_rec.part_id(i));

EXIT WHEN rec_array%NOTFOUND;
END LOOP;
COMMIT;
CLOSE rec_array;
END proc_1;]]> http://www.oraerp.com/Thread-Send-sms Thu, 07 Oct 2010 03:39:44 -0500 http://www.oraerp.com/Thread-Send-sms procedure is as....
Create or Replace Procedure SEND_SMS
( p_sender in varchar2,
p_recipient in varchar2,
p_message in varchar2)
as
mailcon utl_smtp.connection;
begin
mailcon :=utl_smtp.open_connection('mail.thxis..com');
utl_smtp.helo(mailcon,'mail.thxis..com');
utl_smtp.mail(mailcon,p_sender);
utl_smtp.rcpt(mailcon,p_recipient );
utl_smtp.data(mailcon,'From: ' ||p_sender|| utl_tcp.crlf||
'To: ' || p_recipient ||utl_tcp.crlf ||
'Subject: SMS From Database' || utl_tcp.crlf ||
p_message);
utl_smtp.quit(mailcon);
end;


executing as
exec SEND_SMS('test@thxis.com', '9921182989@ideacellular.net','This is my first SMS');

getting error as
ORA-30678: too many open connections
ORA-06512: at "SYS.UTL_TCP", line 17
ORA-06512: at "SYS.UTL_TCP", line 246
ORA-06512: at "SYS.UTL_SMTP", line 115
ORA-06512: at "SYS.UTL_SMTP", line 138
ORA-06512: at "APPS.SEND_SMS", line 8
ORA-06512: at line 1


at which position i get wrong....
by using same process mail going correctly but sms is not going it throwing error]]> procedure is as....
Create or Replace Procedure SEND_SMS
( p_sender in varchar2,
p_recipient in varchar2,
p_message in varchar2)
as
mailcon utl_smtp.connection;
begin
mailcon :=utl_smtp.open_connection('mail.thxis..com');
utl_smtp.helo(mailcon,'mail.thxis..com');
utl_smtp.mail(mailcon,p_sender);
utl_smtp.rcpt(mailcon,p_recipient );
utl_smtp.data(mailcon,'From: ' ||p_sender|| utl_tcp.crlf||
'To: ' || p_recipient ||utl_tcp.crlf ||
'Subject: SMS From Database' || utl_tcp.crlf ||
p_message);
utl_smtp.quit(mailcon);
end;


executing as
exec SEND_SMS('test@thxis.com', '9921182989@ideacellular.net','This is my first SMS');

getting error as
ORA-30678: too many open connections
ORA-06512: at "SYS.UTL_TCP", line 17
ORA-06512: at "SYS.UTL_TCP", line 246
ORA-06512: at "SYS.UTL_SMTP", line 115
ORA-06512: at "SYS.UTL_SMTP", line 138
ORA-06512: at "APPS.SEND_SMS", line 8
ORA-06512: at line 1


at which position i get wrong....
by using same process mail going correctly but sms is not going it throwing error]]>